[Oracle] 分析函数（2）- 开窗（WINDOWING）

Oracle分析函数的开窗部分分为3个类型，分别是rows, range, keep。

ROWS窗口

rows 是关键字，指定窗口由物理行构成，即物理位置排数。

between…and是关键字，用来指定窗口的起始点和终结点；
Unbounded preceding指明窗口开始于分组的第一行；
Current row，作为起始点，指明窗口开始于当前行或当前值；作为终结点，指明窗口结束于当前行或当前值；
Unbounded following指明窗口结束于分组的最后一行；
Value_expr为物理或逻辑偏移量表达式。


Windowing_clause用来指定分组中当前行的计算范围。
不论rows还是range窗口，窗口总是在分组中从上至下滑动的。
窗口范围可以由between…and限定，也可以不用between…and，不用的都表示窗口到当前行结束。

ROWS窗口，是由分组排序后分组中若干连续的行构成的窗口。
以下是全部合法的ROWS窗口范围:
1)rows between unbounded preceding and unbounded following
窗口开始于分组第一行，结束于分组最后一行。
2)rows [between] unbounded preceding [and current row]
窗口开始于分组第一行，结束于当前行。
3)rows between unbounded preceding and value_expr preceding
窗口开始于分组第一行，结束于当前行前value_expr行。
4)rows between unbounded preceding and value_expr following
窗口开始于分组第一行，结束于当前行后value_expr行。
5)rows between current row and unbounded following
窗口开始于当前行，结束于分组最后一行。
6)rows [between current row and] current row
窗口开始于当前行，结束于当前行。
7)rows between current row and value_expr following
窗口开始于当前行，结束于当前行后value_expr行。
8)rows between value_expr preceding and unbounded following
窗口开始于当前行前value_expr行，结束于分组最后一行。
9)rows [between value_expr] preceding [and current row]
窗口开始于当前行前value_expr行，结束于当前行。
10)rows between value_expr1 preceding and value_expr2 preceding
窗口开始于当前行前value_expr1行，结束于当前行前value_expr2行。这里一定要满足value_expr1>=value_expr2。
11)rows between value_expr1 preceding and value_expr2 following
窗口开始于当前行前value_expr1行，结束于当前行后value_expr2行。
12)rows between value_expr following and unbounded following
窗口开始于当前行后value_expr行，结束于分组最后一行。
13)rows between value_expr1 following and value_expr2 following
窗口开始于当前行后value_expr1行，结束于当前行后value_expr2行。这里一定要满足value_expr1<=value_expr2
14)rows unbounded preceding
与2等价。
15)rows current row
与6等价。
16)rows value_expr preceding
与9等价。

下面看几个例子：

SELECT 
 emp_id,ename,dept_id,hire_date,sal, 
-- 以下均为首先按dept_id进行分组，其次按照hire_date进行排序，且所有统计不能跨越其所在分区，故不再重复
-- 窗口范围为该分区的第一行到该分区的最后一行，与sum_sal_part等同
 SUM(sal) OVER (PARTITION BY dept_id ORDER BY hire_date   ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) sum_1_to_last,
-- 窗口范围为该分区的第一行到本行，与sum_sal_part_order等同
 SUM(sal) OVER (PARTITION BY dept_id ORDER BY hire_date   ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) sum_1_to_cur,
-- 窗口范围为该分区的第一行到本行前一行，统计的是第一行到本行前一行薪资的累计
 SUM(sal) OVER (PARTITION BY dept_id ORDER BY hire_date   ROWS BETWEEN UNBOUNDED PRECEDING AND 1/*value_expr*/ PRECEDING) sum_1_to_curbef1,
 -- 窗口范围为该分区的第一行到本行后一行，统计的是第一行到本行后一行薪资的累计
 SUM(sal) OVER (PARTITION BY dept_id ORDER BY hire_date   ROWS BETWEEN UNBOUNDED PRECEDING AND 1 FOLLOWING) sum_1_to_curaft1
FROM emp order by dept_id,hire_date;  
  
   
EMP_ID ENAME DEPT_ID HIRE_DATE        SAL SUM_1_TO_LAST SUM_1_TO_CUR SUM_1_TO_CURBEF1 SUM_1_TO_CURAFT1
------ ----- ------- -------------- ----- ------------- ------------ ---------------- ----------------
   100 Stev       10 01-1月 -90      7000          7000         7000                              7000
   101 Tom        20 21-9月 -89      2000         10000         2000                             10000              解说SUM1_TO_CUR(dept_id=50部分）                                                               解说SUM1_TO_CURaft1 (dept_id=50部分）                                                      
   102 Mike       20 13-1月 -93      8000         10000        10000             2000            10000                                                                                                                                                                             
   122 Rich       50 01-5月 -95      3000         19000         3000                              4000   ---从第1行hiredates顺序到当前行(也就是到第1行），多少？该3000就3000                       ---从第1行hiredates顺序到当前行后1行(也就是到第2行），多少？300+1000                     
   120 John       50 18-7月 -96      1000         19000         4000             3000             8000   ---比第1行hiredates顺序到当前行(也就是到第2行），多少？3000+1000=4000                     ---比第1行hiredates顺序到当前行后1行(也就是到第3行），多少？3000+1000+4000           
   121 Joy        50 10-4月 -97      4000         19000         8000             4000            13000   ---比第1行hiredates顺序到当前行(也就是到第3行），多少？3000+1000+4000                     ---比第1行hiredates顺序到当前行后1行(也就是到第4行），多少？3000+1000+4000+5000           
   123 Kate       50 10-10月-97      5000         19000        13000             8000            19000   ---比第1行hiredates顺序到当前行(也就是到第4行），多少？3000+1000+4000+5000                ---比第1行hiredates顺序到当前行后1行(也就是到第5行），多少？3000+1000+4000+5000+6000      
   124 Jess       50 16-11月-99      6000         19000        19000            13000            19000   ---比第1行hiredates顺序到当前行(也就是到第4行），多少？3000+1000+4000+5000+6000           ---比第1行hiredates顺序到当前行后1行(也就是到第6行，第6行没记录了，那结果和上一次一样）

SELEC