SQL Server 2005 OVER 子句進階應用：聚合函數配合窗口運算避免子查詢

核心觀念

OVER() 子句不僅限於排名函數，**聚合函數（SUM, AVG, MIN, MAX, COUNT）同樣支援**，可直接在明細列上顯示分組彙總值，避免自我 JOIN 或相關子查詢。

範例資料

USE tempdb;
GO
IF OBJECT_ID('tb') IS NOT NULL DROP TABLE tb;
CREATE TABLE tb (name VARCHAR(10), val INT);
INSERT tb VALUES
('aa',10),('aa',20),('aa',20),('aa',30),
('bb',55),('bb',45),('bb',0);

單一查詢同時取得明細與多種分組統計

SELECT 
    name, val,
    -- 排名
    排名 = RANK() OVER (PARTITION BY name ORDER BY val),
    -- 佔比
    佔比 = CAST(val * 1.0 / SUM(val) OVER (PARTITION BY name) AS DECIMAL(5,2)),
    -- 距最大值
    距最大 = val - MAX(val) OVER (PARTITION BY name),
    -- 距最小值
    距最小 = val - MIN(val) OVER (PARTITION BY name),
    -- 距平均值
    距平均 = val - AVG(val) OVER (PARTITION BY name)
FROM tb;

輸出結果

效能優勢分析

進階視窗框架（SQL 2012+）

-- 僅 SQL 2012+ 支援 ROWS/RANGE 框架
SUM(val) OVER (PARTITION BY name ORDER BY val 
               ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS RunningTotal

SQL 2005/2008/R2 僅支援 **完整分區框架**（等同 ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING），不支援排序後的滑動視窗。

實務應用場景

• **單據明細顯示累計/佔比**
• **異常偵測**：明細值與群組平均/標準差偏離度
• **Top N per Group**：ROW_NUMBER() OVER (PARTITION BY grp ORDER BY val DESC) <= N
• **資料品質報表**：每群組 NULL 計數、唯一值計數等

注意事項

• OVER 內不可再套用彙總函數（如 SUM(SUM(val))）。
• 大量資料時，PARTITION BY 欄位建立索引可避免 Sort 運算子。
• 窗口函數結果**不可**直接用於同層級的 WHERE / GROUP BY，需包裹 CTE/衍生表。