oracle的分析函数over

参考地址：https://www.cnblogs.com/chinas/p/7058771.html?utm_source=itdadao&utm_medium=referral#_lab2_0_0

说明：聚合函数（如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 (SELECT E.ENAME,E.DEPTNO,RANK() OVER(PARTITION BY E.DEPTNO ORDER BY E.SAL DESC) RANK 
          FROM SCOTT.EMP E) E
 WHERE E.RANK = 1 

方法二：

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

结果：

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

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

SELECT E.ENAME,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 SCOTT.EMP E;

结果：

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

MAX() OVER(PARTITION BY .. ORDER BY .. DESC) 排序规则只能为desc，否则不起作用，将查询到目前为止排序值最高字段的对应值
MIN() OVER(PARTITION BY .. ORDER BY .. ASC ) 排序规则只能为asc，否则不起作用，将查询到目前为止排序值最低的字段的对应值，

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

说明：

lead(列名,n,m): 当前记录后面第n行记录的<列名>的值，没有则默认值为m；如果不带参数n,m，则查找当前记录后面第一行的记录<列名>的值，没有则默认值为null。
lag(列名,m): 当前记录前面第n行记录的<列名>的值，没有则默认值为m；如果不带参数n,m，则查找当前记录前面第一行的记录<列名>的值，没有则默认值为null。

?示例：查询个人工资与比自己高一位、低一位的工资的差额

SELECT E.ENAME,LEAD(E.SAL,1,0) OVER(PARTITION BY E.DEPTNO ORDER BY E.SAL) LEAD_SAL,--记录后面第n行记录
        LAG(E.SAL,0) OVER(PARTITION BY E.DEPTNO ORDER BY E.SAL) LAG_SAL,--记录前面第n行记录
        NVL(LEAD(E.SAL) OVER(PARTITION BY E.DEPTNO ORDER BY E.SAL) - E.SAL,0) DIFF_LEAD_SAL,NVL(E.SAL - LEAD(E.SAL) OVER(PARTITION BY E.DEPTNO ORDER BY E.SAL),0) DIFF_LAG_SAL
   FROM SCOTT.EMP E;

结果：

?

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

SELECT E.EMPNO,E.ENAME,E.MGR,FIRST_VALUE(E.SAL) OVER(PARTITION BY E.DEPTNO) FIRST_SAL,LAST_VALUE(E.SAL) OVER(PARTITION BY E.DEPTNO) LAST_SAL
   FROM SCOTT.EMP E;

结果：

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

SELECT E.ENAME,E.ROW_NUM
   FROM (SELECT E.ENAME,ROW_NUMBER() OVER(PARTITION BY E.DEPTNO ORDER BY E.SAL) ROW_NUM
           FROM SCOTT.EMP E) E
  WHERE E.ROW_NUM > 3;

补充：

类似分页的操作还可以用rownum、fetch(Oracle12C后的新特性)实现

结果：

?

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

SELECT E.ENAME,SUM(E.SAL) OVER(PARTITION BY E.DEPTNO) SUM_SAL,--统计某组中的总计值
       AVG(E.SAL) OVER(PARTITION BY E.DEPTNO) AVG_SAL,--统计某组中的平均值
       COUNT(E.SAL) OVER(PARTITION BY E.DEPTNO) COUNT_SAL  --按某列分组，并统计该组中记录数量
  FROM SCOTT.EMP E;

结果：

（编辑：青岛站长网）

【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!