oracle over()分析函数的例子
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;EMPNOENAMETOTAL7369SMITH290257499ALLEN290257521WARD290257566JONES290257654MARTIN290257698BLAKE290257782CLARK290257788SCOTT290257839KING290257844TURNER290257876ADAMS290257900JAMES290257902FORD290257934MILLER29025--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_AVG7934MILLERACCOUNTING $2,916.677839KINGACCOUNTING $2,916.677782CLARKACCOUNTING $2,916.677876ADAMSRESEARCH $2,175.007902FORDRESEARCH $2,175.007566JONESRESEARCH $2,175.007369SMITHRESEARCH $2,175.007788SCOTTRESEARCH $2,175.007521WARDSALES $1,566.677844TURNERSALES $1,566.677499ALLENSALES $1,566.677900JAMESSALES $1,566.677698BLAKESALES $1,566.677654MARTINSALES $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;ENAMEENAMEMGRCNTJONESSCOTT75662JONESFORD75662BLAKEWARD76985BLAKETURNER76985BLAKEALLEN76985BLAKEJAMES76985BLAKEMARTIN76985CLARKMILLER77821SCOTTADAMS77881KINGBLAKE78393KINGJONES78393KINGCLARK78393FORDSMITH79021--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;EMPNODEPTNOENAMESALSUM787620ADAMS1100.001100749930ALLEN1600.002700769830BLAKE2850.005550778210CLARK2450.008000790220FORD3000.0011000790030JAMES950.0011950756620JONES2975.0014925783910KING5000.0019925765430MARTIN1250.0021175793410MILLER1300.0022475778820SCOTT3000.0025475736920SMITH800.0026275784430TURNER1500.0027775752130WARD1250.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 SAL17839KINGACCOUNTING 5000.0027782CLARKACCOUNTING 2450.0037934MILLERACCOUNTING 1300.0017902FORDRESEARCH 3000.0017788SCOTTRESEARCH 3000.0037566JONESRESEARCH 2975.0017698BLAKESALES 2850.0027499ALLENSALES 1600.0037844TURNERSALES 1500.00
