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