Oracle 分析函數深度解析：RANK、ROW_NUMBER、LAG/LEAD 實戰應用

Oracle 分析函數深度解析：RANK、ROW_NUMBER、LAG/LEAD 實戰應用

Oracle 分析函數 (Analytic Functions) 是處理複雜排序、分組統計、同比環比計算的利器。本文系統整理 RANK、DENSE_RANK、ROW_NUMBER、LAG/LEAD 核心語法、行為差異與實戰範例，助您精準驾驭資料分析需求。

核心函數對照表

通用語法結構

FUNCTION_NAME ( [arguments] ) OVER (
  [ PARTITION BY partition_column ]  -- 分組，可省略 = 全表單一分組
  ORDER BY sort_column [ASC|DESC] [NULLS FIRST|LAST]  -- 排序，必填
  [ WINDOWING_CLAUSE ]               -- 視窗範圍，進階用法
)

實戰範例 1：RANK vs DENSE_RANK 差異示範

-- 測試資料
WITH sales AS (
  SELECT '2023-Q1' quarter, 'East' region, 100 amount FROM dual UNION ALL
  SELECT '2023-Q1', 'West', 100 FROM dual UNION ALL
  SELECT '2023-Q1', 'North', 90 FROM dual UNION ALL
  SELECT '2023-Q2', 'East', 120 FROM dual UNION ALL
  SELECT '2023-Q2', 'West', 120 FROM dual UNION ALL
  SELECT '2023-Q2', 'South', 80 FROM dual
)
SELECT 
  quarter, region, amount,
  RANK()       OVER (PARTITION BY quarter ORDER BY amount DESC) AS rk,
  DENSE_RANK() OVER (PARTITION BY quarter ORDER BY amount DESC) AS drk
FROM sales
ORDER BY quarter, rk;

結果對照

實戰範例 2：ROW_NUMBER 實現分頁與 Top-N

場景 A：標準分頁查詢 (第 2 頁，每頁 10 筆)

SELECT * FROM (
  SELECT e.*, ROW_NUMBER() OVER (ORDER BY hire_date DESC) AS rn
  FROM employees e
) WHERE rn BETWEEN 11 AND 20;

場景 B：各部門薪資前 3 高 (Top-N per Group)

SELECT * FROM (
  SELECT 
    e.department_id, e.employee_id, e.salary,
    ROW_NUMBER() OVER (PARTITION BY e.department_id ORDER BY e.salary DESC) AS rn
  FROM employees e
) WHERE rn <= 3;

場景 C：去重保留最新記錄

DELETE FROM logs 
WHERE rowid IN (
  SELECT rowid FROM (
    SELECT rowid, ROW_NUMBER() OVER (
      PARTITION BY user_id, action_type 
      ORDER BY created_time DESC
    ) AS rn
    FROM logs
  ) WHERE rn > 1
);

實戰範例 3：LAG/LEAD 同比環比計算

建立測試資料：訂單流程追蹤

CREATE TABLE order_flow (
  case_id   VARCHAR2(10),
  step_id   VARCHAR2(10),
  action_dt DATE
);

INSERT INTO order_flow VALUES ('Case1','Step1',DATE'2023-01-01');
INSERT INTO order_flow VALUES ('Case1','Step2',DATE'2023-01-02');
INSERT INTO order_flow VALUES ('Case1','Step3',DATE'2023-01-03');
INSERT INTO order_flow VALUES ('Case1','Step4',DATE'2023-01-04');
INSERT INTO order_flow VALUES ('Case1','Step5',DATE'2023-01-05');
INSERT INTO order_flow VALUES ('Case1','Step4',DATE'2023-01-06'); -- 返工
INSERT INTO order_flow VALUES ('Case1','Step6',DATE'2023-01-07');
INSERT INTO order_flow VALUES ('Case2','Step1',DATE'2023-02-01');
INSERT INTO order_flow VALUES ('Case2','Step2',DATE'2023-02-02');
INSERT INTO order_flow VALUES ('Case2','Step3',DATE'2023-02-03');
COMMIT;

查詢：每步驟與前/後步驟的天數差異

SELECT 
  case_id, step_id, action_dt,
  LAG(step_id)   OVER (PARTITION BY case_id ORDER BY action_dt) AS prev_step,
  LAG(action_dt) OVER (PARTITION BY case_id ORDER BY action_dt) AS prev_dt,
  LEAD(step_id)  OVER (PARTITION BY case_id ORDER BY action_dt) AS next_step,
  LEAD(action_dt) OVER (PARTITION BY case_id ORDER BY action_dt) AS next_dt,
  LEAD(action_dt) OVER (PARTITION BY case_id ORDER BY action_dt) - action_dt AS days_diff
FROM order_flow
ORDER BY case_id, action_dt;

結果示例

進階語法：聚合函數語法 (9i+)

-- 計算特定值在排序中的位置
SELECT RANK(4, 3) WITHIN GROUP (ORDER BY col1, col2) AS pos FROM table;

-- 等價於：WHERE col1=4 AND col2=3 在排序後的排名

效能優化關鍵點

1. 索引支援：PARTITION BY + ORDER BY 欄位建立複合索引可避免全表排序
2. 減少分區數：大量資料時，過多 PARTITION 會增加排序記憶體壓力
3. 避免 SELECT *：僅選取必要欄位，減少排序資料量
4. 物化視圖預計算：固定報表可預先計算排名結果

常見錯誤與修正

分析函數將「行間運算」SQL 化，取代傳統自連接/子查詢/游標邏輯，不僅程式碼簡潔，更能利用 Oracle 內部排序優化引擎。掌握 RANK 族、ROW_NUMBER、LAG/LEAD 五大核心函數，即可覆蓋 90% 以上的分析型查詢需求。