LATERAL 与 DISTINCT ON：取每组第一名的三种写法

准备中…重置数据集

「取每个分组里的第一名」是最常见的 SQL 需求之一，Postgres 提供了三种写法。它们的性能差距可以达到两个数量级，而且快慢关系会随索引存在与否而反转——这一节把三种写法和它们的适用条件讲清楚。

需求：每个用户最近的一笔订单。

写法一：DISTINCT ON

这是 Postgres 独有的语法，标准 SQL 里没有：

SELECT DISTINCT ON (user_id)
     user_id, id, created_at::date, amount
FROM orders
ORDER BY user_id, created_at DESC, id DESC
LIMIT 5;

▶ 运行⌘/Ctrl + Enter

语义是：按 ORDER BY 排序后，每个 DISTINCT ON 表达式的值只保留第一行。

有两条硬规则：

1. ORDER BY 的前缀必须是 DISTINCT ON 的表达式（这里 user_id 必须排在最前）；
2. 「第一行」由 ORDER BY 的后续列决定（这里 created_at DESC 决定了取最近的）。

我加了 id DESC 做最终 tiebreaker。这不是可有可无的——如果同一用户有两笔时间完全相同的订单，不加 tiebreaker 时返回哪一笔是不确定的，同一条 SQL 两次执行可能给出不同结果。

写法二：窗口函数

标准 SQL 的写法，可移植：

SELECT user_id, id, created_at::date, amount
FROM (
SELECT user_id, id, created_at, amount,
       row_number() OVER (PARTITION BY user_id ORDER BY created_at DESC, id DESC) AS rn
FROM orders
) t
WHERE rn = 1
ORDER BY user_id
LIMIT 5;

▶ 运行⌘/Ctrl + Enter

好处是灵活：把 rn = 1 改成 rn <= 3 就变成「每组前三名」，DISTINCT ON 做不到这一点。

写法三：LATERAL

LATERAL 让子查询能够引用它左边表的列——普通子查询做不到这件事：

SELECT u.id AS user_id, o.id AS order_id, o.created_at::date, o.amount
FROM users u
CROSS JOIN LATERAL (
SELECT id, created_at, amount
FROM orders
WHERE user_id = u.id           -- 这里引用了左表的 u.id，没有 LATERAL 就是语法错误
ORDER BY created_at DESC, id DESC
LIMIT 1
) o
WHERE u.id <= 5
ORDER BY u.id;

▶ 运行⌘/Ctrl + Enter

想要「每人前三」，把 LIMIT 1 改成 LIMIT 3 就行：

SELECT u.id AS user_id, o.id AS order_id, o.created_at::date, o.amount
FROM users u
CROSS JOIN LATERAL (
SELECT id, created_at, amount FROM orders
WHERE user_id = u.id ORDER BY created_at DESC, id DESC LIMIT 3
) o
WHERE u.id <= 2
ORDER BY u.id, o.created_at DESC;

▶ 运行⌘/Ctrl + Enter

CROSS JOIN LATERAL 会丢掉没有订单的用户

CROSS JOIN LATERAL 在子查询返回空集时会丢掉左表那一行。要保留，用 LEFT JOIN LATERAL (...) ON true。

这是 LATERAL 最常见的 bug：统计报表里「零订单用户」凭空消失。

实测：三种写法差多少

先看没有合适索引的情况。

SELECT DISTINCT ON (user_id) user_id, id, amount FROM orders ORDER BY user_id, created_at DESC, id DESC

▶ 分析执行计划

SELECT u.id AS user_id, o.id, o.amount FROM users u CROSS JOIN LATERAL (SELECT id, amount FROM orders WHERE user_id = u.id ORDER BY created_at DESC, id DESC LIMIT 1) o

▶ 分析执行计划

LATERAL 的计划里，内层 Seq Scan on orders 的 loops 是 1000——每个用户扫一遍全表。10000 行 × 1000 次，这是灾难。

现在建一个匹配的索引：

CREATE INDEX IF NOT EXISTS idx_orders_user_recent
ON orders (user_id, created_at DESC, id DESC);
ANALYZE orders;

▶ 运行⌘/Ctrl + Enter

SELECT u.id AS user_id, o.id, o.amount FROM users u CROSS JOIN LATERAL (SELECT id, amount FROM orders WHERE user_id = u.id ORDER BY created_at DESC, id DESC LIMIT 1) o

▶ 分析执行计划

SELECT DISTINCT ON (user_id) user_id, id, amount FROM orders ORDER BY user_id, created_at DESC, id DESC

▶ 分析执行计划

在我的实测里（PostgreSQL 18.3，1000 用户 / 10000 订单）：

写法	无索引	有索引 (user_id, created_at DESC, id DESC)
DISTINCT ON	7.4 ms	3.8 ms
窗口函数	6.4 ms	4.3 ms
LATERAL	446 ms	1.8 ms

★ 快慢关系反转了。理解这个反转，比记住哪种写法快重要得多：

• DISTINCT ON 和窗口函数都是一次扫过全部数据再筛。有索引时省掉排序（Index Scan 替代 Sort），但仍然要读完 10000 个索引项。
• LATERAL 是每个分组独立查一次。没有索引时每次都要全表扫描（1000 × 10000）；有了索引，每次只需定位到该用户的第一个索引项就 LIMIT 1 返回——实际读取行数从 10000 降到 1000。

选择规则

• 分组数少、每组数据多（1000 个用户 / 千万订单）→ 用 LATERAL + 匹配索引，差距是数量级的。
• 分组数多、每组数据少，或者反正要全表扫 → 用 DISTINCT ON，写法最简洁。
• 需要每组前 N 名且 N > 1 → 窗口函数或 LATERAL，DISTINCT ON 只能取 1。
• 要保证跨数据库可移植 → 窗口函数。

LATERAL 真正不可替代的场景

取 top-N 只是 LATERAL 的入门用法。它真正无可替代的地方是：子查询需要用左表的值做参数计算。

SELECT u.id, u.name, u.created_at::date AS 注册日,
     s.cnt AS 注册后订单数, s.avg_amount AS 均单价
FROM users u
CROSS JOIN LATERAL (
SELECT count(*) AS cnt, round(avg(amount), 2) AS avg_amount
FROM orders
WHERE user_id = u.id
  AND created_at >= u.created_at    -- 每个用户的时间窗口不同
) s
WHERE u.id <= 5
ORDER BY u.id;

▶ 运行⌘/Ctrl + Enter

每个用户的统计窗口起点都不一样（各自的注册时间）。这种「参数化的相关聚合」用普通 JOIN 写不出来——JOIN 的 ON 条件不能给右侧子查询提供 LIMIT 或聚合边界。

判断标准很简单：如果子查询里出现了左表的列，并且它影响的是子查询的 LIMIT、ORDER BY 或聚合范围，那就只能用 LATERAL。

本节验收

先自己回答，再点开对照。

DISTINCT ON 的 ORDER BY 有什么硬性要求？为什么必须加最终 tiebreaker？

硬性要求：ORDER BY 的前缀必须是 DISTINCT ON 里的表达式。写 DISTINCT ON (user_id) 就必须 ORDER BY user_id, ...，否则直接报错。

tiebreaker 的必要性：「保留第一行」由 ORDER BY 的后续列决定。如果同一个 user_id 下有两行 created_at 完全相同，返回哪一行是不确定的——同一条 SQL 两次执行可能给出不同结果。

常见错误：认为「时间戳精确到微秒，不可能撞」。批量导入、同一事务内的多行插入、now() 在同一语句内返回相同值——这些场景下时间戳撞车非常常见。加一个 id DESC 成本是零，不加则是一颗随机哑弹。

为什么 LATERAL 在没有索引时会慢两个数量级？从执行计划的哪个字段能看出来？

因为 LATERAL 的执行模型是 Nested Loop：外层每一行都触发一次内层查询。没有索引时，每次内层查询都是一次全表扫描——1000 个用户 × 10000 行订单 = 一千万行的扫描量。

看执行计划的 loops 字段。内层节点显示 loops=1000 就意味着它被执行了 1000 次，而 actual time 和 rows 都是单次的平均值，要乘以 loops 才是总量。

常见错误：看到内层 actual time=0.4ms 就以为很快。0.4 × 1000 = 400ms，这才是真实开销。这是读 EXPLAIN 最常见的误读，本书专门讲了一节。

什么情况下应该优先选 LATERAL 而不是 DISTINCT ON？

分组数少、每组数据多，且有匹配索引时。比如 1000 个用户 / 千万条订单，配上 (user_id, created_at DESC) 索引——LATERAL 每组只需定位到第一个索引项就 LIMIT 1 返回，总读取量是「分组数」而不是「总行数」。

实测：有索引时 LATERAL 1.8 ms，DISTINCT ON 3.8 ms；没索引时反过来，LATERAL 446 ms，DISTINCT ON 7.4 ms。

常见错误：记住「LATERAL 更快」这个结论就直接用。快慢关系完全取决于索引是否存在——这是本节最重要的一点。没有匹配索引时用 LATERAL 是灾难性的选择。

（回到实测对比）

举一个 LATERAL 不可被普通子查询替代的例子。

「统计每个用户注册之后的订单数」——每个用户的时间窗口起点都不同：

SELECT u.id, s.cnt
FROM users u
CROSS JOIN LATERAL (
  SELECT count(*) AS cnt FROM orders
  WHERE user_id = u.id AND created_at >= u.created_at
) s;

判断标准：子查询里出现了左表的列，并且它影响的是子查询的 LIMIT、ORDER BY 或聚合范围，就只能用 LATERAL。普通子查询看不到左表，JOIN 的 ON 条件也没法给右侧子查询提供 LIMIT 边界。

常见错误：以为相关子查询（correlated subquery）能替代。SELECT (SELECT count(*) FROM ... WHERE user_id = u.id) 确实能引用外层列，但它只能返回一个标量值。要返回多列或多行（比如「每人最近三笔订单的 id 和金额」），就非 LATERAL 不可。

CROSS JOIN LATERAL 和 LEFT JOIN LATERAL ... ON true 的区别是什么？

子查询返回空集时：CROSS JOIN LATERAL 会丢掉左表那一行，LEFT JOIN LATERAL ... ON true 会保留它并把右侧填成 NULL。

常见错误：在统计报表里用 CROSS JOIN LATERAL，然后「零订单用户」凭空消失，总数对不上。这是 LATERAL 最常见的 bug，而且很难发现——结果看起来完全正常，只是少了几行。

判断方法：问自己「子查询有没有可能返回 0 行」。只要有可能，就用 LEFT JOIN LATERAL ... ON true。