Oracle 分析函數詳解

在Oracle數據庫中，分析函數（Analytical Functions）是一類非常強大的工具，它們允許在查詢結果集上進行復雜的計算和分析，而無需使用自連接或子查詢等復雜操作。本文將詳細介紹Oracle分析函數的使用方法和應用場景，包括排名函數、統計函數、取首尾記錄、取上下行記錄以及滑動窗口等功能。

一、排名函數

1. RANK()

RANK()函數用于計算排序后的排名，相同值排名相同，排名之間可能有間隔。

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

說明：PARTITION BY E.DEPTNO將數據按部門分組，ORDER BY E.SAL DESC在每個部門內按工資降序排列，RANK()為每行分配排名。

2. DENSE_RANK()

DENSE_RANK()函數用于計算排序后的排名，相同值排名相同，但排名之間沒有間隔。

SELECT E.ENAME, E.JOB, E.SAL, E.DEPTNO,DENSE_RANK() OVER(PARTITION BY E.DEPTNO ORDER BY E.SAL DESC) AS RANK
FROM EMP E;

3. ROW_NUMBER()

ROW_NUMBER()函數為查詢結果中的每一行分配一個唯一的序號。

SELECT E.ENAME, E.JOB, E.SAL, E.DEPTNO,ROW_NUMBER() OVER(PARTITION BY E.DEPTNO ORDER BY E.SAL DESC) AS ROW_NUM
FROM EMP E;

4. NTILE(n)

NTILE(n)函數將結果集分成指定數量的組，并為每一行分配組編號。

SELECT E.ENAME, E.JOB, E.SAL, E.DEPTNO,NTILE(4) OVER(PARTITION BY E.DEPTNO ORDER BY E.SAL DESC) AS QUARTILE
FROM EMP E;

二、統計函數

OVER關鍵字可以與聚合函數（如SUM()、AVG()、COUNT()等）結合使用，對分組數據進行統計。

SELECT E.DEPTNO, E.ENAME, E.SAL,SUM(E.SAL) OVER(PARTITION BY E.DEPTNO) AS SUM_SAL,AVG(E.SAL) OVER(PARTITION BY E.DEPTNO) AS AVG_SAL,COUNT(*) OVER(PARTITION BY E.DEPTNO) AS COUNT_EMP
FROM EMP E;

說明：PARTITION BY E.DEPTNO將數據按部門分組，然后在每個分組內分別計算工資總和、平均工資和員工數量。

三、取首尾記錄

OVER關鍵字可以與FIRST_VALUE()和LAST_VALUE()函數結合使用，獲取分組中的首尾記錄。

SELECT E.DEPTNO, E.ENAME, E.SAL,FIRST_VALUE(E.ENAME) OVER(PARTITION BY E.DEPTNO ORDER BY E.SAL DESC) AS MAX_SAL_EMP,LAST_VALUE(E.ENAME) OVER(PARTITION BY E.DEPTNO ORDER BY E.SAL DESC) AS MIN_SAL_EMP
FROM EMP E;

說明：PARTITION BY E.DEPTNO將數據按部門分組，ORDER BY E.SAL DESC在每個部門內按工資降序排列，FIRST_VALUE()獲取每個部門工資最高的員工姓名，LAST_VALUE()獲取工資最低的員工姓名。

四、取上下行記錄

OVER關鍵字可以與LEAD()和LAG()函數結合使用，獲取當前行的上下行記錄。

SELECT E.ENAME, E.SAL,LAG(E.SAL, 1, 0) OVER(ORDER BY E.SAL) AS PREV_SAL,LEAD(E.SAL, 1, 0) OVER(ORDER BY E.SAL) AS NEXT_SAL,E.SAL - LAG(E.SAL, 1, 0) OVER(ORDER BY E.SAL) AS DIFF_PREV,LEAD(E.SAL, 1, 0) OVER(ORDER BY E.SAL) - E.SAL AS DIFF_NEXT
FROM EMP E;

說明：ORDER BY E.SAL按工資升序排列，LAG(E.SAL, 1, 0)獲取當前行的前一行工資，LEAD(E.SAL, 1, 0)獲取當前行的后一行工資，然后計算當前行工資與前后行工資的差額。

五、滑動窗口

通過ROWS BETWEEN或RANGE BETWEEN子句，OVER關鍵字可以指定窗口范圍，實現滑動窗口計算。

SELECT E.DEPTNO, E.ENAME, E.SAL,SUM(E.SAL) OVER(PARTITION BY E.DEPTNOORDER BY E.SALROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS CUM_SUM_SAL
FROM EMP E;

說明：PARTITION BY E.DEPTNO將數據按部門分組，ORDER BY E.SAL在每個部門內按工資升序排列，ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW定義窗口范圍為從分組的第一行到當前行，SUM(E.SAL)計算從分組的第一行到當前行的工資累計和。