LAG / LEAD / FIRST_VALUE / LAST_VALUE

本节讲解四类分析函数（Analytic Function）——lag、lead、first_value、last_value（含 nth_value）——如何在同一个分区内取「其它行」的值，重点拆解 last_value 与默认帧组合时的经典陷阱，并给出 IGNORE NULLS 的版本差异与替代写法。学完本节，你将能正确写出组内环比、同比、会话切分与变化点检测。

前置知识

阅读本节前，建议先了解：排名函数

基础概念

排名函数（Ranking Function） 回答的是「我排第几」，返回的是一个名次数字；分析函数（Analytic Function） 回答的是「别人是多少」，返回的是同分区内其它行的值。这是两者最本质的分界。

概念	术语	说明
分析函数	Analytic Function	返回同分区内其它行值的窗口函数
偏移量	Offset	相对当前行前后移动的行数，默认 1
默认值	Default	偏移超出分区边界时返回的值，默认 `NULL`
前一行	Previous Row	`lag` 取的、位于当前行之前的那一行
后一行	Next Row	`lead` 取的、位于当前行之后的那一行
首值	First Value	帧内第一行的值
末值	Last Value	帧内最后一行的值
第 N 值	Nth Value	帧内第 N 行的值，从 1 开始计数
帧	Frame	决定 `first_value` / `last_value` 能看到哪些行
忽略空值	`IGNORE NULLS`	跳过 `NULL` 再取值（PostgreSQL 16+）
保留空值	`RESPECT NULLS`	不跳过 `NULL`（默认行为）

四个函数的行为对照（分区内按 id 排序）：

排序后的行	`lag(v)`	`v`	`lead(v)`	`first_value(v)`	`last_value(v)` 默认帧	`last_value(v)` 全分区帧
第 1 行	`NULL`	10	20	10	10	40
第 2 行	10	20	30	10	20	40
第 3 行	20	30	40	10	30	40
第 4 行	30	40	`NULL`	10	40	40

表格中加粗的那一列就是本节最重要的知识点：last_value 在默认帧下只返回到当前行为止的末行，而不是分区末行。原因在下一节展开。

与排名函数的关键区别：

对比项	排名函数	分析函数
返回值类型	整数名次	与参数同类型的「值」
是否要求 `ORDER BY`	必须	`lag` / `lead` / `nth_value` 必须；`first_value` / `last_value` 强烈建议
是否受帧影响	不受	`first_value` / `last_value` / `nth_value` 受；`lag` / `lead` 不受
典型用途	Top-N、去重	环比、同比、差值、切分

lag 与 lead 完全不受帧影响——它们只看「物理上第 N 行」，哪怕你把帧缩到只有当前行一行，lag 依然能取到前面的值。而 first_value / last_value / nth_value 的结果完全由帧决定。

sql

-- 用一张人造表把四个函数的默认行为一次看清
WITH t(seq, v) AS (
  VALUES (1, 10), (2, 20), (3, 30), (4, 40)
)
SELECT
  seq,
  v,
  lag(v)  OVER w AS lag_1,
  lead(v) OVER w AS lead_1,
  first_value(v) OVER w AS first_value_默认帧,
  last_value(v)  OVER w AS last_value_默认帧
FROM t
WINDOW w AS (ORDER BY seq)
ORDER BY seq;

text

 seq | v  | lag_1 | lead_1 | first_value_默认帧 | last_value_默认帧
-----+----+-------+--------+--------------------+-------------------
   1 | 10 | (null)|     20 |                 10 |                10
   2 | 20 |    10 |     30 |                 10 |                20
   3 | 30 |    20 |     40 |                 10 |                30
   4 | 40 |    30 | (null)|                 10 |                40
(4 rows)

注意最后两列：first_value 四行都是 10（正确），而 last_value 是 10、20、30、40 逐行增长——这是错的，业务上几乎总是想要四行都为 40。

语法与用法

text

lag(表达式 [, 偏移量 [, 默认值]])  OVER ([PARTITION BY ...] ORDER BY ...)
lead(表达式 [, 偏移量 [, 默认值]]) OVER ([PARTITION BY ...] ORDER BY ...)

first_value(表达式)   OVER ([PARTITION BY ...] ORDER BY ... [帧子句])
last_value(表达式)    OVER ([PARTITION BY ...] ORDER BY ... [帧子句])
nth_value(表达式, N)  OVER ([PARTITION BY ...] ORDER BY ... [帧子句])

-- PostgreSQL 16+：忽略 NULL 的写法，加在参数之后
lag(表达式 [, 偏移量 [, 默认值]]) IGNORE NULLS OVER (...)

参数规则：

参数	是否必填	默认值	说明
表达式	必填	—	要取值的列或表达式
偏移量	可选	`1`	正整数，前/后第 N 行
默认值	可选	`NULL`	越界时返回该值，且必须与表达式类型兼容
N（`nth_value`）	必填	—	从 1 开始，`nth_value(v, 1)` 等价于 `first_value(v)`

准备本节贯穿使用的示例表：

sql

CREATE TABLE users (
  id         INTEGER GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  username   TEXT NOT NULL,
  city       TEXT NOT NULL,
  status     TEXT NOT NULL,
  created_at DATE NOT NULL
);

CREATE TABLE orders (
  id         INTEGER GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  order_no   TEXT NOT NULL,
  user_id    INTEGER NOT NULL REFERENCES users (id),
  amount     NUMERIC(10, 2),
  status     TEXT NOT NULL,
  created_at DATE NOT NULL
);

CREATE INDEX ON users (city);
CREATE INDEX ON orders (user_id, created_at, id);

sql

INSERT INTO users (username, city, status, created_at)
SELECT
  'user_' || i,
  (ARRAY['北京', '上海', '广州', '深圳', '杭州'])[1 + (i % 5)],
  CASE WHEN i % 20 = 0 THEN 'inactive' ELSE 'active' END,
  DATE '2024-01-01' + (i % 365)
FROM generate_series(1, 20000) AS i;

-- amount 允许 NULL，用于演示 IGNORE NULLS 与越界默认值
INSERT INTO orders (order_no, user_id, amount, status, created_at)
SELECT
  'SO' || lpad(i::text, 8, '0'),
  1 + (i % 20000),
  CASE WHEN i % 17 = 0 THEN NULL ELSE round((random() * 900 + 100)::numeric, 2) END,
  (ARRAY['pending', 'paid', 'shipped', 'cancelled'])[1 + (i % 4)],
  DATE '2024-06-01' + (i % 400)
FROM generate_series(1, 200000) AS i;

ANALYZE users;
ANALYZE orders;

详细说明

`lag` 与 `lead`：取前后第 N 行

lag(expr, offset, default) 取当前行之前第 offset 行的值；lead(expr, offset, default) 取当前行之后第 offset 行的值。偏移量默认 1，越界返回默认值（默认 NULL）。

sql

-- 单个用户的时间序列：上一笔金额、下一笔金额
SELECT
  order_no,
  created_at,
  amount,
  lag(amount)  OVER w AS 上一笔金额,
  lead(amount) OVER w AS 下一笔金额
FROM orders
WHERE user_id = 42 AND status <> 'cancelled'
WINDOW w AS (ORDER BY created_at, id)
ORDER BY created_at, id;

text

  order_no  | created_at | amount | 上一笔金额 | 下一笔金额
------------+------------+--------+------------+------------
 SO00000043 | 2024-06-03 | 736.10 |     (null) |     654.60
 SO00000045 | 2024-07-01 | 654.60 |     736.10 |     180.90
 SO00000047 | 2024-08-15 | 180.90 |     654.60 |     233.10
 SO00000049 | 2024-09-20 | 233.10 |     180.90 |     703.70
 SO00020003 | 2025-04-11 | 703.70 |     233.10 |     512.30
 SO00040005 | 2025-06-02 | 512.30 |     703.70 |     (null)
(6 rows)

第一行的 上一笔金额 与最后一行的 下一笔金额 都是 NULL，这就是分区边界越界。给一个默认值可以让报表更好看：

sql

-- 用默认值填补边界，避免报表出现 NULL
SELECT
  order_no,
  amount,
  lag(amount, 1, 0) OVER w AS 上一笔金额_补零
FROM orders
WHERE user_id = 42 AND status <> 'cancelled'
WINDOW w AS (ORDER BY created_at, id)
ORDER BY created_at, id;

偏移量可以是 2、3 甚至更多，用于跨期对比（如「上上个月」）：

sql

-- 取前两笔订单金额，做双期滞后对比
SELECT
  order_no,
  created_at,
  amount,
  lag(amount, 1) OVER w AS 上一笔,
  lag(amount, 2) OVER w AS 上上笔
FROM orders
WHERE user_id = 42 AND status <> 'cancelled'
WINDOW w AS (ORDER BY created_at, id)
ORDER BY created_at, id;

lag / lead 配合 PARTITION BY 就是组内环比 / 同比的标准写法：

sql

-- 按月汇总后，用 lag 取上月销售额算环比
WITH monthly AS (
  SELECT
    u.city,
    date_trunc('month', o.created_at)::date AS 月份,
    round(sum(o.amount) FILTER (WHERE o.status <> 'cancelled'), 2) AS 销售额
  FROM orders o
  JOIN users u ON u.id = o.user_id
  WHERE u.city = '杭州'
  GROUP BY u.city, date_trunc('month', o.created_at)
)
SELECT
  月份,
  销售额,
  lag(销售额) OVER (ORDER BY 月份) AS 上月销售额,
  round(
    100.0 * (销售额 - lag(销售额) OVER (ORDER BY 月份))
          / nullif(lag(销售额) OVER (ORDER BY 月份), 0)
  , 2) AS 环比百分比
FROM monthly
ORDER BY 月份
LIMIT 6;

text

    月份    |  销售额   | 上月销售额 | 环比百分比
------------+-----------+------------+------------
 2024-06-01 | 183245.60 |     (null) |     (null)
 2024-07-01 | 179882.30 |  183245.60 |      -1.83
 2024-08-01 | 184011.90 |  179882.30 |       2.30
 2024-09-01 | 177430.20 |  184011.90 |      -3.58
 2024-10-01 | 181990.50 |  177430.20 |       2.57
(5 rows)
(6 rows)

注意：这里用 nullif(..., 0) 防止除零。首月 lag 为 NULL，整个算术表达式求值为 NULL，因此环比也是 NULL——这是正确行为，不要用 coalesce 强行补 0，否则会得到「首月增长 0%」的误导结论。

同比只需把排序键从「月份」换成「去年同期」的偏移量，或用自连接；用 lag(..., 12) 的前提是数据按月连续无缺失：

sql

-- 数据按月连续时，lag(销售额, 12) 即去年同月
lag(销售额, 12) OVER (ORDER BY 月份) AS 去年同月销售额

`first_value`、`last_value` 与 `nth_value`

这三个函数取值时不看「相对位置」，而看帧的边界：

first_value(expr)：取帧内第一行的值。
last_value(expr)：取帧内最后一行的值。
nth_value(expr, N)：取帧内第 N 行的值，N 从 1 开始。

nth_value 的计数从 1 开始，这一点和 lag 的偏移量容易混淆：

写法	取的是帧内第几行	等价写法
`nth_value(v, 1)`	第 1 行	`first_value(v)`
`nth_value(v, 2)`	第 2 行	无
`nth_value(v, 0)`	—	报错：`argument of nth_value must be greater than zero`
`nth_value(v, -1)`	—	报错（同上）

sql

-- 三个取值函数在同一分区上的表现（全分区帧）
SELECT
  order_no,
  amount,
  first_value(amount)    OVER w AS 首值,
  nth_value(amount, 2)   OVER w AS 第2值,
  last_value(amount)     OVER w AS 末值
FROM orders
WHERE user_id = 42 AND status <> 'cancelled'
WINDOW w AS (
  ORDER BY created_at, id
  ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING   -- 必须显式写全分区
)
ORDER BY created_at, id;

text

  order_no  | amount | 首值   | 第2值  | 末值
------------+--------+--------+--------+--------
 SO00000043 | 736.10 | 736.10 | 654.60 | 512.30
 SO00000045 | 654.60 | 736.10 | 654.60 | 512.30
 SO00000047 | 180.90 | 736.10 | 654.60 | 512.30
 SO00000049 | 233.10 | 736.10 | 654.60 | 512.30
 SO00020003 | 703.70 | 736.10 | 654.60 | 512.30
 SO00040005 | 512.30 | 736.10 | 654.60 | 512.30
(6 rows)

六行的三列全部相同——因为帧覆盖整个分区。

`last_value` 与默认帧的巨大陷阱

这是本节最重要的知识点，也是窗口函数最经典的坑。

回顾默认帧规则：只要 OVER 里出现 ORDER BY，帧就自动变成 RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW，即「分区起点到当前行（含同行集）」。于是 last_value 取到的「帧内最后一行」就是当前行自己，而不是分区末行。

sql

-- 错误！默认帧下，last_value 只返回到当前行为止
SELECT
  order_no,
  created_at,
  amount,
  last_value(amount) OVER w AS 末值_错误
FROM orders
WHERE user_id = 42 AND status <> 'cancelled'
WINDOW w AS (ORDER BY created_at, id)
ORDER BY created_at, id;

text

  order_no  | created_at | amount | 末值_错误
------------+------------+--------+-----------
 SO00000043 | 2024-06-03 | 736.10 |    736.10
 SO00000045 | 2024-07-01 | 654.60 |    654.60
 SO00000047 | 2024-08-15 | 180.90 |    180.90
 SO00000049 | 2024-09-20 | 233.10 |    233.10
 SO00020003 | 2025-04-11 | 703.70 |    703.70
 SO00040005 | 2025-06-02 | 512.30 |    512.30
(6 rows)

末值_错误 与 amount 逐行相同——last_value 退化成了「取自己」。业务期望的是四行（六行）都等于最后一笔 512.30。

sql

-- 正确！显式写全分区帧，last_value 才能取到分区末行
SELECT
  order_no,
  created_at,
  amount,
  last_value(amount) OVER (
    ORDER BY created_at, id
    ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
  ) AS 末值_正确
FROM orders
WHERE user_id = 42 AND status <> 'cancelled'
ORDER BY created_at, id;

text

  order_no  | created_at | amount | 末值_正确
------------+------------+--------+-----------
 SO00000043 | 2024-06-03 | 736.10 |    512.30
 SO00000045 | 2024-07-01 | 654.60 |    512.30
 SO00000047 | 2024-08-15 | 180.90 |    512.30
 SO00000049 | 2024-09-20 | 233.10 |    512.30
 SO00020003 | 2025-04-11 | 703.70 |    512.30
 SO00040005 | 2025-06-02 | 512.30 |    512.30
(6 rows)

两种帧写法的对照：

帧写法	`last_value` 返回	适用场景
默认（`RANGE UNBOUNDED PRECEDING AND CURRENT ROW`）	当前行	几乎无用
`ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING`	分区末行	取组内最新值
`RANGE BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING`	分区末行（含末同行集）	同上

记法：first_value 恰好因为默认帧起点是 UNBOUNDED PRECEDING，所以天然正确；last_value 因为终点是 CURRENT ROW，所以天然错误。这个不对称是默认帧规则直接导致的。

用 ROWS 与 RANGE 的区别在末列有并列排序键时才会显现：

sql

-- 最后一个日期有多笔订单时，RANGE 会把同日的行一起纳入
-- 选择 ROWS 通常更符合「取物理最后一行」的直觉
last_value(amount) OVER (
  ORDER BY created_at ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
)

`IGNORE NULLS` 与 `RESPECT NULLS`

默认行为是 RESPECT NULLS——把 NULL 当作普通值参与取值。当数据中存在 NULL 时，lag 可能返回 NULL，first_value 可能返回 NULL。

PostgreSQL 16 起，lag / lead / first_value / last_value / nth_value 支持 IGNORE NULLS，跳过 NULL 再取值：

sql

-- PostgreSQL 16+
-- 跳过 NULL，取「上一个有值的金额」
SELECT
  order_no,
  amount,
  lag(amount) IGNORE NULLS OVER (ORDER BY created_at, id) AS 上一个非空金额,
  first_value(amount) IGNORE NULLS OVER (
    ORDER BY created_at, id
    ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
  ) AS 首个非空金额
FROM orders
WHERE user_id = 42
ORDER BY created_at, id;

text

  order_no  | amount | 上一个非空金额 | 首个非空金额
------------+--------+----------------+--------------
 SO00000043 | 736.10 |         (null) |       736.10
 SO00000045 | 654.60 |         736.10 |       736.10
 SO00000047 | 180.90 |         654.60 |       736.10
 SO00000049 | (null) |         180.90 |       736.10
 SO00020003 | 703.70 |         180.90 |       736.10
 SO00040005 | 512.30 |         703.70 |       736.10
(6 rows)

PostgreSQL 16 之前的替代写法：先用 FILTER 或 CASE 把 NULL 转成不存在，再配合「累积分组」技巧。最通用的方法是先标记最近一个非空行的序号，再按该序号分组取首值：

sql

-- PostgreSQL 16 之前：手工实现 IGNORE NULLS（取上一个非空值）
WITH marked AS (
  SELECT
    order_no,
    created_at,
    amount,
    -- 用累积和把 NULL 行归到最近一个非空行之后
    count(amount) OVER (ORDER BY created_at, id) AS grp
  FROM orders
  WHERE user_id = 42
),
filled AS (
  SELECT
    *,
    -- 每个 grp 内首行就是最近的非空值
    first_value(amount) OVER (
      PARTITION BY grp
      ORDER BY created_at, id
      ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
    ) AS 上一个非空金额_补全
  FROM marked
)
SELECT order_no, created_at, amount, grp, 上一个非空金额_补全
FROM filled
ORDER BY created_at, id;

text

  order_no  | created_at | amount | grp | 上一个非空金额_补全
------------+------------+--------+-----+---------------------
 SO00000043 | 2024-06-03 | 736.10 |   1 |              736.10
 SO00000045 | 2024-07-01 | 654.60 |   2 |              654.60
 SO00000047 | 2024-08-15 | 180.90 |   3 |              180.90
 SO00000049 | 2024-09-20 | (null) |   3 |              180.90
 SO00020003 | 2025-04-11 | 703.70 |   4 |              703.70
 SO00040005 | 2025-06-02 | 512.30 |   5 |              512.30
(6 rows)

count(amount) 只对非 NULL 计数，因此每一段「非空值 + 其后的 NULL」共享同一个 grp，再按 grp 分区取 first_value 就得到了「前向填充（Forward Fill）」的效果。这个技巧同时适用于「取上一个非空值」和「补全缺失值」。

方案	版本要求	复杂度	说明
`IGNORE NULLS`	16+	简单	语法直接，推荐
`count(...) OVER` + `first_value`	全版本	稍繁	通用替代，还能做前向填充
`coalesce(lag(...), ...)`	全版本	脆弱	只能补一层，多层 `NULL` 会失败

用分析函数做差值与切分

lag 最实用的用途之一是计算相邻行的时间差，这是「会话切分（Sessionization）」和「连续活跃天数」的基础。

sql

-- 计算每笔订单距上一笔的间隔天数
SELECT
  order_no,
  created_at,
  created_at - lag(created_at) OVER w AS 距上次天数
FROM orders
WHERE user_id = 42 AND status <> 'cancelled'
WINDOW w AS (ORDER BY created_at, id)
ORDER BY created_at, id;

text

  order_no  | created_at | 距上次天数
------------+------------+------------
 SO00000043 | 2024-06-03 |     (null)
 SO00000045 | 2024-07-01 |         28
 SO00000047 | 2024-08-15 |         45
 SO00000049 | 2024-09-20 |         36
 SO00020003 | 2025-04-11 |        203   -- 超过 90 天，应切分为新会话
 SO00040005 | 2025-06-02 |         52
(6 rows)

用 lead 计算「距下次事件间隔」，常用于计算用户停留时长、工单处理时长：

sql

-- 每笔订单距离下一笔订单的间隔
SELECT
  order_no,
  created_at,
  lead(created_at) OVER w - created_at AS 距下次天数
FROM orders
WHERE user_id = 42 AND status <> 'cancelled'
WINDOW w AS (ORDER BY created_at, id)
ORDER BY created_at, id;

用 first_value 取组内首值，典型场景是「用户首单金额」并判断后续订单是否达到首单的某个比例：

sql

-- 每个城市金额最高的订单（取组内首值）
SELECT DISTINCT
  u.city,
  first_value(o.order_no) OVER w AS 最高金额订单号,
  first_value(o.amount)   OVER w AS 最高金额
FROM orders o
JOIN users u ON u.id = o.user_id
WHERE o.status <> 'cancelled'
WINDOW w AS (PARTITION BY u.city ORDER BY o.amount DESC, o.id)
ORDER BY u.city;

text

 city | 最高金额订单号 | 最高金额
------+----------------+----------
 上海 | SO00000003     |   999.99
 北京 | SO00000001     |   999.99
 广州 | SO00000002     |   999.98
 深圳 | SO00000004     |   999.97
 杭州 | SO00000005     |   999.96
(5 rows)

用 lag 判断值是否变化（变化点检测）：比较当前行与前一行的值，不同则标记为变化点。这是渐变维度（SCD）与状态流转分析的核心手法。

sql

-- 标记状态发生变化的那一笔订单
SELECT
  order_no,
  created_at,
  status,
  lag(status) OVER w AS 上一状态,
  CASE
    WHEN lag(status) OVER w IS DISTINCT FROM status THEN '变化点'
    ELSE ''
  END AS 标记
FROM orders
WHERE user_id = 42
WINDOW w AS (ORDER BY created_at, id)
ORDER BY created_at, id;

这里必须用 IS DISTINCT FROM 而不是 <>，因为首行的 lag 是 NULL，NULL <> 'paid' 求值为 NULL（非 true），首行就不会被标为变化点。IS DISTINCT FROM 会把 NULL 当作一个可比较的值。

实战示例

场景：为运营部门做一份「用户订单时间轴」报表，需要输出每个用户每一笔订单的：上一笔金额、下一笔金额、距上次下单天数、至今累计金额、以及「是否发生了超过 90 天的沉默」（会话切分标记）。

步骤 1：准备带时间跨度的小数据集

sql

-- 造一个订单间隔有明显断层的用户，便于观察会话切分
DROP TABLE IF EXISTS user_events;
CREATE TABLE user_events (
  id         INTEGER GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  user_id    INTEGER NOT NULL,
  amount     NUMERIC(10, 2),
  created_at DATE NOT NULL
);

INSERT INTO user_events (user_id, amount, created_at) VALUES
  (1001, 120.00, DATE '2026-01-05'),
  (1001, 340.00, DATE '2026-01-09'),
  (1001, 210.00, DATE '2026-01-14'),   -- 间隔 5 天，同一会话
  (1001, 560.00, DATE '2026-05-02'),   -- 间隔 108 天，新会话
  (1001, 430.00, DATE '2026-05-06'),   -- 间隔 4 天，同一会话
  (1001, 990.00, DATE '2026-09-30'),   -- 间隔 147 天，新会话
  (1001, 150.00, DATE '2026-10-03');

ANALYZE user_events;

步骤 2：先看一个错误的嵌套写法

sql

-- 错误！窗口函数不能嵌套：外层 OVER 的 PARTITION BY 里又套了一个窗口函数
WITH calc AS (
  SELECT
    created_at,
    amount,
    CASE
      WHEN lag(created_at) OVER w IS NULL THEN 1
      WHEN created_at - lag(created_at) OVER w > 90 THEN 1
      ELSE 0
    END AS 新会话标记
  FROM user_events
  WHERE user_id = 1001
  WINDOW w AS (ORDER BY created_at, id)
)
SELECT
  created_at,
  amount,
  round(sum(amount) OVER (
    PARTITION BY sum(新会话标记) OVER (ORDER BY created_at, id)   -- 非法
  ), 2) AS 该会话总额
FROM calc;
-- 报错：window function calls cannot be nested

规则：窗口函数只能出现在 SELECT 列表与 ORDER BY 中，不能嵌套在另一个窗口函数的参数或 PARTITION BY / ORDER BY 里。要让「会话号」参与分区，必须把它落到上一层结果集里。

步骤 3：用两个 CTE 把会话号固定下来

sql

-- 正确写法：第一层算标记，第二层算会话号，第三层按会话号聚合
WITH calc AS (
  SELECT
    id,
    created_at,
    amount,
    CASE
      WHEN lag(created_at) OVER w IS NULL THEN 1
      WHEN created_at - lag(created_at) OVER w > 90 THEN 1
      ELSE 0
    END AS 新会话标记
  FROM user_events
  WHERE user_id = 1001
  WINDOW w AS (ORDER BY created_at, id)
),
sessioned AS (
  SELECT
    *,
    sum(新会话标记) OVER (ORDER BY created_at, id
      ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS 会话号
  FROM calc
)
SELECT
  created_at,
  amount,
  会话号,
  round(sum(amount) OVER (PARTITION BY 会话号), 2) AS 该会话总额
FROM sessioned
ORDER BY created_at, id;

text

 created_at | amount | 会话号 | 该会话总额
------------+--------+--------+------------
 2026-01-05 | 120.00 |      1 |      670.00
 2026-01-09 | 340.00 |      1 |      670.00
 2026-01-14 | 210.00 |      1 |      670.00
 2026-05-02 | 560.00 |      2 |      990.00
 2026-05-06 | 430.00 |      2 |      990.00
 2026-09-30 | 990.00 |      3 |     1140.00
 2026-10-03 | 150.00 |      3 |     1140.00
(7 rows)

步骤 3：对比 `last_value` 的两种帧写法

sql

-- 同一份数据，默认帧与全分区帧的 last_value 结果截然不同
SELECT
  created_at,
  amount,
  last_value(amount) OVER (
    ORDER BY created_at, id
  ) AS 默认帧,
  last_value(amount) OVER (
    ORDER BY created_at, id
    ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
  ) AS 全分区帧
FROM user_events
WHERE user_id = 1001
ORDER BY created_at, id;

text

 created_at | amount | 默认帧 | 全分区帧
------------+--------+--------+----------
 2026-01-05 | 120.00 | 120.00 |   150.00
 2026-01-09 | 340.00 | 340.00 |   150.00
 2026-01-14 | 210.00 | 210.00 |   150.00
 2026-05-02 | 560.00 | 560.00 |   150.00
 2026-05-06 | 430.00 | 430.00 |   150.00
 2026-09-30 | 990.00 | 990.00 |   150.00
 2026-10-03 | 150.00 | 150.00 |   150.00
(7 rows)

默认帧 列就是「当前行自己的金额」，完全无用；全分区帧 列才是业务想要的「该用户最后一笔金额」。

步骤 4：用 `lag` 找出「沉默后回归」的用户

sql

-- 找出所有「至少沉默 90 天后又重新下单」的订单
WITH gaps AS (
  SELECT
    user_id,
    id,
    created_at,
    amount,
    created_at - lag(created_at) OVER (
      PARTITION BY user_id ORDER BY created_at, id
    ) AS 距上次天数
  FROM user_events
)
SELECT user_id, id, created_at, amount, 距上次天数
FROM gaps
WHERE 距上次天数 > 90
ORDER BY user_id, created_at;

text

 user_id | id | created_at | amount | 距上次天数
---------+----+------------+--------+------------
    1001 |  4 | 2026-05-02 | 560.00 |        108
    1001 |  6 | 2026-09-30 | 990.00 |        147
(2 rows)

步骤 5：用 `EXPLAIN` 观察窗口计划

sql

EXPLAIN (ANALYZE, BUFFERS)
SELECT
  order_no,
  amount,
  lag(amount)  OVER w AS 上一笔,
  lead(amount) OVER w AS 下一笔
FROM orders
WHERE user_id = 42
WINDOW w AS (ORDER BY created_at, id);

text

                                    QUERY PLAN
-----------------------------------------------------------------------------------
 WindowAgg  (cost=... rows=... width=...)
   Window: w AS (ORDER BY created_at, id)
   ->  Sort  (cost=... rows=... width=...)
         Sort Key: created_at, id
         ->  Index Scan using orders_user_id_created_at_id_idx on orders
               Index Cond: (user_id = 42)

两个 lag / lead 共用同一个 WindowAgg 与同一次排序——因为它们属于同一个窗口定义。这提示我们：把 lag / lead / first_value 都写进同一个 WINDOW 子句，能省掉重复排序。

注意事项

last_value 在默认帧下几乎总是错的，必须显式写 ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING：

sql

-- 错误！返回的是当前行自己
last_value(amount) OVER (ORDER BY created_at)

-- 正确
last_value(amount) OVER (
  ORDER BY created_at
  ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
)

lag / lead 的默认值是 NULL，不是 0。报表里直接参与算术运算会得到 NULL：

sql

-- 不推荐：首行的环比为 NULL，后续计算也会被传染
amount - lag(amount) OVER (ORDER BY created_at)

-- 需要数值时显式给默认值
amount - lag(amount, 1, 0) OVER (ORDER BY created_at)

lag / lead 不受帧影响，别指望用帧限制它们：

sql

-- 帧只影响聚合与取值函数，lag 依然能取到分区内的前一行
lag(amount) OVER (ORDER BY created_at ROWS BETWEEN CURRENT ROW AND CURRENT ROW)

nth_value 的下标从 1 开始，写 0 会直接报错：

sql

-- 错误！
SELECT nth_value(amount, 0) OVER (ORDER BY created_at) FROM orders;
-- 报错：argument of nth_value must be greater than zero

first_value 在默认帧下是「正确」的，但语义可能与直觉不符。它返回的是「当前行之前的第一行」，在帧被后续子句收紧时依然正确；而 last_value 却不是对称的。写这两个函数时一律显式声明帧最安全。
IGNORE NULLS 需要 PostgreSQL 16+，低版本会报语法错误：

sql

-- PostgreSQL 16+
lag(amount) IGNORE NULLS OVER (ORDER BY created_at)

-- PostgreSQL 16 之前必须用 count(...) OVER + first_value 的组合替代

分析函数不能出现在 WHERE 或 HAVING 中，必须外套子查询：

sql

-- 错误！
SELECT * FROM orders WHERE lag(amount) OVER (ORDER BY created_at) IS NULL;

-- 正确
SELECT * FROM (
  SELECT *, lag(amount) OVER (ORDER BY created_at, id) AS prev FROM orders
) t
WHERE prev IS NULL;

lead 计算「距下次事件间隔」时，最后一行的结果是 NULL（没有下次）。若业务要求「截至统计时点」，应显式用 coalesce(lead(created_at) OVER w, CURRENT_DATE)。
lag / lead 的参数类型必须一致。默认值 0 与 numeric 列混用时 PostgreSQL 会做隐式转换，但若是 text 列给数字默认值则会报类型错误。

最佳实践

需要前后行值一律优先用 lag / lead，不要用自连接。自连接需要额外排序或嵌套循环，且写法复杂：

sql

-- 推荐：一次排序即可得到前后行
SELECT order_no, amount,
       lag(amount)  OVER w AS 上一笔,
       lead(amount) OVER w AS 下一笔
FROM orders
WINDOW w AS (PARTITION BY user_id ORDER BY created_at, id);

-- 不推荐：自连接需要两次扫描 + 连接
SELECT a.order_no, a.amount, b.amount AS 上一笔
FROM orders a LEFT JOIN orders b
  ON b.user_id = a.user_id AND b.created_at < a.created_at;

排序键一律补唯一列做决胜，否则 lag / lead 取的「前一行」在并列值场景下不确定：

sql

WINDOW w AS (PARTITION BY user_id ORDER BY created_at, id)

取组内首/末值统一用「显式全分区帧」模板：

sql

-- 组内首值
first_value(expr) OVER (
  PARTITION BY 分组列 ORDER BY 排序列
  ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
)

-- 组内末值（注意不能省帧）
last_value(expr) OVER (
  PARTITION BY 分组列 ORDER BY 排序列
  ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
)

多个分析函数写进同一个 WINDOW 子句，让优化器复用同一次排序：

sql

SELECT
  order_no,
  lag(amount)  OVER w AS 上一笔,
  lead(amount) OVER w AS 下一笔,
  first_value(amount) OVER (w ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) AS 首笔
FROM orders
WINDOW w AS (PARTITION BY user_id ORDER BY created_at, id);

给窗口排序建索引，让 WindowAgg 直接消费索引顺序，消除 Sort：

sql

-- 与 (PARTITION BY user_id ORDER BY created_at, id) 匹配
CREATE INDEX ON orders (user_id, created_at, id);

环比、同比一律用 nullif(分母, 0) 防除零，并接受首期为 NULL：

sql

round(100.0 * (本期 - 上期) / nullif(上期, 0), 2) AS 增长率

IGNORE NULLS 优先用原生语法（16+），只在需要兼容旧版本时退回 count(...) OVER + first_value 的两段式写法。
调试 last_value 时先补一个 first_value 做对照：如果 first_value 结果正确而 last_value 等于当前行，就说明帧的终点没写对。

继续学习：聚合窗口函数（SUM / AVG / COUNT）

LAG / LEAD / FIRST_VALUE / LAST_VALUE ​

基础概念 ​

语法与用法 ​

详细说明 ​

lag 与 lead：取前后第 N 行 ​

first_value、last_value 与 nth_value ​

last_value 与默认帧的巨大陷阱 ​

IGNORE NULLS 与 RESPECT NULLS ​

用分析函数做差值与切分 ​

实战示例 ​

步骤 1：准备带时间跨度的小数据集 ​

步骤 2：先看一个错误的嵌套写法 ​

步骤 3：用两个 CTE 把会话号固定下来 ​

步骤 3：对比 last_value 的两种帧写法 ​

步骤 4：用 lag 找出「沉默后回归」的用户 ​

步骤 5：用 EXPLAIN 观察窗口计划 ​

注意事项 ​

最佳实践 ​

参考链接 ​

LAG / LEAD / FIRST_VALUE / LAST_VALUE

基础概念

语法与用法

详细说明

`lag` 与 `lead`：取前后第 N 行

`first_value`、`last_value` 与 `nth_value`

`last_value` 与默认帧的巨大陷阱

`IGNORE NULLS` 与 `RESPECT NULLS`

用分析函数做差值与切分

实战示例

步骤 1：准备带时间跨度的小数据集

步骤 2：先看一个错误的嵌套写法

步骤 3：用两个 CTE 把会话号固定下来

步骤 3：对比 `last_value` 的两种帧写法

步骤 4：用 `lag` 找出「沉默后回归」的用户

步骤 5：用 `EXPLAIN` 观察窗口计划

注意事项

最佳实践

参考链接