7.3 LAG、LEAD 与移动聚合：环比、累计与趋势

本节摘要：LAG 与 LEAD 让每一行"看见"它的上一行与下一行——环比、同比、涨跌幅这些行间比较由它们一步完成；SUM 加窗内 ORDER BY 做逐行累计，ROWS BETWEEN 显式圈出移动窗口算滑动均值。首行取不到上一行得到 NULL，用 IFNULL 或 LAG 的第三参数兜底。本节把报表里的趋势类指标全部拿下。

一、看上一行，才知道这一行

月度销售表里，"本月比上月涨了多少"这个问题的难点在于：比较的两方不在同一行。传统解法是自连接（本月 join 上月），写法绕且容易错位；LAG 把上一行的值直接拉到本行：


-- LAG：把上一行的值拉到当前行 同一行内完成环比
SELECT
  month,
  sales,
  LAG(sales) OVER (ORDER BY month)      AS 上月销量,
  sales - LAG(sales) OVER (ORDER BY month) AS 增量,
  ROUND(
    (sales - LAG(sales) OVER (ORDER BY month))
    / LAG(sales) OVER (ORDER BY month) * 100, 1
  ) AS 环比百分比
FROM monthly_sales;


+---------+-------+--------------+--------+-----------------+
| month   | sales | 上月销量     | 增量   | 环比百分比      |
+---------+-------+--------------+--------+-----------------+
| 2026-03 |  1200 |         NULL |   NULL |            NULL |
| 2026-04 |  1350 |         1200 |    150 |            12.5 |
| 2026-05 |  1180 |         1350 |   -170 |           -12.6 |
| 2026-06 |  1420 |         1180 |    240 |            20.3 |
+---------+-------+--------------+--------+-----------------+

读法：LAG(sales) OVER (ORDER BY month) 的意思是"按月份排好队，取我前面那行的 sales"。第一行没有"上一行"，LAG 返回 NULL——第 3 章的规则再次生效：NULL 参与减法与除法结果都是 NULL，所以首月的增量与环比都是 NULL，这是"无法计算"的诚实表达，不是漏算。LAG 的第二、三参数分别是偏移量与默认值：LAG(sales, 1, 0) 让首行得到 0，LAG(sales, 12) 则取十二行之前——同比（今年 6 月对去年 6 月）正是偏移 12 的 LAG。LEAD 方向相反，取下一行："下月是否开始促销，提前看一眼下一行"。

分组场景加 PARTITION BY 即可："每个顾客相邻两单的间隔天数"：


-- 分区的 LAG：每位顾客自己的上一单
SELECT
  customer_id,
  order_date,
  DATEDIFF(order_date,
           LAG(order_date) OVER (PARTITION BY customer_id ORDER BY order_date)
  ) AS 距上单天数
FROM orders
ORDER BY customer_id, order_date;


+-------------+------------+--------------+
| customer_id | order_date | 距上单天数   |
+-------------+------------+--------------+
| 1           | 2026-03-10 |         NULL |
| 1           | 2026-04-02 |           23 |
| 1           | 2026-04-20 |           18 |
| 2           | 2026-03-15 |         NULL |
+-------------+------------+--------------+

每位顾客的第一单都是 NULL——PARTITION BY 切出的每个窗都有自己的"首行"。这个查询是复购分析的地基：间隔天数的分布直接告诉你用户多久买一次。

累计与移动窗：ROWS BETWEEN 登场

7.1 节见过"窗首到当前行"的默认累计。想精确控制窗口范围，用 ROWS BETWEEN 起点 AND 终点：


-- 三个窗口帧：开盘至今累计 近三月移动合计 及整体总量
SELECT
  month,
  sales,
  SUM(sales) OVER (ORDER BY month)                          AS 累计,
  SUM(sales) OVER (ORDER BY month
    ROWS BETWEEN 2 PRECEDING AND CURRENT ROW)               AS 近三月合计,
  SUM(sales) OVER ()                                        AS 全期总量
FROM monthly_sales;


+---------+-------+--------+--------------+--------------+
| month   | sales | 累计   | 近三月合计   | 全期总量     |
+---------+-------+--------+--------------+--------------+
| 2026-03 |  1200 |   1200 |         1200 |         5150 |
| 2026-04 |  1350 |   2550 |         2550 |         5150 |
| 2026-05 |  1180 |   3730 |         3730 |         5150 |
| 2026-06 |  1420 |   5150 |         3950 |         5150 |
+---------+-------+--------+--------------+--------------+

近三月合计在 2026-06 那行只加 1180+1420 加上 1350？看仔细：1350+1180+1420 = 3950，正好是最近三行；而默认累计到 5150 是"窗首到当前行"的全量。ROWS BETWEEN 的两端可取：CURRENT ROW（当前行）、N PRECEDING（前 N 行）、N FOLLOWING（后 N 行）、UNBOUNDED PRECEDING（窗首）、UNBOUNDED FOLLOWING（窗尾）。移动均值就是把 SUM 换成 AVG：AVG(sales) OVER (... ROWS BETWEEN 2 PRECEDING AND CURRENT ROW) 即三个月滑动平均，趋势图的标配。

一个容易踩的细节：ORDER BY 的默认帧是 RANGE（同值行一起纳入），ROWS 是物理行。7.2 节并列 79 元的场景里，ORDER BY price 后同价的两行在 RANGE 语义下互相"看见"，ROWS 语义下各自只算前面的物理行。多数业务计算该用 ROWS，显式写 ROWS BETWEEN 既精确又自文档化。

图 7-3 帧的移动：以近三月合计为例

综合：一条查询出完整经营月报

把本章三节拼成一张管理层月报：每月销量、环比、三个月滑动均值、累计、全年占比，一条查询全部就位：


-- 窗口函数全家桶：一次扫描产出五个趋势指标
SELECT
  month,
  sales,
  LAG(sales) OVER w                             AS 上月,
  ROUND((sales - LAG(sales) OVER w)
        / LAG(sales) OVER w * 100, 1)           AS 环比百分比,
  ROUND(AVG(sales) OVER (
    ORDER BY month ROWS BETWEEN 2 PRECEDING AND CURRENT ROW), 0) AS 三月滑动均值,
  SUM(sales)  OVER (ORDER BY month)             AS 年初至今,
  ROUND(sales / SUM(sales) OVER () * 100, 1)    AS 占全年百分比
FROM monthly_sales
WINDOW w AS (ORDER BY month);     -- 命名窗口：同一窗口定义只写一次


+---------+-------+--------+-----------------+--------------------+----------+----------------+
| month   | sales | 上月   | 环比百分比      | 三月滑动均值       | 年初至今 | 占全年百分比   |
+---------+-------+--------+-----------------+--------------------+----------+----------------+
| 2026-03 |  1200 | NULL   | NULL            |               1200 |     1200 | 23.3           |
| 2026-04 |  1350 | 1200   | 12.5            |               1275 |     2550 | 26.2           |
| 2026-05 |  1180 | 1350   | -12.6           |               1243 |     3730 | 22.9           |
| 2026-06 |  1420 | 1180   | 20.3            |               1317 |     5150 | 27.6           |
+---------+-------+--------+-----------------+--------------------+----------+----------------+

新语法 WINDOW w AS (...) 顺手学了：多个窗口共享同一定义时命名复用，MySQL 8 与 PostgreSQL 都支持。这条查询若用第 6 章的子查询写，至少三层嵌套、五个重复的子查询块；窗口函数让"每行配参照"一族需求全部回归一行一列的直白表达——这就是它在查询阶梯顶层的位置。

⚠️ 常见坑：LAG 与 LEAD 忘了 PARTITION BY，把不同顾客、不同分类的行错当成"上一行"，环比瞬间变成张冠李戴的数值；以及拿环比百分比直接 AVERAGE——各月百分比的算术平均不等于总体增长率，正确做法是首末两行相除（数学口径问题，不是 SQL 问题，但报表背锅的常是写 SQL 的人）。

💡 关键直觉：LAG 是回头看，LEAD 是向前看，ROWS BETWEEN 是划定"目光所及的范围"。报表上每一个趋势类指标，先想清楚"这一行要看见哪些行"，窗口写法自然浮现。

本节要点回顾

LAG 取上一行、LEAD 取下一行：偏移量第二参（12 即同比），默认值第三参兜首行 NULL；
首行 NULL 是诚实表达：无法计算不是漏算，展示层用 IFNULL 补文案；
PARTITION BY 分区的 LAG 是复购间隔、组内前后对比的地基，每个窗独立有自己的首行；
ROWS BETWEEN 显式定帧：移动合计、滑动均值的精确控制，业务计算显式写 ROWS 防 RANGE 同值并入；
WINDOW 命名复用窗口定义：多个相同 OVER 定义时少写少错；
月报全家桶一条查询：明细加环比、滑动均值、累计、占比——窗口函数的主场战役。

作者与出处

原作者：灏天文库

来源：灏天文库

整理：灏天文库整理

由灏天文库平台收录，内容或由平台用户上传，仅供学习交流