Oracl之开窗函数 - Range

开窗函数之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种，但实际上不过是几个关键字的排列组合而已，只要了解几个关键字的含义，在应用时加以灵活使用即可。