分析函式用法及視窗子句 range/rows差別

分析函式的語法結構一般是：分析函式名(引數) OVER (PARTITION BY子句 ORDER BY子句 ROWS/RANGE子句)。
即由以下三部分組成：
分析函式名：如sum、max、min、count、avg等聚集函式以及lead、lag行比較函式等；
over：關鍵字，表示前面的函式是分析函式，不是普通的集合函式；
分析子句：over關鍵字後面掛號內的內容；

分析子句又由下面三部分組成：
partition by ：分組子句，表示分析函式的計算範圍，不同的組互不相干；
ORDER BY：排序子句，表示分組後，組內的排序方式；
ROWS/RANGE：視窗子句，是在分組(PARTITION BY)後，組內的子分組（也稱視窗），此時分析函式的計算範圍視窗，而不是PARTITON。視窗有兩種，ROWS和RANGE；
下面分析rows與range視窗子句的用法，先看下面例子：

點選(此處)摺疊或開啟

SQL> edit
已寫入 file afiedt.buf
1 WITH t AS
2 (SELECT (CASE
3 WHEN LEVEL IN (1, 2) THEN
4 1
5 WHEN LEVEL IN (4, 5) THEN
6 6
7 ELSE
8 LEVEL
9 END) ID
10 FROM dual
11 CONNECT BY LEVEL < 10)
12 SELECT id,
13 SUM(ID) over(ORDER BY ID) default_sum,
14 SUM(ID) over(ORDER BY ID RANGE BETWEEN unbounded preceding AND CURRENT ROW) range_unbound_sum,
15 SUM(ID) over(ORDER BY ID ROWS BETWEEN unbounded preceding AND CURRENT ROW) rows_unbound_sum,
16 SUM(ID) over(ORDER BY ID RANGE BETWEEN 1 preceding AND 2 following) range_sum,
17 SUM(ID) over(ORDER BY ID ROWS BETWEEN 1 preceding AND 2 following) rows_sum
18* FROM t
SQL> /
ID DEFAULT_SUM RANGE_UNBOUND_SUM ROWS_UNBOUND_SUM RANGE_SUM ROWS_SUM
---------- ----------- ----------------- ---------------- ---------- ----------
1 2 2 1 5 5
1 2 2 2 5 11
3 5 5 5 3 16
6 23 23 11 33 21
6 23 23 17 33 25
6 23 23 23 33 27
7 30 30 30 42 30
8 38 38 38 24 24
9 47 47 47 17 17
已選擇9行。

從上面的例子可知：
1、視窗子句必須和order by 子句同時使用，且如果指定了order by 子句未指定視窗子句，則預設為RANGE BETWEEN unbounded preceding AND CURRENT ROW，如上例結果集中的defult_sum等於range_unbound_sum；
2、如果分析函式沒有指定ORDER BY子句，也就不存在ROWS/RANGE視窗的計算；
3、range是邏輯窗口，是指定當前行對應值的範圍取值，列數不固定，只要行值在範圍內，對應列都包含在內，如上例中range_sum（即range 1 preceing and 2 following）例的分析結果：
當id=1時，是sum為1-1<=id<=1+2 的和，即sum=1+1+3=5(取id為1,1,3);
當id=3時，是sum為3-1<=id<=3+2 的和，即sum=3（取id為3）；
當id=6時，是sum為6-1<=id<=6+2 的和，即sum=6+6+6+7+8=33（取id為6，6，6，7，8）;
以此類推下去，結果如上例中所示。
4、rows是物理視窗，即根據order by 子句排序後，取的前N行及後N行的資料計算（與當前行的值無關，只與排序後的行號相關），如上例中rows_sum例結果，是取前1行和後2行資料的求和，分析上例rows_sum的結果：
當id=1（第一個1時）時，前一行沒數，後二行分別是1和3，sum=1+1+3=5；
當id=3時，前一行id=1,後二行id都為6，則sum=1+3+6+6=16；
以此類推下去，結果如上例所示。
注：行比較分析函式lead和lag無window(視窗)子句。

來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/21251711/viewspace-1068855/，如需轉載，請註明出處，否則將追究法律責任。

分析函式用法及視窗子句 range/rows差別

相關文章