SQL 技巧：階梯式累加計算累計數量（跑馬燈/柏拉圖分析必備）

應用場景

柏拉圖分析（二八法則）、缺陷分類統計、銷售累計佔比等，皆需「依排序順序逐行累加」得到 Running Total。

範例資料準備

-- 建立暫存表，AutoId 必須依排序順序遞增
SELECT * INTO #TmpInvIssQuy FROM (
    SELECT 1 AS AutoId, 60 AS Inv_Qty, CAST(NULL AS INT) AS Inv_TotalQty UNION ALL
    SELECT 2, 50, NULL UNION ALL
    SELECT 3, 40, NULL UNION ALL
    SELECT 4, 30, NULL UNION ALL
    SELECT 5, 20, NULL
) t;

-- 檢視初始資料
SELECT * FROM #TmpInvIssQuy;

方法一：相關子查詢（SQL 2000 相容，效能較差）

SELECT 
    t1.AutoId,
    t1.Inv_Qty,
    (SELECT SUM(t2.Inv_Qty) FROM #TmpInvIssQuy t2 WHERE t2.AutoId <= t1.AutoId) AS Inv_TotalQty
FROM #TmpInvIssQuy t1
ORDER BY t1.AutoId;

缺點：每列掃描一次，複雜度 O(N²)，資料量大時極慢。

方法二：自我 JOIN 聚合（中等效能）

SELECT 
    t1.AutoId,
    t1.Inv_Qty,
    SUM(t2.Inv_Qty) AS Inv_TotalQty
FROM #TmpInvIssQuy t1
JOIN #TmpInvIssQuy t2 ON t2.AutoId <= t1.AutoId
GROUP BY t1.AutoId, t1.Inv_Qty
ORDER BY t1.AutoId;

方法三：視窗函數 SUM() OVER（SQL 2012+，最佳效能）

SELECT 
    AutoId,
    Inv_Qty,
    SUM(Inv_Qty) OVER (ORDER BY AutoId ROWS UNBOUNDED PRECEDING) AS Inv_TotalQty
FROM #TmpInvIssQuy
ORDER BY AutoId;

• ROWS UNBOUNDED PRECEDING：從分區第一列累加到目前列。
• 單次掃描，複雜度 O(N log N)（排序成本），大量資料首選。

方法四：變數累加（SQL 2000/2005 相容，單執行緒）

DECLARE @RunningTotal INT = 0;
SELECT 
    AutoId,
    Inv_Qty,
    @RunningTotal = @RunningTotal + Inv_Qty AS Inv_TotalQty
FROM #TmpInvIssQuy
ORDER BY AutoId;

⚠️ 警告：此寫法依賴未文件化的執行順序，平行計畫可能導致錯誤。生產環境建議加 OPTION (MAXDOP 1) 或改用方法三。

完整結果展示

延伸：計算累計佔比與帕雷托分類

WITH CTE AS (
    SELECT 
        AutoId, Inv_Qty,
        SUM(Inv_Qty) OVER (ORDER BY AutoId ROWS UNBOUNDED PRECEDING) AS RunningTotal,
        SUM(Inv_Qty) OVER () AS GrandTotal
    FROM #TmpInvIssQuy
)
SELECT 
    AutoId, Inv_Qty, RunningTotal,
    CAST(100.0 * RunningTotal / GrandTotal AS DECIMAL(5,1)) AS CumulativePct,
    CASE 
        WHEN 100.0 * RunningTotal / GrandTotal <= 80 THEN 'A類 (核心 80%)'
        ELSE 'B類 (長尾 20%)'
    END AS ParetoClass
FROM CTE;

清理

DROP TABLE #TmpInvIssQuy;