oracle——分析函数OVER ()

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

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

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

exp:

select t.empno,t.ename,sum(t.sal) from emp t

这样会报：ORA-00937:非单组分组函数；
结论：聚合函数只能返回1行值。

select t.empno,t.ename,sum(t.sal)over() sum from emp t

返回的结果：
[table]
|empno|ename|sum|
|7369|SMITH|142781.99|
|7499|ALLEN|142781.99|
|7521|WARD|142781.99|
|7566|JONES|142781.99|
|7654|MARTIN|142781.99|
|7698|BLAKE|142781.99|
|7782|CLARK|142781.99|
|7788|SCOTT|142781.99|
|7839|KING|142781.99|
|7844|TURNER|142781.99|
|7876|ADAMS|142781.99|
|7900|JAMES|142781.99|
|7902|FORD|142781.99|
|7934|MILLER|142781.99|
[/table]
结论：这就是每个组返回多行。

二.
over()、over(order by...)与over(partition by...)之间的区别
1.分析函数over()用在聚合函数（max(),sun()....）后面，可返回多行所有结果的聚合值；
exp:如上；

2.over(order by...)用在聚合函数（max(),sun()....）后面，可返回根据排序结果进行统计到当前行的聚合值(即“连续”统计)；
exp:

select t.empno,
       t.deptno,
       t.ename,
       t.sal,
       sum(t.sal) over(order by t.ename) sum
  from emp t

返回的结果：
[table]
|empno | deptno | ename | sal | sum|
|7876|20|ADAMS| 4646.11| 4646.11|
|7499|30|ALLEN| 8493.66 |13139.77|
|7698|30|BLAKE| 15047.69 |28187.46|
|7782|10|CLARK| 11823.85 |40011.31|
|7902|20|FORD| 14935.97| 54947.28|
|7900|30|JAMES| 4935.36 |59882.64|
|7566|20|JONES| 15086.30 |74968.94|
|7839|10|KING| 23841.13 |98810.07|
|7654|30|MARTIN| 6526.80 |105336.87|
|7934|10|MILLER| 6167.32 |111504.19|
|7788|20| SCOTT| 12710.16 |124214.35|
|7369|20| SMITH| 4089.17 |128303.52|
|7844|30| TURNER| 7843.77 |136147.29|
|7521|30| WARD| 6634.70 |142781.99|
[/table]
分析：第二行的sum=第一行里的sal+第二行里的sal；
第二行的sum=第一行里的sal+第二行里的sal+第三行里的sal；
.....
结论：返回值是根据排序后的结果，当前所在的行进行统计的。

3.over(partition by...)用在聚合函数（max(),sun()....）后面，可根据pratition by里指定的某一列来统计聚合值。
exp:

select t.empno,
       t.deptno,
       t.ename,
       t.sal,
       sum(t.sal) over(partition by t.deptno) sum
  from emp t

返回的结果：
[table]
|empno|deptno | ename | sal | sum|
|7782 |10| CLARK| 11823.85 |41832.3|
|7839 |10| KING| 23841.13 |41832.3|
|7934| 10| MILLER| 6167.32| 41832.3|
|7369| 20| SMITH| 4089.17| 51467.71|
|7876| 20| ADAMS| 4646.11| 51467.71|
|7902| 20| FORD| 14935.97| 51467.71|
|7788| 20| SCOTT| 12710.16| 51467.71|
|7566| 20| JONES| 15086.30| 51467.71|
|7499| 30| ALLEN| 8493.66| 49481.98|
|7698| 30 |BLAKE| 15047.69 |49481.98|
|7654 |30| MARTIN |6526.80 |49481.98|
|7900| 30 |JAMES| 4935.36| 49481.98|
|7844 |30| TURNER| 7843.77| 49481.98|
|7521 |30| WARD| 6634.70| 49481.98|
[/table]
分析：每个sum的值都是把deptno相同的sal值进行求和。
结论：根据pratition by里指定的某一列来统计聚合值。

三.一个综合的例子：
exp:
question:
按部门“连续”求总和;
answer:

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

返回的结果：
[table]
|empno|deptno | ename | sal | sum|
|7782| 10| CLARK| 11823.85| 11823.85|
|7839| 10| KING| 23841.13| 35664.98|
|7934| 10| MILLER| 6167.32 |41832.3|
|7876| 20| ADAMS| 4646.11 |4646.11|
|7902| 20| FORD| 14935.97 |19582.08|
|7566| 20| JONES| 15086.30| 34668.38|
|7788| 20| SCOTT| 12710.16 |47378.54|
|7369| 20| SMITH| 4089.17| 51467.71|
|7499| 30| ALLEN| 8493.66| 8493.66|
|7698| 30| BLAKE| 15047.69| 23541.35|
|7900| 30| JAMES| 4935.36 |28476.71|
|7654| 30| MARTIN| 6526.80| 35003.51|
|7844| 30| TURNER| 7843.77| 42847.28|
|7521| 30| WARD| 6634.70 |49481.98|
[/table]
分析：先根据partition by 进行分组，然后再根据order by 进行排序“连续”统计。

四.一个实际的例子来说明over()分析函数在代码上能简化和提高效率。
question:
查询出管理员工人数最多的人的名字和他管理的人的名字
answer:
1.普通的方法：

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()分析函数：

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

输出结果是一样的：
[table]
|管理员工人数最多的人的名字|他管理的人的名字|
|BLAKE| KING|
[/table]