SQL Server 累加求和高效寫法：聚合函數與子查詢迭代 vs 窗口函數

前言：從游標到集合思維

需求：依主鍵順序計算每筆記錄的「截至目前累加總和」。傳統寫法用游標/迴圈，效能極差。本文對比三種集合化寫法：相關子查詢、窗口函數 (SQL 2012+)、變數累加 (舊版相容)，並分析執行計畫、擴展性與適用場景。

一、測試資料準備

CREATE TABLE dbo.t_product (
    xh INT PRIMARY KEY CLUSTERED,
    price DECIMAL(10,2) NOT NULL
);

-- 插入測試資料 (10 萬筆)
INSERT INTO dbo.t_product (xh, price)
SELECT TOP (100000) ROW_NUMBER() OVER (ORDER BY (SELECT NULL)),
       ABS(CHECKSUM(NEWID())) % 1000 + 1
FROM sys.objects a CROSS JOIN sys.objects b;

二、三種核心寫法對比

1. 相關子查詢 (Correlated Subquery) - 經典寫法

SELECT
    a.xh,
    a.price,
    (SELECT SUM(b.price) FROM dbo.t_product b WHERE b.xh <= a.xh) AS totalprice
FROM dbo.t_product a
ORDER BY a.xh;

• 原理：每行觸發一次子查詢掃描索引範圍
• 複雜度：O(N²) 索引搜尋 (N=10萬 → 100億次搜尋)
• 執行計畫：Nested Loops + Index Seek (Outer Ref)，無並行
• 適用：小資料量 (< 5,000 筆)、臨時查詢、SQL 2008 以前版本

2. 窗口函數 `SUM() OVER` - 現代標準 (SQL 2012+)

SELECT
    xh,
    price,
    SUM(price) OVER (ORDER BY xh ROWS UNBOUNDED PRECEDING) AS totalprice
FROM dbo.t_product
ORDER BY xh;

• 原理：單次索引順序掃描，維護執行中累加器
• 複雜度：O(N) 掃描 + O(1) 累加
• 執行計畫：Clustered Index Scan → Window Aggregate (Streaming) → 並行可能
• 關鍵子句：`ROWS UNBOUNDED PRECEDING` (而非預設 `RANGE`)，避免相同值重複計算、效能更穩定
• 適用：所有生產環境首選，SQL 2012+、Azure SQL、受控執行個體

3. 變數累加 (Variable Accumulation) - SQL 2008/2005 相容寫法

DECLARE @running_total DECIMAL(18,2) = 0;
SELECT
    xh,
    price,
    @running_total = @running_total + price AS totalprice
FROM dbo.t_product
ORDER BY xh;  -- 必須有序，否則結果不確定

• 原理：單執行緒順序處理，變數保持狀態
• 風險：不保證順序執行 (平行計畫、索引提示可能打亂)，Microsoft 文件明確標註「不支援、行為未定義」
• 緩解：`OPTION (MAXDOP 1)` 強制單執行緒 + `ORDER BY` 叢集索引鍵
• 適用：僅限舊版本維護、臨時腳本、嚴禁用於生產核心邏輯

三、效能實測 (10 萬筆測試資料)

四、進階場景：分組累加 (Partition By)

-- 依類別分組累加
SELECT
    CategoryID,
    ProductID,
    Price,
    SUM(Price) OVER (PARTITION BY CategoryID ORDER BY ProductID
                     ROWS UNBOUNDED PRECEDING) AS CategoryRunningTotal
FROM dbo.Products
ORDER BY CategoryID, ProductID;

五、排除當前行的累加 (嚴格「之前」)

-- 窗口函數：ROWS BETWEEN UNBOUNDED PRECEDING AND 1 PRECEDING
SELECT
    xh,
    price,
    SUM(price) OVER (ORDER BY xh ROWS BETWEEN UNBOUNDED PRECEDING AND 1 PRECEDING) AS totalprice_before
FROM dbo.t_product;

-- 相關子查詢：b.xh < a.xh
SELECT a.xh, a.price,
       (SELECT SUM(b.price) FROM dbo.t_product b WHERE b.xh < a.xh) AS totalprice_before
FROM dbo.t_product a;

六、第一筆記錄特殊處理

-- 窗口函數自然處理：第一行無前序行 → SUM 回傳 NULL
-- 若需第一行 = 當前價格 (包含自己)
SUM(price) OVER (ORDER BY xh ROWS UNBOUNDED PRECEDING)

-- 若需第一行 = 0 (不包含自己)
COALESCE(SUM(price) OVER (ORDER BY xh ROWS BETWEEN UNBOUNDED PRECEDING AND 1 PRECEDING), 0)

七、總結與最佳實踐

核心原則：用 集合思維 替代 迭代思維。窗口函數是 SQL 語言為有序集合運算設計的原生工具，無論可讀性、效能、擴展性皆完勝子查詢與變數法。只要版本允許，無理由不使用窗口函數。