SQL Server 2012 LEAD/LAG 視窗函數詳解與實戰範例

SQL Server 2012 LEAD/LAG 視窗函數詳解與實戰範例

SQL Server 2012 引入 ANSI SQL 2008 標準的 LEAD() 與 LAG() 視窗函數，可在不自聯結的情況下存取同一結果集中其他列的值，極大簡化同比、環比、差值計算等邏輯。

函數語法

LEAD (scalar_expression [, offset [, default] ]) OVER ( [ partition_by_clause ] order_by_clause )
LAG  (scalar_expression [, offset [, default] ]) OVER ( [ partition_by_clause ] order_by_clause )

• scalar_expression：要取得的運算式（通常為欄位）
• offset：向後/向前幾列（預設 1），不可為負數
• default：超出範圍時回傳值（預設 NULL）
• OVER 子句必須包含 ORDER BY，可選 PARTITION BY

測試資料準備

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() 範例：取得下一列值

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

結果：第 1 列顯示第 2 列的 LastName，以此類推；最後一列為 NULL。

LEAD() 指定間距

LEAD(LastName, 2) OVER (ORDER BY ID)  -- 取得第 3 列的值

LEAD() 指定預設值

LEAD(LastName, 1, 'N/A') OVER (ORDER BY ID)  -- 最後一列顯示 'N/A' 而非 NULL

LAG() 範例：取得上一列值

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

結果：第 1 列為 NULL，第 2 列顯示第 1 列的 LastName。

進階：搭配 PARTITION BY 分組計算

SELECT 
    ID, Department, LastName, Rate,
    LEAD(Rate) OVER (PARTITION BY Department ORDER BY ID) AS NextRateInDept,
    Rate - LAG(Rate) OVER (PARTITION BY Department ORDER BY ID) AS RateDiff
FROM @TestData;

依部門分組，計算組內下一筆薪資與差值，適用於部門內薪資趨勢分析。

常見錯誤與注意事項

• Offset 不可為負數：LEAD(col, -1) 會報錯，需改用 LAG(col, 1)。
• 必須指定 ORDER BY：視窗函數要求明確排序，否則結果不確定。
• 相容性：SQL Server 2012+ 支援；2008R2 及之前版本無此函數，需用自聯結或 ROW_NUMBER() 替代。
• 效能：視窗函數單次掃描即可完成，效能優於自聯結或子查詢。

應用場景

• 時間序列資料：計算環比增長、移動平均
• 日誌分析：偵測連續錯誤、計算操作間隔
• 薪資/銷售報表：同組別前後期比較
• 資料品質：找出欄位值異常跳躍的列

善用 LEAD/LAG 可將複雜的列間運算轉為清晰的宣告式 SQL，提升可讀性與維護性。