DB2 OLAP 函數深度解析：窗口函數實戰與進階技巧

OLTP vs OLAP：為什麼需要 OLAP 函數

OLTP（聯機事務處理）面向日常操作，數據量小、並發高；OLAP（聯機分析處理）面向決策支持，數據量大、查詢復雜。DB2 OLAP 函數（窗口函數）讓復雜統計查詢在單條 SQL 中高效完成，避免多次掃表或臨時表。

核心語法結構

函數() OVER (
    [PARTITION BY 分組列]
    [ORDER BY 排序列 [NULLS FIRST|LAST]]
    [窗口幀子句: ROWS|RANGE BETWEEN ...]
)

常用 OLAP 函數分類

實戰範例：員工薪資多維分析

樣本數據：

VALUES ('張三','市場部',4000), ('趙紅','技術部',2000),
       ('李四','市場部',5000), ('李白','技術部',5000),
       ('王五','市場部',NULL), ('王藍','技術部',4000)
AS EMPLOY(NAME, DEPT, SALARY)

1. 全局與分組雙重排名

SELECT
  ROW_NUMBER() OVER (ORDER BY SALARY) AS 全局序號,
  ROW_NUMBER() OVER (PARTITION BY DEPT ORDER BY SALARY) AS 部門序號,
  NAME, DEPT, SALARY
FROM EMPLOY;

PARTITION BY DEPT 相當於分組，各部門內部獨立排序編號。

2. 同步顯示部門/全員平均薪資

SELECT
  NAME, DEPT, SALARY,
  AVG(SALARY) OVER (PARTITION BY DEPT) AS 部門平均,
  AVG(SALARY) OVER () AS 全員平均
FROM EMPLOY;

無需子查詢/連接，單次掃描完成。

3. NULL 排序控制

ROW_NUMBER() OVER (ORDER BY SALARY DESC NULLS FIRST)  -- NULL 排最前
ROW_NUMBER() OVER (ORDER BY SALARY DESC NULLS LAST)   -- 默認：NULL 排最後

4. RANK vs DENSE_RANK 差異

薪資 5000 兩人：RANK 並列第 2，下一名第 4；DENSE_RANK 並列第 2，下一名第 3。

5. 移動窗口聚合：薪資低於/高於當前行的累計

SUM(SALARY) OVER (ORDER BY SALARY NULLS FIRST
    ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS 低於本人累計,
SUM(SALARY) OVER (ORDER BY SALARY NULLS FIRST
    ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING) AS 高於本人累計

窗口幀關鍵字：

• UNBOUNDED PRECEDING：從第一行
• N PRECEDING：前 N 行
• CURRENT ROW：當前行
• N FOLLOWING：後 N 行
• UNBOUNDED FOLLOWING：到最後一行

6. ROWS vs RANGE 差異

• ROWS：按物理行數計算幀。ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING 即前後各 1 行。
• RANGE：按ORDER BY 列的值域計算幀。RANGE BETWEEN 500 PRECEDING AND 500 FOLLOWING 表示薪資 ±500 範圍內的所有行。

例：薪資 3000 的行，RANGE 500 會包含薪資 2500–3500 的所有行，而非固定行數。

7. LAG/LEAD 靈活取值

LAG(SALARY) OVER (ORDER BY SALARY)           -- 前一行薪資
LAG(SALARY, 2) OVER (ORDER BY SALARY)        -- 前兩行薪資
LAG(SALARY, 3, 0, 'IGNORE NULLS') OVER (...) -- 前三行，跳過 NULL，缺省 0
LEAD(SALARY) OVER (ORDER BY SALARY)          -- 後一行薪資

總結：OLAP 函數通用模板

聚合/排序/取值函數() OVER (
    PARTITION BY 分組維度
    ORDER BY 排序維度 [NULLS FIRST|LAST]
    ROWS|RANGE BETWEEN 起始幀 AND 結束幀
)

掌握「分組→排序→幀定義」三步法，即可將額度統計、同比環比、Top-N、異常檢測等復雜分析轉化為高性能單條 SQL。