«

Oracle中的over()函数怎么使用

时间：2024-3-10 22:21     作者：韩俊     分类： Mysql

今天小编给大家分享一下Oracle中的over()函数怎么使用的相关知识点，内容详细，逻辑清晰，相信大部分人都还太了解这方面的知识，所以分享这篇文章给大家参考一下，希望大家阅读完这篇文章后有所收获，下面我们一起来了解一下吧。

1 over()分析函数

说明：聚合函数（如 

sum()

、

max()

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


1.1 rank()/dense_rank()

1.1.1 基础 

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

说明： 


  • over()
    在什么条件之上; 



  • partition by
    按哪个字段划分组(如果要分组必须，有此关键字 
    partition
    )； 



  • order by
    按哪个字段排序；


注意：

  • 使用 

    rank()/dense_rank()
    时，必须要带 
    order by
    否则非法 



  • rank()/dense_rank()
    分级的区别 



rank()

: 跳跃排序，如果有两个第一级时，接下来就是第三级。



dense_rank()

:连续排序，如果有两个第一级时，接下来仍然是第二级。


1.1.2 示例

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

一般的写法： 

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;

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

1.2 min()/max() 

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

1.2.1 示例

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

一般的写法： 

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

排序条件



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



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


如下： 

SELECT E.ENAME,
       E.JOB,
       E.SAL,
       E.DEPTNO,
       MIN(E.SAL) OVER(PARTITION BY E.DEPTNO) MIN_SAL01,
       MAX(E.SAL) OVER(PARTITION BY E.DEPTNO) MAX_SAL01,
       MIN(E.SAL) OVER(PARTITION BY E.DEPTNO ORDER BY E.SAL) MIN_SAL02,
       MAX(E.SAL) OVER(PARTITION BY E.DEPTNO ORDER BY E.SAL) MAX_SAL02, --不起作用
       MIN(E.SAL) OVER(PARTITION BY E.DEPTNO ORDER BY E.SAL DESC) MIN_SAL03, --不起作用
       MAX(E.SAL) OVER(PARTITION BY E.DEPTNO ORDER BY E.SAL DESC) MAX_SAL03,
       MIN(E.SAL) OVER(PARTITION BY E.DEPTNO ORDER BY E.SAL ASC) MIN_SAL04,
       MAX(E.SAL) OVER(PARTITION BY E.DEPTNO ORDER BY E.SAL ASC) MAX_SAL04, --不起作用
       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;

结果如下：

1.3 lead()/lag()

1.3.1 基础 

lead()/lag()  over(partition by ... order by ...)

取前面/后面第 

n

行记录说明：



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



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


1.3.2 示例

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

使用分析函数： 

SELECT E.ENAME,
        E.JOB,
        E.SAL,
        E.DEPTNO,
        LEAD(E.SAL, 1, 0) OVER(PARTITION BY E.DEPTNO ORDER BY E.SAL) LEAD_SAL, --记录后面第n行记录
        LAG(E.SAL, 1, 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 EMP E;

查询结果：

1.4 FIRST_VALUE/LAST_VALUE() 

FIRST_VALUE/LAST_VALUE()  OVER(PARTITION BY ...)

取首尾记录示例： 

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

查询结果：

1.5 ROW_NUMBER() 

ROW_NUMBER() OVER(PARTITION BY.. ORDER BY ..)

排序（应用：分页）

示例： 

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

查询结果：

补充：类似分页的操作还可以用 

rownum

、

fetch

(

Oracle12C

后的新特性)实现


1.6 sum/avg/count() 

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

示例1： 

SELECT E.ENAME,
       E.JOB,
       E.SAL,
       E.DEPTNO,
       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 EMP E;

查询结果：

示例2（全统计）：为数据集统计部门销售总和，全公司销售总和，部门销售均值，全公司销售均值 

SELECT A.DEPT_ID,
       A.SALE_DATE,
       A.GOODS_TYPE,
       A.SALE_CNT,
       SUM(A.SALE_CNT) OVER(PARTITION BY A.DEPT_ID) DEPT_TOTAL, --部门销售总和
       SUM(A.SALE_CNT) OVER() CMP_TOTAL,  --公司销售总额
       AVG(A.SALE_CNT) OVER(PARTITION BY A.DEPT_ID) DEPT_AVG, --部门销售均值
       AVG(A.SALE_CNT) OVER() CMP_AVG  --公司销售均值
  FROM LEARN_FUN_KEEP A;

1.7 rows/range 

rows/range  between … preceding and … following

1.7.1 基础

上下范围内求值说明： 


  • unbounded
    ：不受控制的，无限的 



  • preceding
    ：在…之前 



  • following
    ：在…之后 


rows between … preceding and … following

示例1：

显示各部门员工的工资，并附带显示该部门的最高工资

写法一： 

SELECT E.DEPTNO,
       E.EMPNO,
       E.ENAME,
       E.SAL,
       LAST_VALUE(E.SAL) OVER(PARTITION BY E.DEPTNO ORDER BY E.SAL ROWS
       /*MAX(E.SAL) OVER(PARTITION BY E.DEPTNO ORDER BY E.SAL ROWS*/
       --unbounded preceding and unbouned following针对当前所有记录的前一条、后一条记录，也就是表中的所有记录
       --unbounded：不受控制的，无限的
       --preceding：在...之前
       --following：在...之后
        BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) MAX_SAL
  FROM EMP E;

结果如下：

写法二： 

SELECT E.DEPTNO,
       E.EMPNO,
       E.ENAME,
       E.SAL,
       MAX(E.SAL) OVER(PARTITION BY E.DEPTNO
       /*ORDER BY E.SAL*/) MAX_SAL
  FROM EMP E;

示例2：

对各部门进行分组，并附带显示第一行至当前行的汇总 

SELECT EMPNO,
       ENAME,
       DEPTNO,
       SAL,
       --注意ROWS BETWEEN unbounded preceding AND current row  是指第一行至当前行的汇总
       SUM(SAL) OVER(PARTITION BY DEPTNO 
                     ORDER BY ENAME 
                     ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) max_sal
  FROM SCOTT.EMP;

结果如下：

示例3：

当前行至最后一行的汇总 

SELECT EMPNO,
       ENAME,
       DEPTNO,
       SAL,
       --注意ROWS BETWEEN current row AND unbounded following 指当前行到最后一行的汇总
       SUM(SAL) OVER(PARTITION BY DEPTNO 
                     ORDER BY ENAME 
                     ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING) max_sal
  FROM SCOTT.EMP;

结果如下：

示例4：

当前行的上一行( 

rownum-1

)到当前行的汇总


SELECT EMPNO,
       ENAME,
       DEPTNO,
       SAL,
       --注意ROWS BETWEEN 1 preceding AND current row 是指当前行的上一行(rownum-1)到当前行的汇总 
       SUM(SAL) OVER(PARTITION BY DEPTNO 
                     ORDER BY ENAME ROWS 
                     BETWEEN 1 PRECEDING AND CURRENT ROW) max_sal
  FROM SCOTT.EMP;

结果如下：

示例5：

当前行的上一行( 

rownum-1

)到当前行的下两行(

rownum+2

)的汇总


SELECT EMPNO,
       ENAME,
       DEPTNO,
       SAL,
       --注意ROWS BETWEEN 1 preceding AND 1 following 是指当前行的上一行(rownum-1)到当前行的下辆行(rownum+2)的汇总
       SUM(SAL) OVER(PARTITION BY DEPTNO 
                     ORDER BY ENAME 
                     ROWS BETWEEN 1 PRECEDING AND 2 FOLLOWING) max_sal
  FROM SCOTT.EMP;

结果如下：

2 其他

2.1 NULLS FIRST/LAST

将空值字段记录放到最前或最后显示

说明：通过 

RANK()

、

DENSE_RANK()

、

ROW_NUMBER()

对记录进行全排列、分组排列取值，但有时候，会遇到空值的情况，空值会影响得到的结果的正确性



NULLS FIRST/LAST

可以帮助我们在处理含有空值的排序排列中，将空值字段记录放到最前或最后显示，帮助我们得到期望的结果


SELECT E.ENAME,
       E.JOB,
       E.SAL,
       E.DEPTNO,
       RANK() OVER(PARTITION BY E.DEPTNO ORDER BY E.SAL NULLS LAST)
  FROM EMP E;

结果如下：

2.2 NTILE(n)

有时会有这样的需求:如果数据排序后分为三部分，业务人员只关心其中的一部分，如何将这中间的三分之一数据拿出来呢?

这时比较好的选择，就是使用NTILE函数

示例： 

SELECT E.ENAME,
       E.JOB,
       E.SAL,
       E.DEPTNO,
       NTILE(3) OVER(ORDER BY E.SAL DESC NULLS LAST) ALL_CMP, --若只取前三分之一，all_cmp=1即可，若只取中间三分之一，all_cmp=2即可
       NTILE(3) OVER(PARTITION BY E.DEPTNO ORDER BY E.SAL DESC NULLS LAST) ALL_DEPT  --每个部门的分成三部分
  FROM EMP E

结果如下：

2.3 keep(dense_rank first/last)

说明： 


keep

字面意思就是保持,也就是说保存满足

keep()

括号内条件的记录，这里可以想象到，会有多条记录的情况，即存在多个

last

或

first

的情况



  • dense_rank
    是排序策略 



  • first/last
    是筛选策略


示例：

查看部门 

D02

内，销售记录时间最早，销售量最小的记录


SELECT A.DEPT_ID,
       MIN(A.SALE_CNT) KEEP(DENSE_RANK FIRST ORDER BY A.SALE_DATE) MIN_EARLY_DATE
  FROM LEARN_FUN_KEEP A
 WHERE A.DEPT_ID = 'D02'
 GROUP BY A.DEPT_ID;

标签: mysql oracle

热门推荐

1 检查mysql是否成功启动的方法(bat+bash)
2 PHP使用Redis替代文件存储Session的方法
3 PHP实现基于面向对象的mysqli扩展库增删改查操作工具类
4 MySQL解决SQL注入的另类方法详解
5 ubuntu下mysql版本升级到5.7
6 MySQL编程中的6个实用技巧
7 Mysql查询语句优化技巧
8 详解mysql 获取当前日期及格式化
9 redis分布式锁如何优化
10 mysql备份脚本 mysqldump使用方法详解

日历

标签

hbase maven eclipse sybase PostgreSQL spring emcache oracle mongodb redis nginx golang java vue android ios mysql linux 微信小程序 css html php教程 javascript python php

搜索

最新文章

热门文章