MySQL 模擬 LAG/LEAD 窗口函數：用戶變量實現行間取值

MySQL 模擬 LAG/LEAD 窗口函數

MySQL 8.0 前無原生 LAG()、LEAD()，但可利用用戶變量在單次查詢中實現「取上一行/下一行欄位值」的效果，常用於同比環比、差值計算、分組邊界判斷等場景。

模擬 LAG() — 取上一行值

SELECT
    @lag_value AS lag_col,        -- 輸出上一行值 (第一行為 NULL/初始值)
    @lag_value := target_col,     -- 更新變量為當前行值，供下一行使用
    t.*
FROM target_table t,
     (SELECT @lag_value := 初始值) r
ORDER BY 排序欄位;  -- 必須指定順序，否則結果不可預期

初始值選擇：

• 數值型欄位：(SELECT @lag_value := 0) 或 NULL
• 字符型欄位：(SELECT @lag_value := '') 或 NULL

範例：計算每日銷售額環比增長

SELECT
    dt,
    amount,
    @prev_amount AS prev_amount,
    @prev_amount := amount,
    CASE WHEN @prev_amount IS NULL OR @prev_amount = 0 THEN NULL
         ELSE ROUND((amount - @prev_amount) / @prev_amount * 100, 2)
    END AS mom_growth_pct
FROM daily_sales,
     (SELECT @prev_amount := NULL) r
ORDER BY dt;

模擬 LEAD() — 取下一行值

MySQL 變量只能順序讀取，需「倒序 + LAG + 正序」三步走：

1. 生成行號 @rn := @rn + 1 按目標順序排序。
2. 外層按行號 降序，套用 LAG 邏輯（此時實際取的是原順序的下一行）。
3. 最外層按行號 升序 恢復原順序。

SELECT dt, amount, lead_amount FROM (
    SELECT
        @lag_val AS lead_amount,
        @lag_val := amount,
        t.dt, t.amount, t.rn
    FROM (
        SELECT
            @rn := @rn + 1 AS rn,
            dt, amount
        FROM daily_sales,
             (SELECT @rn := 0) r
        ORDER BY dt
    ) t,
    (SELECT @lag_val := NULL) r
    ORDER BY rn DESC
) t2
ORDER BY rn;

關鍵注意事項

• ORDER BY 必不可少：無序查詢結果不可重現，變量賦值順序會亂。
• 變量初始化：每次查詢必須在衍生表中 (SELECT @var := init) 顯式初始化，否則受會話殘留值影響。
• 賦值與讀取順序：SELECT @var, @var:=col ... 先讀舊值、後賦新值；切勿寫成 @var:=col, @var。
• 不支援 PARTITION BY：變量模擬無法直接按分組重置，需在應用層分組查詢或升級 MySQL 8.0 使用原生窗口函數。
• 效能限制：大數據集下變量查詢無法利用索引優化排序，建議配合 LIMIT 或物化視表。

MySQL 8.0+ 原生寫法 (標準 SQL)

-- LAG
SELECT dt, amount,
       LAG(amount) OVER (ORDER BY dt) AS prev_amount
FROM daily_sales;

-- LEAD
SELECT dt, amount,
       LEAD(amount) OVER (ORDER BY dt) AS next_amount
FROM daily_sales;

-- 分組內 LAG/LEAD
SELECT dept, emp, salary,
       LAG(salary) OVER (PARTITION BY dept ORDER BY salary) AS prev_in_dept
FROM employee;

結論： 變量模擬可作為舊版本臨時方案，但代碼脆弱、難維護。**生產環境強烈建議升級至 MySQL 8.0+**，獲得標準、高效、可維護的窗口函數支援。