Oracle 分析函数 over() 续

http://penggle.iteye.com/blog/1122467

 

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;

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

--查询出管理员工人数最多的人的名字和他管理的人的名字
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;

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

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