LATERAL 与 GROUPING SETS · 两把撑大表达力的武器 · PostgreSQL 特性详解

LATERAL 让 JOIN 右侧的子查询能引用左表的列——「每个用户最新 3 单」这类每组 Top-N 一句写完；GROUPING SETS / ROLLUP / CUBE 让一条 SQL 同时输出多种粒度的小计与总计，一次查出整张透视表。

LATERAL 与 GROUPING SETS · 两把撑大表达力的武器

这一章把两把「让 SQL 少写很多代码」的武器放在一起：LATERAL 解决「对左表每一行，跑一次带参数的子查询」，GROUPING SETS 解决「一次算出多种粒度的汇总」。它们各自都能替掉一堆应用层拼接。

LATERAL：让右子查询看见左表

普通 JOIN 有个限制：右侧子查询不能引用左侧的列。加上 LATERAL 关键字，就可以了——语义是「对左表的每一行，执行一次右侧子查询」，像在 FROM 里写了个能带参数的相关子查询。

它最经典的用途是每组 Top-N。「每个用户最新的 2 单」：

SELECT u.name, o.placed_at, o.amount
FROM users u,
     LATERAL (
       SELECT placed_at, amount FROM orders
       WHERE user_id = u.id            -- ← 引用了左表 u.id,这就是 LATERAL 的本事
       ORDER BY placed_at DESC LIMIT 2
     ) o;

Alice 2025-04-01 ¥50    Bob 2025-03-15 ¥500    Carol 2025-02-20 ¥999
Alice 2025-03-01 ¥300   Bob 2025-01-15 ¥75

比起「窗口函数编号再过滤」，LATERAL 版有个性能优势：配上 orders(user_id, placed_at DESC) 索引，它对每个用户只取前 2 行就停，不必给全表排名再筛。用户多、每组只取少数几行时，这往往更快。

LATERAL 还擅长两件事：

-- ① 配 generate_series:给每个用户补齐「每月一行」(填洞)
SELECT u.name, m.month, COALESCE(s.total, 0)
FROM users u
CROSS JOIN LATERAL generate_series('2025-01-01'::date, '2025-04-01', '1 month') AS m(month)
LEFT JOIN ...;

-- ② 配返回多行的函数:把每条描述里的 #标签 全拆出来
SELECT t.tag, count(*)
FROM tickets, LATERAL regexp_matches(descr, '#(\w+)', 'g') AS t(tag)
GROUP BY t.tag;
-- ['bug']: 2 | ['urgent']: 1

见过 SQL Server 的 CROSS APPLY / OUTER APPLY 吗？那就是 LATERAL——同一个概念的不同名字。CROSS JOIN LATERAL ≈ CROSS APPLY（右侧空则该左行消失），LEFT JOIN LATERAL ... ON true ≈ OUTER APPLY（右侧空仍保留左行）。执行上，planner 通常用嵌套循环：外层每出一行，内层子查询跑一次，所以内层的索引至关重要。

GROUPING SETS：一条 SQL 出一张透视表

普通 GROUP BY 一次只能按一种维度组合分组。做报表时你往往既要「按地区小计」、又要「按部门小计」、还要「总计」——传统做法是查三次再拼。GROUPING SETS 让你一条 SQL 同时拿到多种粒度：

SELECT region, dept, sum(amount)
FROM sales
GROUP BY GROUPING SETS ((region), (dept), ());

[按 region] East  -     110      [按 dept] -    Eng   90
[按 region] North -      30      [按 dept] -    Sales 120
[按 region] South -      70      [总计   ] -    -     210

() 是空分组，即「全表总计」。这一条查询里，region 小计、dept 小计、总计一次出齐。

ROLLUP 和 CUBE 是 GROUPING SETS 的两个便捷简写：

ROLLUP (region, dept) —— 产生层级小计：(region, dept) → (region) → ()。适合「大类套小类」的层级汇总。
CUBE (region, dept) —— 产生全部 2ⁿ 组合：(region,dept)、(region)、(dept)、()。适合要交叉分析所有维度组合的透视表。

SELECT region, dept, sum(amount) FROM sales GROUP BY ROLLUP (region, dept);
-- region+dept 明细 → 每个 region 小计 → 总计,层层收拢

GROUPING()：分清「小计行」和「明细行」

多粒度混在一个结果集里，有个现实问题：某行的 dept 是 NULL，到底是「这行真的没部门」，还是「这是 region 的小计行」？GROUPING() 函数专门回答这个——它对「因为是小计而被置空的列」返回 1：

SELECT region, dept, sum(amount),
       GROUPING(region) AS g_r, GROUPING(dept) AS g_d
FROM sales GROUP BY ROLLUP (region, dept);

[明细    ] East Eng   50      g_r=0 g_d=0
[region 小计] East -   110      g_r=0 g_d=1   ← dept 被 rollup 置空
[总计    ] -    -     210      g_r=1 g_d=1   ← 两列都被置空

前端拿到 g_r / g_d，就能给小计行加粗、给总计行描边，或者做折叠——不用猜 NULL 的含义。

横向对比

能力	PostgreSQL	MySQL	Oracle	SQL Server
LATERAL	PG 9.3+	8.0.14+	12c+	`CROSS/OUTER APPLY`
GROUPING SETS	有	❌ 无	有	有
ROLLUP	有（标准语法）	有（`WITH ROLLUP`，弱）	有	有
CUBE	有	❌ 无	有	有

差距集中在多维汇总：MySQL 只有一个残缺的 WITH ROLLUP，没有 GROUPING SETS 和 CUBE，想做交叉透视只能查多次拼。PostgreSQL、Oracle、SQL Server 都是完整的一套。

应用场景

每组 Top-N：每个用户最近 N 单、每个类目最贵 N 件——LATERAL + LIMIT + 索引，又快又直观。
调用集合返回函数：generate_series 补时间序列、regexp_matches / unnest 展开多值，LATERAL 把它们和左表连起来。
报表透视表：一条 GROUPING SETS / CUBE 出「行小计 + 列小计 + 总计」，替代前端多次查询拼合。
财务多维汇总：按「地区 × 部门 × 季度」的各种粒度小计，CUBE 一次算全。

小结与下一章

LATERAL 把「相关子查询」搬进了 FROM，让每组 Top-N 和集合函数连接变得自然；GROUPING SETS / ROLLUP / CUBE 则把「多粒度汇总」压进一条 SQL。两者都是把本该在应用层反复查询、拼接的活，收敛成数据库一次算完。

查询表达力的最后一站，是一个常被以为「必须上 Elasticsearch」的能力——全文检索。PostgreSQL 内置就能做分词、词干归一、相关性排序。👉 全文检索：数据库自带的搜索引擎。

参考文献

[1] PostgreSQL 官方文档.「7.2.1.5. LATERAL Subqueries.」https://www.postgresql.org/docs/current/queries-table-expressions.html#QUERIES-LATERAL —— LATERAL 语义与用法。
[2] PostgreSQL 官方文档.「7.2.4. GROUPING SETS, CUBE, and ROLLUP.」https://www.postgresql.org/docs/current/queries-table-expressions.html#QUERIES-GROUPING-SETS —— 多维分组与 GROUPING()。

LATERAL 与 GROUPING SETS · 两把撑大表达力的武器

本页目录