SQL 技巧：階梯式累減計算（遞減序列差值分析）

場景說明

與累加相反，階梯式累減用於計算「當前值與下一筆值的差異」，常見於：

• 庫存每日變化量（今日 - 明日）
• 排名分數差距分析
• 時間序列一階差分

測試資料

SELECT * INTO #t FROM (
    SELECT 'a1' AS t1, 440 AS t2, 1 AS t3 UNION ALL
    SELECT 'a2', 445, 2 UNION ALL
    SELECT 'a3', 253, 3 UNION ALL
    SELECT 'a4', 509, 4 UNION ALL
    SELECT 'a5', 376, 5 UNION ALL
    SELECT 'a6', 142, 6
) a;

SELECT * FROM #t ORDER BY t3;

核心邏輯：相關子查詢取得「下一筆」值

SELECT 
    b1.t1,
    b1.t2,
    b1.t3,
    b1.t2 - (SELECT TOP 1 b2.t2 FROM #t b2 WHERE b1.t3 < b2.t3 ORDER BY b2.t3) AS DiffToNext
FROM #t b1
ORDER BY b1.t3;

解析

• WHERE b1.t3 < b2.t3 找出排序順序在當前列之後的所有列。
• TOP 1 ... ORDER BY b2.t3 取最接近的下一筆（最小的較大 t3）。
• 最後一筆無下一筆，子查詢回傳 NULL，相減結果為 NULL。

結果

現代寫法：LEAD() 視窗函數 (SQL 2012+)

SELECT 
    t1, t2, t3,
    t2 - LEAD(t2) OVER (ORDER BY t3) AS DiffToNext
FROM #t
ORDER BY t3;

優點：單次掃描、語意清晰、可指定預設值 LEAD(t2, 1, 0) OVER (...) 避免 NULL。

清理

DROP TABLE #t;