分析函式視窗子句 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)後，組內的子分組（也稱視窗），

是分析函式的計算範圍視窗，而不是PARTITION；

視窗有兩種，ROWS 和 RANGE

下面分析 ROWS 與 RANGE 視窗子句的用法，先看下面例子：

從上面的例子可知：

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(視窗)子句。

----------------------------------------------------------------------------

一、 ROWS 的具體用法

SET LINESIZE 200;
SET PAGESIZE 1000;

SELECT
  dept_id,hire_date,salary,
--視窗範圍為該分割槽的第一行和該分割槽的最後一行
  CAST(SUM(salary) OVER(PARTITION BY dept_id ORDER BY hire_date ROWS BETWEEN UNBOUNDED PRECEDING 

                               AND UNBOUNDED FOLLOWING) AS varchar2(6)) SUM_1,
--視窗範圍為該分割槽的第一行和當前行
  CAST(SUM(salary) OVER(PARTITION BY dept_id ORDER BY hire_date ROWS BETWEEN UNBOUNDED PRECEDING 

                               AND CURRENT ROW) AS varchar2(6)) SUM_2,
--視窗範圍為該分割槽的第一行和當前行的前一行，統計的是第一行和當前行的前一行的薪資累計
  CAST(SUM(salary) OVER(PARTITION BY dept_id ORDER BY hire_date ROWS BETWEEN UNBOUNDED PRECEDING 

                               AND 1/*value_expr*/ PRECEDING) AS varchar2(6)) SUM_3,
--視窗範圍為該分割槽的第一行和當前行的後一行，統計的是第一行和當前行的後一行的薪資累計
  CAST(SUM(salary) OVER(PARTITION BY dept_id ORDER BY hire_date ROWS BETWEEN UNBOUNDED PRECEDING 

                               AND 1/*value_expr*/ FOLLOWING) AS varchar2(6)) SUM_4,
--視窗範圍為當前行和該分割槽的最後一行，統計的是大於等於當前記錄hire_date之後的所有薪資
  CAST(SUM(salary) OVER(PARTITION BY dept_id ORDER BY hire_date ROWS BETWEEN CURRENT ROW 

                               AND UNBOUNDED FOLLOWING)AS varchar2(6)) SUM_5,
--視窗範圍只是當前行，所以與當前行薪資一樣
  CAST(SUM(salary) OVER(PARTITION BY dept_id ORDER BY hire_date ROWS BETWEEN CURRENT ROW 

                               AND CURRENT ROW) AS varchar2(6)) SUM_6,
--視窗範圍為當前行和當前行的後一行，統計的是當前行和後一行的薪資累計
  CAST(SUM(salary) OVER(PARTITION BY dept_id ORDER BY hire_date ROWS BETWEEN CURRENT ROW 

                               AND 1/*value_expr*/ FOLLOWING) AS varchar2(6)) SUM_7,
--視窗範圍為當前行的前一行和該分割槽的最後一行，統計的是當前行的前一行和該分割槽最後一行的薪資累計 
  CAST(SUM(salary) OVER(PARTITION BY dept_id ORDER BY hire_date ROWS BETWEEN 1/*value_expr*/ PRECEDING 

                               AND UNBOUNDED FOLLOWING) AS varchar2(6))SUM_8,
--視窗範圍為當前行的前一行和當前行，統計的是當前行的前一行和當前行的薪資累計 
  CAST(SUM(salary) OVER(PARTITION BY dept_id ORDER BY hire_date ROWS BETWEEN 1/*value_expr*/ PRECEDING 

                               AND CURRENT ROW)AS varchar2(6)) SUM_9,
--視窗範圍為該分割槽的當前行前value_expr1到當前行前value_expr2的累計，本例為本行前2行和前1行的累計
--強調value_expr1 >= value_expr2
  CAST(SUM(salary) OVER(PARTITION BY dept_id ORDER BY hire_date ROWS BETWEEN 2/*value_expr1*/ PRECEDING 

                               AND 1/*value_expr2*/ PRECEDING) AS varchar2(6)) SUM_10,
--視窗範圍為該分割槽的本行前value_expr1到本行後value_expr2的累計，本例為本行前1行和後2行的累計  
  CAST(SUM(salary) OVER(PARTITION BY dept_id ORDER BY hire_date ROWS BETWEEN 1/*value_expr1*/ PRECEDING 

                               AND 2/*value_expr2*/ FOLLOWING) AS varchar2(6)) SUM_11,
--視窗範圍為本行後一行和本分割槽最後一行，統計的是本行後一行和本區最後一行的薪資累計  
  CAST(SUM(salary) OVER(PARTITION BY dept_id ORDER BY hire_date ROWS BETWEEN 1/*value_expr*/ FOLLOWING 

                               AND UNBOUNDED FOLLOWING) AS varchar2(6)) SUM_12,
--視窗範圍為該分割槽的本行後value_expr1行到本行後value_expr2的累計，本例為本行後1行到後2行的累計
--強調value_expr1 <= value_expr2  
  CAST(SUM(salary) OVER(PARTITION BY dept_id ORDER BY hire_date ROWS BETWEEN 1/*value_expr1*/ FOLLOWING 

                               AND 2/*value_expr2*/ FOLLOWING) as varchar2(6))SUM_13,
--視窗範圍為該分割槽的第一行，結束行預設為本行，同sum_2  
  CAST(SUM(salary) OVER(PARTITION BY dept_id ORDER BY hire_date ROWS UNBOUNDED PRECEDING) 

                               AS varchar2(6))  SUM_14,
--視窗範圍僅為當前行，所以與本行薪資一樣，同sum_6
  CAST(SUM(salary) OVER(PARTITION BY dept_id ORDER BY hire_date ROWS CURRENT ROW) 

                               AS varchar2(6)) SUM_15,
--視窗範圍為該分割槽的第一行和本行前一行，統計的是本行前一行和本行的薪資累計  
  CAST(SUM(salary) OVER(PARTITION BY dept_id ORDER BY hire_date ROWS 1/*value_expr*/ PRECEDING) 

                               AS varchar2(6)) SUM_16
FROM employeeinfo;

----------------------------------------------------------------------------

二、 RANGE 的具體用法

SELECT 
  dept_id,hire_date,salary,
--視窗範圍為該分割槽的第一行和該分割槽的最後一行，在非條件表示式中等同於ROWS
  CAST(SUM(salary) OVER(PARTITION BY dept_id ORDER BY hire_date RANGE BETWEEN UNBOUNDED PRECEDING 

                                     AND UNBOUNDED FOLLOWING)AS varchar2(6)) SUM_1,
--視窗範圍為該分割槽的第一行和本行，在非條件表示式中等同於ROWS
  CAST(SUM(salary) OVER(PARTITION BY dept_id ORDER BY hire_date RANGE BETWEEN UNBOUNDED PRECEDING 

                                     AND CURRENT ROW) AS varchar2(6)) SUM_2,
--視窗範圍為該分割槽內小於本記錄hire_date-365天的所有的薪資累計
  CAST(SUM(salary) OVER(PARTITION BY dept_id ORDER BY hire_date RANGE BETWEEN UNBOUNDED PRECEDING 

                                     AND 365/*value_expr*/ PRECEDING) AS varchar2(6)) SUM_3,
--視窗範圍為該分割槽內小於本記錄hire_date+365天的所有的薪資累計
  CAST(SUM(salary) OVER(PARTITION BY dept_id ORDER BY hire_date RANGE BETWEEN UNBOUNDED PRECEDING 

                                     AND 365/*value_expr*/ FOLLOWING) AS varchar2(6)) SUM_4, 
--視窗範圍為本行和該分割槽的最後一行，統計的是大於等於本記錄hire_date之後的所有薪資，在非條件表示式中等同於ROWS
  CAST(SUM(salary) OVER(PARTITION BY dept_id ORDER BY hire_date RANGE BETWEEN CURRENT ROW 

                                     AND UNBOUNDED FOLLOWING) AS varchar2(6)) SUM_5,
--視窗範圍只是本行，所以與本行薪資一樣
  CAST(SUM(salary) OVER(PARTITION BY dept_id ORDER BY hire_date RANGE BETWEEN CURRENT ROW 

                                     AND CURRENT ROW) AS varchar2(6)) SUM_6,
--視窗範圍為該分割槽內本記錄起和小於本記錄hire_date-365天的所有的薪資累計
  CAST(SUM(salary) OVER(PARTITION BY dept_id ORDER BY hire_date RANGE BETWEEN CURRENT ROW 

                                     AND 365/*value_expr*/ FOLLOWING) AS varchar2(6)) SUM_7,
--視窗範圍為該分割槽內本記錄起和小於本記錄hire_date+365天的所有的薪資累計
  CAST(SUM(salary) OVER(PARTITION BY dept_id ORDER BY hire_date RANGE BETWEEN 365/*value_expr*/ PRECEDING 

                                     AND CURRENT ROW) AS varchar2(6)) SUM_8,
--視窗範圍為該分割槽內大於本記錄hire_date-365天，並且截止到當前記錄的所有的薪資累計
  CAST(SUM(salary) OVER(PARTITION BY dept_id ORDER BY hire_date RANGE BETWEEN 365/*value_expr*/ PRECEDING 

                                     AND CURRENT ROW) AS varchar2(6)) SUM_9,
--視窗範圍為該分割槽的本行current_value-value_expr1到本行current_value-value_expr2之間的累計

--強調value_expr1>=value_expr2
  CAST(SUM(salary) OVER(PARTITION BY dept_id ORDER BY hire_date RANGE BETWEEN 365/*value_expr1*/ PRECEDING 

                                     AND 30/*value_expr2*/ PRECEDING) AS varchar2(6)) SUM_10, 
--視窗範圍為該分割槽的本行current_value-value_expr1到本行current_value+value_expr2之間的累計
  CAST(SUM(salary) OVER(PARTITION BY dept_id ORDER BY hire_date RANGE BETWEEN 365/*value_expr1*/ PRECEDING 

                                     AND 30/*value_expr2*/ FOLLOWING) AS varchar2(6)) SUM_11,
--視窗範圍為該分割槽內大於本記錄hire_date+365天的所有的薪資累計
  CAST(SUM(salary) OVER(PARTITION BY dept_id ORDER BY hire_date RANGE BETWEEN 365/*value_expr*/ FOLLOWING 

                                     AND UNBOUNDED FOLLOWING) AS varchar2(6)) SUM_12,
--視窗範圍為該分割槽的本行current_value+value_expr1到本行current_value+value_expr2的累計

--強調value_expr1<=value_expr2
  CAST(SUM(salary) OVER(PARTITION BY dept_id ORDER BY hire_date RANGE BETWEEN 30/*value_expr1*/ FOLLOWING 

                                     AND 365/*value_expr2*/ FOLLOWING)AS varchar2(6)) SUM_13,
--視窗範圍為該分割槽的第一行，結束行預設為本行，與sum_salary_part_order，sum_2等同，在非條件表示式中等同於ROWS
  CAST(SUM(salary) OVER(PARTITION BY dept_id ORDER BY hire_date RANGE UNBOUNDED PRECEDING) 

                                     AS varchar2(6)) SUM_14,
--視窗範圍為僅為當前行，所有與本行薪資一樣，與sum_6一樣，在非條件表示式的中等同於ROWS
  CAST(SUM(salary) OVER(PARTITION BY dept_id ORDER BY hire_date RANGE CURRENT ROW) 

                                     AS varchar2(6)) SUM_15,
--視窗範圍為該分割槽內大於本記錄hire_date-365天，並且截止到當前記錄的所有的薪資累計，與sum_9等價
  CAST(SUM(salary) OVER(PARTITION BY dept_id ORDER BY hire_date RANGE 365/*value_expr*/ PRECEDING) 

                                     AS varchar2(6)) SUM_16

FROM employeeinfo;

附：另一種寫法

--統計當天及前30天的薪資累計
  CAST(SUM(salary) OVER(PARTTION BY dept_id ORDER BY hire_date RANGE INTERVAL '30' day PRECEDING) 

                               AS varchar2(6)) SUM_17,
--統計當天及後30天的薪資累計   
  CAST(SUM(salary) OVER(PARTTION BY dept_id ORDER BY hire_date RANGE INTERVAL '30' day FOLLOWING) 

                               AS varchar2(6)) SUM_18

 

本文使用的指令碼在 http://pan.baidu.com/s/1GpFH0