Oracle分析函数Over() 详述

一、Over()分析函数

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

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

3、lead()/lag() over(partition by ... order by ...) 取前面/后面第n行记录

4、FIRST_VALUE/LAST_VALUE() OVER(PARTITION BY ...) 取首尾记录

5、ROW_NUMBER() OVER(PARTITION BY.. ORDER BY ..) 排序（应用：分页）

6、sum/avg/count() over(partition by ..)

 7、 rows/range between … preceding and … following 上下范围内求值

 rows between … preceding and … following

 二、其他

1、NULLS FIRST/LAST 将空值字段记录放到最前或最后显示

2、NTILE(n)

3、keep(dense_rank first/last)

一、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) OVER(PARTITION BY E.DEPTNO) MAX_SAL, NVL(E.SAL - MIN(E.SAL) OVER(PARTITION BY E.DEPTNO), 0) DIFF_MIN_SAL, NVL(MAX(E.SAL) OVER(PARTITION BY E.DEPTNO) - E.SAL, 0) DIFF_MAX_SAL FROM EMP E;

注：这里没有排序条件，若加上order by 排序条件，