Oracle 中 Over() 函数学习总结

Oracle从8.1.6开始提供分析函数，分析函数用于计算基于组的某种聚合值，它和聚合函数的不同之处是对于每个组返回多行，而聚合函数对于每个组只返回一行。

 

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

over(order by salary) 按照salary排序进行累计，order by是个默认的开窗函数；
over(partition by deptid)按照部门分区；

over(order by salary range between 50 preceding and 150 following)  每行对应的数据窗口是之前行幅度值不超过50，之后行幅度值不超过150；
over(order by salary rows between 50 preceding and 150 following)  每行对应的数据窗口是之前50行，之后150行；
over(order by salary rows between unbounded preceding and unbounded following)  每行对应的数据窗口是从第一行到最后一行，等效：
over(order by salary range between unbounded preceding and unbounded following）

1、首先看看测试表的测试数据，顺便演示一下分组功能

SQL> select test.*,row_number() over(partition by deptid order by deptid) rw from test;

    DEPTID NAME       SALARY         RW
---------- ------ ---------- ----------
        10 david        1000          1
        10 john         1100          2
        10 jerry        1200          3
        20 smith        1300          1
        20 scott        1400          2
        30 tony         1500          1
        30 amanda       1600          2

7 rows selected

SQL>

测试表test中共做了7条测试数据，包括三列（机构ID，员工姓名，工资）。

 

2、查看每个部门的平均工资

SQL> select a.*, avg(salary) OVER (PARTITION BY deptid) sum_c from test a;

    DEPTID NAME       SALARY      SUM_C
---------- ------ ---------- ----------
        10 david        1000       1100
        10 john         1100       1100
        10 jerry        1200       1100
        20 smith        1300       1350
        20 scott        1400       1350
        30 tony         1500       1550
        30 amanda       1600       1550

7 rows selected

以及查看每个部门的最大、最小工资

SQL> select a.*, min(salary) OVER (PARTITION BY deptid) min_c, max(salary) OVER (PARTITION BY deptid) max_c from test a;

    DEPTID NAME       SALARY      MIN_C      MAX_C
---------- ------ ---------- ---------- ----------
        10 david        1210       1100       1210
        10 john         1100       1100       1210
        10 jerry        1200       1100       1210
        20 smith        1100       1100       1300
        20 scott        1300       1100       1300
        30 tony         1200       1200       1250
        30 amanda       1250       1200       1250

7 rows selected

 

3、查看每个部门的人数

SQL> select a.*, count(*) OVER (PARTITION BY deptid) sum_c from test a;

    DEPTID NAME       SALARY      SUM_C
---------- ------ ---------- ----------
        10 david        1000          3
        10 john         1100          3
        10 jerry        1200          3
        20 smith        1300          2
        20 scott        1400          2
        30 tony         1500          2
        30 amanda       1600          2

7 rows selected

 

4、查看每个部门的工资总数

SQL> select a.*, sum(salary) OVER (PARTITION BY deptid) sum_c from test a;

    DEPTID NAME       SALARY      SUM_C
---------- ------ ---------- ----------
        10 david        1000       3300
        10 john         1100       3300
        10 jerry        1200       3300
        20 smith        1300       2700
        20 scott        1400       2700
        30 tony         1500       3100
        30 amanda       1600       3100

7 rows selected

 

按照每个部门工资排序进行统计

SQL> select a.*, sum(salary) OVER (PARTITION BY deptid order by salary) sum_c from test a;

    DEPTID NAME       SALARY      SUM_C
---------- ------ ---------- ----------
        10 david        1000       1000
        10 john         1100       2100
        10 jerry        1200       3300
        20 smith        1300       1300
        20 scott        1400       2700
        30 tony         1500       1500
        30 amanda       1600       3100

7 rows selected

 

按照所有员工工资排序进行统计

SQL> select a.*, sum(salary) OVER (order by salary) sum_c from test a;

    DEPTID NAME       SALARY      SUM_C
---------- ------ ---------- ----------
        10 david        1000       1000
        10 john         1100       2100
        10 jerry        1200       3300
        20 smith        1300       4600
        20 scott        1400       6000
        30 tony         1500       7500
        30 amanda       1600       9100

7 rows selected

 

如果不需要已某个栏位的值分割，那就要用 null，等同于OVER ()

SQL> select a.*, sum(salary) OVER (order by null) sum_c from test a;

    DEPTID NAME       SALARY      SUM_C
---------- ------ ---------- ----------
        10 david        1000       9100
        10 john         1100       9100
        10 jerry        1200       9100
        30 amanda       1600       9100
        20 scott        1400       9100
        30 tony         1500       9100
        20 smith        1300       9100

7 rows selected

SQL> select a.*, sum(salary) OVER () sum_c from test a;

    DEPTID NAME       SALARY      SUM_C
---------- ------ ---------- ----------
        10 david        1000       9100
        10 john         1100       9100
        10 jerry        1200       9100
        20 smith        1300       9100
        20 scott        1400       9100
        30 tony         1500       9100
        30 amanda       1600       9100

7 rows selected

 

5、查看每人工资占部门总工资的百分比

SQL> select a.*, round(salary*100/sum(salary) OVER (PARTITION BY deptid) ,2) sum_c from test a;

    DEPTID NAME       SALARY      SUM_C
---------- ------ ---------- ----------
        10 david        1000       30.3
        10 john         1100      33.33
        10 jerry        1200      36.36
        20 smith        1300      48.15
        20 scott        1400      51.85
        30 tony         1500      48.39
        30 amanda       1600      51.61

7 rows selected

个人工资占整个公司工资的百分比

SQL> select a.*, round(salary*100/sum(salary) OVER (PARTITION BY null) ,2) sum_c from test a;

    DEPTID NAME       SALARY      SUM_C
---------- ------ ---------- ----------
        10 david        1000      10.99
        10 john         1100      12.09
        10 jerry        1200      13.19
        30 amanda       1600      17.58
        20 scott        1400      15.38
        30 tony         1500      16.48
        20 smith        1300      14.29

7 rows selected

Oracle提供了另一个函数RATIO_TO_REPORT计算百分比，比上面的算法更简单。该函数计算expression/(sum(expression))的值，它给出相对于总数的百分比，即当前行对sum(expression)的贡献

SQL> select a.*, round(ratio_to_report(salary) OVER (PARTITION BY deptid),4) sum_b,round(salary*100/sum(salary) OVER (PARTITION BY deptid) ,2) sum_c from test a;

    DEPTID NAME       SALARY      SUM_B      SUM_C
---------- ------ ---------- ---------- ----------
        10 david        1210     0.3447      34.47
        10 john         1100     0.3134      31.34
        10 jerry        1200     0.3419      34.19
        20 smith        1100     0.4583      45.83
        20 scott        1300     0.5417      54.17
        30 tony         1200     0.4898      48.98
        30 amanda       1250     0.5102      51.02

7 rows selected

 

 

6、分部门按姓名排序后，统计对应的数据窗口之前1行，之后1行

SQL> select a.*, sum(salary) OVER (PARTITION BY deptid order by name rows between 1 preceding and 1 following) sum_c from test a;

    DEPTID NAME       SALARY      SUM_C
---------- ------ ---------- ----------
        10 david        1000       2200
        10 jerry        1200       3300
        10 john         1100       2300
        20 scott        1400       2700
        20 smith        1300       2700
        30 amanda       1600       3100
        30 tony         1500       3100

7 rows selected

结果可以看出，按照部门分组后，按姓名排序并sum每个部门每条记录的前一行，本行和后一行。

SQL> select a.*, sum(salary) OVER (order by salary rows between 1 preceding and 1 following) sum_c from test a;

    DEPTID NAME       SALARY      SUM_C
---------- ------ ---------- ----------
        10 david        1000       2100
        10 john         1100       3300
        10 jerry        1200       3600
        20 smith        1300       3900
        20 scott        1400       4200
        30 tony         1500       4500
        30 amanda       1600       3100

7 rows selected

按工资排序后sum每条记录的前一行，本行和后一行。

 

7、排序函数rank()和dense_rank()的区别

rank() 是跳跃排序，有两个第二名时接下来就是第四名，有三个第二名时接下来就是第五名；

dense_rank() 是连续排序，有两个第二名时仍然跟着第三名；

下面调整一下测试数据的salary，使存在相同salary的情况，然后把整个员工按salary排序，看看效果。

SQL> select a.*, rank() OVER (/*PARTITION BY deptid*/ order by salary) sum_c from test a;

    DEPTID NAME       SALARY      SUM_C
---------- ------ ---------- ----------
        20 smith        1100          1
        10 john         1100          1
        10 jerry        1200          3
        30 tony         1200          3
        10 david        1210          5
        30 amanda       1250          6
        20 scott        1300          7

7 rows selected

SQL> select a.*, dense_rank() OVER (/*PARTITION BY deptid*/ order by salary) sum_c from test a;

    DEPTID NAME       SALARY      SUM_C
---------- ------ ---------- ----------
        20 smith        1100          1
        10 john         1100          1
        10 jerry        1200          2
        30 tony         1200          2
        10 david        1210          3
        30 amanda       1250          4
        20 scott        1300          5

7 rows selected

SQL>

可以看出，使用rank()排序的为跳跃数字，而使用dense_rank() 排序的为连续数字。

 

8、LAG 和 LEAD 函数的区别

LAG 可以访问结果集中的其它行而不用进行自连接。它允许去处理游标，就好像游标是一个数组一样。在给定组中可参考当前行之前的行，这样就可以从组中与当前行一起选择以前的行。Offset是一个正整数，其默认值为1，若索引超出窗口的范围，就返回默认值（默认返回的是组中第一行），其相反的函数是LEAD

SQL> select a.*, lag(salary) OVER (PARTITION BY deptid order by salary desc) sum_c from test a;

    DEPTID NAME       SALARY      SUM_C
---------- ------ ---------- ----------
        10 david        1210
        10 jerry        1200       1210
        10 john         1100       1200
        20 scott        1300
        20 smith        1100       1300
        30 amanda       1250
        30 tony         1200       1250

7 rows selected

按部门分组，按salary倒序排序，通过lag()选择部门内前一行的salary；

 

SQL> select a.*, lead(salary) OVER (PARTITION BY deptid order by salary) sum_c from test a;

    DEPTID NAME       SALARY      SUM_C
---------- ------ ---------- ----------
        10 john         1100       1200
        10 jerry        1200       1210
        10 david        1210
        20 smith        1100       1300
        20 scott        1300
        30 tony         1200       1250
        30 amanda       1250

7 rows selected

SQL>

按部门分组，按salary排序，通过lead()选择部门内后一行的salary；

利用lag和lead函数，我们可以在同一行中显示前n行的数据，也可以显示后n行的数据。

9、first_value() 与 last_value() 函数比较

FIRST_VALUE，返回组中数据窗口的第一个值；

LAST_VALUE，返回组中数据窗口的最后一个值。

SQL> select a.*, first_value(salary) OVER (PARTITION BY deptid) first_c, last_value(salary) OVER (PARTITION BY deptid) last_c from test a;

    DEPTID NAME       SALARY    FIRST_C     LAST_C
---------- ------ ---------- ---------- ----------
        10 david        1210       1210       1200
        10 john         1100       1210       1200
        10 jerry        1200       1210       1200
        20 smith        1100       1100       1300
        20 scott        1300       1100       1300
        30 tony         1200       1200       1250
        30 amanda       1250       1200       1250

7 rows selected

 

 

over()很神奇，很强大，还有更多功能需要去学习和掌握。

 

 

 

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/9399028/viewspace-712967/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/9399028/viewspace-712967/