[转]over()函数的使用

oracle over()函数,从oracle 8i开始支持,后面的版本支持的比较好.
通常在做统计分析时我们都想尽可能多滴选择出原始列和统计值列,但是这样group by后面就必须跟随更多的列,使用分析函数可以避免使用group by时选择出来的列名必须出现在group by列表中的痛苦.

sql over的作用及用法

RANK ( ) OVER ( [query_partition_clause] order_by_clause )
DENSE_RANK ( ) OVER ( [query_partition_clause] order_by_clause )
可实现按指定的字段分组排序，对于相同分组字段的结果集进行排序,
其中PARTITION BY 为分组字段，ORDER BY 指定排序字段

over不能单独使用，要和分析函数：rank(),dense_rank(),row_number()等一起使用。
其参数：over（partition by columnname1 order by columnname2）
含义：按columname1指定的字段进行分组排序，或者说按字段columnname1的值进行分组排序。
例如：employees表中，有两个部门的记录：department_id ＝10和20
select department_id，rank（） over（partition by department_id order by salary) from employees就是指在部门10中进行薪水的排名，在部门20中进行薪水排名。如果是partition by org_id，则是在整个公司内进行排名。



--1、over() 注(9i下over括号内必须有内容,不允许为空,本文所有示例均在10g下运行的)
--所有人的总工资
select a.empno, a.ename, sum(a.sal) over() total from emp a;

EMPNO	ENAME	TOTAL
7369	SMITH	29025
7499	ALLEN	29025
7521	WARD	29025
7566	JONES	29025
7654	MARTIN	29025
7698	BLAKE	29025
7782	CLARK	29025
7788	SCOTT	29025
7839	KING	29025
7844	TURNER	29025
7876	ADAMS	29025
7900	JAMES	29025
7902	FORD	29025
7934	MILLER	29025

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

EMPNO	ENAME	DNAME	        DEPT_AVG
7934	MILLER	ACCOUNTING      $2,916.67
7839	KING	ACCOUNTING      $2,916.67
7782	CLARK	ACCOUNTING      $2,916.67
7876	ADAMS	RESEARCH	    $2,175.00
7902	FORD	RESEARCH	    $2,175.00
7566	JONES	RESEARCH	    $2,175.00
7369	SMITH	RESEARCH	    $2,175.00
7788	SCOTT	RESEARCH	    $2,175.00
7521	WARD	SALES	        $1,566.67
7844	TURNER	SALES	        $1,566.67
7499	ALLEN	SALES	        $1,566.67
7900	JAMES	SALES	        $1,566.67
7698	BLAKE	SALES	        $1,566.67
7654	MARTIN	SALES	        $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;

ENAME	ENAME	MGR	CNT
JONES	SCOTT	7566	2
JONES	FORD	7566	2
BLAKE	WARD	7698	5
BLAKE	TURNER	7698	5
BLAKE	ALLEN	7698	5
BLAKE	JAMES	7698	5
BLAKE	MARTIN	7698	5
CLARK	MILLER	7782	1
SCOTT	ADAMS	7788	1
KING	BLAKE	7839	3
KING	JONES	7839	3
KING	CLARK	7839	3
FORD	SMITH	7902	1

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

EMPNO	DEPTNO	ENAME	SAL	SUM
7876	20	ADAMS	1100.00	1100
7499	30	ALLEN	1600.00	2700
7698	30	BLAKE	2850.00	5550
7782	10	CLARK	2450.00	8000
7902	20	FORD	3000.00	11000
7900	30	JAMES	950.00	11950
7566	20	JONES	2975.00	14925
7839	10	KING	5000.00	19925
7654	30	MARTIN	1250.00	21175
7934	10	MILLER	1300.00	22475
7788	20	SCOTT	3000.00	25475
7369	20	SMITH	800.00	26275
7844	30	TURNER	1500.00	27775
7521	30	WARD	1250.00	29025

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

RK	EMPNO	ENAME	DNAME	         SAL
1	7839	KING	ACCOUNTING	     5000.00
2	7782	CLARK	ACCOUNTING	     2450.00
3	7934	MILLER	ACCOUNTING	     1300.00
1	7902	FORD	RESEARCH	     3000.00
1	7788	SCOTT	RESEARCH	     3000.00
3	7566	JONES	RESEARCH	     2975.00
1	7698	BLAKE	SALES	         2850.00
2	7499	ALLEN	SALES	         1600.00
3	7844	TURNER	SALES	         1500.00