网站导航
> 技术文档 > SQL偏移类窗口函数—— LAG（）、LEAD（）用法详解_sql lag函数

SQL偏移类窗口函数—— LAG（）、LEAD（）用法详解_sql lag函数

网友: 技术文档 2025-07-30 12:01:43

SQL偏移类窗口函数：LAG() 和 LEAD() 用法详解

SQL偏移类窗口函数—— LAG（）、LEAD（）用法详解_sql lag函数

在 SQL 中，偏移类窗口函数 LAG() 和 LEAD() 用于访问当前行的前几行或后几行的值。

1. LAG() 函数

SQL偏移类窗口函数—— LAG（）、LEAD（）用法详解_sql lag函数

LAG() 函数返回当前行的前几行的数据。

LAG(Expression, OffSetValue, DefaultVar) OVER ( PARTITION BY [Expression] ORDER BY Expression [ASC|DESC]);
  • expression🍔: 你想要获取的列或表达式。
  • offset🍟 (可选): 你希望向前偏移的行数。默认是 1，表示获取前一行的数据。
  • default_value🍿 (可选): 如果当前行之前没有足够的行，返回的默认值。默认是 NULL，如果没有设置 default_value，且当前行是窗口的第一行或没有前几行数据时，返回 NULL。
  • PARTITION BY🥓 (可选): 按某列分组计算窗口函数，类似于 GROUP BY。如果没有此项，整个数据集视为一个窗口。
  • ORDER BY🥩: 按照某列排序，确定偏移的顺序。

Demo🍕🍕🍕🍕🍕🍕：

表格数据😎

sales 表，表结构和数据如下：

id month revenue 1 Jan 100 2 Feb 150 3 Mar 200

Demo🍕🍕：基础用法

使用 LAG() 函数来获取按月排序后的“revenue”列的前一行的值。

SELECT id, month, revenue, LAG(revenue) OVER (ORDER BY month) AS prev_revenueFROM sales;
id month revenue prev_revenue 1 Jan 100 NULL 2 Feb 150 100 3 Mar 200 150

Tips🍬🍬：

  • 第一行没有前一行，所以 prev_revenue 为 NULL。
  • 第二行的 prev_revenue 为第一行的 revenue 值（100）。
  • 第三行的 prev_revenue 为第二行的 revenue 值（150）。

Demo🍕🍕：带偏移量的 LAG() 函数

使用 LAG() 函数，并指定偏移量为 2，获取两行之前的“revenue”值。

SELECT id, month, revenue, LAG(revenue, 2) OVER (ORDER BY month) AS prev_revenueFROM sales;
id month revenue prev_revenue 1 Jan 100 NULL 2 Feb 150 NULL 3 Mar 200 100

Tips🍬🍬：

  • 第一行和第二行都没有两行之前的记录，所以 prev_revenue 为 NULL。
  • 第三行的 prev_revenue 为第一行的 revenue 值（100）。

Demo🍕🍕：带默认值的 LAG() 函数

使用 LAG() 函数，并指定默认值为 0，当无法获取前一行的值时返回默认值。

SELECT id, month, revenue, LAG(revenue, 1, 0) OVER (ORDER BY month) AS prev_revenueFROM sales;
id month revenue prev_revenue 1 Jan 100 0 2 Feb 150 100 3 Mar 200 150

Tips🍬🍬：

  • 使用 LAG(revenue, 1, 0) 来获取前一行的“revenue”值，如果没有前一行则返回默认值 0。
  • 第一行没有前一行，所以 prev_revenue 为 0。
  • 第二行的 prev_revenue 为第一行的 revenue 值（100）。
  • 第三行的 prev_revenue 为第二行的 revenue 值（150）。

Demo🍕🍕： LAG() 函数，比较每一天的销售额与前一天的销售额的差异。

SELECT sale_date, amount, LAG(amount, 1, 0) OVER (ORDER BY sale_date) AS previous_day_amount, amount - LAG(amount, 1, 0) OVER (ORDER BY sale_date) AS differenceFROM sales;
  • LAG(amount, 1, 0)：这行的 LAG 函数表示获取前一天（前一行）的 amount 列的值，如果前一天没有数据（例如第一行），则返回 0。
  • 通过 ORDER BY sale_date，确保按日期顺序排列数据。
sale_date amount previous_day_amount difference 2025-01-01 100 0 100 2025-01-02 150 100 50 2025-01-03 200 150 50 2025-01-04 180 200 -20

2. LEAD() 函数

SQL偏移类窗口函数—— LAG（）、LEAD（）用法详解_sql lag函数

LEAD() 函数与 LAG() 类似，但它返回的是当前行的后几行的数据。

LEAD(Expression, OffSetValue, DefaultVar) OVER ( PARTITION BY [Expression] ORDER BY Expression [ASC|DESC]);
  • expression🍔: 你想要获取的列或表达式。
  • offset🍟 (可选): 你希望向前偏移的行数。默认是 1，表示获取前一行的数据。
  • default_value🍿 (可选): 如果当前行之前没有足够的行，返回的默认值。默认是 NULL，如果没有设置 default_value，且当前行是窗口的第一行或没有前几行数据时，返回 NULL。
  • PARTITION BY🥓 (可选): 按某列分组计算窗口函数，类似于 GROUP BY。如果没有此项，整个数据集视为一个窗口。
  • ORDER BY🥩: 按照某列排序，确定偏移的顺序。

Demo🍕🍕：基础用法

使用 LEAD() 函数来获取按月排序后的“revenue”列的后一行的值。

SELECT id, month, revenue, LEAD(revenue) OVER (ORDER BY month) AS next_revenueFROM sales;
id month revenue next_revenue 1 Jan 100 150 2 Feb 150 200 3 Mar 200 NULL

Tips🍬🍬：

  • 第一行的 next_revenue 为第二行的 revenue 值（150）。
  • 第二行的 next_revenue 为第三行的 revenue 值（200）。
  • 第三行没有后续行，所以 next_revenue 为 NULL。

Demo🍕🍕：带偏移量的 LEAD() 函数

使用 LEAD() 函数，并指定偏移量为 2，获取两行之后的“revenue”值。

SELECT id, month,revenue, LEAD(revenue, 2) OVER (ORDER BY month) AS next_revenueFROM sales;
id month revenue next_revenue 1 Jan 100 200 2 Feb 150 NULL 3 Mar 200 NULL

Tips🍬🍬：

  • 使用 LEAD(revenue, 2) 来获取两行之后的“revenue”值。
  • 第一行的 next_revenue 为第三行的 revenue 值（200）。
  • 第二行和第三行都没有两行之后的记录，所以 next_revenue 为 NULL。

Demo🍕🍕：带默认值的 LEAD() 函数

使用 LEAD() 函数，并指定默认值为 0，当无法获取后一行的值时返回默认值。

SELECT id, month, revenue, LEAD(revenue, 1, 0) OVER (ORDER BY month) AS next_revenueFROM sales;
id month revenue next_revenue 1 Jan 100 150 2 Feb 150 200 3 Mar 200 0

Tips🍬🍬：

  • 使用 LEAD(revenue, 1, 0) 来获取后一行的“revenue”值，如果没有后一行则返回默认值 0。
  • 第一行的 next_revenue 为第二行的 revenue 值（150）。
  • 第二行的 next_revenue 为第三行的 revenue 值（200）。
  • 第三行没有后一行，所以 next_revenue 为 0。

Demo🍕🍕：LEAD() 函数，比较每一天的销售额与下一天的销售额的差异。

SELECT sale_date, amount, LEAD(amount, 1, 0) OVER (ORDER BY sale_date) AS next_day_amount, LEAD(amount, 1, 0) OVER (ORDER BY sale_date) - amount AS differenceFROM sales;
  • LEAD(amount, 1, 0)：这行的 LEAD 函数表示获取下一天（下一行）的 amount 列的值。如果下一天没有数据（例如最后一行），则返回 0。
  • 通过 ORDER BY sale_date，确保按日期顺序排列数据。
sale_date amount next_day_amount difference 2025-01-01 100 150 50 2025-01-02 150 200 50 2025-01-03 200 180 -20 2025-01-04 180 0 -180

最后再来一个小练习（lc会员题）：查找电影院所有连续可用的座位。

SQL偏移类窗口函数—— LAG（）、LEAD（）用法详解_sql lag函数
SQL偏移类窗口函数—— LAG（）、LEAD（）用法详解_sql lag函数

WITH t1 AS ( SELECT seat_id, -- 选择座位ID free, -- 选择当前座位的空闲状态 lag(free, 1, 999) OVER() AS pre, -- 获取当前座位前一个座位的空闲状态，默认值为 999 lead(free, 1, 999) OVER() AS next -- 获取当前座位后一个座位的空闲状态，默认值为 999 FROM Cinema -- 从 Cinema 表中选择数据)SELECT seat_id -- 返回座位IDFROM t1 -- 从 t1 子查询中选择数据WHERE free = 1 -- 当前座位为空闲 AND (pre = 1 OR next = 1) -- 前一个座位或后一个座位为空闲ORDER BY seat_id; -- 按座位ID升序排序

思路：

  1. lag(free, 1, 999) 和 lead(free, 1, 999):

    • lag(free, 1, 999) 用于获取当前座位前一个座位的 free 值（默认为 999，表示没有前一个座位）。
    • lead(free, 1, 999) 用于获取当前座位后一个座位的 free 值（默认为 999，表示没有后一个座位）。

  2. free = 1 和 (pre = 1 OR next = 1):

    • 只选择当前座位是空闲的 (free = 1)。
    • 选择那些前一个或后一个座位也是空闲的 (pre = 1 OR next = 1)，表示这些座位是连续空闲的。

  3. ORDER BY seat_id:

    • 确保最终返回的结果按座位 ID 升序排序。
seat_id free 1 1 2 0 3 1 4 1 5 1

通过执行查询，得到的 t1 子查询结果：

seat_id free pre next 1 1 999 0 2 0 1 1 3 1 0 1 4 1 1 1 5 1 1 999

从 t1 中筛选出满足 free = 1 且 (pre = 1 OR next = 1) 的行，得到的结果：

seat_id 3 4 5

整理不易，一键三连呀列位🤣

网络标签：

相关问题