[Oracle] 分析函数（二）- 开窗（WINDOWING）
[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行没记录了，那结果和上一次一样） SELECT emp_id,ename,dept_id,hire_date,sal, -- 窗口范围为本行和该分区的最后一行，统计的是大于等于本记录hire_date之后的所有薪资 SUM(sal) OVER (PARTITION BY dept_id ORDER BY hire_date ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING) sum_cur_to_last, -- 窗口范围只是本行，所以与本行薪资一样 SUM(sal) OVER (PARTITION BY dept_id ORDER BY hire_date ROWS BETWEEN CURRENT ROW AND CURRENT ROW) sum_cur, -- 窗口范围为本行到本行的后一行，统计的本行到后一行的薪资累计 SUM(sal) OVER (PARTITION BY dept_id ORDER BY hire_date ROWS BETWEEN CURRENT ROW AND 1/*value_expr*/ FOLLOWING) sum_cur_to_aft1, -- 窗口范围为本行和本行的后一行，统计的本行前一行到本分区最后一行的薪资累计，如本行为分区首行，则直接从本行开始算起 SUM(sal) OVER (PARTITION BY dept_id ORDER BY hire_date ROWS BETWEEN 1/*value_expr*/ PRECEDING AND UNBOUNDED FOLLOWING) sum_curbef1_to_last FROM emp order by dept_id,hire_date; EMP_ID ENAME DEPT_ID HIRE_DATE SAL SUM_CUR_TO_LAST SUM_CUR SUM_CUR_TO_AFT1 SUM_CURBEF1_TO_LAST ------ ----- ------- -------------- ----- --------------- ---------- --------------- ------------------- 100 Stev 10 01-1月 -90 7000 7000 7000 7000 7000 101 Tom 20 21-9月 -89 2000 10000 2000 10000 10000 解说SUM_CUR_TO_AFT1(dept_id=50部分） 102 Mike 20 13-1月 -93 8000 8000 8000 8000 10000 122 Rich 50 01-5月 -95 3000 19000 3000 4000 19000 ---从第1行hiredates顺序到当前行(也就是第1行)到下1行，多少？3000+1000 120 John 50 18-7月 -96 1000 16000 1000 5000 19000 ---比第1行hiredates顺序到当前行(也就是第2行）到下1行，多少？1000+4000 121 Joy 50 10-4月 -97 4000 15000 4000 9000 16000 ---比第1行hiredates顺序到当前行(也就是第3行）到下1行，多少？4000+5000 123 Kate 50 10-10月-97 5000 11000 5000 11000 15000 ---比第1行hiredates顺序到当前行(也就是第4行）到下1行，多少？5000+6000 124 Jess 50 16-11月-99 6000 6000 6000 6000 11000 ---比第1行hiredates顺序到当前行(也就是第5行）到下1行，多少？下行没了，那就是6000 SELECT emp_id,ename,dept_id,hire_date,sal, -- 窗口范围为该分区的本行和本行前一行，统计的是当本行和本行的薪资累计 SUM(sal) OVER (PARTITION BY dept_id ORDER BY hire_date ROWS BETWEEN 1/*value_expr*/ PRECEDING AND CURRENT ROW) sum_cur_to_bef1, -- 窗口范围为该分区的本行前value_expr1到本行前value_expr2的累计，本例为本行前2行和前1行的累计，强调value_expr1>=value_expr2 SUM(sal) OVER (PARTITION BY dept_id ORDER BY hire_date ROWS BETWEEN 2/*value_expr1*/ PRECEDING AND 1/*value_expr2*/ PRECEDING) sum_curbef2_to_bef1, -- 窗口范围为该分区的本行前value_expr1到本行后value_expr2的累计，本例为本行前1行到后2行的累计 SUM(sal) OVER (PARTITION BY dept_id ORDER BY hire_date ROWS BETWEEN 1/*value_expr1*/ PRECEDING AND 2/*value_expr2*/ FOLLOWING) sum_curbef1_to_aft2, -- 窗口范围为该分区的本行后一行和本区最后一行，统计的是本行后一行和本区最后一行的薪资累计 SUM(sal) OVER (PARTITION BY dept_id ORDER BY hire_date ROWS BETWEEN 1/*value_expr*/ FOLLOWING AND UNBOUNDED FOLLOWING) sum_curaft1_to_last FROM emp order by dept_id,hire_date; EMP_ID ENAME DEPT_ID HIRE_DATE SAL SUM_CUR_TO_BEF1 SUM_CURBEF2_TO_BEF1 SUM_CURBEF1_TO_AFT2 SUM_CURAFT1_TO_LAST ------ ----- ------- -------------- ----- --------------- ------------------- ------------------- ------------------- 100 Stev 10 01-1月 -90 7000 7000 7000 101 Tom 20 21-9月 -89 2000 2000 10000 8000 解说SUM_CURBEF2_TO_BEF1(dept_id=50部分） 102 Mike 20 13-1月 -93 8000 10000 2000 10000 122 Rich 50 01-5月 -95 3000 3000 8000 16000 ---从第1行hiredate顺序到当前行(也就是第1行),往前2位，没数字，往前1位，依然没数字！ 120 John 50 18-7月 -96 1000 4000 3000 13000 15000 ---比第1行hiredate顺序到当前行(也就是第2行）往前2位，没数字，往前1位，3000 121 Joy 50 10-4月 -97 4000 5000 4000 16000 11000 ---比第1行hiredate顺序到当前行(也就是第3行）往前2位，3000，往前1位，1000，是3000+1000 123 Kate 50 10-10月-97 5000 9000 5000 15000 6000 ---比第1行hiredate顺序到当前行(也就是第4行）往前2位，1000，往前1位，4000，是1000+4000 124 Jess 50 16-11月-99 6000 11000 9000 11000 ---比第1行hiredate顺序到当前行(也就是第5行）往前2位，4000，往前1位，5000，是4000+5000 SELECT emp_id,ename,dept_id,hire_date,sal, -- 窗口范围为该分区的本行后value_expr1到本行后value_expr2的累计，本例为本行后1行和后2行的累计，强调value_expr1<=value_expr2 SUM(sal) OVER (PARTITION BY dept_id ORDER BY hire_date ROWS BETWEEN 1/*value_expr1*/ FOLLOWING AND 2/*value_expr2*/ FOLLOWING) sum_curaft1_to_after2, -- 窗口范围为该分区的第一行,结束行缺省为本行，与之前出现的sum_sal_part_order，sum_1_to_cur等同 SUM(sal) OVER (PARTITION BY dept_id ORDER BY hire_date ROWS UNBOUNDED PRECEDING) sum_1_to_cur, -- 窗口范围仅为当前行，所以与本行薪资一样，与之前出现的sum_cur一样 SUM(sal) OVER (PARTITION BY dept_id ORDER BY hire_date ROWS CURRENT ROW) sum_cur, -- 窗口范围为该分区的本行和本行前一行，统计的是本行前一行和本行的薪资累计,与之前出现的sum_cur一样 SUM(sal) OVER (PARTITION BY dept_id ORDER BY hire_date ROWS 1/*value_expr*/ PRECEDING) sum_cur_to_bef1 FROM emp order by dept_id,hire_date; EMP_ID ENAME DEPT_ID HIRE_DATE SAL SUM_CURAFT1_TO_AFTER2 SUM_1_TO_CUR SUM_CUR SUM_CUR_TO_BEF1 ------ ----- ------- -------------- ----- --------------------- ------------ ---------- --------------- 100 Stev 10 01-1月 -90 7000 7000 7000 7000 101 Tom 20 21-9月 -89 2000 8000 2000 2000 2000 解说SUM_CURAFTER1_TO_AFTER1(dept_id=50部分） 102 Mike 20 13-1月 -93 8000 10000 8000 10000 122 Rich 50 01-5月 -95 3000 5000 3000 3000 3000 ---从第1行hiredate顺序到当前行(也就是第1行),往后1位，1000，后2位，4000，是1000+4000 120 John 50 18-7月 -96 1000 9000 4000 1000 4000 ---从第1行hiredate顺序到当前行(也就是第2行),往后1位，4000，后2位，5000，是4000+5000 121 Joy 50 10-4月 -97 4000 11000 8000 4000 5000 ---从第1行hiredate顺序到当前行(也就是第3行),往后1位，5000，后2位，6000，是5000+6000 123 Kate 50 10-10月-97 5000 6000 13000 5000 9000 ---从第1行hiredate顺序到当前行(也就是第4行),往后1位，6000，后2位，没了 124 Jess 50 16-11月-99 6000 19000 6000 11000 ---从第1行hiredate顺序到当前行(也就是第5行),往后1位，没了，后2位，更没了！
RANGE窗口
range 是关键字，指定窗口由逻辑偏移量构成，即符合指定的逻辑条件的范围。 between…and是关键字，用来指定窗口的起始点和终结点； Unbounded preceding指明窗口开始于分组的第一行； Current row，作为起始点，指明窗口开始于当前行或当前值；作为终结点，指明窗口结束于当前行或当前值； Unbounded following指明窗口结束于分组的最后一行； Value_expr为物理或逻辑偏移量表达式。 RANGE窗口，相当于给order_by_clause中的expr加一个where限定条件，分组中满足条件 (当order by expr asc时，where expr between a and b 当order by expr desc时 where expr between b and a)的所有行构成一个逻辑窗口。 其中a由分组中第ra行的值计算而来，b由分组中第rb行的值计算而来，且ra<=rb。 根据range中是否包含unbounded，可以分为两类，含unbounded range窗口，和不含unbounded range窗口。 对于前者，有ra<=rb。当order by expr asc时，a<=b，须使用where expr between a and b；当order by expr desc时，a>b，须使用where expr between b and a。 对于后者，有ra=rb。总有a<=b，使用where expr between a and b。 对于在order_by_clause中可以使用多个expr的窗口： Range between unbounded preceding and current row Range between current row and unbounded following 当它们使用多个expr排序时(注意不是一个)分别等价于： Rows between unbounded preceding and current row Rows between current row and unbounded following 假设分组第一行的值为first_value，最后一行的值为last_value。当前行的值为current_value。 1)range between unbounded preceding and unbounded following 按升序排序的时候，表达式介于第一个值和最后一个值之间，或者 按降序排序的时候，表达式介于最后一个值和第一个值之间 2)range [between] unbounded preceding [and current row] 表达式介于第一个值与当前行的值之间，或者 表达式介于当前行的值和第一个值之间 3)range between unbounded preceding and value_expr preceding 表达式介于第一个值与当前行的值-value_expr之间，或者 表达式介于当前行的值-value_expr与第一个值之间 4)range between unbounded preceding and value_expr following 表达式介于第一个值与当前行的值+value_expr之间，或者 表达式介于当前行的值+value_expr与第一个值之间 5)range between current row and unbounded following 表达式介于当前行的值和最后一个值之间，或者 表达式介于最后一个值和当前行的值之间 6)range [between current row and] current row 表达式等于当前行的值 7)range between current row and value_expr following 表达式介于当前行的值和当前行的值+value_expr之间 8)range between value_expr preceding and unbounded following 表达式介于当前行的值-value_expr和最后一个值之间 9)range [between value_expr] preceding [and current row] 表达式介于当前行的值-value_expr和当前行的值之间 10)range between value_expr1 preceding and value_expr2 preceding 这里一定要满足value_expr1>=value_expr2。 然后表达式介于当前行的值-value_expr1和当前行的值-value_expr2之间 11)range between value_expr1 preceding and value_expr2 following 表达式介于当前行的值-value_expr1和当前行的值+value_expr2之间 12)range between value_expr following and unbounded following 表达式介于当前行的值+value_expr和最后一个值之间 表达式介于最后一个值和当前行的值+value_expr之间 13)range between value_expr1 following and value_expr2 following 这里一定要满足value_expr1<=value_expr2。 然后表达式介于当前行的值+value_expr1和当前行的值+value_expr2之间 14)range unbounded preceding 与2等价。 15)range current row 与6等价。 16)range value_expr preceding 与9等价。 注意事项： 1.若windowing_clause由rows指定，则： (1)value_expr是物理偏移量，它必须是常量或值为非负数的表达式。 (2)若value_expr是起点的一部分，那么它必须在终点之前对行求值。 2.若windowing_clause由range指定，则: (1)value_expr是逻辑偏移量。它必须是常量或值为非负的表达式或时间间隔文字常量。 (2)value_expr值为一个数字，那么order_by_clause中 expr必须为数字或date类型。 (3)value_expr为一个间隔值，那么order_by_clause中expr必须是一个date类型。 3.若完全忽略windowing_clause，那么默认的窗口范围为 range between unbounded preceding and current row。 关于ROWS、RANGE中的条件组合加起来达到32种，但实际上不过是几个关键字的排列组合而已，只要了解几个关键字的含义，在应用时加以灵活使用即可。RANGE的例子：
SELECT
emp_id,ename,dept_id,hire_date,sal,
-- 后面均为以dept_id分组，再按hire_date排序，且所有统计不能跨分区，由于是逻辑范围，因此PRECEDING和FOLLOWING表达式有符号
-- 窗口范围为该分区的第一行到该分区的最后一行，与sum_sal_part等同,在非条件表达式中等同于ROWS
SUM(sal) OVER (PARTITION BY dept_id ORDER BY sal RANGE BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) sum_1_to_last,
-- 窗口范围为该分区的第一行到本行，与sum_sal_part_order等同,在非条件表达式中等同于ROWS
SUM(sal) OVER (PARTITION BY dept_id ORDER BY sal RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) sum_1_to_cur,
-- 窗口范围为该分区内小于本记录sal少2500的所有的薪资累计
SUM(sal) OVER (PARTITION BY dept_id ORDER BY sal RANGE BETWEEN UNBOUNDED PRECEDING AND 2500/*value_expr*/ PRECEDING) sum1,
-- 窗口范围为该分区内小于本记录sal多2500的所有的薪资累计
SUM(sal) OVER (PARTITION BY dept_id ORDER BY sal RANGE BETWEEN UNBOUNDED PRECEDING AND 2500/*value_expr*/ FOLLOWING) sum2
FROM emp;
EMP_ID ENAME DEPT_ID HIRE_DATE SAL SUM_1_TO_LAST SUM_1_TO_CUR SUM1 SUM2
------ ----- ------- -------------- ----- ------------- ------------ ---------- ---------- 有数字，记住precding,就是减，following是加，就对了。
100 Stev 10 01-1月 -90 7000 7000 7000 7000
101 Tom 20 21-9月 -89 2000 10000 2000 2000 解说SUM1（dept_id=50部分） 解说SUM2(dept_id=50部分）
102 Mike 20 13-1月 -93 8000 10000 10000 2000 10000
120 John 50 18-7月 -96 1000 19000 1000 4000 ---比1000-2500少的数字有木有，没有 ---比1000+2500少的数字有木有，有啊，1000和3000
122 RICH 50 01-5月 -95 3000 19000 4000 13000 ---比3000-2500少的数字有木有，没有，该组第1排的1000也比500大 ---比3000+2500少的数字有木有，有啊，1到4排都是
121 Joy 50 10-4月 -97 4000 19000 8000 1000 19000 ---比4000-2500少的数字有木有，有啊，该组第1排的1000就比1500小 ---比4000+2500少的数字有木有，有啊，该组都是
123 Kate 50 10-10月-97 5000 19000 13000 1000 19000 ---比5000-2500少的数字有木有，有啊，该组第1排的1000就比2500小 ---比5000+2500少的数字有木有，有啊，该组都是
124 Jess 50 16-11月-99 6000 19000 19000 4000 19000 ---比6000-2500少的数字有木有，有啊，该组第1排和第2排哦。 ---比6000+2500少的数字有木有，有啊，该组都是
SELECT
emp_id,ename,dept_id,hire_date,sal,
-- 窗口范围为本行和该分区的最后一行，统计的是大于等于本记录hire_date之后的所有薪资,在非条件表达式中等同于ROWS
SUM(sal) OVER (PARTITION BY dept_id ORDER BY sal RANGE BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING) sum3,
-- 窗口范围只是本行，所以与本行薪资一样
SUM(sal) OVER (PARTITION BY dept_id ORDER BY sal RANGE BETWEEN CURRENT ROW AND CURRENT ROW) sum4,
-- 窗口范围为该分区内本记录起和小于本记录sal多2500的所有的薪资累计
SUM(sal) OVER (PARTITION BY dept_id ORDER BY sal RANGE BETWEEN CURRENT ROW AND 2500/*value_expr*/ FOLLOWING) sum5,
-- 窗口范围为该分区内本记录起和小于本记录sal多2500的所有的薪资累计
SUM(sal) OVER (PARTITION BY dept_id ORDER BY sal RANGE BETWEEN 2500/*value_expr*/ PRECEDING AND UNBOUNDED FOLLOWING) sum6
FROM emp;
EMP_ID ENAME DEPT_ID HIRE_DATE SAL SUM3 SUM4 SUM5 SUM6
------ ----- ------- -------------- ----- ---------- ---------- ---------- ----------
100 Stev 10 01-1月 -90 7000 7000 7000 7000 7000
101 Tom 20 21-9月 -89 2000 10000 2000 2000 10000
102 Mike 20 13-1月 -93 8000 8000 8000 8000 8000
120 John 50 18-7月 -96 1000 19000 1000 4000 19000
122 RICH 50 01-5月 -95 3000 18000 3000 12000 19000
121 Joy 50 10-4月 -97 4000 15000 4000 15000 18000
123 Kate 50 10-10月-97 5000 11000 5000 11000 18000
124 Jess 50 16-11月-99 6000 6000 6000 6000 15000
SELECT
emp_id,ename,dept_id,hire_date,sal,
-- 窗口范围为该分区内大于本记录sal少2500，并且截止到当前记录的所有的薪资累计
SUM(sal) OVER (PARTITION BY dept_id ORDER BY sal RANGE BETWEEN 2500/*value_expr*/ PRECEDING AND CURRENT ROW) sum7,
-- 窗口范围为该分区的本行current_value-value_expr1到本行current_value-value_expr2的累计，强调value_expr1>=value_expr2
SUM(sal) OVER (PARTITION BY dept_id ORDER BY sal RANGE BETWEEN 2500/*value_expr1*/ PRECEDING AND 1000/*value_expr2*/ PRECEDING) sum8,
-- 窗口范围为该分区的本行current_value-value_expr1到本行current_value+value_expr2之间的累计
SUM(sal) OVER (PARTITION BY dept_id ORDER BY sal RANGE BETWEEN 2500/*value_expr1*/ PRECEDING AND 1000/*value_expr2*/ FOLLOWING) sum9,
-- 窗口范围为该分区内大于本记录sal多2500的所有的薪资累计
SUM(sal) OVER (PARTITION BY dept_id ORDER BY sal RANGE BETWEEN 2500/*value_expr*/ FOLLOWING AND UNBOUNDED FOLLOWING) sum10
FROM emp;
EMP_ID ENAME DEPT_ID HIRE_DATE SAL SUM7 SUM8 SUM9 SUM10
------ ----- ------- -------------- ----- ---------- ---------- ---------- ----------
100 Stev 10 01-1月 -90 7000 7000 7000
101 Tom 20 21-9月 -89 2000 2000 2000 8000 解说SUM9
102 Mike 20 13-1月 -93 8000 8000 8000
120 John 50 18-7月 -96 1000 1000 1000 15000 ---有木有介于 1000-2500 到 1000+1000之间的数字，有，该组第一行1000就介于其中
122 RICH 50 01-5月 -95 3000 4000 1000 8000 6000 ---有木有介于 3000-2500 到 3000+1000之间的数字，有，该第1行到第3行都是，第3行正好符合
121 Joy 50 10-4月 -97 4000 7000 3000 12000 ---有木有介于 4000-2500 到 4000+1000之间的数字，有，该第2行到第4行都是，第4行正好符合
123 Kate 50 10-10月-97 5000 12000 7000 18000 ---有木有介于 5000-2500 到 5000+1000之间的数字，有，该第2行到第5行都是，第5行正好符合
124 Jess 50 16-11月-99 6000 15000 9000 15000 ---有木有介于 6000-2500 到 6000+1000之间的数字，有，该第3行到第5行都是
SELECT
emp_id,ename,dept_id,hire_date,sal,
-- 窗口范围为该分区的本行current_value+value_expr1到本行current_value+value_expr2的累计，强调value_expr1<=value_expr2
SUM(sal) OVER (PARTITION BY dept_id ORDER BY sal RANGE BETWEEN 1000/*value_expr1*/ FOLLOWING AND 2500/*value_expr2*/ FOLLOWING) sum11,
-- 窗口范围为该分区的第一行,结束行缺省为本行，与sum_sal_part_order，sum_2等同,在非条件表达式中等同于ROWS
SUM(sal) OVER (PARTITION BY dept_id ORDER BY sal RANGE UNBOUNDED PRECEDING) sum12,
-- 窗口范围仅为当前行，在非条件表达式中等同于ROWS
SUM(sal) OVER (PARTITION BY dept_id ORDER BY sal RANGE CURRENT ROW) sum13,
-- 窗口范围为该分区内大于本记录sal少2500，并且截止到当前记录的所有的薪资累计
SUM(sal) OVER (PARTITION BY dept_id ORDER BY sal RANGE 2500/*value_expr*/ PRECEDING) sum14
FROM emp;
EMP_ID ENAME DEPT_ID HIRE_DATE SAL SUM11 SUM12 SUM13 SUM14
------ ----- ------- -------------- ----- ---------- ---------- ---------- ----------
100 Stev 10 01-1月 -90 7000 7000 7000 7000
101 Tom 20 21-9月 -89 2000 2000 2000 2000
102 Mike 20 13-1月 -93 8000 10000 8000 8000
120 John 50 18-7月 -96 1000 3000 1000 1000 1000
122 RICH 50 01-5月 -95 3000 9000 4000 3000 4000
121 Joy 50 10-4月 -97 4000 11000 8000 4000 7000
123 Kate 50 10-10月-97 5000 6000 13000 5000 12000
124 Jess 50 16-11月-99 6000 19000 6000 15000
KEEP窗口
聚合函数的特殊关键字KEEP 聚合函数MIN, MAX, SUM, AVG, COUNT, VARIANCE,和STDDEV, 当使用KEEP 时和DENSE_RANK FIRST /DENSE_RANK LAST一起使用，获取一组中排名第一或者排名最后的记录。必须有order by 子句用来排序。后面也可以接over（）分析函数部分。 Min（col2）keep（dense_rank first order by col1）保留按col1排名第一的col2的最小值。 Min（col2）keep（dense_rank first order by col1）over (partition by col3) 按col3分组保留按col1排名各组第一的col2的最小值。举例说明：
---需要注意的是KEEP只能与DENSE_RANK FIRST、DENSE_RANK LAST搭配使用。 SELECT emp_id,ename,dept_id,hire_date,sal, DENSE_RANK() OVER(PARTITION BY dept_id ORDER BY sal) DENSE_RANK, MIN(hire_date) KEEP (DENSE_RANK FIRST ORDER BY sal) OVER(PARTITION BY dept_id) min_first, MIN(hire_date) KEEP (DENSE_RANK LAST ORDER BY sal) OVER(PARTITION BY dept_id) min_last, MAX(hire_date) KEEP (DENSE_RANK FIRST ORDER BY sal) OVER(PARTITION BY dept_id) max_first, MAX(hire_date) KEEP (DENSE_RANK LAST ORDER BY sal) OVER(PARTITION BY dept_id) max_last FROM emp; EMP_ID ENAME DEPT_ID HIRE_DATE SAL DENSE_RANK MIN_FIRST MIN_LAST MAX_FIRST MAX_LAST ------ ----- ------- -------------- ----- ---------- -------------- -------------- -------------- ----------- 100 Stev 10 01-1月 -90 7000 1 01-1月 -90 01-1月 -90 01-1月 -90 01-1月 -90 101 Tom 20 21-9月 -89 2000 1 21-9月 -89 13-1月 -93 21-9月 -89 13-1月 -93 102 Mike 20 13-1月 -93 8000 2 21-9月 -89 13-1月 -93 21-9月 -89 13-1月 -93 120 John 50 18-7月 -96 1000 1 18-7月 -96 16-11月-99 10-4月 -97 16-11月-99 121 Joy 50 10-4月 -97 1000 1 18-7月 -96 16-11月-99 10-4月 -97 16-11月-99 122 Rich 50 01-5月 -95 3000 2 18-7月 -96 16-11月-99 10-4月 -97 16-11月-99 123 Kate 50 10-10月-97 5000 3 18-7月 -96 16-11月-99 10-4月 -97 16-11月-99 124 Jess 50 16-11月-99 6000 4 18-7月 -96 16-11月-99 10-4月 -97 16-11月-99