Oracle分析函数Over()

一、Over()分析函数

说明：聚合函数（如sum()、max()等）可以计算基于组的某种聚合值，但是聚合函数对于某个组只能返回一行记录。若想对于某组返回多行记录，则需要使用分析函数。

1、rank()/dense_rank over(partition by ... order by ...)

说明：over()在什么条件之上; 

　　partition by 按哪个字段划分组；

　　order by 按哪个字段排序；

注意：

　　（1）使用rank()/dense_rank() 时，必须要带order by否则非法

　　（2）rank()/dense_rank()分级的区别：

　　　　rank(): 跳跃排序，如果有两个第一级时，接下来就是第三级。
　　　　dense_rank(): 连续排序，如果有两个第一级时，接下来仍然是第二级。

示例：查询每个部门工资最高的员工信息

一般的写法：

SELECT E.ENAME, E.JOB, E.SAL, E.DEPTNO
  FROM SCOTT.EMP E,
       (SELECT E.DEPTNO, MAX(E.SAL) SAL FROM SCOTT.EMP E GROUP BY E.DEPTNO) ME
 WHERE E.DEPTNO = ME.DEPTNO
   AND E.SAL = ME.SAL;

使用Over()函数：

方法一：

SELECT E.ENAME, E.JOB, E.SAL, E.DEPTNO
  FROM (SELECT E.ENAME,
               E.JOB,
               E.SAL,
               E.DEPTNO,
               RANK() OVER(PARTITION BY E.DEPTNO ORDER BY E.SAL DESC) RANK  --在按部门划分的基础上，工资从高到低分级，级别RANK从1开始依次递增
          FROM EMP E) E
 WHERE E.RANK = 1 ;

方法二：

 SELECT E.ENAME, E.JOB, E.SAL, E.DEPTNO
  FROM (SELECT E.ENAME,
               E.JOB,
               E.SAL,
               E.DEPTNO,
               DENSE_RANK() OVER(PARTITION BY E.DEPTNO ORDER BY E.SAL DESC) RANK
          FROM EMP E) E
 WHERE E.RANK = 1;

对比查询结果：左边的是用一般的方法查询结果，右边的是分析函数查询结果（两种方法结果相同）

 

2、min()/max() over(partition by ...)

 

查询员工信息的同时，查询员工工资与所在部门最低、最高工资的差额

一般的写法：

SELECT E.ENAME,
       E.JOB,
       E.SAL,
       E.DEPTNO,
       E.SAL - ME.MIN_SAL DIFF_MIN_SAL,
       ME.MAX_SAL - E.SAL DIFF_MAX_SAL
  FROM SCOTT.EMP E,
       (SELECT E.DEPTNO, MIN(E.SAL) MIN_SAL, MAX(E.SAL) MAX_SAL
          FROM SCOTT.EMP E
         GROUP BY E.DEPTNO) ME
 WHERE E.DEPTNO = ME.DEPTNO
 ORDER BY E.DEPTNO, E.SAL;

使用分析函数：

SELECT E.ENAME,
       E.JOB,
       E.SAL,
       E.DEPTNO,
       MIN(E.SAL) OVER(PARTITION BY E.DEPTNO) MIN_SAL,
       MAX(E.SAL)