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

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

- 作者：David（道雾轩）
- 专栏：PostgreSQL 特性详解（https://daiw.org/manual/postgresql.md）
- 最后更新：2026-07-13
- 原文：https://daiw.org/manual/postgresql/lateral-grouping
- 转载与引用：请注明出处并附原文链接（https://daiw.org/about/copyright）

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

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

## LATERAL：让右子查询看见左表

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

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

```sql
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;
```

```text
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 还擅长两件事：

```sql
-- ① 配 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
```

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

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

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

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

```text
[按 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)`、`()`。适合要交叉分析所有维度组合的透视表。

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

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

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

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

```text
[明细    ] 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 内置就能做分词、词干归一、相关性排序。👉 [全文检索：数据库自带的搜索引擎](https://daiw.org/manual/postgresql/full-text-search)。

## 参考文献

- [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()`。