Oracle从8.1.6开始提供分析函数，分析函数用于计算基于组的某种聚合值，它和聚合函数的不同之处是对于每个组返回多行，而聚合函数对于每个组只返回一行。
开窗函数指定了分析函数工作的数据窗口大小，这个数据窗口大小可能会随着行的变化而变化，举例如下：
over(order by salary) 按照salary排序进行累计，order by是个默认的开窗函数；
over(partition by deptid)按照部门分区；
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）
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
---------- ------ ---------- ----------
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
---------- ------ ---------- ----------
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
---------- ------ ---------- ---------- ----------
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
---------- ------ ---------- ----------
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
---------- ------ ---------- ----------
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
---------- ------ ---------- ----------
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
---------- ------ ---------- ----------
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
---------- ------ ---------- ----------
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
---------- ------ ---------- ----------
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
---------- ------ ---------- ----------
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
---------- ------ ---------- ----------
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
---------- ------ ---------- ---------- ----------
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
---------- ------ ---------- ----------
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
---------- ------ ---------- ----------
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
---------- ------ ---------- ----------
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
---------- ------ ---------- ----------
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
---------- ------ ---------- ----------
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
---------- ------ ---------- ----------
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
---------- ------ ---------- ---------- ----------
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博客 ” ，链接：https://blog.itpub.net/9399028/viewspace-712967/ ，如需转载，请注明出处，否则将追究法律责任。
下一篇：
Java 正则表达式
请登录后发表评论
登录
全部评论
