SQL偏移类窗口函数 LAG、LEAD的用法小结实用指南

平时做技术实践时，很多问题不是概念不会，而是细节没串起来。拿“SQL偏移类窗口函数 LAG、LEAD的用法小结”来说，它看着像小点，放到项目里常会牵出环境、配置、兼容性和维护成本。下面按实际采用顺序，把思路、关键写法和容易踩坑的地方讲清楚，便于大家直接对照操作。

从实现思路看，在 SQL 里，偏移类窗口函数 LAG() 和 LEAD() 用来访问当前行的前几行或后几行的值。

1.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 表，表结构和数据如下所示：

Demo：基础用法

采用 LAG() 函数来拿到按月排序后的“revenue”列的前一行的值。

SELECT  id, 
        month, 
        revenue, 
        LAG(revenue) OVER (ORDER BY month) AS prev_revenue
FROM sales;

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_revenue
FROM sales;

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_revenue
FROM sales;

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 difference
FROM sales;

• LAG(amount, 1, 0)：这行的 LAG 函数表示拿到前一天（前一行）的 amount 列的值，如果前一天没有数据（比如第一行），则得到 0。
• 借助 ORDER BY sale_date，确保按日期顺序排列数据。

2.LEAD()函数

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_revenue
FROM sales;

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_revenue
FROM sales;

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_revenue
FROM sales;

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 difference
FROM sales;

• LEAD(amount, 1, 0)：这行的 LEAD 函数表示拿到下一天（下一行）的 amount 列的值。如果下一天没有数据（比如最后一行），则得到 0。
• 借助 ORDER BY sale_date，确保按日期顺序排列数据。

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

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  -- 返回座位ID
FROM 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 升序排序。

借助执行查询，得到的 t1 子查询结果：

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

到此这篇关于SQL偏移类窗口函数 LAG、LEAD的用法小结的文章就介绍到这了,更多相关SQL偏移类窗口函数 内容请搜索脚本之家以前的文章或继续浏览下面的相关文章希望大家以后多多兼容脚本之家！