开窗函数之Range:
1.Range是对范围进行统计
2. 有数字，记住precding,就是减，following是加， 统计在这个范围内的数据
3.不论范围如何，依然在 自己的分区 内进行数据计算查询、计算
/*
1. RANGE窗口仅对NUMBERS和DATES起作用,因为不可能从VARCHAR2中增加或减去N个单元
2. 在RANGE的开窗中，ORDER BY中只能有一列；ROWS的开窗的ORDER BY 可以有多列。
*/
Rich table emp purge;
CREATE TABLE emp
(
emp_id NUMBER(6),
ename VARCHAR2(45),
dept_id NUMBER(4),
hire_date DATE,
sal NUMBER(8,2)
);
–创建emp数据
INSERT INTO emp (emp_id, ename, dept_id, hire_date, sal) VALUES (101, ‘Tom’, 20, TO_DATE(’21-09-1989′, ‘DD-MM-YYYY’), 2000);
INSERT INTO emp (emp_id, ename, dept_id, hire_date, sal) VALUES (102, ‘Mike’, 20, TO_DATE(’13-01-1993′, ‘DD-MM-YYYY’), 8000);
INSERT INTO emp (emp_id, ename, dept_id, hire_date, sal) VALUES (120, ‘John’, 50, TO_DATE(’18-07-1996′, ‘DD-MM-YYYY’), 1000);
INSERT INTO emp (emp_id, ename, dept_id, hire_date, sal) VALUES (121, ‘Joy’, 50, TO_DATE(’10-04-1997′, ‘DD-MM-YYYY’), 4000);
INSERT INTO emp (emp_id, ename, dept_id, hire_date, sal) VALUES (122, ‘RICH’, 50, TO_DATE(’01-05-1995′, ‘DD-MM-YYYY’), 3000);
INSERT INTO emp (emp_id, ename, dept_id, hire_date, sal) VALUES (123, ‘Kate’, 50, TO_DATE(’10-10-1997′, ‘DD-MM-YYYY’), 5000);
INSERT INTO emp (emp_id, ename, dept_id, hire_date, sal) VALUES (124, ‘Jess’, 50, TO_DATE(’16-11-1999′, ‘DD-MM-YYYY’), 6000);
INSERT INTO emp (emp_id, ename, dept_id, hire_date, sal) VALUES (100, ‘Stev’, 10, TO_DATE(’01-01-1990′, ‘DD-MM-YYYY’), 7000);
COMMIT;
set linesize 2000
set pagesize 2000
col emp_id format 999
col dept_id format 99
col sal format 9999
col ename format a5前
col hire_date FORMAT DATE
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
—— —– ——- ————– —– ————- ———— ———- ———-
100 Stev 10 01-1月 -90 7000 7000 7000 7000
101 Tom 20 21-9月 -89 2000 10000 2000 2000
102 Mike 20 13-1月 -93 8000 10000 10000 2000 10000
120 John 50 18-7月 -96 1000 19000 1000 4000
122 RICH 50 01-5月 -95 3000 19000 4000 13000
121 Joy 50 10-4月 -97 4000 19000 8000 1000 19000
123 Kate 50 10-10月-97 5000 19000 13000 1000 19000
124 Jess 50 16-11月-99 6000 19000 19000 4000 19000
有数字，记住precding,就是减，following是加，就对了。
解说SUM1（dept_id=50部分）
—比1000-2500少的数字有木有，没有
—比3000-2500少的数字有木有，没有，该组第1排的1000也比500大
—比4000-2500少的数字有木有，有啊，该组第1排的1000就比1500小
—比5000-2500少的数字有木有，有啊，该组第1排的1000就比2500小
—比6000-2500少的数字有木有，有啊，该组第1排和第2排哦。
解说SUM2(dept_id=50部分）
—比1000+2500少的数字有木有，有啊，1000和3000
—比3000+2500少的数字有木有，有啊，1到4排都是
—比4000+2500少的数字有木有，有啊，该组都是
—比5000+2500少的数字有木有，有啊，该组都是
—比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
102 Mike 20 13-1月 -93 8000 8000 8000
120 John 50 18-7月 -96 1000 1000 1000 15000
122 RICH 50 01-5月 -95 3000 4000 1000 8000 6000
121 Joy 50 10-4月 -97 4000 7000 3000 12000
123 Kate 50 10-10月-97 5000 12000 7000 18000
124 Jess 50 16-11月-99 6000 15000 9000 15000
解说SUM9
—有木有介于 1000-2500 到 1000+1000之间的数字，有，该组第一行1000就介于其中
—有木有介于 3000-2500 到 3000+1000之间的数字，有，该第1行到第3行都是，第3行正好符合
—有木有介于 4000-2500 到 4000+1000之间的数字，有，该第2行到第4行都是，第4行正好符合
—有木有介于 5000-2500 到 5000+1000之间的数字，有，该第2行到第5行都是，第5行正好符合
—有木有介于 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
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种，但实际上不过是几个关键字的排列组合而已，只要了解几个关键字的含义，在应用时加以灵活使用即可。