SQL Server 視窗函數 LEAD 與 LAG 完整解析：跨列比較不再需要 Self-Join

LEAD / LAG：在同一結果集中存取前後列數值

SQL Server 2012 引入 LEAD、LAG 讓我們能在單一掃描中比較「當前列與前/後 N 列」的值，完美解決「環比增長」、「連續登入天數」、「狀態變更偵測」等需求，效能遠優於 Self-Join 或游標。

語法對照

LEAD(scalar_expression [, offset [, default]]) OVER ([PARTITION BY ...] ORDER BY ...)
LAG (scalar_expression [, offset [, default]]) OVER ([PARTITION BY ...] ORDER BY ...)

• offset：預設 1，正整數（列/運算式/子查詢），不可為負數或視窗函數。
• default：預設 NULL，當位移超出分區邊界時回傳此值。
• PARTITION BY：分組（如每位用戶、每檔股票），省略則整個結果集為一分區。

範例資料與完整查詢

WITH test AS (
    SELECT NULL AS score UNION ALL SELECT 10 UNION ALL SELECT 20 UNION ALL
    SELECT 30 UNION ALL SELECT 40 UNION ALL SELECT 50
)
SELECT
    ROW_NUMBER() OVER (ORDER BY score) AS rownum,
    score,
    LEAD(score)                 OVER (ORDER BY score) AS next_1_def,
    LEAD(score, 1)              OVER (ORDER BY score) AS next_1_exp,
    LEAD(score, 1, 0)           OVER (ORDER BY score) AS next_1_def0,
    LEAD(score, 2)              OVER (ORDER BY score) AS next_2,
    LAG(score)                  OVER (ORDER BY score) AS prev_1_def,
    LAG(score, 1)               OVER (ORDER BY score) AS prev_1_exp,
    LAG(score, 1, 0)            OVER (ORDER BY score) AS prev_1_def0,
    LAG(score, 2)               OVER (ORDER BY score) AS prev_2
FROM test;

結果前半部：LEAD（向後看）

• LEAD(score) = LEAD(score,1)：取下一列值。
• LEAD(score,2)：取下下列值（第 X+2 列）。
• 最後一列無下一列 → 回傳 NULL；指定 default=0 則回傳 0。

結果後半部：LAG（向前看）

• 第一列無上一列 → NULL（或 default）。
• LAG(score,2)：取上上列值（第 X-2 列）。

實戰應用場景

1. 環比/同比增長率：

   SELECT month, sales,
          (sales - LAG(sales) OVER (ORDER BY month)) * 1.0 / LAG(sales) OVER (ORDER BY month) AS mom_pct
     FROM monthly_sales;

2. 偵測狀態變更（如訂單狀態流轉）：

   SELECT *, 
          CASE WHEN status <> LAG(status) OVER (PARTITION BY order_id ORDER BY changed_at)
               THEN 1 ELSE 0 END AS status_changed
     FROM order_status_log;

3. 連續登入天數：

   SELECT user_id, login_date,
          CASE WHEN login_date = DATEADD(DAY, 1, LAG(login_date) OVER (PARTITION BY user_id ORDER BY login_date))
               THEN 0 ELSE 1 END AS new_streak_flag
     FROM user_logins;

4. 填補缺失值（前值填充）：

   SELECT id, COALESCE(value, LAG(value) OVER (ORDER BY id)) AS filled_value
     FROM sparse_data;

效能優勢

• 單一掃描 + 排序（O(N log N)），無 Self-Join 的 O(N²) 或游標的逐列處理。
• 配合 PARTITION BY 可並行處理多組資料。
• 記憶體優化：視窗運算子僅保留必要的位移緩衝區。

總結

LEAD 看未來，LAG 看過去。善用 offset、default、PARTITION BY，即可用極簡潔的宣告式 SQL 解決所有「跨列比較」問題，告別 Self-Join 與游標。