oracle——分析函数OVER ()

准备工作：
table:oracle用户scott下的emp表 ；
一.
Oracle 从8.1.6开始提供分析函数，分析函数用于计算基于组的某种聚合值，它和聚合函数的不同之处是
对于每个组返回多行，而聚合函数对于每个组只返回一行。
exp:
Sql代码


select t.empno,t.ename,sum(t.sal) from emp t select t.empno,t.ename,sum(t.sal) from emp t
这样会报：ORA-00937:非单组分组函数；
结论：聚合函数只能返回1行值。
Sql代码


select t.empno,t.ename,sum(t.sal)over() sum from emp t select t.empno,t.ename,sum(t.sal)over() sum from emp t
返回的结果：
empnoenamesum7369SMITH142781.997499ALLEN142781.997521WARD142781.997566JONES142781.997654MARTIN142781.997698BLAKE142781.997782CLARK142781.997788SCOTT142781.997839KING142781.997844TURNER142781.997876ADAMS142781.997900JAMES142781.997902FORD142781.997934MILLER142781.99
结论：这就是每个组返回多行。
二.
over()、over(order by...)与over(partition by...)之间的区别
1.分析函数over()用在聚合函数（max(),sun()....）后面，可返回多行所有结果的聚合值；
exp:如上；
2.over(order by...)用在聚合函数（max(),sun()....）后面，可返回根据排序结果进行统计到当前行的聚合值(即“连续”统计)；
exp:
Sql代码


select t.empno, t.deptno, t.ename, t.sal, sum(t.sal) over(order by t.ename) sum from emp t select t.empno, t.deptno, t.ename, t.sal, sum(t.sal) over(order by t.ename) sum from emp t
返回的结果：
empno deptno ename sal sum787620ADAMS4646.114646.11749930ALLEN8493.66 13139.77769830BLAKE15047.69 28187.46778210CLARK11823.85 40011.31790220FORD14935.9754947.28790030JAMES4935.36 59882.64756620JONES15086.30 74968.94783910KING23841.13 98810.07765430MARTIN6526.80 105336.87793410MILLER6167.32 111504.19778820SCOTT12710.16 124214.35736920SMITH4089.17 128303.52784430TURNER7843.77 136147.29752130WARD6634.70 142781.99
分析：第二行的sum=第一行里的sal+第二行里的sal；
第二行的sum=第一行里的sal+第二行里的sal+第三行里的sal；
.....
结论：返回值是根据排序后的结果，当前所在的行进行统计的。
3.over(partition by...)用在聚合函数（max(),sun()....）后面，可根据pratition by里指定的某一列来统计聚合值。
exp:
Sql代码


select t.empno, t.deptno, t.ename, t.sal, sum(t.sal) over(partition by t.deptno) sum from emp t select t.empno, t.deptno, t.ename, t.sal, sum(t.sal) over(partition by t.deptno) sum from emp t
返回的结果：
empnodeptno ename sal sum7782 10CLARK11823.85 41832.37839 10KING23841.13 41832.3793410MILLER6167.3241832.3736920SMITH4089.1751467.71787620ADAMS4646.1151467.71790220FORD14935.9751467.71778820SCOTT12710.1651467.71756620JONES15086.3051467.71749930ALLEN8493.6649481.98769830 BLAKE15047.69 49481.987654 30MARTIN 6526.80 49481.98790030 JAMES4935.3649481.987844 30TURNER7843.7749481.987521 30WARD6634.7049481.98
分析：每个sum的值都是把deptno相同的sal值进行求和。
结论：根据pratition by里指定的某一列来统计聚合值。
三.一个综合的例子：
exp:
question:
按部门“连续”求总和;
answer:
Sql代码


select t.empno, t.deptno, t.ename, t.sal, sum(t.sal) over(partition by t.deptno order by t.ename) sum from emp t select t.empno, t.deptno, t.ename, t.sal, sum(t.sal) over(partition by t.deptno order by t.ename) sum from emp t
返回的结果：
empnodeptno ename sal sum778210CLARK11823.8511823.85783910KING23841.1335664.98793410MILLER6167.32 41832.3787620ADAMS4646.11 4646.11790220FORD14935.97 19582.08756620JONES15086.3034668.38778820SCOTT12710.16 47378.54736920SMITH4089.1751467.71749930ALLEN8493.668493.66769830BLAKE15047.6923541.35790030JAMES4935.36 28476.71765430MARTIN6526.8035003.51784430TURNER7843.7742847.28752130WARD6634.70 49481.98
分析：先根据partition by 进行分组，然后再根据order by 进行排序“连续”统计。
四.一个实际的例子来说明over()分析函数在代码上能简化和提高效率。
question:
查询出管理员工人数最多的人的名字和他管理的人的名字
answer:
1.普通的方法：
Sql代码


select d.ename 管理员工人数最多的人的名字, o.ename 他管理的人的名字 from emp d, emp o where o.empno = d.mgr and d.empno in (select p.empno from emp p where p.empno in (select r.mgr from (select e.mgr, count(e.mgr) c from emp e group by e.mgr) r where r.c is (select max(w.z) from (select count(m.mgr) z from emp m group by m.mgr) w))) select d.ename 管理员工人数最多的人的名字, o.ename 他管理的人的名字 from emp d, emp o where o.empno = d.mgr and d.empno in (select p.empno from emp p where p.empno in (select r.mgr from (select e.mgr, count(e.mgr) c from emp e group by e.mgr) r where r.c is (select max(w.z) from (select count(m.mgr) z from emp m group by m.mgr) w)))
2.使用over()分析函数：
Sql代码


select e.ename 管理员工人数最多的人的名字, j.ename 他管理的人的名字 from emp e, emp j where j.empno = e.mgr and e.empno in (select distinct (r.mgr) from (select m.mgr, count(m.mgr) over(partition by m.mgr order by m.empno) t from emp m) r where r.t is (select max(y.h) from (select count(p.mgr) over(partition by p.mgr order by p.empno) h from emp p) y)) select e.ename 管理员工人数最多的人的名字, j.ename 他管理的人的名字 from emp e, emp j where j.empno = e.mgr and e.empno in (select distinct (r.mgr) from (select m.mgr, count(m.mgr) over(partition by m.mgr order by m.empno) t from emp m) r where r.t is (select max(y.h) from (select count(p.mgr) over(partition by p.mgr order by p.empno) h from emp p) y))
输出结果是一样的：
管理员工人数最多的人的名字他管理的人的名字BLAKEKING

延伸阅读