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

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

這篇文章主要介紹了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()分級的區(qū)別

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

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

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) 排序規(guī)則只能為desc，否則不起作用，將查詢到目前為止排序值最高字段的對應值
MIN() OVER(PARTITION BY .. ORDER BY .. ASC )排序規(guī)則只能為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,     --統(tǒng)計某組中的總計值
       AVG(E.SAL) OVER(PARTITION BY E.DEPTNO) AVG_SAL,     --統(tǒng)計某組中的平均值
       COUNT(E.SAL) OVER(PARTITION BY E.DEPTNO) COUNT_SAL  --按某列分組，并統(tǒng)計該組中記錄數量
  FROM EMP E;

查詢結果：

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

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)

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

這時比較好的選擇，就是使用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;

總結

以上為個人經驗，希望能給大家一個參考，也希望大家多多支持腳本之家。

您可能感興趣的文章:

Oracle 中 decode 函數用法
這篇文章主要介紹了Oracle 中 decode 函數用法的相關資料,需要的朋友可以參考下
2017-10-10
Oracle數據庫技術(38)
Oracle數據庫技術(38)...
2007-03-03
PLSQL導入dmp文件的詳細完整步驟
plsql導入.sql和.dmp文件時,會經常用到,對于初學者來說可能沒有那么簡單,畢竟oracle數據庫比較麻煩,下面這篇文章主要給大家介紹了關于PLSQL導入dmp文件的詳細完整步驟,需要的朋友可以參考下
2023-05-05
使用Oracle通過gateway連接MSSQL的詳細流程
oracle Gateway是Oracle公司提供的一種訪問非Oracle數據源的方法,它可以將非Oracle數據源(如SQLserver、mysql等)的數據看作是oracle數據庫中的表,從而可以使用SQL和PLSQL等Oracle語言來訪問這些數據,本文介紹了使用Oracle通過gateway連接MSSQL的詳細流程
2024-12-12
Oracle新增字段和注釋代碼示例
Oracle數據庫中給字段添加注釋是非常有用的,在我們對數據庫進行分析和維護時,通過字段注釋可以更容易地了解這些字段的含義,這篇文章主要給大家介紹了關于Oracle新增字段和注釋的相關資料,需要的朋友可以參考下
2023-09-09
oracle數據與文本導入導出源碼示例
這篇文章主要介紹了oracle數據與文本導入導出源碼示例，具有一定參考價值，需要的朋友可以了解下。
2017-10-10
Oracle如何查詢表索引和索引字段
這篇文章主要介紹了Oracle如何查詢表索引和索引字段問題，具有很好的參考價值，希望對大家有所幫助。如有錯誤或未考慮完全的地方，望不吝賜教
2023-07-07
關于ORACLE通過file_id與block_id定位數據庫對象遇到的問題引發(fā)的思考
這篇文章主要介紹了關于ORACLE通過file_id與block_id定位數據庫對象遇到的問題引發(fā)的思考,非常不錯，具有參考借鑒價值，需要的朋友可以參考下
2017-03-03
oracle中修改表名的幾種方式
這篇文章主要介紹了oracle中修改表名的幾種方式,需要的朋友可以參考下
2014-07-07
oracle 虛擬專用數據庫詳細介紹
這篇文章詳細介紹了oracle 虛擬專用數據庫，對行級別和列級別分別舉了代碼實例并進行分析，內容比較詳細，需要的朋友可以參考下。
2017-09-09