数据库--oracle--分析函数OVER ()

来源：互联网发布：淘宝宝贝信息重复编辑：程序博客网时间：2024/06/05 07:17

准备工作：
table:oracle用户scott下的emp表；

一.
Oracle 从8.1.6开始提供分析函数，分析函数用于计算基于组的某种聚合值，它和聚合函数的不同之处是

　　对于每个组返回多行，而聚合函数对于每个组只返回一行。

exp:

Sql代码  
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  

返回的结果：
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  

返回的结果：
empnodeptnoename    sal      sum787620ADAMS4646.114646.11749930ALLEN8493.6613139.77769830BLAKE15047.6928187.46778210CLARK11823.8540011.31790220FORD14935.9754947.28790030JAMES4935.3659882.64756620JONES15086.3074968.94783910KING23841.1398810.07765430MARTIN6526.80105336.87793410MILLER6167.32111504.19778820SCOTT12710.16124214.35736920SMITH4089.17128303.52784430TURNER7843.77136147.29752130WARD6634.70142781.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  

返回的结果：
empnodeptno ename sal sum778210CLARK11823.8541832.3783910KING23841.1341832.3793410MILLER6167.3241832.3736920SMITH4089.1751467.71787620ADAMS4646.1151467.71790220FORD14935.9751467.71778820SCOTT12710.1651467.71756620JONES15086.3051467.71749930ALLEN8493.6649481.98769830BLAKE15047.6949481.98765430MARTIN6526.8049481.98790030JAMES4935.3649481.98784430TURNER7843.7749481.98752130WARD6634.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  

返回的结果：
empnodeptno ename sal sum778210CLARK11823.8511823.85783910KING23841.1335664.98793410MILLER6167.3241832.3787620ADAMS4646.114646.11790220FORD14935.9719582.08756620JONES15086.3034668.38778820SCOTT12710.1647378.54736920SMITH4089.1751467.71749930ALLEN8493.668493.66769830BLAKE15047.6923541.35790030JAMES4935.3628476.71765430MARTIN6526.8035003.51784430TURNER7843.7742847.28752130WARD6634.7049481.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)))  

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))  

输出结果是一样的：
管理员工人数最多的人的名字他管理的人的名字BLAKEKING

0 0