分析函数是Oracle专门用于解决复杂报表统计需求的函数，它可以在数据中进行分组，然后计算基于组的某种统计值，并且每一组的每一行都可以返回一个统计值。
普通的聚合函数用group by分组，每个分组返回一个统计值，只有一行，而分析函数采用partition by分组，每组中包含多个值。
开窗函数 其实就是group by的另一种。它与group by的区别在于开窗函数可以在分组列中再排序,其实就是加了一列隐藏列,可以在group by中再分组的意思.
开窗函数指定了分析函数中的分组的大小。
分析函数带有一个开窗函数over()，包含三个分析子句:分组(partition by), 排序(order by), 窗口(rows) ，这些就是窗口的规则。
他们的使用形式如下：over(partition by xxx order by yyy rows between zzz)。
注意：窗口子句不能单独出现，必须有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是个默认的开窗函数。
功能：按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）按照部门分区。
功能：按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）按照部门分区。
功能：按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）
功能：按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)
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
返回结果
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
解释:如果分组后本行前一行或者后一行没有数据了,就不进行求和了.
版权声明：
本文来源网络，所有图片文章版权属于原作者，如有侵权，联系删除。
本文网址：https://www.mushiming.com/mjsbk/9691.html
