溫馨提示×
您好，登錄后才能下訂單哦！
點擊 登錄注冊 即表示同意《億速云用戶服務條款》
您好，登錄后才能下訂單哦！
今天小編給大家分享一下Oracle中的over()函數(shù)怎么使用的相關知識點，內容詳細，邏輯清晰，相信大部分人都還太了解這方面的知識，所以分享這篇文章給大家參考一下，希望大家閱讀完這篇文章后有所收獲，下面我們一起來了解一下吧。
說明：聚合函數(shù)（如sum()、max()等）可以計算基于組的某種聚合值，但是聚合函數(shù)對于某個組只能返回一行記錄。若想對于某組返回多行記錄，則需要使用分析函數(shù)。
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()函數(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 --在按部門劃分的基礎上，工資從高到低分級，級別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;
對比查詢結果：左邊的是用一般的方法查詢結果，右邊的是分析函數(shù)查詢結果（兩種方法結果相同）
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;
使用分析函數(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，否則不起作用，將查詢到目前為止排序值最高字段的對應值
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.1 基礎
lead()/lag() over(partition by ... order by ...)
取前面/后面第n行記錄說明：
lead(列名,n,m): 當前記錄后面第n行記錄的列名的值，沒有則默認值為m；如果不帶參數(shù)n,m，則查找當前記錄后面第一行的記錄列名的值，沒有則默認值為null。
lag(列名,n,m): 當前記錄前面第n行記錄的列名的值，沒有則默認值為m；如果不帶參數(shù)n,m，則查找當前記錄前面第一行的記錄列名的值，沒有則默認值為null
1.3.2 示例
查詢個人工資與比自己高一位、低一位的工資的差額
使用分析函數(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;
查詢結果：
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;
查詢結果：
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后的新特性)實現(xiàn)
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)計該組中記錄數(shù)量 FROM EMP E;
查詢結果：
示例2（全統(tǒng)計）：為數(shù)據(jù)集統(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;
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;
結果如下：
將空值字段記錄放到最前或最后顯示
說明：通過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;
結果如下：
有時會有這樣的需求:如果數(shù)據(jù)排序后分為三部分，業(yè)務人員只關心其中的一部分，如何將這中間的三分之一數(shù)據(jù)拿出來呢?
這時比較好的選擇，就是使用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 --每個部門的分成三部分 FROM EMP E
結果如下：
說明：
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中的over()函數(shù)怎么使用”這篇文章的所有內容，感謝各位的閱讀！相信大家閱讀完這篇文章都有很大的收獲，小編每天都會為大家更新不同的知識，如果還想學習更多的知識，請關注億速云行業(yè)資訊頻道。
免責聲明：本站發(fā)布的內容（圖片、視頻和文字）以原創(chuàng)、轉載和分享為主，文章觀點不代表本網(wǎng)站立場，如果涉及侵權請聯(lián)系站長郵箱：is@yisu.com進行舉報，并提供相關證據(jù)，一經查實，將立刻刪除涉嫌侵權內容。
