Oracle中分析函數(shù)over()的用法及說明

更新時(shí)間：2023年02月28日 09:05:41 作者：愛吃牛肉的大老虎

這篇文章主要介紹了Oracle中分析函數(shù)over()的用法及說明，具有很好的參考價(jià)值，希望對大家有所幫助。如有錯(cuò)誤或未考慮完全的地方，望不吝賜教

1 over()分析函數(shù)

說明：聚合函數(shù)（如sum()、max()等）可以計(jì)算基于組的某種聚合值，但是聚合函數(shù)對于某個(gè)組只能返回一行記錄。若想對于某組返回多行記錄，則需要使用分析函數(shù)。

1.1 rank()/dense_rank()

1.1.1 基礎(chǔ)

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

說明：

over()在什么條件之上;
partition by 按哪個(gè)字段劃分組(如果要分組必須，有此關(guān)鍵字partition)；
order by 按哪個(gè)字段排序；

注意：

使用rank()/dense_rank()時(shí)，必須要帶order by否則非法
rank()/dense_rank()分級(jí)的區(qū)別

rank(): 跳躍排序，如果有兩個(gè)第一級(jí)時(shí)，接下來就是第三級(jí)。

dense_rank():連續(xù)排序，如果有兩個(gè)第一級(jí)時(shí)，接下來仍然是第二級(jí)。

1.1.2 示例

示例：查詢每個(gè)部門工資最高的員工信息

一般的寫法：

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()函數(shù)：

方法一：

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  --在按部門劃分的基礎(chǔ)上，工資從高到低分級(jí)，級(jí)別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;

對比查詢結(jié)果：左邊的是用一般的方法查詢結(jié)果，右邊的是分析函數(shù)查詢結(jié)果（兩種方法結(jié)果相同）

1.2 min()/max()

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

1.2.1 示例

查詢員工信息的同時(shí)，查詢員工工資與所在部門最低、最高工資的差額

一般的寫法：

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;

使用分析函數(shù)：

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) 排序規(guī)則只能為desc，否則不起作用，將查詢到目前為止排序值最高字段的對應(yīng)值
MIN() OVER(PARTITION BY .. ORDER BY .. ASC )排序規(guī)則只能為asc，否則不起作用，將查詢到目前為止排序值最低的字段的對應(yīng)值

如下：

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;

結(jié)果如下：

1.3 lead()/lag()

1.3.1 基礎(chǔ)

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

取前面/后面第n行記錄說明：

lead(列名,n,m): 當(dāng)前記錄后面第n行記錄的列名的值，沒有則默認(rèn)值為m；如果不帶參數(shù)n,m，則查找當(dāng)前記錄后面第一行的記錄列名的值，沒有則默認(rèn)值為null。
lag(列名,n,m): 當(dāng)前記錄前面第n行記錄的列名的值，沒有則默認(rèn)值為m；如果不帶參數(shù)n,m，則查找當(dāng)前記錄前面第一行的記錄列名的值，沒有則默認(rèn)值為null

1.3.2 示例

查詢個(gè)人工資與比自己高一位、低一位的工資的差額

使用分析函數(shù)：

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;

查詢結(jié)果：

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;

查詢結(jié)果：

1.5 ROW_NUMBER()

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

排序（應(yīng)用：分頁）

示例：

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;

查詢結(jié)果：

補(bǔ)充：類似分頁的操作還可以用rownum、fetch(Oracle12C后的新特性)實(shí)現(xiàn)

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,     --統(tǒng)計(jì)某組中的總計(jì)值
       AVG(E.SAL) OVER(PARTITION BY E.DEPTNO) AVG_SAL,     --統(tǒng)計(jì)某組中的平均值
       COUNT(E.SAL) OVER(PARTITION BY E.DEPTNO) COUNT_SAL  --按某列分組，并統(tǒng)計(jì)該組中記錄數(shù)量
  FROM EMP E;

查詢結(jié)果：

示例2（全統(tǒng)計(jì)）：為數(shù)據(jù)集統(tǒng)計(jì)部門銷售總和，全公司銷售總和，部門銷售均值，全公司銷售均值

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 基礎(chǔ)

上下范圍內(nèi)求值說明：

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針對當(dāng)前所有記錄的前一條、后一條記錄，也就是表中的所有記錄
       --unbounded：不受控制的，無限的
       --preceding：在...之前
       --following：在...之后
        BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) MAX_SAL
  FROM EMP E;

結(jié)果如下：

寫法二：

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：

對各部門進(jìn)行分組，并附帶顯示第一行至當(dāng)前行的匯總

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

結(jié)果如下：

示例3：

當(dāng)前行至最后一行的匯總

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

結(jié)果如下：

示例4：

當(dāng)前行的上一行(rownum-1)到當(dāng)前行的匯總

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

結(jié)果如下：

示例5：

當(dāng)前行的上一行(rownum-1)到當(dāng)前行的下兩行(rownum+2)的匯總

SELECT EMPNO,
       ENAME,
       DEPTNO,
       SAL,
       --注意ROWS BETWEEN 1 preceding AND 1 following 是指當(dāng)前行的上一行(rownum-1)到當(dāng)前行的下輛行(rownum+2)的匯總
       SUM(SAL) OVER(PARTITION BY DEPTNO 
                     ORDER BY ENAME 
                     ROWS BETWEEN 1 PRECEDING AND 2 FOLLOWING) max_sal
  FROM SCOTT.EMP;

結(jié)果如下：

2 其他

2.1 NULLS FIRST/LAST

將空值字段記錄放到最前或最后顯示

說明：通過RANK()、DENSE_RANK()、ROW_NUMBER()對記錄進(jìn)行全排列、分組排列取值，但有時(shí)候，會(huì)遇到空值的情況，空值會(huì)影響得到的結(jié)果的正確性

NULLS FIRST/LAST 可以幫助我們在處理含有空值的排序排列中，將空值字段記錄放到最前或最后顯示，幫助我們得到期望的結(jié)果

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

結(jié)果如下：

2.2 NTILE(n)

有時(shí)會(huì)有這樣的需求:如果數(shù)據(jù)排序后分為三部分，業(yè)務(wù)人員只關(guān)心其中的一部分，如何將這中間的三分之一數(shù)據(jù)拿出來呢?

這時(shí)比較好的選擇，就是使用NTILE函數(shù)

示例：

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  --每個(gè)部門的分成三部分
  FROM EMP E

結(jié)果如下：

2.3 keep(dense_rank first/last)

說明：

keep字面意思就是保持,也就是說保存滿足keep()括號(hào)內(nèi)條件的記錄，這里可以想象到，會(huì)有多條記錄的情況，即存在多個(gè)last或first的情況

dense_rank是排序策略
first/last是篩選策略

示例：

查看部門 D02 內(nèi)，銷售記錄時(shí)間最早，銷售量最小的記錄

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;