oracle over()分析函数的例子

ogtblvqber

oracle over()函数,从oracle 8i开始支持,后面的版本支持的比较好.
通常在做统计分析时我们都想尽可能多滴选择出原始列和统计值列,但是这样group by后面就必须跟随更多的列,使用分析函数可以避免使用group by时选择出来的列名必须出现在group by列表中的痛苦.
--1、over() 注(9i下over括号内必须有内容,不允许为空,本文所有示例均在10g下运行的)
--所有人的总工资
select a.empno, a.ename, sum(a.sal) over() total from emp a;
EMPNOENAMETOTAL
7369SMITH29025
7499ALLEN29025
7521WARD29025
7566JONES29025
7654MARTIN29025
7698BLAKE29025
7782CLARK29025
7788SCOTT29025
7839KING29025
7844TURNER29025
7876ADAMS29025
7900JAMES29025
7902FORD29025
7934MILLER29025
--2、over(partition by ...) 分组统计
--统计部门的平均工资
select a.empno,
a.ename,
b.dname,
to_char(round(avg(a.sal) over(partition by b.dname), 2),'$999,999,999.99') dept_avg
from emp a, dept b
where a.deptno = b.deptno;
EMPNOENAMEDNAME        DEPT_AVG
7934MILLERACCOUNTING      $2,916.67
7839KINGACCOUNTING      $2,916.67
7782CLARKACCOUNTING      $2,916.67
7876ADAMSRESEARCH    $2,175.00
7902FORDRESEARCH    $2,175.00
7566JONESRESEARCH    $2,175.00
7369SMITHRESEARCH    $2,175.00
7788SCOTTRESEARCH    $2,175.00
7521WARDSALES        $1,566.67
7844TURNERSALES        $1,566.67
7499ALLENSALES        $1,566.67
7900JAMESSALES        $1,566.67
7698BLAKESALES        $1,566.67
7654MARTINSALES        $1,566.67
--查询出管理员工人数最多的人的名字和他管理的人的名字
select b.ename, t.ename, t.mgr, t.cnt
from (select a.empno,
a.ename,
a.mgr,
count(1) over(partition by a.mgr) cnt
from emp a) t,
emp b
where t.mgr = b.empno;
ENAMEENAMEMGRCNT
JONESSCOTT75662
JONESFORD75662
BLAKEWARD76985
BLAKETURNER76985
BLAKEALLEN76985
BLAKEJAMES76985
BLAKEMARTIN76985
CLARKMILLER77821
SCOTTADAMS77881
KINGBLAKE78393
KINGJONES78393
KINGCLARK78393
FORDSMITH79021
--3、over(order by ...) 排序统计
select a.empno,
a.deptno,
a.ename,
a.sal,
sum(a.sal) over(order by a.ename) sum
from emp a;
EMPNODEPTNOENAMESALSUM
787620ADAMS1100.001100
749930ALLEN1600.002700
769830BLAKE2850.005550
778210CLARK2450.008000
790220FORD3000.0011000
790030JAMES950.0011950
756620JONES2975.0014925
783910KING5000.0019925
765430MARTIN1250.0021175
793410MILLER1300.0022475
778820SCOTT3000.0025475
736920SMITH800.0026275
784430TURNER1500.0027775
752130WARD1250.0029025
--4、over(partition by ... order by ...) 分组排序统计
--统计各部门薪水前三名的人员
select t.*
from (select rank() over(partition by b.dname order by a.sal desc) rk,
a.empno,
a.ename,
b.dname,
a.sal
from emp a, dept b
where a.deptno = b.deptno) t
where t.rk <= 3;
RKEMPNOENAMEDNAME         SAL
17839KINGACCOUNTING     5000.00
27782CLARKACCOUNTING     2450.00
37934MILLERACCOUNTING     1300.00
17902FORDRESEARCH     3000.00
17788SCOTTRESEARCH     3000.00
37566JONESRESEARCH     2975.00
17698BLAKESALES         2850.00
27499ALLENSALES         1600.00
37844TURNERSALES         1500.00