萬盛學電腦網

您现在的位置: 萬盛學電腦網 >> 程式設計 >  >> 資料庫 >> MS SQL 資料庫

SQL Server 窗口函數 LEAD 與 LAG 完整指南:語法、參數、實戰示例與避坑

函數定義

  • 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

結果解析

rnscorelead1lead1_def0lead2lag1lag1_def0lag2
1NULL101020NULL0NULL
210202030NULL0NULL
3203030401010NULL
430404050202010
5405050NULL303020
650NULL0NULL404030

核心規律

  • LEAD(score, Y):按排序第 X 行 → 第 X+Y 行的值。
  • LAG(score, Y):按排序第 X 行 → 第 X-Y 行的值。
  • 超出邊界返回 default(默認 NULL)。
  • PARTITION BY 可在分組內獨立計算(如每部門員工薪資前後對比)。

典型應用場景

  1. 同比/環比:LAG(Sales, 12) OVER (PARTITION BY Shop ORDER BY Month) 取去年同期銷售額。
  2. 連續登錄/簽到:DATEDIFF(day, LAG(LoginDate) OVER (PARTITION BY User ORDER BY LoginDate), LoginDate) = 1 判斷連續。
  3. 異常檢測:當前值與前值差異超過閾值報警。
  4. 會話化:LAG(EventTime) OVER (PARTITION BY User ORDER BY EventTime) 計算間隔,超過閾值劃分新會話。

避坑提示

  • offset/default 不能為窗口函數、負數。
  • 配合 FIRST_VALUE/LAST_VALUE 使用時注意 LAST_VALUE 默認框架陷阱(見上篇)。
  • 大數據集上窗口函數需排序,確保有合適索引支持 ORDER BY 列,避免 Sort 操作符溢出 TempDB。
copyright © 萬盛學電腦網 all rights reserved