MySQL LAG() 函数使用指南

MySQL LAG() 函数返回来自当前行所在的分区内当前行之前的指定行之内的值。

LAG() 语法

这里是 MySQL LAG() 函数的语法：

LAG(expr[, offset[, default]])
OVER (
  [PARTITION BY partition_column_list]
  [ORDER BY order_column_list]
)

参数

expr
必需的。它可以是一个列名或者表达式。
offset
可选的。相对于当前行的偏移的行数。默认值为 1。
default
可选的。它可以是一个列名或者表达式。
partition_column_list
参与分区的列的列表。
order_column_list
参与排序的列的列表。

返回值

MySQL LAG() 函数返回来自当前行所在的分区内当前行之前的指定行之内的值。

LAG() 示例

演示数据准备

使用下面的 CREATE TABLE 语句创建一个表 tax_revenue 以存储每季度的税收收益：

CREATE TABLE tax_revenue (
  id INT AUTO_INCREMENT PRIMARY KEY,
  year CHAR(4) NOT NULL,
  quarter CHAR(1) NOT NULL,
  revenue INT NOT NULL
);

这里创建了一个 tax_revenue 表，它有 5 个列：

  • id - 行 ID，主键。
  • year - 年份。
  • quarter - 季节，1 - 4。
  • revenue - 税收收益。

使用下面的 INSERT 语句向 tax_revenue 表中插入一些行：

INSERT INTO tax_revenue
  (year, quarter, revenue)
VALUES
  ('2020', '1', 3515),
  ('2020', '2', 3678),
  ('2020', '3', 4203),
  ('2020', '4', 3924),
  ('2021', '1', 3102),
  ('2021', '2', 3293),
  ('2021', '3', 3602),
  ('2021', '4', 2901);

使用下面的 SELECT 语句检索表中的数据：

SELECT * FROM tax_revenue;

+----+------+---------+---------+
| id | year | quarter | revenue |
+----+------+---------+---------+
|  1 | 2020 | 1       |    3515 |
|  2 | 2020 | 2       |    3678 |
|  3 | 2020 | 3       |    4203 |
|  4 | 2020 | 4       |    3924 |
|  5 | 2021 | 1       |    3102 |
|  6 | 2021 | 2       |    3293 |
|  7 | 2021 | 3       |    3602 |
|  8 | 2021 | 4       |    2901 |
+----+------+---------+---------+
8 rows in set (0.00 sec)

使用 MySQL LAG() 函数和下一季度的收益比较

下面的语句，在使用 MySQL LAG() 函数在每行中添加 next_quarter_revenue 列以比较当前季度和下一季度的收益：

SELECT
  *,
  LAG(revenue, 1) OVER (
    PARTITION BY year
    ORDER BY quarter DESC
  ) next_quarter_revenue
FROM tax_revenue;

+----+------+---------+---------+----------------------+
| id | year | quarter | revenue | next_quarter_revenue |
+----+------+---------+---------+----------------------+
|  4 | 2020 | 4       |    3924 |                 NULL |
|  3 | 2020 | 3       |    4203 |                 3924 |
|  2 | 2020 | 2       |    3678 |                 4203 |
|  1 | 2020 | 1       |    3515 |                 3678 |
|  8 | 2021 | 4       |    2901 |                 NULL |
|  7 | 2021 | 3       |    3602 |                 2901 |
|  6 | 2021 | 2       |    3293 |                 3602 |
|  5 | 2021 | 1       |    3102 |                 3293 |
+----+------+---------+---------+----------------------+
8 rows in set (0.00 sec)

注意，上面 SQL 语句中的窗口函数：

LAG(revenue, 1) OVER (
  PARTITION BY year
  ORDER BY quarter DESC
) next_quarter_revenue

在 OVER 子句中，

  • PARTITION BY year 将所有行按照年份进行分区
  • ORDER BY quarter DESC 将每个分区内的行按照季度逆序排列。
  • LAG(revenue, 1) 返回每行在其关联的分区内之前一行(1)中的收益(revenue)。

那么在 next_quarter_revenue 列中存放的就是当前行的下一个季度的收益。所以，每个分区的第一行中的 next_quarter_revenue 列的值为 null。

当然，您可以为 next_quarter_revenue 列中的 null 值指定要给默认值。下面的语句使用了 0 作为默认值：

SELECT
  *,
  LAG(revenue, 1, 0) OVER (
    PARTITION BY year
    ORDER BY quarter DESC
  ) next_quarter_revenue
FROM tax_revenue;

+----+------+---------+---------+----------------------+
| id | year | quarter | revenue | next_quarter_revenue |
+----+------+---------+---------+----------------------+
|  4 | 2020 | 4       |    3924 |                    0 |
|  3 | 2020 | 3       |    4203 |                 3924 |
|  2 | 2020 | 2       |    3678 |                 4203 |
|  1 | 2020 | 1       |    3515 |                 3678 |
|  8 | 2021 | 4       |    2901 |                    0 |
|  7 | 2021 | 3       |    3602 |                 2901 |
|  6 | 2021 | 2       |    3293 |                 3602 |
|  5 | 2021 | 1       |    3102 |                 3293 |
+----+------+---------+---------+----------------------+
8 rows in set (0.00 sec)