MySQL窗口函数LAG和LEAD访问相邻行数据的分析场景

在进行数据分析时，我们经常需要将当前行的数据与其前一行或后一行进行比较。例如，计算销售额的月环比、判断用户的连续登录状态、查看设备的温度变化趋势等。传统的SQL聚合函数（如SUM、AVG）在处理这种“行与行之间”的关系时显得笨拙，通常需要复杂的自连接或子查询。窗口函数LAG和LEAD正是为了解决这类问题而生的利器。

它们能让你在不减少查询结果行数的情况下，轻松访问同一结果集中位于当前行之前（LAG）或之后（LEAD）的指定偏移量行的数据。

一、 理解核心概念：窗口函数与LAG/LEAD

窗口函数在SQL中执行一个“窗口”内的聚合或计算，这个“窗口”由OVER()子句定义，而查询的每一行都会在这个窗口内得到一个计算结果。与聚合函数不同，窗口函数不会将多行合并为一行。

LAG 函数用于访问当前行之前的第N行数据。
LEAD 函数用于访问当前行之后的第N行数据。

它们的基本语法结构一致：

LAG( expression, offset, default ) OVER ( 
    PARTITION BY partition_expression 
    ORDER BY sort_expression [ASC|DESC] 
)

语法参数解析：

1. expression：你想要获取其值的列或表达式。
2. offset：一个可选的整数，表示偏移的行数。对于LAG，1代表前一行，2代表前两行；对于LEAD，1代表后一行。默认值为1。
3. default：一个可选值，当偏移指向不存在的行时（例如，第一行的“前一行”不存在），返回该默认值。如果未指定，则默认返回NULL。
4. OVER() 子句：定义窗口如何划分和排序。
   • PARTITION BY：可选，将数据分成多个“组”（窗口），函数在每个组内独立计算。
   • ORDER BY：必选，它定义了在每个窗口内行的逻辑顺序，LAG和LEAD正是基于这个顺序来寻找“前一行”或“后一行”的。

二、 动手实践：典型应用场景与示例

假设我们有以下销售数据表 monthly_sales：

CREATE TABLE monthly_sales (
    id INT,
    sale_month VARCHAR(7),
    sales_amount DECIMAL(10, 2)
);

INSERT INTO monthly_sales VALUES
(1, '2023-01', 1000.00),
(2, '2023-02', 1200.00),
(3, '2023-03', 950.00),
(4, '2023-04', 1350.00),
(5, '2023-05', 1400.00);

场景一：使用 LAG 计算环比增长率

计算每个月销售额与上个月相比的增长百分比。

执行以下查询：

SELECT 
    sale_month,
    sales_amount,
    LAG(sales_amount, 1, 0) OVER (ORDER BY sale_month) AS prev_month_amount,
    (sales_amount - LAG(sales_amount, 1, 0) OVER (ORDER BY sale_month)) 
    / LAG(sales_amount, 1, 0) OVER (ORDER BY sale_month) * 100 AS growth_rate_percentage
FROM monthly_sales
ORDER BY sale_month;

结果解读：

• 第一行没有“前一个月”，因此LAG返回了我们设置的默认值0。增长率计算也因此为NULL。
• 第二行(1200-1000)/1000 * 100 = 20%，即环比增长20%。
• 这个查询清晰地展示了每个月的业绩相对于上个月是增长还是下滑。

场景二：使用 LEAD 获取下一次活动的日期

假设有一个用户活动表 user_activities，我们需要查看每个用户每次活动后，下一次活动是什么时候。

SELECT 
    user_id,
    activity_date,
    LEAD(activity_date, 1, 'No Next Activity') OVER (PARTITION BY user_id ORDER BY activity_date) AS next_activity_date
FROM user_activities;

执行此查询后，你可以通过筛选next_activity_date为NULL或特定值的记录，找出那些很久未活动的用户，或者分析用户的活动频率。

场景三：标记连续状态变化

在时序数据中，判断某个状态是否发生了变化。例如，监控服务器状态 server_status。

SELECT 
    check_time,
    status,
    CASE 
        WHEN status = LAG(status, 1, status) OVER (ORDER BY check_time) THEN 'No Change'
        ELSE 'Changed'
    END AS status_change
FROM server_monitoring;

这个查询比较当前行的状态与前一行的状态。如果相同，则标记为No Change；如果不同，则标记为Changed，从而快速定位状态切换的时间点。

三、 进阶技巧与注意事项

1. 在分组内进行比较：PARTITION BY 的用法

当数据逻辑上属于不同的组时，必须使用PARTITION BY来确保LAG和LEAD只在每个组内进行比较，避免跨组干扰。

例如，计算每个部门内部员工的薪资与同部门前一位员工的差异：

SELECT 
    department_id,
    employee_name,
    salary,
    LAG(salary, 1, salary) OVER (PARTITION BY department_id ORDER BY salary) AS prev_salary_in_dept,
    salary - LAG(salary, 1, salary) OVER (PARTITION BY department_id ORDER BY salary) AS salary_diff
FROM employees;

2. 访问非相邻行：调整 offset 参数

通过修改offset参数，你可以轻松访问更前或更后的数据。例如，获取上个月的数据用于计算季度初到当前的累计：

-- 获取两个月前的数据
LAG(sales_amount, 2, 0) OVER (ORDER BY sale_month) AS two_months_ago_amount

3. 性能考虑

窗口函数通常在执行完所有筛选、连接和聚合之后，在最后的结果集上运行。因此，确保在OVER()子句中定义的ORDER BY列上有合适的索引（特别是对于大表），可以显著提升查询性能。PARTITION BY的列也建议建立索引。

4. 与聚合函数结合

你可以在窗口内使用聚合函数，然后结合LAG/LEAD进行比较。例如，计算每个部门当月的平均薪资，并与上个月的部门平均薪资进行对比。

四、 总结应用场景

• 财务与销售分析：计算环比、同比。
• 用户行为分析：计算用户连续登录天数、识别流失前的最后一次活动。
• 物联网（IoT）数据：计算设备读数的变化率（如温度、压力的变化）。
• 序列数据处理：在订单流、日志流中识别事件间的间隔或模式。
• 数据质量检查：检测数据行中的突变或不连续情况。

核心要领是：LAG和LEAD为你提供了在SQL查询结果集中进行“行间穿梭”的能力。通过PARTITION BY划定范围，ORDER BY确定路径，offset指定步数，你便能高效地完成许多过去需要复杂自连接才能实现的行间数据分析任务。