发布于 由admin

MySQL函数：`LAG()`与`LEAD()`访问同一分区中前一行或后一行的值。

MySQL窗口函数：LAG()与LEAD()详解

大家好，今天我们来深入探讨MySQL中的两个强大的窗口函数：LAG()和LEAD()。这两个函数允许我们访问同一结果集中，特别是同一分区内的前一行或后一行的值，这在很多数据分析和报表场景中非常有用。我们将通过具体的例子，详细讲解这两个函数的语法、用法以及一些高级应用。

1. 窗口函数的基本概念

在深入LAG()和LEAD()之前，我们需要先理解什么是窗口函数。窗口函数，也称为分析函数，允许我们对一组与当前行相关的行（即“窗口”）执行计算，而不像聚合函数那样将多行数据聚合为单行。窗口函数在SELECT语句的OVER()子句中使用，OVER()子句定义了窗口的范围和排序方式。

2. LAG()函数

LAG()函数允许我们访问结果集中当前行之前的行的值。它的基本语法如下：

LAG(expression [, offset [, default_value]]) OVER (
    [PARTITION BY partition_expression]
    ORDER BY sort_expression [ASC | DESC]
)
  • expression: 需要返回的值的表达式。
  • offset: 从当前行向前偏移的行数。如果省略，则默认为 1。
  • default_value: 如果偏移量超过分区的开始，则返回的默认值。如果省略，则默认返回 NULL。
  • PARTITION BY partition_expression: 将结果集划分为多个分区。LAG()函数将单独应用于每个分区。
  • ORDER BY sort_expression [ASC | DESC]: 定义每个分区内行的排序方式。

示例 1：简单LAG()应用

假设我们有一个名为 sales 的表，包含以下数据：

sale_date product quantity
2023-01-01 A 10
2023-01-02 A 15
2023-01-03 A 20
2023-01-04 A 12

我们想计算每天的销量与前一天的销量之差。可以使用以下查询：

SELECT
    sale_date,
    product,
    quantity,
    LAG(quantity, 1, 0) OVER (ORDER BY sale_date) AS previous_quantity,
    quantity - LAG(quantity, 1, 0) OVER (ORDER BY sale_date) AS quantity_difference
FROM
    sales;

结果如下：

sale_date product quantity previous_quantity quantity_difference
2023-01-01 A 10 0 10
2023-01-02 A 15 10 5
2023-01-03 A 20 15 5
2023-01-04 A 12 20 -8

在这个例子中，LAG(quantity, 1, 0) OVER (ORDER BY sale_date) 返回当前行之前一行的 quantity 值。offset 设置为 1，表示前一行。default_value 设置为 0，表示如果当前行是第一行（没有前一行），则返回 0。ORDER BY sale_date 指定了排序方式，确保 LAG() 函数按照日期顺序访问前一行的值。

示例 2：使用PARTITION BY

现在，假设我们的 sales 表还包含 region 列：

sale_date product quantity region
2023-01-01 A 10 East
2023-01-02 A 15 East
2023-01-03 A 20 East
2023-01-01 A 5 West
2023-01-02 A 8 West
2023-01-03 A 12 West

我们想分别计算每个区域的销量与前一天的销量之差。可以使用 PARTITION BY 子句：

SELECT
    sale_date,
    product,
    quantity,
    region,
    LAG(quantity, 1, 0) OVER (PARTITION BY region ORDER BY sale_date) AS previous_quantity,
    quantity - LAG(quantity, 1, 0) OVER (PARTITION BY region ORDER BY sale_date) AS quantity_difference
FROM
    sales;

结果如下：

sale_date product quantity region previous_quantity quantity_difference
2023-01-01 A 10 East 0 10
2023-01-02 A 15 East 10 5
2023-01-03 A 20 East 15 5
2023-01-01 A 5 West 0 5
2023-01-02 A 8 West 5 3
2023-01-03 A 12 West 8 4

在这个例子中，PARTITION BY region 将结果集划分为两个分区：East 和 West。LAG() 函数分别应用于每个分区，计算每个区域的销量与前一天的销量之差。

3. LEAD()函数

LEAD()函数与LAG()函数类似，但它允许我们访问结果集中当前行之后的行的值。它的基本语法如下：

LEAD(expression [, offset [, default_value]]) OVER (
    [PARTITION BY partition_expression]
    ORDER BY sort_expression [ASC | DESC]
)
  • expression: 需要返回的值的表达式。
  • offset: 从当前行向后偏移的行数。如果省略，则默认为 1。
  • default_value: 如果偏移量超过分区的末尾，则返回的默认值。如果省略，则默认返回 NULL。
  • PARTITION BY partition_expression: 将结果集划分为多个分区。LEAD()函数将单独应用于每个分区。
  • ORDER BY sort_expression [ASC | DESC]: 定义每个分区内行的排序方式。

示例 3：简单LEAD()应用

继续使用前面的 sales 表（没有 region 列）：

sale_date product quantity
2023-01-01 A 10
2023-01-02 A 15
2023-01-03 A 20
2023-01-04 A 12

我们想计算每天的销量与后一天的销量之差。可以使用以下查询：

SELECT
    sale_date,
    product,
    quantity,
    LEAD(quantity, 1, 0) OVER (ORDER BY sale_date) AS next_quantity,
    quantity - LEAD(quantity, 1, 0) OVER (ORDER BY sale_date) AS quantity_difference
FROM
    sales;

结果如下：

sale_date product quantity next_quantity quantity_difference
2023-01-01 A 10 15 -5
2023-01-02 A 15 20 -5
2023-01-03 A 20 12 8
2023-01-04 A 12 0 12

在这个例子中，LEAD(quantity, 1, 0) OVER (ORDER BY sale_date) 返回当前行之后一行的 quantity 值。offset 设置为 1，表示后一行。default_value 设置为 0，表示如果当前行是最后一行（没有后一行），则返回 0。ORDER BY sale_date 指定了排序方式，确保 LEAD() 函数按照日期顺序访问后一行的值。

示例 4：使用PARTITION BY

再次使用包含 region 列的 sales 表：

sale_date product quantity region
2023-01-01 A 10 East
2023-01-02 A 15 East
2023-01-03 A 20 East
2023-01-01 A 5 West
2023-01-02 A 8 West
2023-01-03 A 12 West

我们想分别计算每个区域的销量与后一天的销量之差。可以使用 PARTITION BY 子句：

SELECT
    sale_date,
    product,
    quantity,
    region,
    LEAD(quantity, 1, 0) OVER (PARTITION BY region ORDER BY sale_date) AS next_quantity,
    quantity - LEAD(quantity, 1, 0) OVER (PARTITION BY region ORDER BY sale_date) AS quantity_difference
FROM
    sales;

结果如下：

sale_date product quantity region next_quantity quantity_difference
2023-01-01 A 10 East 15 -5
2023-01-02 A 15 East 20 -5
2023-01-03 A 20 East 0 20
2023-01-01 A 5 West 8 -3
2023-01-02 A 8 West 12 -4
2023-01-03 A 12 West 0 12

在这个例子中，PARTITION BY region 将结果集划分为两个分区：East 和 West。LEAD() 函数分别应用于每个分区，计算每个区域的销量与后一天的销量之差。

4. 高级应用：计算移动平均

LAG() 和 LEAD() 函数可以用于计算移动平均，这是一种常用的数据平滑技术。例如，我们可以计算过去三天的移动平均销量。

SELECT
    sale_date,
    product,
    quantity,
    (quantity + LAG(quantity, 1, 0) OVER (ORDER BY sale_date) + LAG(quantity, 2, 0) OVER (ORDER BY sale_date)) / 3 AS moving_average
FROM
    sales;

这个查询计算了当前行、前一行和前两行的销量之和，然后除以 3，得到三天的移动平均销量。

5. LAG()和LEAD()与子查询的对比

在没有窗口函数之前，实现类似的功能通常需要使用子查询或者自连接，这往往使SQL语句变得复杂且效率较低。例如，使用自连接实现上述计算前一天销量之差的例子：

SELECT
    s1.sale_date,
    s1.product,
    s1.quantity,
    s2.quantity AS previous_quantity,
    s1.quantity - COALESCE(s2.quantity, 0) AS quantity_difference
FROM
    sales s1
LEFT JOIN
    sales s2 ON s1.product = s2.product AND s1.sale_date = DATE_ADD(s2.sale_date, INTERVAL 1 DAY)
ORDER BY
    s1.sale_date;

可以看出，使用自连接不仅使SQL语句更长，而且需要考虑边界情况（例如，第一天没有前一天的数据）。LAG()函数则更简洁明了，且性能通常更好。

6. 实际案例：分析用户行为序列

假设我们有一个用户行为日志表 user_activity，包含以下数据：

user_id activity_time activity_type
1 2023-01-01 10:00:00 Login
1 2023-01-01 10:05:00 Browse
1 2023-01-01 10:10:00 Add to Cart
1 2023-01-01 10:15:00 Checkout
2 2023-01-01 11:00:00 Login
2 2023-01-01 11:02:00 Browse
2 2023-01-01 11:05:00 Login

我们可以使用 LAG() 函数分析用户行为序列，例如，找出用户在两次登录之间做了什么：

SELECT
    user_id,
    activity_time,
    activity_type,
    LAG(activity_type, 1, 'N/A') OVER (PARTITION BY user_id ORDER BY activity_time) AS previous_activity_type
FROM
    user_activity;

结果如下：

user_id activity_time activity_type previous_activity_type
1 2023-01-01 10:00:00 Login N/A
1 2023-01-01 10:05:00 Browse Login
1 2023-01-01 10:10:00 Add to Cart Browse
1 2023-01-01 10:15:00 Checkout Add to Cart
2 2023-01-01 11:00:00 Login N/A
2 2023-01-01 11:02:00 Browse Login
2 2023-01-01 11:05:00 Login Browse

通过这个查询，我们可以看到用户 2 在第二次登录之前浏览了页面。

7. 注意事项

  • ORDER BY 子句在 OVER() 子句中是强制性的，除非你不需要排序，仅需要分区。
  • LAG() 和 LEAD() 函数的性能取决于数据量和索引。合理使用索引可以提高查询效率。
  • default_value 参数可以避免返回 NULL 值，使结果更易于处理。
  • 在复杂的查询中，可以结合其他窗口函数一起使用，例如 ROW_NUMBER()、RANK() 等。
  • MySQL 8.0 及以上版本支持窗口函数。

8. LAG()和LEAD()的差异总结

特性 LAG() LEAD()
功能 访问当前行之前的行的值 访问当前行之后的行的值
偏移方向 向前偏移 向后偏移
默认偏移量 1 1
默认值 如果偏移量超过分区的开始，返回 NULL 如果偏移量超过分区的末尾，返回 NULL

9. 使用场景的概括

LAG()和LEAD()函数在时间序列分析、数据比较、用户行为分析等方面有着广泛的应用。掌握这两个函数，可以让我们更方便地进行数据分析和报表生成，提高工作效率。通过本篇对这两个函数的详细讲解，相信大家已经能够熟练掌握并在实际工作中灵活应用。

发表回复

您的邮箱地址不会被公开。 必填项已用 * 标注