oracle开窗函数有哪些,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是个默认的开窗函数。

SELECTEMPLOYEE_ID,

SALARY,

MANAGER_ID,

DEPARTMENT_ID,SUM(SALARY) OVER(ORDER BYSALARY) DDFROMINFA_TEST.EMPLOYEES EMPORDER 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)按照部门分区。

SELECTEMPLOYEE_ID,

SALARY,

MANAGER_ID,

DEPARTMENT_ID,SUM(SALARY) OVER(PARTITION BYDEPARTMENT_ID) DDFROMINFA_TEST.EMPLOYEES EMPORDER BY DEPARTMENT_ID

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

返回结果：

EMPLOYEE_ID  SALARY   MANAGER_ID   DEPARTMENT_ID   DD

200          4400     101          10              4400

201          13000    100          20              19000

202          6000     201          20              19000

114          11000    100          30              24900

115          3100     114          30              24900

116          2900     114          30              24900

119          2500     114          30              24900

118          2600     114          30              24900

117          2800     114          30              24900

注意 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的和。

返回结果：

EMPLOYEE_ID  SALARY   MANAGER_ID   DEPARTMENT_ID   DD

200          4400     101          10              4400

201          13000    100          20              6000

202          6000     201          20              19000

114          11000    100          30              2500

115          3100     114          30              5100

116          2900     114          30              7900

119          2500     114          30              10800

118          2600     114          30              13900

117          2800     114          30              24900

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

4、over(order by salary range between 50 preceding and 150 following)

SQL> select

empno,sal,mgr,deptno,

sum(sal) over (partition by deptno order by sal

RANGE BETWEEN 0 PRECEDING AND 100 FOLLOWING) dd

from emp;

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

返回结果：

EMPNO    SAL    MGR     DEPTNO     DD

----- ------ ------ -------   --------

7934   1300   7782      10     1300

7782   2450   7839      10     2450

7839   5000             10     5000

7369    800   7902      20      800

7566   2975   7839      20     5975       3000在2975和(2975+100)之间，故求2975与3000的和

7902   3000   7566      20     3000

7900    950   7698      30      950

7521   1250   7698      30     2500

7654   1250   7698      30     2500

7844   1500   7698      30     3100

7499   1600   7698      30     1600

7698   2850   7839      30     2850

已选择12行。

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

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

SQL> select empno,sal,mgr,deptno,

sum(sal) over (partition by deptno order by sal

RANGE BETWEEN unbounded PRECEDING AND unbounded FOLLOWING) dd

from emp;

EMPNO        SAL        MGR     DEPTNO         DD

---------- ---------- ---------- ---------- ----------

7934       1300       7782         10       8750

7782       2450       7839         10       8750

7839       5000                    10       8750

7369        800       7902         20       6775

7566       2975       7839         20       6775

7902       3000       7566         20       6775

7900        950       7698         30       9400

7521       1250       7698         30       9400

7654       1250       7698         30       9400

7844       1500       7698         30       9400

7499       1600       7698         30       9400

7698       2850       7839         30       9400

已选择12行。

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

SQL> select empno,sal,mgr,deptno,

sum(sal) over (partition by deptno order by sal

rows BETWEEN 1 PRECEDING AND 2 FOLLOWING) dd

from emp;

返回结果

EMPNO        SAL        MGR     DEPTNO         DD

---------- ---------- ---------- ---------- ----------

7934       1300       7782         10       8750

7782       2450       7839         10       8750

7839       5000                    10       7450

7369        800       7902         20       6775

7566       2975       7839         20       6775

7902       3000       7566         20       5975

7900        950       7698         30       3450

7521       1250       7698         30       4950

7654       1250       7698         30       5600

7844       1500       7698         30       7200

7499       1600       7698         30       5950

7698       2850       7839         30       4450

已选择12行。

原文：https://www.cnblogs.com/wk-missQ1/p/12935272.html