前言

SQL Server 2012 引入 LEAD 與 LAG 窗口函數，讓查詢能以「當前行」為基準，**向後/向前存取任意行的欄位值**，徹底取代過去需自聯結、ROW_NUMBER 子查詢或游標的繁瑣寫法。本文系統解析語法、參數、負向位移技巧與實戰場景。

一、語法與參數

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

參數	說明	預設
scalar_expression	要取回的運算式 (欄位/函數/常數)	—
offset	向後/向前行數，**正整數**	1
default	超出範圍時回傳值	NULL

二、測試資料 (同 FIRST_VALUE 文章)

DECLARE @TestData TABLE (ID INT IDENTITY(1,1), Department VARCHAR(20), LastName VARCHAR(20), Rate FLOAT);
-- 插入 11 筆資料 (略)

三、核心行為演示

1. 基本用法：取下一行 / 上一行

SELECT *,
       LEAD(LastName) OVER (ORDER BY ID) AS NextName,   -- offset=1, default=NULL
       LAG(LastName)  OVER (ORDER BY ID) AS PrevName
FROM @TestData;

第 1 行 NextName = 第 2 行 LastName (Norred)；第 11 行 NextName = NULL。LAG 相反。

2. 間隔多行：offset = 2

LEAD(LastName, 2) OVER (ORDER BY ID) AS Next2Name  -- 取第 3 行值

3. 自訂預設值：default = 'N/A'

LEAD(LastName, 1, 'N/A') OVER (ORDER BY ID) AS NextNameSafe  -- 最後一行回傳 'N/A' 而非 NULL

4. 負向位移技巧：LEAD(..., -1) ≡ LAG(..., 1)

SELECT
    LEAD(LastName, -1) OVER (ORDER BY ID) AS ViaLeadNeg1,  -- 等同 LAG(LastName, 1)
    LAG(LastName, 1)  OVER (ORDER BY ID) AS ViaLag1
FROM @TestData;

**發現**：SQL Server **不支援負數 offset** (執行時報錯)。若需雙向存取，請分別使用 LEAD (正向) 與 LAG (反向)，或統一用 FIRST_VALUE/LAST_VALUE + 框架子句。

5. 搭配 PARTITION BY：分組內跨行存取

SELECT *,
       LEAD(Rate) OVER (PARTITION BY Department ORDER BY ID) AS NextRateInDept
FROM @TestData;

各部門內部獨立計算，跨部門不干擾。

四、實戰場景對照表

場景	舊寫法 (自聯結/子查詢)	LEAD/LAG 新寫法	優勢
逐日環比增長率	自聯結 t1.Date = DATEADD(day,-1,t2.Date)	(Amt - LAG(Amt) OVER (ORDER BY Date)) * 1.0 / LAG(Amt) OVER (ORDER BY Date)	單掃描、無聯結、平行化友善
檢測連續登入天數	遞迴 CTE / 游標	CASE WHEN DATEDIFF(day, LAG(LoginDate) OVER (PARTITION BY UserID ORDER BY LoginDate), LoginDate) = 1 THEN 1 ELSE 0 END	聲明式、易維護
異常值偵測 (與前後 N 筆比較)	多次自聯結	ABS(Value - LEAD(Value,1) OVER (...)) > Threshold OR ABS(Value - LAG(Value,1) OVER (...)) > Threshold	單次掃描完成雙向比較
填補缺失值 (向前/向後填充)	UPDATE + 子查詢迴圈	COALESCE(Value, LEAD(Value) OVER (...), LAG(Value) OVER (...)) (配合 IGNORE NULLS 需 2022+)	集合式一次完成

五、效能與最佳實踐

• 索引支援：PARTITION BY + ORDER BY 欄位建立索引，消除 Sort 運算子，實現串流模式 (Streaming) 執行，記憶體常數級。
• 避免 SELECT *：僅輸出必要欄位，減少資料流寬度，降低記憶體授予。
• 大資料集分頁：LEAD/LAG 本身不支援分頁，需搭配 ROW_NUMBER / OFFSET FETCH。
• 相容性：資料庫相容性等級 ≥ 110 (SQL Server 2012) 方可使用。

結語

LEAD / LAG 是窗口函數家族的「時光機」，讓集合運算輕鬆跨越行邊界。掌握 offset、default、PARTITION BY 三大靈活參數，即可將「環比、連續性、異常偵測、填補缺失」等經典時序分析需求，轉化為簡潔、高效、可並行的單一查詢陳述式。