深入探索MySQL高级分析：掌握LAG与LEAD窗口函数，解锁序列数据分析新维度

深入探索MySQL高级分析：掌握LAG与LEAD窗口函数，解锁序列数据分析新维度

引用

CSDN

1.

https://m.blog.csdn.net/weixin_40381772/article/details/144429785

在MySQL中，LAG() 和 LEAD() 是两个强大的窗口函数，它们分别用于获取当前行之前或之后的行的数据。这两个函数在处理有序数据集时特别有用，可以帮助进行诸如计算变化、比较相邻行的值等操作。本文将详细介绍这两个函数的使用方法和应用场景。

LAG() 函数

LAG() 函数允许你访问窗口内的前一行数据。这对于计算与前一个元素的差异、获取历史状态或其他基于序列位置的操作非常有用。

语法

LAG(expression, offset, default_value) OVER (
    [PARTITION BY column_list]
    ORDER BY column_list
    [ROWS BETWEEN ... AND ...]
)

• expression: 你希望从上一行获取的列表达式。
• offset: 可选，表示向前数的行数，默认为1。
• default_value: 可选，当偏移量导致没有前一行时返回的值，默认为 NULL。
• PARTITION BY: 定义了行如何被分组进行独立的计算。
• ORDER BY: 确定行在窗口内的排序顺序。
• ROWS BETWEEN ... AND ...: 限制窗口的范围，但通常在使用 LAG() 时可以省略。

示例

假设有一个员工薪资表 employee_salary，包含 employee_id, year, 和 salary 列，我们想比较每位员工当前薪资与其上一年度的薪资：

SELECT 
    employee_id, 
    year, 
    salary, 
    LAG(salary, 1, 0) OVER (PARTITION BY employee_id ORDER BY year) as last_year_salary
FROM salaries;

LEAD() 函数

与 LAG() 相反，LEAD() 函数用于获取当前行之后的行数据。这在预测未来值、计算与下一行的差异等方面很有帮助。

语法

与 LAG() 函数的语法几乎完全相同，只是它朝向数据集的未来方向操作。

LEAD(expression, offset, default_value) OVER (
    [PARTITION BY column_list]
    ORDER BY column_list
    [ROWS BETWEEN ... AND ...]
)

示例

继续使用员工薪资表的例子，如果我们想预测每位员工下一年的薪资：

SELECT 
    employee_id, 
    year, 
    salary, 
    LEAD(salary, 1, NULL) OVER (PARTITION BY employee_id ORDER BY year) as next_year_salary
FROM salaries;

注意事项

• MySQL 8.0 及以上版本支持 LAG() 和 LEAD() 函数。
• 这两个函数都依赖于 OVER() 子句定义的窗口，通过 PARTITION BY 和 ORDER BY 控制数据的分组和排序。
• 使用这些函数时，务必确保数据已经按照预期的顺序排列，因为它们的结果依赖于这个顺序。
• default_value 参数可以避免当没有前驱或后继行时返回 NULL，提供更好的控制和数据完整性。

区别

LAG() 函数:

• 目的: 获取当前行前面的行数据。
• 关键参数:
• expression: 想要获取的列或表达式的值。
• offset: 偏移量，默认为1，表示前一行。
• default_value: 当偏移超出范围时的默认返回值，默认为 NULL。
• 应用场景: 计算与前一行的差异、百分比变化等。

LEAD() 函数:

• 目的: 获取当前行之后的行数据。
• 语法: 与 LAG() 相似，只是方向相反。
• 关键参数: 同 LAG() 函数。
• 应用场景: 预测下一周期的值、计算未来趋势等。

共同特点

• 窗口定义: 使用 OVER() 子句定义操作窗口，并可结合 PARTITION BY 进行分组，以及 ORDER BY 控制排序。
• 版本要求: MySQL 8.0 及更高版本支持这两个函数。
• 数据处理: 强大的工具，特别是在处理时间序列数据、进行排名分析、计算移动平均等场景。

通过灵活运用 LAG() 和 LEAD()，开发者可以在SQL查询中实现复杂的分析逻辑，无需依赖外部程序进行循环或额外的数据处理步骤。