SQL 数据分析 · 指南 · 2026年10月30日 · 14 分钟阅读

SQL LAG 和 LEAD 函数：取上一行、下一行，算环比，附例子

LAG(column) 返回上一行的 column 值，LEAD(column) 返回下一行的值，「上」和「下」由 OVER (ORDER BY ...) 里的顺序决定。两者都保留结果里的每一行，所以你可以把「本期」和「上期」并排放在一起相减，这就是数据分析里天天要算的环比。加上 PARTITION BY，就只在同一个客户、产品或航班的行里往前、往后看。完整语法是 LAG(column, offset, default)：offset 是往回看几行（默认 1），default 是没有那一行时返回什么（默认 NULL）。

所有例子都基于 Nomto SQL 课程的航空公司数据库，使用 PostgreSQL 语法，运行在 Nomto SQL 练习场所用的 DuckDB 引擎上，每张结果表都是真实输出。LAG 和 LEAD 是窗口函数；如果你还不熟悉 OVER 和 PARTITION BY，先看 SQL 窗口函数详解。

LAG 和 LEAD：上一行和下一行

8 月初每天的订单数，每一行旁边放上前一天和后一天的数字：

WITH daily AS (
  SELECT CAST(book_date AS date) AS day, COUNT(*) AS bookings
  FROM bookings
  WHERE book_date >= '2026-08-01' AND book_date < '2026-08-06'
  GROUP BY CAST(book_date AS date)
)
SELECT day, bookings,
       LAG(bookings)  OVER (ORDER BY day) AS prev_day,
       LEAD(bookings) OVER (ORDER BY day) AS next_day
FROM daily
ORDER BY day;

第一行没有上一行，所以 LAG 返回 NULL；最后一行没有下一行，所以 LEAD 返回 NULL。注意，窗口只能看到通过了 WHERE 的行：8 月 1 日在这里没有「前一天」，是因为 7 月 31 日被筛掉了，而不是那天没有订单。

LEAD(x) 等于把顺序反过来的 LAG(x)：LEAD(bookings) OVER (ORDER BY day) 和 LAG(bookings) OVER (ORDER BY day DESC) 结果相同。哪个读起来顺就用哪个。

偏移量和默认值

第二个参数决定往回看多远，第三个参数在没有可看的行时替换 NULL：

WITH daily AS (
  SELECT CAST(book_date AS date) AS day, COUNT(*) AS bookings
  FROM bookings
  WHERE book_date >= '2026-07-01' AND book_date < '2026-08-01'
  GROUP BY CAST(book_date AS date)
)
SELECT day, bookings,
       LAG(bookings, 1, 0) OVER (ORDER BY day) AS prev_or_zero,
       LAG(bookings, 7)    OVER (ORDER BY day) AS same_day_last_week,
       ROUND(100.0 * (bookings - LAG(bookings, 7) OVER (ORDER BY day))
             / LAG(bookings, 7) OVER (ORDER BY day), 1) AS wow_pct
FROM daily
ORDER BY day
LIMIT 10;

（只显示 7 月前 10 天。）

• LAG(bookings, 7) 拿这周三和上周三比，也就是常说的「周同比」。对有明显周规律的日数据，这比和昨天比更公平。
• LAG(bookings, 1, 0) 在第一行返回 0。默认值要慎用：这里的 0 意思是「没有上一行」，而不是「昨天没有订单」，而且拿 0 算变化百分比就是除以零。计算变化时，NULL 通常才是诚实的答案。
• 默认值的类型必须和列兼容。在日期列上写 LAG(day, 1, 'none') 会报错。

LAG(x, 7) 是往回数七行，不是七天。如果 daily 里缺了某一天，比较就会悄悄错位。可能有缺口时，先补全日历，见日期序列补全指南（英文）。

环比：和上一期比

LAG 最常见的用途：按周计算收入环比，包括绝对变化和百分比。只取完整的周，从 6 月 29 日到 8 月 9 日：

WITH weekly AS (
  SELECT CAST(date_trunc('week', book_date) AS date) AS week,
         SUM(total_amount) AS revenue
  FROM bookings
  WHERE book_date >= '2026-06-29' AND book_date < '2026-08-10'
  GROUP BY 1
)
SELECT week, revenue,
       LAG(revenue) OVER (ORDER BY week) AS prev_week,
       revenue - LAG(revenue) OVER (ORDER BY week) AS change,
       ROUND(100.0 * (revenue - LAG(revenue) OVER (ORDER BY week))
             / LAG(revenue) OVER (ORDER BY week), 1) AS pct_change
FROM weekly
ORDER BY week;

收入单位是美元。窗口在 GROUP BY 之后执行，所以它作用于每周的合计，而不是单个订单。有两个习惯很有帮助：

• 去掉不完整的周期。 数据从 6 月 17 日（周中）开始，到 8 月 11 日结束；把这两个残缺的周算进去，开头会出现假的暴涨，结尾会出现假的暴跌。
• 写 100.0，不要写 100。 在 PostgreSQL 和 SQL Server 里，整数除以整数结果还是整数，小的变化会被舍成 0。这里 revenue 是小数，不受影响，但如果是 COUNT(*) 就会出问题。

LAG(revenue) OVER (ORDER BY week) 重复写三次虽然啰嗦，但没有坏处：相同的窗口共用一次排序。想更易读，也可以先在 CTE 里算出 prev_week，再在外层做算术。

月环比的写法完全一样，只是把 date_trunc('week', ...) 换成 date_trunc('month', ...)；在 MySQL 里可以用 DATE_FORMAT(book_date, '%Y-%m-01')。更多日期写法见 SQL 日期函数。

LAG 搭配 PARTITION BY

没有 PARTITION BY，窗口就是整个结果集。只有一条时间序列时没问题，但只要行属于多个对象就错了。下面是两个常旅客会员的航班，用两种方式计算上一个到达机场：

WITH trips AS (
  SELECT tm.member_id, f.scheduled_departure, f.arrival_airport
  FROM ticket_members tm
  JOIN ticket_flights tf ON tf.ticket_no = tm.ticket_no
  JOIN flights f ON f.flight_id = tf.flight_id
  WHERE tm.member_id IN (2969, 12589)
), x AS (
  SELECT member_id, scheduled_departure,
         LAG(arrival_airport) OVER (ORDER BY member_id, scheduled_departure) AS prev_no_partition,
         LAG(arrival_airport) OVER (PARTITION BY member_id ORDER BY scheduled_departure) AS prev_partitioned,
         ROW_NUMBER() OVER (PARTITION BY member_id ORDER BY scheduled_departure) AS n
  FROM trips
)
SELECT member_id, scheduled_departure, prev_no_partition, prev_partitioned
FROM x
WHERE n <= 2
ORDER BY member_id, scheduled_departure;

不分区时，会员 12589 的第一个航班「继承」了 VKO，那是会员 2969 的最后一个到达机场。用这种方式算出的时间差，在每个会员的第一行都是错的。加上 PARTITION BY member_id，每个会员的第一行都正确地得到 NULL。

事件之间的间隔

用时间戳减去它的 LAG，就得到距上一个事件的时间。会员 12589 的前几个航班，以及距上一次起飞的小时数：

WITH trips AS (
  SELECT tm.member_id, f.scheduled_departure, f.departure_airport, f.arrival_airport
  FROM ticket_members tm
  JOIN ticket_flights tf ON tf.ticket_no = tm.ticket_no
  JOIN flights f ON f.flight_id = tf.flight_id
  WHERE tm.member_id = 12589
)
SELECT scheduled_departure, departure_airport AS dep, arrival_airport AS arr,
       LAG(arrival_airport) OVER (ORDER BY scheduled_departure) AS prev_arr,
       ROUND(EXTRACT(EPOCH FROM scheduled_departure
             - LAG(scheduled_departure) OVER (ORDER BY scheduled_departure)) / 3600, 1) AS hours_since_prev
FROM trips
ORDER BY scheduled_departure
LIMIT 6;

时间戳减时间戳得到 INTERVAL；EXTRACT(EPOCH FROM ...) 把它转成秒，便于求平均和比较。MySQL 里对应的是 TIMESTAMPDIFF(SECOND, a, b)。日期相减在 PostgreSQL 和 DuckDB 里直接得到整数天数。

第二个航班从第一个航班落地的机场（ESL）出发，相隔 4.6 小时：这是一次中转。把这条规则写成筛选条件，统计所有会员在每个机场的中转次数。转机时间是从上一段的计划到达到这一段的起飞，而窗口函数不能写在 WHERE 里，所以放在 CTE 里计算：

WITH trips AS (
  SELECT tm.member_id, f.scheduled_departure, f.scheduled_arrival,
         f.departure_airport, f.arrival_airport
  FROM ticket_members tm
  JOIN ticket_flights tf ON tf.ticket_no = tm.ticket_no
  JOIN flights f ON f.flight_id = tf.flight_id
), legs AS (
  SELECT member_id, departure_airport,
         LAG(arrival_airport) OVER w AS prev_arr,
         EXTRACT(EPOCH FROM scheduled_departure - LAG(scheduled_arrival) OVER w) / 60 AS layover_min
  FROM trips
  WINDOW w AS (PARTITION BY member_id ORDER BY scheduled_departure)
)
SELECT departure_airport AS hub, COUNT(*) AS connections,
       MIN(layover_min) AS min_layover, ROUND(AVG(layover_min)) AS avg_layover
FROM legs
WHERE prev_arr = departure_airport AND layover_min < 360
GROUP BY departure_airport
ORDER BY connections DESC
LIMIT 5;

WINDOW w AS (...) 给窗口起个名字，在多个函数里复用；PostgreSQL、DuckDB 和 MySQL 8 都支持，SQL Server 从 2022 版起支持。同样的中转问题也可以用自连接按时间窗口来解，两种思路的对比见 SQL 自连接详解。

下一步发生了什么：在事件上用 LEAD

LEAD 回答的是「用户做完 X 之后紧接着做了什么」。App 里 select_fare（选择票价）之后的下一个事件，以及到下一个事件的中位时间：

WITH e AS (
  SELECT client_id, event_time, event_name,
         LEAD(event_name) OVER w AS next_event,
         EXTRACT(EPOCH FROM LEAD(event_time) OVER w - event_time) AS secs_to_next
  FROM app_events
  WINDOW w AS (PARTITION BY client_id ORDER BY event_time, event_id)
)
SELECT COALESCE(next_event, '(none)') AS next_event,
       COUNT(*) AS n,
       ROUND(100.0 * COUNT(*) / SUM(COUNT(*)) OVER (), 1) AS pct,
       PERCENTILE_CONT(0.5) WITHIN GROUP (ORDER BY secs_to_next) AS median_secs
FROM e
WHERE event_name = 'select_fare'
GROUP BY 1
ORDER BY n DESC;

86.4% 的人接下来会添加乘客，通常在一分钟之内。(none) 表示该客户端在数据里此后再没有任何操作。而 select_fare 之后 0 秒又是 select_fare，这是个危险信号：这些是重复事件，应该先去掉（去重的写法见 ROW_NUMBER、RANK、DENSE_RANK 的区别）。注意窗口排序里的决胜列 event_id：同一秒有两个事件时，不加它，「下一个」就是随机的。这类「下一步」分析就是漏斗和用户路径分析的基础。

识别变化和切分会话

LAG 适用于任何类型，不只是数字。把会员的等级和上一个等级比较，就能看到每一次升级和降级：

SELECT member_id, tier, valid_from,
       LAG(tier)        OVER (PARTITION BY member_id ORDER BY valid_from) AS prev_tier,
       LEAD(valid_from) OVER (PARTITION BY member_id ORDER BY valid_from) AS next_change
FROM loyalty_tier_history
WHERE member_id = 12589
ORDER BY valid_from;

对所有会员做聚合，同一个 LAG 就能得到等级流转表：

WITH h AS (
  SELECT member_id, tier, valid_from,
         LAG(tier) OVER (PARTITION BY member_id ORDER BY valid_from) AS prev_tier
  FROM loyalty_tier_history
)
SELECT prev_tier, tier, COUNT(*) AS moves
FROM h
WHERE prev_tier IS NOT NULL AND prev_tier <> tier
GROUP BY prev_tier, tier
ORDER BY moves DESC;

上一个查询里的 LEAD(valid_from) 是历史记录检查的基础：下一个版本应该恰好在这个版本结束时开始。这项检查以及它在这张表里发现的重叠，见 SCD type 2 指南（英文）。

会话切分。 常见规则：距离用户上一个事件超过 30 分钟，就开始一个新会话。用 LAG 打上标记，再对标记做累计 SUM，就给会话编上了号：

WITH e AS (
  SELECT client_id, event_time,
         CASE WHEN event_time - LAG(event_time) OVER (PARTITION BY client_id ORDER BY event_time)
                   <= INTERVAL '30 minutes' THEN 0 ELSE 1 END AS new_session
  FROM app_events
  WHERE client_id = '00017a7b4ac7'
)
SELECT event_time, new_session,
       SUM(new_session) OVER (PARTITION BY client_id ORDER BY event_time
                              ROWS UNBOUNDED PRECEDING) AS session_no
FROM e
ORDER BY event_time
LIMIT 8;

第一个事件的间隔是 NULL，比较结果不为真，ELSE 1 正确地开启了第 1 个会话。同样的「打标记再累加」思路也能找出连续的天数和其间的断档，国内数据分析面试的高频题「连续登录 N 天的用户」就属于这一类；这类问题的完整解法见 gaps and islands 指南（英文）。

常见错误

• 在 WHERE 里用 LAG。 WHERE LAG(status) OVER (...) = 'Cancelled' 会报错（「WHERE clause cannot contain window functions」）。先在 CTE 里算，再在外层筛选。
• 在窗口之前就筛掉了数据。 日期范围上的 WHERE 会删掉 LAG 需要的行，于是范围内的第一行显示 NULL。如果第一天也需要上一期的值，就在更宽的范围上算 LAG，再在外层筛选。
• 忘了 PARTITION BY。 每个对象的第一行会拿到上一个对象的值。
• ORDER BY 不唯一。 两行时间戳相同时，哪一行算「上一行」是不确定的。加一个决胜列。
• 把行当成周期。 LAG(x, 7) 是七行，不是七天。先补全缺失的日期。
• 以为到处都有 IGNORE NULLS。 要取最近一个非 NULL 的值，Oracle 和 Snowflake 支持 LAG(x) IGNORE NULLS OVER (...)，DuckDB 写作 LAG(x IGNORE NULLS) OVER (...)，但 PostgreSQL 和 MySQL 都没有 IGNORE NULLS。通用的替代写法：COUNT(x) OVER (ORDER BY t) 得到一个只在非 NULL 行上递增的组号，再用 MAX(x) OVER (PARTITION BY 这个组号) 向下填充。

常见问题

SQL 里 LAG 和 LEAD 有什么区别？

LAG 返回当前行之前某一行的值，LEAD 返回之后某一行的值，顺序由 OVER (ORDER BY ...) 决定。LEAD(x) OVER (ORDER BY t) 等于 LAG(x) OVER (ORDER BY t DESC)。

SQL 怎么取上一行的值？

LAG(column) OVER (ORDER BY 排序列)。如果行属于不同的用户、产品或账户，加上 PARTITION BY，这样每个对象的第一行得到 NULL，而不是别的对象的值。

SQL 怎么算月环比？

先聚合成每月一行，然后用 value - LAG(value) OVER (ORDER BY month) 算绝对变化，用 100.0 * (value - LAG(value) OVER (ORDER BY month)) / LAG(value) OVER (ORDER BY month) 算百分比。不完整的月份要排除。同比则是按月数据上的 LAG(value, 12)，前提是没有缺月。

LAG 返回的 NULL 怎么替换？

传第三个参数：LAG(value, 1, 0) 在没有上一行时返回 0。或者包一层：COALESCE(LAG(value) OVER (...), 0)。区别在于：COALESCE 还会替换上一行里本来就存着的 NULL，默认值参数不会。

MySQL 支持 LAG 和 LEAD 吗？

支持，从 MySQL 8.0 开始。SQL Server 从 2012 版开始支持，PostgreSQL、DuckDB、Oracle、BigQuery、Snowflake、Hive 和 Spark SQL 都支持，语法相同。MySQL 5.7 没有窗口函数，只能用自连接或变量来模拟。

其他语言： English, Español, Português (Brasil), Tiếng Việt