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

2023-02-28 18:02:35

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;