sql中lead和lag的作用前后行数据获取函数的实战演示

尼克

发布时间：2025-07-08 12:17:02

770人浏览过

来源于php中文网

原创

sql中的lead和lag函数属于窗口函数，用于访问当前行之后或之前的数据。1. lead(expression, offset, default)用于获取“之后”的数据；2. lag(expression, offset, default)用于获取“之前”的数据；3. 两者均支持partition by进行分组计算；4. 常用于销售额差值、用户行为时间间隔等场景；5. 可通过coalesce处理null值；6. 性能优化包括索引、分区控制、简化表达式；7. 应用案例涵盖金融分析、日志处理、报表生成等多个领域。

sql中lead和lag的作用前后行数据获取函数的实战演示

在SQL中，LEAD和LAG函数允许你访问结果集中当前行之前或之后的行的数据，而无需自连接。它们对于比较相邻行、计算差异等场景非常有用。简单来说，LEAD让你看到“未来”，LAG让你回顾“过去”。

解决方案

LEAD和LAG函数属于窗口函数，这意味着它们在结果集的“窗口”上进行操作。它们的语法如下：

LEAD(expression, offset, default) OVER (PARTITION BY partition_expression ORDER BY sort_expression)
LAG(expression, offset, default) OVER (PARTITION BY partition_expression ORDER BY sort_expression)

expression: 你要访问的列。
offset: 从当前行向前或向后移动的行数。默认为1。
default: 如果超出窗口范围，则返回的默认值。如果未指定，则返回NULL。
PARTITION BY: 将结果集分成多个分区。每个分区独立应用窗口函数。
ORDER BY: 定义每个分区内行的顺序。

实战演示：销售额比较

假设我们有一个sales表，包含sale_date（日期）和amount（销售额）两列。我们想计算每天的销售额与前一天的销售额的差额。

CREATE TABLE sales (
    sale_date DATE,
    amount DECIMAL(10, 2)
);

INSERT INTO sales (sale_date, amount) VALUES
('2023-01-01', 100.00),
('2023-01-02', 120.00),
('2023-01-03', 90.00),
('2023-01-04', 110.00);

使用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;

这个查询会返回每一天的销售额、前一天的销售额以及两者的差额。第一天的previous_day_amount会是0，因为我们指定了默认值为0。

实战演示：用户行为分析

绘蛙

电商场景的AI创作平台，无需高薪聘请商拍和文案团队，使用绘蛙即可低成本、批量创作优质的商拍图、种草文案

下载

假设我们有一个user_activity表，包含user_id和activity_time。我们想找出每个用户相邻两次活动的时间间隔。

CREATE TABLE user_activity (
    user_id INT,
    activity_time TIMESTAMP
);

INSERT INTO user_activity (user_id, activity_time) VALUES
(1, '2023-01-01 10:00:00'),
(1, '2023-01-01 10:30:00'),
(1, '2023-01-01 11:00:00'),
(2, '2023-01-02 12:00:00'),
(2, '2023-01-02 13:00:00');

使用LEAD函数：

SELECT
    user_id,
    activity_time,
    LEAD(activity_time, 1, NULL) OVER (PARTITION BY user_id ORDER BY activity_time) AS next_activity_time,
    TIMESTAMPDIFF(MINUTE, activity_time, LEAD(activity_time, 1, NULL) OVER (PARTITION BY user_id ORDER BY activity_time)) AS time_difference_minutes
FROM
    user_activity;

这个查询会返回每个用户的活动时间、下一次活动的时间以及两次活动的时间间隔（分钟）。注意PARTITION BY user_id，这确保了我们只比较同一用户的活动。

如何处理`LEAD`和`LAG`中的`NULL`值？

NULL值是数据库中常见的挑战。在使用LEAD和LAG时，如果expression本身可能包含NULL，或者offset超出了窗口范围，你可能会遇到NULL值。

expression是NULL： 如果expression是NULL，LEAD和LAG会直接返回NULL。你可以使用COALESCE函数来替换NULL值。例如：COALESCE(LAG(amount) OVER (ORDER BY sale_date), 0)。
offset超出范围： 如果offset导致超出窗口范围，LEAD和LAG会返回你指定的default值。如果没有指定default，则返回NULL。
条件判断： 有时，你可能需要根据LEAD或LAG返回的值进行条件判断。例如，只有当LEAD(amount)大于当前行的amount时才执行某些操作。你可以直接在WHERE子句或CASE表达式中使用LEAD和LAG的结果。