SQL Server 2012 LEAD 函數實戰解析：跨列取值、間隔控制與預設值處理

LEAD 是 SQL Server 2012 引入的視窗函數，可在不自聯結的情況下存取「後續列」的值。本文透過完整測試資料示範基本用法、間隔參數、預設值參數，並對比 LAG (前向取值) 與嘗試負數間隔的失敗案例。

測試資料

DECLARE @TestData TABLE (
    ID INT IDENTITY(1,1),
    Department VARCHAR(20),
    LastName VARCHAR(20),
    Rate FLOAT
);

INSERT INTO @TestData (Department, LastName, Rate) VALUES
('Document Control','Arifin',17.7885),
('Document Control','Norred',16.8269),
('Document Control','Kharatishvili',16.8269),
('Information Services','Chai',10.25),
('Information Services','Berge',10.25),
('Information Services','Trenary',50.4808),
('Information Services','Conroy',39.6635),
('Information Services','Ajenstat',38.4615),
('Information Services','Wilson',38.4615),
('Information Services','Connelly',32.4519),
('Information Services','Meyyappan',32.4519);

基本語法

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

• scalar_expression：要取得的運算式 (通常是欄位)。
• offset：向後幾列 (預設 1)，不支援負數。
• default：超出範圍時的預設值 (預設 NULL)。

案例 1：取得下一列的 LastName (offset=1, default=NULL)

SELECT
    ID, Department, LastName,
    LEAD(LastName) OVER (ORDER BY ID) AS NextUser
FROM @TestData;

結果：第 1 列顯示第 2 列的 LastName (Norred)，最後一列為 NULL。

案例 2：間隔兩列取值 (offset=2)

SELECT
    ID, LastName,
    LEAD(LastName, 2) OVER (ORDER BY ID) AS Next2User
FROM @TestData;

第 1 列顯示第 3 列 (Kharatishvili)，倒數第 2、1 列為 NULL。

案例 3：指定預設值 (offset=2, default='N/A')

SELECT
    ID, LastName,
    LEAD(LastName, 2, 'N/A') OVER (ORDER BY ID) AS Next2User
FROM @TestData;

超出範圍列顯示 'N/A' 而非 NULL。注意： 若指定 offset，default 參數位置固定為第 3 參數，不可省略 offset 只給 default。

對比：LAG (向前取值)

SELECT
    ID, LastName,
    LAG(LastName) OVER (ORDER BY ID) AS PrevUser,
    LEAD(LastName) OVER (ORDER BY ID) AS NextUser
FROM @TestData;

• LAG：取得上一列的值 (offset=1 為預設)。
• LEAD：取得下一列的值。
• 兩者參數語法完全相同，方向相反。

嘗試負數間隔：失敗

SELECT LEAD(LastName, -1) OVER (ORDER BY ID) FROM @TestData;
-- 錯誤: The offset argument for the LEAD function cannot be negative.

官方設計不允許負數，LEAD/LAG 分工明確，不可合併。

進階：搭配 PARTITION BY 分組

SELECT
    Department, LastName,
    LEAD(LastName) OVER (PARTITION BY Department ORDER BY ID) AS NextInDept
FROM @TestData;

各部門內部獨立計算跨列取值，部門邊界自動截斷。

應用場景

• 計算連續列差值：Rate - LEAD(Rate) OVER (...)
• 偵測狀態變更：CASE WHEN Status <> LEAD(Status) OVER (...) THEN 1 ELSE 0 END
• 填補缺失值：COALESCE(Value, LEAD(Value) OVER (...))
• 年度同比：LEAD(Sales, 12) OVER (PARTITION BY Store ORDER BY Month)

效能優勢

• 避免自我聯結 (T1 JOIN T2 ON T1.ID = T2.ID - 1) 的雙倍掃描與排序成本。
• 單次掃描配合視窗運算子 (Window Spool) 完成，記憶體效率高。
• 搭配適當索引 (ORDER BY 欄位) 可獲得最佳效能。

LEAD/LAG 是時間序列、序列分析、資料品質檢查的核心利器，SQL Server 2012+ 專案必掌握。