DB2 OLAP 視窗函數深度解析：排序、報表、累加與 Top-N 實戰模式

OLAP 函數三大家族

DB2 自 V6 引入 OLAP 函數，擴展關聯模型使其理解「行集合內的順序」。按功能分為：

1. 排序函數：RANK、DENSE_RANK、ROW_NUMBER
2. 標量-聚合函數：SUM/AVG/COUNT/MAX/MIN... OVER()，分「報表函數」（無 ORDER BY）與「累加函數」（有 ORDER BY）
3. 分布統計函數：V9+ 新增 PERCENT_RANK、CUME_DIST、NTILE 等

排序函數：PARTITION BY + ORDER BY 核心模式

SELECT empnum, dept, salary,
       RANK()        OVER (PARTITION BY dept ORDER BY salary DESC NULLS LAST) AS rnk,
       DENSE_RANK()  OVER (PARTITION BY dept ORDER BY salary DESC NULLS LAST) AS drnk,
       ROW_NUMBER()  OVER (PARTITION BY dept ORDER BY salary DESC NULLS LAST) AS rn
FROM emptab;

三函數行為差異對照

NULLS LAST（V7+）將 NULL 排最後，避免 NULL 薪水佔據第一名。V6 可用 CASE WHEN salary IS NULL THEN 1 ELSE 0 END, salary DESC 模擬。

標量-聚合函數：報表與累加雙模式

報表函數（無 ORDER BY）：全分區統計廣播至每行

SELECT dept, salary,
       SUM(salary) OVER (PARTITION BY dept) AS dept_sum,
       AVG(salary) OVER (PARTITION BY dept) AS dept_avg,
       COUNT(*)    OVER (PARTITION BY dept) AS dept_cnt,
       MAX(salary) OVER (PARTITION BY dept) AS dept_max
FROM emptab;

等價於 GROUP BY dept 再 JOIN 回原表，但語法更簡潔、執行計畫更優。百分比計算 一行搞定：
DECIMAL(salary,10,2) / SUM(salary) OVER (PARTITION BY dept) AS pct

累加函數（有 ORDER BY）：截至當前行的運行統計

SELECT date, sales,
       SUM(sales) OVER (ORDER BY date) AS ytd_sales,
       COUNT(*)   OVER (ORDER BY date) AS day_cnt
FROM sales
WHERE YEAR(date) = 2000;

結果：第 1 行 = 1 月銷售，第 2 行 = 1+2 月累計... 實現 Year-To-Date 報表零自連接。

分區累加：年度重置

SUM(sales) OVER (PARTITION BY YEAR(date) ORDER BY MONTH(date)) AS ytd_sales

跨年自動歸零，OVER 子句支援運算式分區/排序，極度靈活。

進階模式：嵌套聚合與查詢處理順序

年銷售總額累加：

SELECT YEAR(date) AS yr, SUM(sales) AS yr_sum,
       SUM(SUM(sales)) OVER (ORDER BY YEAR(date)) AS cum_yr_sum
FROM sales
WHERE YEAR(date) >= 1995
GROUP BY YEAR(date);

處理順序：FROM → WHERE → GROUP BY → HAVING → SELECT（含 OLAP）。因此 OLAP 函數可引用 GROUP BY 聚合結果，但不可直接用於 WHERE/HAVING。

Top-N 查詢標準模式：CTE + 排序函數

WITH ranked_years AS (
  SELECT YEAR(date) AS yr, SUM(sales) AS yr_sum,
         RANK() OVER (ORDER BY SUM(sales) DESC) AS rnk
  FROM sales
  GROUP BY YEAR(date)
)
SELECT yr, yr_sum, rnk
FROM ranked_years
WHERE rnk <= 3;

同理可求中位數、百分位：
WHERE rnk IN (FLOOR((cnt+1)/2), CEIL((cnt+1)/2))。

效能與最佳實踐提示

• 索引支援：PARTITION BY、ORDER BY 欄位建立複合索引可避免排序溢寫磁碟。
• 窗口框架：預設 RANGE UNBOUNDED PRECEDING（累加），可顯式指定 ROWS BETWEEN 3 PRECEDING AND CURRENT ROW 實現移動平均。
• 避免 SELECT *：僅投影必要欄位，減少排序緩衝區壓力。
• 版本演進：V6 排序、V7 標量-聚合、V9 分布統計、V10.5 LAG/LEAD/FIRST_VALUE/LAST_VALUE、V11.1 RATIO_TO_REPORT，持續跟進新函數。

OLAP 函數將複雜的自連接、子查詢、臨時表邏輯轉化為宣告式單查詢，是 SQL 開發者從「寫查詢」進階到「設計資料流」的關鍵槓桿。