Oracle 分析函數完全指南：ROW_NUMBER、RANK、DENSE_RANK、LAG/LEAD 窗口函數語法與實戰模式

分析函數核心語法

analytic_function([arguments]) OVER (
    [PARTITION BY partition_expr [, ...]]
    [ORDER BY order_expr [ASC|DESC] [NULLS FIRST|LAST] [, ...]]
    [WINDOWING_CLAUSE]
)

四大排序型函數對比

實戰範例：員工薪資排名

SELECT 
    deptno, ename, sal,
    ROW_NUMBER() OVER (PARTITION BY deptno ORDER BY sal DESC) AS rn,
    RANK() OVER (PARTITION BY deptno ORDER BY sal DESC) AS rk,
    DENSE_RANK() OVER (PARTITION BY deptno ORDER BY sal DESC) AS drk
FROM emp
ORDER BY deptno, sal DESC;

結果對比

偏移型函數：LAG / LEAD

語法與參數

LAG(expr [, offset [, default]]) OVER (PARTITION BY ... ORDER BY ...)
LEAD(expr [, offset [, default]]) OVER (PARTITION BY ... ORDER BY ...)

• expr：要訪問的列/表達式
• offset：偏移行數 (默認 1)，正數向前/向後
• default：超出分區邊界時返回值 (默認 NULL)

實戰：環比增長率計算

SELECT 
    deptno, ename, hiredate, sal,
    LAG(sal) OVER (PARTITION BY deptno ORDER BY hiredate) AS prev_sal,
    LEAD(sal) OVER (PARTITION BY deptno ORDER BY hiredate) AS next_sal,
    ROUND((sal - LAG(sal) OVER (PARTITION BY deptno ORDER BY hiredate)) 
          / LAG(sal) OVER (PARTITION BY deptno ORDER BY hiredate) * 100, 2) AS growth_pct
FROM emp;

窗口框架子句

-- RANGE/ROWS BETWEEN start AND end
SUM(sal) OVER (
    PARTITION BY deptno 
    ORDER BY hiredate
    ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW  -- 累計
) AS cumulative_sal

AVG(sal) OVER (
    PARTITION BY deptno 
    ORDER BY hiredate
    ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING  -- 移動平均
) AS moving_avg

經典應用模式

1. 高效分頁查詢 (12c+ OFFSET/FETCH 替代方案)

SELECT * FROM (
    SELECT e.*, ROW_NUMBER() OVER (ORDER BY empno) rn
    FROM emp e
) WHERE rn BETWEEN 21 AND 30;  -- 第3頁，每頁10行

2. 去重保留最新/最舊記錄

-- 保留每個 deptno 最新 hiredate 的記錄
DELETE FROM emp
WHERE rowid IN (
    SELECT rowid FROM (
        SELECT rowid, 
               ROW_NUMBER() OVER (PARTITION BY deptno ORDER BY hiredate DESC) rn
        FROM emp
    ) WHERE rn > 1
);

3. Top-N 每組查詢

-- 每個部門薪資前 3 名
SELECT * FROM (
    SELECT e.*, 
           RANK() OVER (PARTITION BY deptno ORDER BY sal DESC) rk
    FROM emp e
) WHERE rk <= 3;

4. 會話化/分組島問題

-- 識別連續登錄天數分組
SELECT 
    user_id, login_dt,
    login_dt - ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY login_dt) AS grp_flag
FROM user_logins
-- 相同 grp_flag 即為連續登錄組

5. 累計/移動統計

SELECT 
    order_dt, amount,
    SUM(amount) OVER (ORDER BY order_dt ROWS UNBOUNDED PRECEDING) AS running_total,
    AVG(amount) OVER (ORDER BY order_dt ROWS BETWEEN 6 PRECEDING AND CURRENT ROW) AS ma_7day
FROM daily_sales;

性能優化關鍵點

1. 索引支持：PARTITION BY + ORDER BY 列創建複合索引可避免排序
2. 減少分區數：大量微小分區增加窗口管理開銷
3. 避免不必要的窗口框架：默認 RANGE UNBOUNDED PRECEDING 可能觸發磁盤排序，ROWS 通常更快
4. 物化視圖預計算：高頻報表預先計算分析函數結果

版本支持矩陣