SQL Server 視窗函數 (Window Functions) 完整入門：語法、分類與消除 JOIN 實戰

SQL Server 2012 大幅增強視窗函數 (OLAP/分析函數)。本文從 聚合函數對比 切入，詳細說明語法結構、四大分類、典型應用場景 (分頁、去重、分組 Top N、Running Total、Gaps & Islands) 並展示如何消除 Self-Join 提升效能。

聚合 vs 視窗函數

• 聚合：多行合併為一行 (行數減少)。SUM, AVG, COUNT 配合 GROUP BY。
• 視窗函數：輸入行數 = 輸出行數，結果以額外欄位「附掛」在每一列上。
  範例：產品類別 A (5 子類)、B (2 子類)，COUNT(*) OVER (PARTITION BY Category) → A 每列顯示 5，B 每列顯示 2。

語法範本

函數() OVER (
    PARTITION BY 列1, 列2, ...   -- 分區 (可省略，視為單一分區)
    ORDER BY 列3, 列4, ...       -- 分區內排序 (視函數類型決定是否必填)
    [窗口框架子句]                -- ROWS/RANGE BETWEEN ... (進階)
) AS 別名

四大函數分類

經典範例：消除 Self-JOIN 計算各性別員工總數

-- 傳統寫法：相關子查詢 / Self-JOIN
SELECT LoginID, Gender,
       (SELECT COUNT(*) FROM HumanResources.Employee a WHERE a.Gender = b.Gender) AS GenderTotal
FROM HumanResources.Employee b;

-- 視窗函數寫法：單次掃描
SELECT LoginID, Gender,
       COUNT(*) OVER (PARTITION BY Gender) AS GenderTotal
FROM HumanResources.Employee;

• 視窗函數在 WHERE/GROUP BY/HAVING 之後、ORDER BY 之前 執行，故不受上述子句影響。
• 執行計畫通常顯示 Window Spool / Segment / Sequence Project，單次掃描完成分區聚合。

進階應用場景

1. 分頁：ROW_NUMBER() OVER (ORDER BY id) AS rn 篩選 rn BETWEEN ...。
2. 去重 (保留最新)：ROW_NUMBER() OVER (PARTITION BY Key ORDER BY UpdateTime DESC) AS rn 篩選 rn = 1。
3. 分組 Top N：同去重，篩選 rn <= N。
4. Running Total：SUM(Amount) OVER (PARTITION BY Account ORDER BY TxnDate ROWS UNBOUNDED PRECEDING)。
5. Gaps & Islands：ROW_NUMBER() OVER (ORDER BY Date) - ROW_NUMBER() OVER (PARTITION BY Status ORDER BY Date) 識別連續區間。
6. 百分位數：PERCENTILE_CONT(0.5) WITHIN GROUP (ORDER BY Salary) OVER (PARTITION BY Dept) 中位數。

為何視窗函數常比傳統寫法快？

• 優化器對視窗函數有專用演算法 (Segment + Sort + Spool)，避免多次掃描/Join。
• 傳統寫法常需 Self-Join 或相關子查詢，優化器難以找到最佳計畫 (搜尋空間有限)。
• 視窗函數語意明確，優化器可直接套用成熟規則。