Oracle常用函数--over()开窗函数

什么是分析函数（partition by）：

　　分析函数是Oracle专门用于解决复杂报表统计需求的函数，它可以在数据中进行分组，然后计算基于组的某种统计值，并且每一组的每一行都可以返回一个统计值。

分析函数和聚合函数的不同之处是什么？

　　普通的聚合函数用group by分组，每个分组返回一个统计值，只有一行，而分析函数采用partition by分组，每组中包含多个值。

　　开窗函数 其实就是group by的另一种。它与group by的区别在于开窗函数可以在分组列中再排序,其实就是加了一列隐藏列,可以在group by中再分组的意思.

关于开窗函数(over())：

　　开窗函数指定了分析函数中的分组的大小。

　　分析函数带有一个开窗函数over()，包含三个分析子句:分组(partition by), 排序(order by), 窗口(rows) ，这些就是窗口的规则。

　　他们的使用形式如下：over(partition by xxx order by yyy rows between zzz)。

　　注意：窗口子句不能单独出现，必须有order by子句时才能出现。

两个order by的执行时机:

　　分析函数（以及与其配合的开窗函数over（））是在整个sql查询结束后(sql语句中的order by的执行比较特殊)再进行的操作, 也就是说sql语句中的order by也会影响分析函数的执行结果：

　　a) 两者一致：如果sql语句中的order by满足与分析函数配合的开窗函数over（）分析时要求的排序，即sql语句中的order by子句里的内容和开窗函数over（）中的order by子句里的内容一样，那么sql语句中的排序将先执行,分析函数在分析时就不必再排序；

　　b) 两者不一致：如果sql语句中的order by不满足与分析函数配合的开窗函数over（）分析时要求的排序，即sql语句中的order by子句里的内容和开窗函数over（）中的order by子句里的内容不一样，那么sql语句中的排序将最后在分析函数分析结束后执行排序。

开窗函数的例子;

　　开窗函数指定了分析函数工作的数据窗口大小，这个数据窗口大小可能会随着行的变化而变化，举例如下：

　　1、over（order by salary） 按照salary排序进行累计，order by是个默认的开窗函数。

SELECT EMPLOYEE_ID,
       SALARY,
       MANAGER_ID,
       DEPARTMENT_ID,
       SUM(SALARY) OVER(ORDER BY SALARY) DD
  FROM INFA_TEST.EMPLOYEES EMP
 ORDER BY SALARY

    功能：按salary升序排序，统计小于等于当前salary的salary总和。

    返回结果：

ROWNUM	EMPLOYEE_ID	SALARY	MANAGER_ID	DEPARTMENT_ID	DD	备注
1	132	2100	121	50	2100
2	128	2200	120	50	6500	2200+2200+2100
3	136	2200	122	50	6500	2200+2200+2100
4	127	2400	120	50	11300	6500+2400+2400
5	135	2400	122	50	11300	6500+2400+2400
6	119	2500	114	30	26300	11300+2500*5
7	140	2500	123	50	26300	11300+2500*5
8	144	2500	124	50	26300	11300+2500*5
9	191	2500	122	50	26300	11300+2500*5
10	182	2500	120	50	26300	11300+2500*5

 

    注意 SALARY为2200、2400和2500行的DD值,

    2、over（partition by DEPARTMENT_ID）按照部门分区。

SELECT EMPLOYEE_ID,
       SALARY,
       MANAGER_ID,
       DEPARTMENT_ID,
       SUM(SALARY) OVER(PARTITION BY DEPARTMENT_ID) DD
  FROM INFA_TEST.EMPLOYEES EMP
 ORDER BY DEPARTMENT_ID

    功能：按DEPARTMENT_ID分区，汇总各个部门的SALARY总和。

    返回结果：

ROWNUM	EMPLOYEE_ID	SALARY	MANAGER_ID	DEPARTMENT_ID	DD	备注
1	200	4400	101	10	4400
2	201	13000	100	20	19000	13000+6000
3	202	6000	201	20	19000	13000+6000
4	114	11000	100	30	24900	11000+3100+2900+2500+2600+2800
5	115	3100	114	30	24900	11000+3100+2900+2500+2600+2800
6	116	2900	114	30	24900	11000+3100+2900+2500+2600+2800
7	119	2500	114	30	24900	11000+3100+2900+2500+2600+2800
8	118	2600	114	30	24900	11000+3100+2900+2500+2600+2800
9	117	2800	114	30	24900	11000+3100+2900+2500+2600+2800

    注意 DEPARTMENT_ID为20，30的DD值

    3、over（partition by DEPARTMENT_ID order by SALARY）按照部门分区。

SELECT EMPLOYEE_ID,
       SALARY,
       MANAGER_ID,
       DEPARTMENT_ID,
       SUM(SALARY) OVER(PARTITION BY DEPARTMENT_ID ORDER BY SALARY) DD
  FROM INFA_TEST.EMPLOYEES EMP
 ORDER BY DEPARTMENT_ID

   功能：按DEPARTMENT_ID分区，按SALARY升序排序，统计各个部门内部小于当前SALARY的和。

    返回结果：

ROWNUM	EMPLOYEE_ID	SALARY	MANAGER_ID	DEPARTMENT_ID	DD	备注
1	200	4400	101	10	4400	DEPARTMENT_ID=10的SALARY
2	201	13000	100	20	6000	DEPARTMENT_ID=20的SALARY排序并求和
3	202	6000	201	20	19000	DEPARTMENT_ID=20的SALARY排序并求和:6000+13000
4	114	11000	100	30	2500	DEPARTMENT_ID=30的SALARY排序并求和
5	115	3100	114	30	5100	DEPARTMENT_ID=30的SALARY排序并求和:2500+2600
6	116	2900	114	30	7900	DEPARTMENT_ID=30的SALARY排序并求和:5100+2800
7	119	2500	114	30	10800	DEPARTMENT_ID=30的SALARY排序并求和:7900+2900
8	118	2600	114	30	13900	DEPARTMENT_ID=30的SALARY排序并求和:10800+3100
9	117	2800	114	30	24900	DEPARTMENT_ID=30的SALARY排序并求和:13900+11000

 注意 DEPARTMENT_ID为20、30的DD值和2中的区别   

    4、over（order by salary range between 50 preceding and 150 following）

SELECT EMPNO,
       SAL,
       MGR,
       DEPTNO,
       SUM(SAL) OVER(PARTITION BY DEPTNO ORDER BY SAL RANGE BETWEEN 0 PRECEDING AND 100 FOLLOWING) DD
  FROM EMP;

功能：按DEPTNO分区，按SAL升序排序，汇总当前SAL到比当前SAL大100之间的SAL总和。

    返回结果：

ROWNUM	EMPNO	SAL	MGR	DEPTNO	DD	备注
1	7934	1300	7782	10	1300
2	7782	2450	7839	10	2450
3	7839	5000		10	5000
4	7369	800	7902	20	800
5	7566	2975	7839	20	5975	3000在2975和（2975+100）之间，故求2975与3000的和
6	7902	3000	7566	20	3000
7	7900	950	7698	30	950
8	7521	1250	7698	30	2500	1250在1250和(1250+100)之间,故求1250和1250的和
9	7654	1250	7698	30	2500
10	7844	1500	7698	30	3100	1600在1500和(1500+100)之间,故求1500和1600的和
11	7499	1600	7698	30	1600
12	7698	2850	7839	30	2850

  解释：返回前置行和当前行SAL相等，后续行比他大100的记录，在SAL列上求和。

 上下边界没有限制：OVER (PARTITION BY DEPTNO ORDER BY SAL RANGE BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING)      

SELECT EMPNO,
       SAL,
       MGR,
       DEPTNO,
       SUM(SAL) OVER(PARTITION BY DEPTNO ORDER BY SAL RANGE BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) DD
  FROM EMP;

ROWNUM	EMPNO	SAL	MGR	DEPTNO	DD	备注
1	7934	1300	7782	10	8750	1300+2450+5000
2	7782	2450	7839	10	8750	1300+2450+5000
3	7839	5000		10	8750	1300+2450+5000
4	7369	800	7902	20	6775	800+2975+3000
5	7566	2975	7839	20	6775	800+2975+3000
6	7902	3000	7566	20	6775	800+2975+3000
7	7900	950	7698	30	9400	950+1250+1250+1500+1600+2850
8	7521	1250	7698	30	9400	950+1250+1250+1500+1600+2850
9	7654	1250	7698	30	9400	950+1250+1250+1500+1600+2850
10	7844	1500	7698	30	9400	950+1250+1250+1500+1600+2850
11	7499	1600	7698	30	9400	950+1250+1250+1500+1600+2850
12	7698	2850	7839	30	9400	950+1250+1250+1500+1600+2850

 

    解释:返回以DEPTNO分组的SAL列上求和

5、over（order by salary rows between 1 preceding and 2 following）-- 每行对应的数据窗口是之前行幅度值不超过1，之后行幅度值不超过2

SELECT EMPNO,
       SAL,
       MGR,
       DEPTNO,
       SUM(SAL) OVER(PARTITION BY DEPTNO ORDER BY SAL ROWS BETWEEN 1 PRECEDING AND 2 FOLLOWING) DD
  FROM EMP;

    返回结果

ROWNUM	EMPNO	SAL	MGR	DEPTNO	DD	备注
1	7934	1300	7782	10	8750	本行和后两行SAL的和:1300+2450+5000
2	7782	2450	7839	10	8750	本行和前一行和后一行SAL的和:2450+1300+5000
3	7839	5000		10	7450	本行和前一行SAL的和:5000+2450
4	7369	800	7902	20	6775	本行和后两行SAL的和:800+2975+3000
5	7566	2975	7839	20	6775	本行和前一行和后一行SAL的和:2975+800+3000
6	7902	3000	7566	20	5975	本行和前一行SAL的和:3000+2975
7	7900	950	7698	30	3450	本行和后两行SAL的和:950+1250+1250
8	7521	1250	7698	30	4950	本行和前一行和后两行SAL的和:1250+950+1250+1500
9	7654	1250	7698	30	5600	本行和前一行和后两行SAL的和:1250+1250+1500+1600
10	7844	1500	7698	30	7200	本行和前一行和后两行SAL的和:1500+1250+1600+2850
11	7499	1600	7698	30	5950	本行和前一行和后一行SAL的和:1600+1500+2850
12	7698	2850	7839	30	4450	本行和前一行SAL的和:2850+1600

解释:如果分组后本行前一行或者后一行没有数据了,就不进行求和了.