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

pwz1688發表於2014-01-09
函式
分析函式的語法結構一般是：分析函式名(引數) 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視窗子句的用法，先看下面例子：

點選(此處)摺疊或開啟

  1. SQL> edit
  2. 已寫入 file afiedt.buf

  4.   1 WITH t AS
  5.   2 (SELECT (CASE
  6.   3 WHEN LEVEL IN (1, 2) THEN
  7.   4 1
  8.   5 WHEN LEVEL IN (4, 5) THEN
  9.   6 6
  10.   7 ELSE
  11.   8 LEVEL
  12.   9 END) ID
  13.  10 FROM dual
  14.  11 CONNECT BY LEVEL < 10)
  15.  12 SELECT id,
  16.  13 SUM(ID) over(ORDER BY ID) default_sum,
  17.  14 SUM(ID) over(ORDER BY ID RANGE BETWEEN unbounded preceding AND CURRENT ROW) range_unbound_sum,
  18.  15 SUM(ID) over(ORDER BY ID ROWS BETWEEN unbounded preceding AND CURRENT ROW) rows_unbound_sum,
  19.  16 SUM(ID) over(ORDER BY ID RANGE BETWEEN 1 preceding AND 2 following) range_sum,
  20.  17 SUM(ID) over(ORDER BY ID ROWS BETWEEN 1 preceding AND 2 following) rows_sum
  21.  18* FROM t
  22. SQL> /

  24.         ID DEFAULT_SUM RANGE_UNBOUND_SUM ROWS_UNBOUND_SUM RANGE_SUM ROWS_SUM
  25. ---------- ----------- ----------------- ---------------- ---------- ----------

  27.          1 2           2                 1                5         5
  28.          1 2           2                 2                5         11
  29.          3 5           5                 5                3         16
  30.          6 23          23                11               33        21
  31.          6 23          23                17               33        25
  32.          6 23          23                23               33        27
  33.          7 30          30                30               42        30
  34.          8 38          38                38               24        24
  35.          9 47          47                47               17        17

  37. 已選擇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/，如需轉載，請註明出處，否則將追究法律責任。

相關文章