函數定義
- LEAD:向後看,訪問當前行之後第 N 行的值,無需自連接。
- LAG:向前看,訪問當前行之前第 N 行的值。
均為 SQL Server 2012+ 引入的分析函數,僅允許在 SELECT/ORDER BY 子句中使用。
語法
LEAD | LAG (scalar_expression [, offset [, default]])
OVER ([PARTITION BY partition_expression, ...] ORDER BY sort_expression [ASC|DESC])
| 參數 | 說明 | 默認 |
| scalar_expression | 返回值表達式(列、常量、標量函數),不可為窗口函數 | 必填 |
| offset | 物理偏移行數(正整數),可為列/子查詢/表達式 | 1 |
| default | 超出範圍時返回值 | NULL |
實戰示例:構建測試數據
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 rn,
score,
LEAD(score) OVER (ORDER BY score) AS lead1,
LEAD(score, 1) OVER (ORDER BY score) AS lead1_exp,
LEAD(score, 1, 0) OVER (ORDER BY score) AS lead1_def0,
LEAD(score, 2) OVER (ORDER BY score) AS lead2,
LAG(score) OVER (ORDER BY score) AS lag1,
LAG(score, 1) OVER (ORDER BY score) AS lag1_exp,
LAG(score, 1, 0) OVER (ORDER BY score) AS lag1_def0,
LAG(score, 2) OVER (ORDER BY score) AS lag2
FROM test
結果解析
| rn | score | lead1 | lead1_def0 | lead2 | lag1 | lag1_def0 | lag2 |
| 1 | NULL | 10 | 10 | 20 | NULL | 0 | NULL |
| 2 | 10 | 20 | 20 | 30 | NULL | 0 | NULL |
| 3 | 20 | 30 | 30 | 40 | 10 | 10 | NULL |
| 4 | 30 | 40 | 40 | 50 | 20 | 20 | 10 |
| 5 | 40 | 50 | 50 | NULL | 30 | 30 | 20 |
| 6 | 50 | NULL | 0 | NULL | 40 | 40 | 30 |
核心規律
LEAD(score, Y):按排序第 X 行 → 第 X+Y 行的值。
LAG(score, Y):按排序第 X 行 → 第 X-Y 行的值。
- 超出邊界返回
default(默認 NULL)。
PARTITION BY 可在分組內獨立計算(如每部門員工薪資前後對比)。
典型應用場景
- 同比/環比:
LAG(Sales, 12) OVER (PARTITION BY Shop ORDER BY Month) 取去年同期銷售額。
- 連續登錄/簽到:
DATEDIFF(day, LAG(LoginDate) OVER (PARTITION BY User ORDER BY LoginDate), LoginDate) = 1 判斷連續。
- 異常檢測:當前值與前值差異超過閾值報警。
- 會話化:
LAG(EventTime) OVER (PARTITION BY User ORDER BY EventTime) 計算間隔,超過閾值劃分新會話。
避坑提示
offset/default 不能為窗口函數、負數。
- 配合
FIRST_VALUE/LAST_VALUE 使用時注意 LAST_VALUE 默認框架陷阱(見上篇)。
- 大數據集上窗口函數需排序,確保有合適索引支持
ORDER BY 列,避免 Sort 操作符溢出 TempDB。