SQL Server 窗口函數 (OVER 子句) 完全指南：排名、聚合、分布、偏移四大族群與實戰模式

語法核心

函數() OVER (
    [PARTITION BY 分區列] 
    [ORDER BY 排序列] 
    [ROWS/RANGE BETWEEN 幀範圍]
)

四大函數族群

實戰案例：訂單金額排名

SELECT 
    orderid, orderdate, val,
    RANK() OVER (ORDER BY val DESC) AS rnk,
    DENSE_RANK() OVER (ORDER BY val DESC) AS drnk,
    ROW_NUMBER() OVER (ORDER BY val DESC) AS rn
FROM Sales.OrderValues
ORDER BY rnk;

結果：並列金額 RANK 跳號 (1,2,2,4)，DENSE_RANK 不跳 (1,2,2,3)，ROW_NUMBER 唯一遞增。

高階應用模式

1. 運行總計 (Running Total)

SELECT 
    orderid, val,
    SUM(val) OVER (ORDER BY orderid 
        ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS RunningTotal
FROM Sales.OrderValues;

2. 移動平均 (7日)

SELECT 
    orderdate, val,
    AVG(val) OVER (ORDER BY orderdate 
        ROWS BETWEEN 6 PRECEDING AND CURRENT ROW) AS MA7
FROM Sales.OrderValues;

3. 分組 Top-N (每類前 3)

WITH Ranked AS (
    SELECT *, ROW_NUMBER() OVER (PARTITION BY Category ORDER BY Sales DESC) AS rn
    FROM ProductSales
)
SELECT * FROM Ranked WHERE rn <= 3;

4. 缺口與島嶼 (Gaps and Islands)

-- 島嶼識別：連續日期分組
SELECT *, 
    DATEADD(DAY, -ROW_NUMBER() OVER (ORDER BY Date), Date) AS Grp
FROM Calendar
WHERE IsWorkingDay = 1;
-- 相同 Grp 即同一島嶼

5. 同比環比 (LAG/LEAD)

SELECT 
    Month, Sales,
    LAG(Sales, 1) OVER (ORDER BY Month) AS PrevMonth,
    Sales - LAG(Sales, 1) OVER (ORDER BY Month) AS MoM_Change
FROM MonthlySales;

性能優化原則

• PARTITION BY 列建議有索引支援
• ORDER BY 列與索引順序一致可避免 Sort 運算子
• 幀範圍 `ROWS` 比 `RANGE` 效率高 (不處理 peer 組)
• 大數據集考慮 `INDEX(Column) INCLUDE (窗口函數引用列)` 覆蓋

為什麼窗口函數通常更快？

• 單掃描完成多聚合/排名，避免自我連接/子查詢多次掃描
• 優化器有專用 `Window Spool`/`Segment` 運算子，內存流式處理
• 語義聲明式，優化器自由選擇最佳物理實現