数据库基础体系 · 第 72/139 篇。文章以各产品官方稳定版本的公开语义为准；示例会明确引擎、事务与部署边界。

SQL 聚合与集合：GROUPING SETS、ROLLUP、CUBE、UNION 和去重

在报表和分析查询中，经常需要同时得到多种粒度的数据：

• 按“区域、渠道”统计明细；
• 按“区域”统计小计；
• 按“渠道”统计小计；
• 统计全局总计；
• 将多条查询结果合并；
• 去除重复行，或在每个分组内对某个指标去重计数。

GROUPING SETS、ROLLUP、CUBE 和 UNION 都能产生“多组结果”，但它们解决的问题不同：

• GROUPING SETS：明确指定要计算哪些分组粒度；
• ROLLUP：按层级逐级汇总；
• CUBE：计算所有维度组合；
• UNION：合并多个查询结果；
• DISTINCT 及相关去重操作：消除结果行或聚合输入中的重复值。

理解这些语法的关键，不是记住几个关键字，而是明确：

1. 输入行如何被分组；
2. 每个分组集合产生哪些结果行；
3. NULL 到底是业务值，还是汇总标记；
4. 多个结果集合如何合并；
5. 去重发生在什么阶段。

本文以 PostgreSQL 当前稳定版本的标准语义为主，示例可以在 PostgreSQL 中执行。MySQL 8.4 的差异会单独说明。

---

一、理解高级聚合前的基础：GROUP BY 到底做了什么

先建立一份示例销售数据。

WITH sales(region, channel, month, amount) AS (
    VALUES
        ('East'::text, 'Online'::text, 'Jan'::text, 100::numeric),
        ('East',        'Online',        'Feb',       120),
        ('East',        'Store',         'Jan',        80),
        ('West',        'Online',        'Jan',        90),
        ('West',        'Store',         'Feb',        70),
        (NULL,          'Online',        'Jan',        50)
)
SELECT
    region,
    channel,
    SUM(amount) AS total_amount
FROM sales
GROUP BY region, channel
ORDER BY region NULLS LAST, channel;

结果为：

region	channel	total_amount
East	Online	220
East	Store	80
West	Online	90
West	Store	70
NULL	Online	50

1. GROUP BY 形成等价类

对 GROUP BY region, channel 而言，两行属于同一组，当且仅当它们的 region 和 channel 在分组语义下相同。

可以形式化为：

$$x \sim_G y \iff x.region = y.region \land x.channel = y.channel$$

SQL 中，分组时的 NULL 会被视为同一组中的相同分组值。因此上面的 (NULL, Online) 会形成一个普通分组。

这与普通的三值逻辑比较需要区分：

NULL = NULL

结果不是 TRUE，而是 UNKNOWN；但 GROUP BY 仍然会把多个 NULL 放到同一个分组中。

2. 聚合函数在每个组内执行

对于每个分组，SUM(amount) 只读取该分组中的 amount 值。

如果把某个分组记为 $C$，则：

$$SUM(C) = \sum_{r \in C} r.amount$$

但要注意：

• COUNT(*) 统计分组中的行数；
• COUNT(column) 不统计 column IS NULL 的行；
• SUM、AVG 等通常忽略 NULL；
• 一个分组存在，但其中聚合输入全是 NULL 时，SUM 可能返回 NULL，而不是 0。

例如：

SELECT
    COUNT(*) AS row_count,
    COUNT(amount) AS non_null_amount_count,
    SUM(amount) AS amount_sum
FROM (
    VALUES
        (NULL::numeric),
        (NULL::numeric)
) AS t(amount);

结果大致为：

row_count	non_null_amount_count	amount_sum
2	0	NULL

如果业务上需要把这种结果显示为 0，应明确写出：

COALESCE(SUM(amount), 0)

COALESCE 是显示和业务语义处理，不应与分组逻辑混为一谈。

3. WHERE、GROUP BY、HAVING 的顺序

一个聚合查询可以先抽象为：

1. FROM：产生输入关系；
2. WHERE：过滤输入行；
3. GROUP BY：形成分组；
4. 聚合函数：计算每组指标；
5. HAVING：过滤分组结果；
6. SELECT：形成输出列；
7. ORDER BY：排序最终结果。

例如：

WITH sales(region, channel, month, amount) AS (
    VALUES
        ('East'::text, 'Online'::text, 'Jan'::text, 100::numeric),
        ('East',        'Online',        'Feb',       120),
        ('East',        'Store',         'Jan',        80),
        ('West',        'Online',        'Jan',        90),
        ('West',        'Store',         'Feb',        70),
        (NULL,          'Online',        'Jan',        50)
)
SELECT
    region,
    SUM(amount) AS total_amount
FROM sales
WHERE month = 'Jan'
GROUP BY region
HAVING SUM(amount) >= 100;

这里的 HAVING 不能替代 WHERE：

• WHERE month = 'Jan' 在分组前过滤明细行；
• HAVING SUM(amount) >= 100 在分组后过滤统计结果。

如果把月份条件错误地写到 HAVING 中，通常会改变统计含义。

---

二、GROUPING SETS：一次查询计算指定的多个粒度

1. 基本含义

普通写法：

GROUP BY region, channel

只计算一个分组集合：

$$\{region, channel\}$$

而：

GROUP BY GROUPING SETS (
    (region, channel),
    (region),
    (channel),
    ()
)

表示分别计算四个分组集合：

$$\{ \{region, channel\}, \{region\}, \{channel\}, \{\} \}$$

其中：

• (region, channel)：区域和渠道明细；
• (region)：区域小计；
• (channel)：渠道小计；
• ()：全表总计。

示例：

WITH sales(region, channel, month, amount) AS (
    VALUES
        ('East'::text, 'Online'::text, 'Jan'::text, 100::numeric),
        ('East',        'Online',        'Feb',       120),
        ('East',        'Store',         'Jan',        80),
        ('West',        'Online',        'Jan',        90),
        ('West',        'Store',         'Feb',        70),
        (NULL,          'Online',        'Jan',        50)
)
SELECT
    region,
    channel,
    SUM(amount) AS total_amount
FROM sales
GROUP BY GROUPING SETS (
    (region, channel),
    (region),
    (channel),
    ()
)
ORDER BY
    GROUPING(region),
    region NULLS LAST,
    GROUPING(channel),
    channel NULLS LAST;

结果包含四类行。

部分结果如下：

region	channel	total_amount	行的含义
East	Online	220	区域-渠道明细
East	Store	80	区域-渠道明细
West	Online	90	区域-渠道明细
West	Store	70	区域-渠道明细
NULL	Online	50	真实的 NULL 区域明细
East	NULL	300	East 区域小计
West	NULL	160	West 区域小计
NULL	NULL	50	NULL 区域小计
NULL	Online	360	Online 渠道小计
NULL	Store	150	Store 渠道小计
NULL	NULL	510	全局总计

这里出现了一个重要问题：同样是 NULL，可能表示两种完全不同的事情：

1. 原始数据中的 region 就是 NULL；
2. 当前结果是按 region 汇总后，region 被省略了。

因此，不能通过观察结果列是否为 NULL 来判断行的层级。

2. 用 GROUPING 区分真实 NULL 和汇总 NULL

PostgreSQL 提供 GROUPING(expression)：

• 返回 0：该表达式参与了当前分组；
• 返回 1：该表达式在当前分组中被省略，是由更高层级汇总产生的 NULL。

WITH sales(region, channel, month, amount) AS (
    VALUES
        ('East'::text, 'Online'::text, 'Jan'::text, 100::numeric),
        ('East',        'Online',        'Feb',       120),
        ('East',        'Store',         'Jan',        80),
        ('West',        'Online',        'Jan',        90),
        ('West',        'Store',         'Feb',        70),
        (NULL,          'Online',        'Jan',        50)
)
SELECT
    region,
    channel,
    GROUPING(region) AS region_is_subtotal,
    GROUPING(channel) AS channel_is_subtotal,
    SUM(amount) AS total_amount
FROM sales
GROUP BY GROUPING SETS (
    (region, channel),
    (region),
    (channel),
    ()
)
ORDER BY
    GROUPING(region),
    region NULLS LAST,
    GROUPING(channel),
    channel NULLS LAST;

例如：

region	channel	region_is_subtotal	channel_is_subtotal	total_amount
NULL	Online	0	0	50
East	NULL	0	1	300
NULL	Online	1	0	360
NULL	NULL	1	1	510

含义分别是：

• (NULL, Online, 0, 0)：真实的 NULL 区域；
• (East, NULL, 0, 1)：区域小计；
• (NULL, Online, 1, 0)：渠道小计；
• (NULL, NULL, 1, 1)：总计。

GROUPING 的结果不仅用于显示标签，也用于后续排序、导出和下游程序判断行类型。

可以进一步生成报表标签：

CASE
    WHEN GROUPING(region) = 1 THEN '全部区域'
    WHEN region IS NULL THEN '未知区域'
    ELSE region
END

不能只写：

COALESCE(region, '全部区域')

因为这会把“未知区域”和“全部区域”错误地合并。

3. GROUPING(expression1, expression2) 的位掩码

PostgreSQL 还支持：

GROUPING(region, channel)

它把多个 GROUPING 标记组合成一个整数。每个参数对应一位，右侧参数对应低位。

因此，对于两个参数：

GROUPING(region)	GROUPING(channel)	GROUPING(region, channel)
0	0	0
0	1	1
1	0	2
1	1	3

实际报表中，如果只需要少量维度，分别输出 GROUPING(region) 和 GROUPING(channel) 往往比依赖位掩码更容易阅读。

4. GROUPING SETS 的展开逻辑

下面的查询：

SELECT
    region,
    channel,
    SUM(amount) AS total_amount
FROM sales
GROUP BY GROUPING SETS (
    (region, channel),
    (region),
    (channel),
    ()
);

可以从结果语义上理解为：

SELECT region, channel, SUM(amount)
FROM sales
GROUP BY region, channel

UNION ALL

SELECT region, NULL, SUM(amount)
FROM sales
GROUP BY region

UNION ALL

SELECT NULL, channel, SUM(amount)
FROM sales
GROUP BY channel

UNION ALL

SELECT NULL, NULL, SUM(amount)
FROM sales;

这不是说优化器必须真的执行四次扫描，而是说明两种写法产生的逻辑结果。

GROUPING SETS 的优势在于：

• 查询结构明确；
• 可以避免手写大量重复聚合逻辑；
• 优化器有机会共享排序、哈希或扫描工作；
• 每个分组集合仍然具有独立的聚合语义。

但不能简单断言它一定比多个 UNION ALL 快。实际计划取决于数据规模、索引、排序、并行度、聚合算法和执行引擎。

---

三、ROLLUP：按层级逐级汇总

1. ROLLUP 的形式化展开

GROUP BY ROLLUP (a, b, c)

等价于以下分组集合：

GROUP BY GROUPING SETS (
    (a, b, c),
    (a, b),
    (a),
    ()
)

也就是从最细粒度开始，依次删除最右侧维度。

因此，ROLLUP 不是“所有组合”，而是一个有顺序的层级路径：

$$(a,b,c) \rightarrow (a,b) \rightarrow (a) \rightarrow ()$$

示例：

WITH sales(region, channel, month, amount) AS (
    VALUES
        ('East'::text, 'Online'::text, 'Jan'::text, 100::numeric),
        ('East',        'Online',        'Feb',       120),
        ('East',        'Store',         'Jan',        80),
        ('West',        'Online',        'Jan',        90),
        ('West',        'Store',         'Feb',        70),
        (NULL,          'Online',        'Jan',        50)
)
SELECT
    region,
    channel,
    month,
    SUM(amount) AS total_amount,
    GROUPING(region) AS g_region,
    GROUPING(channel) AS g_channel,
    GROUPING(month) AS g_month
FROM sales
GROUP BY ROLLUP (region, channel, month)
ORDER BY
    GROUPING(region),
    region NULLS LAST,
    GROUPING(channel),
    channel NULLS LAST,
    GROUPING(month),
    month NULLS LAST;

其分组集合是：

1. (region, channel, month)：最细明细；
2. (region, channel)：区域-渠道小计；
3. (region)：区域小计；
4. ()：全局总计。

2. ROLLUP 的维度顺序决定层级

下面两个查询并不等价：

GROUP BY ROLLUP (region, channel)

产生：

(region, channel)
(region)
()

而：

GROUP BY ROLLUP (channel, region)

产生：

(channel, region)
(channel)
()

第一个表示“区域下按渠道汇总”，第二个表示“渠道下按区域汇总”。

因此，ROLLUP 的参数顺序具有业务含义。它不是普通集合，因为：

$$ROLLUP(a,b) \neq ROLLUP(b,a)$$

虽然两者都包含 (a,b) 和 ()，但中间的小计层不同。

3. 组合项：一次保留多个维度

有时 region 和 channel 必须作为一个整体，不能在层级中被拆开。例如：

GROUP BY ROLLUP ((region, channel), month)

其逻辑分组集合为：

GROUPING SETS (
    (region, channel, month),
    (region, channel),
    ()
)

这里的第一个 ROLLUP 项是组合项 (region, channel)，所以不会产生只有 region 或只有 channel 的中间层。

这与下面的查询不同：

GROUP BY ROLLUP (region, channel, month)

后者会产生：

(region, channel, month)
(region, channel)
(region)
()

4. ROLLUP 适合真正存在层级关系的维度

适合使用 ROLLUP 的例子：

年份 -> 季度 -> 月份
国家 -> 省份 -> 城市
部门 -> 团队 -> 员工

但要谨慎处理“看起来有顺序、实际上没有层级”的字段。例如：

地区 -> 渠道

通常并不表示渠道属于地区的业务层级，只是两个分析维度。此时如果既需要区域小计，又需要渠道小计，应使用 GROUPING SETS，而不是误用 ROLLUP。

---

四、CUBE：计算所有维度组合

1. CUBE 的形式化展开

GROUP BY CUBE (a, b, c)

会产生所有维度子集：

GROUP BY GROUPING SETS (
    (a, b, c),
    (a, b),
    (a, c),
    (b, c),
    (a),
    (b),
    (c),
    ()
)

如果有 $n$ 个独立维度，分组集合数量为：

$$2^n$$

其中：

• $n$ 是维度数量；
• 每个维度都有“保留”或“省略”两种状态；
• 所有状态组合构成幂集。

两个维度时有：

$$2^2 = 4$$

三个维度时有：

$$2^3 = 8$$

十个维度时理论上有：

$$2^{10} = 1024$$

这还没有计算每个分组集合内部的实际分组数量，因此 CUBE 很容易产生大量结果。

2. CUBE 示例

WITH sales(region, channel, month, amount) AS (
    VALUES
        ('East'::text, 'Online'::text, 'Jan'::text, 100::numeric),
        ('East',        'Online',        'Feb',       120),
        ('East',        'Store',         'Jan',        80),
        ('West',        'Online',        'Jan',        90),
        ('West',        'Store',         'Feb',        70),
        (NULL,          'Online',        'Jan',        50)
)
SELECT
    region,
    channel,
    month,
    SUM(amount) AS total_amount,
    GROUPING(region) AS g_region,
    GROUPING(channel) AS g_channel,
    GROUPING(month) AS g_month
FROM sales
GROUP BY CUBE (region, channel, month)
ORDER BY
    GROUPING(region),
    region NULLS LAST,
    GROUPING(channel),
    channel NULLS LAST,
    GROUPING(month),
    month NULLS LAST;

它会产生：

• (region, channel, month)；
• (region, channel)；
• (region, month)；
• (channel, month)；
• (region)；
• (channel)；
• (month)；
• ()。

3. CUBE 与 ROLLUP 的核心区别

CUBE (region, channel)

产生：

(region, channel)
(region)
(channel)
()

而：

ROLLUP (region, channel)

只产生：

(region, channel)
(region)
()

因此：

• ROLLUP 保留一条有方向的层级路径；
• CUBE 枚举所有分析方向；
• GROUPING SETS 允许精确选择所需集合。

如果报表只需要“明细、区域小计、总计”，使用 CUBE 会产生不需要的渠道小计，增加结果数量和处理成本。

4. CUBE 中使用组合项

GROUP BY CUBE ((region, channel), month)

把 (region, channel) 当作一个整体维度，因此逻辑上只有四种组合：

GROUPING SETS (
    (region, channel, month),
    (region, channel),
    (month),
    ()
)

不会产生 (region, month) 或 (channel, month) 这种拆分组合。

---

五、GROUPING SETS、ROLLUP、CUBE 的共同语义

这三种语法都可以理解为“多个分组集合的联合”。

设原始输入关系为 $R$，每个分组集合为 $G_i$。对每个 $G_i$，执行一次逻辑上的：

$$\gamma_{G_i,\ aggregate}(R)$$

然后把这些聚合结果合并起来：

$$\bigcup_i \gamma_{G_i,\ aggregate}(R)$$

这里的“合并”在 SQL 聚合扩展语义中需要注意重复分组集合的问题。

例如：

SELECT
    region,
    SUM(amount) AS total_amount
FROM sales
GROUP BY GROUPING SETS (
    (region),
    (region)
);

两个分组集合完全相同，逻辑上可能生成两份相同结果。不要依赖外层 DISTINCT 去掩盖这种错误，因为：

• 重复行可能被错误隐藏；
• 如果还有层级标记、排序标记或其他列，重复行未必完全相同；
• 更重要的是，重复结果往往表示查询结构本身有问题。

HAVING 会作用于每个分组集合

SELECT
    region,
    channel,
    SUM(amount) AS total_amount
FROM sales
GROUP BY GROUPING SETS (
    (region, channel),
    (region),
    ()
)
HAVING SUM(amount) >= 100;

这里的 HAVING 会分别过滤：

• 区域-渠道明细组；
• 区域小计组；
• 总计组。

它不是只对最细粒度结果过滤。

如果要让“明细行”和“小计行”使用不同的过滤条件，需要结合 GROUPING 显式表达，而不能假设 HAVING 会自动识别层级。

条件聚合可以同时产生不同指标

SELECT
    region,
    channel,
    SUM(amount) AS all_amount,
    SUM(amount) FILTER (WHERE month = 'Jan') AS jan_amount
FROM sales
GROUP BY GROUPING SETS (
    (region, channel),
    (region),
    ()
);

FILTER 只限制某个聚合函数的输入，不影响其他聚合函数，也不等同于 WHERE。

如果使用 MySQL，需要改写为：

SUM(CASE WHEN month = 'Jan' THEN amount ELSE 0 END)

因为 PostgreSQL 的 FILTER (WHERE ...) 不是 MySQL 8.4 中的通用写法。

---

六、UNION：合并多个查询结果

1. UNION 的基本约束

UNION 连接两个或多个查询：

SELECT ...
UNION
SELECT ...

参与合并的查询必须满足：

1. 输出列数量相同；
2. 对应位置的类型可以兼容；
3. 结果列名通常取第一个查询的列名。

例如：

SELECT 1 AS value
UNION
SELECT 2;

结果为：

value
1
2

2. UNION 与 UNION ALL

UNION

默认执行重复消除，相当于结果级别的去重。

UNION ALL

保留所有结果行，不进行重复消除。

示例：

SELECT 1 AS value
UNION
SELECT 1 AS value;

结果只有一行：

value
1

而：

SELECT 1 AS value
UNION ALL
SELECT 1 AS value;

结果有两行：

value
1
1

从集合理论角度看：

• UNION 接近集合并集；
• UNION ALL 保留 SQL 关系常见的多重集（bag）语义。

SQL 表和查询结果通常允许重复行，因此 UNION ALL 才是“不主动去重”的合并方式。

3. NULL 在 UNION 去重中的行为

在 UNION 的重复消除阶段，两个结果行如果除 NULL 外其他列相同，通常会被视为重复行，即使普通比较中：

NULL = NULL

不是 TRUE。

例如：

SELECT NULL::text AS value
UNION
SELECT NULL::text AS value;

只返回一行。

这说明“分组语义”“集合去重语义”和“普通谓词比较语义”不是完全同一个概念。

4. ORDER BY 属于整个 UNION 结果

通常应写成：

SELECT 1 AS value
UNION ALL
SELECT 2 AS value
ORDER BY value DESC;

这里的 ORDER BY 排序整个合并结果。

如果分支查询需要单独排序或限制，通常需要括号：

(
    SELECT value
    FROM t1
    ORDER BY value DESC
    LIMIT 10
)
UNION ALL
(
    SELECT value
    FROM t2
    ORDER BY value DESC
    LIMIT 10
);

即便如此，最终合并结果仍然没有自动保证整体顺序；需要在最外层再写 ORDER BY。

5. UNION 不是 GROUPING SETS 的简单替代

可以手写多个查询：

WITH sales(region, channel, amount) AS (
    VALUES
        ('East'::text, 'Online'::text, 100::numeric),
        ('East',        'Store',         80),
        ('West',        'Online',        90)
)
SELECT
    region,
    channel,
    SUM(amount) AS total_amount,
    'detail' AS level_name
FROM sales
GROUP BY region, channel

UNION ALL

SELECT
    region,
    NULL AS channel,
    SUM(amount) AS total_amount,
    'region_total' AS level_name
FROM sales
GROUP BY region

UNION ALL

SELECT
    NULL AS region,
    NULL AS channel,
    SUM(amount) AS total_amount,
    'grand_total' AS level_name
FROM sales;

它在逻辑上类似于：

GROUP BY GROUPING SETS (
    (region, channel),
    (region),
    ()
)

但两者有几个重要差异：

• 手写 UNION ALL 更容易重复写错过滤条件；
• 各分支可以使用完全不同的表达式和数据源；
• UNION 若误用而不是 UNION ALL，可能把业务上不同层级但值相同的行去掉；
• 优化器对两种写法的执行计划不一定相同；
• GROUPING SETS 可以通过 GROUPING() 清晰标记“被省略的维度”。

例如以下两个结果行：

(NULL, NULL, 100, 'region_total')
(NULL, NULL, 100, 'grand_total')

如果没有 level_name，使用 UNION 时它们可能被视为完全重复并合并；这会直接丢失一行业务结果。

因此，当多个分支代表不同业务层级时，通常应：

• 使用 UNION ALL；
• 增加明确的层级标记；
• 只有在确认重复行确实没有业务意义时，才使用 UNION。

---

七、去重：结果行去重与聚合输入去重不是一回事

“去重”至少有三种不同含义。

1. SELECT DISTINCT：去除最终结果中的重复行

SELECT DISTINCT region, channel
FROM sales;

它去重的是最终投影出来的 (region, channel) 行。

如果原表有以下数据：

order_id	region	channel
1	East	Online
2	East	Online
3	East	Store

执行：

SELECT DISTINCT region, channel
FROM sales;

结果只有：

region	channel
East	Online
East	Store

DISTINCT 不会告诉你每个组合出现了多少次，也不会自动改变其他聚合指标。

2. GROUP BY：按键形成分组

SELECT
    region,
    channel,
    COUNT(*) AS row_count
FROM sales
GROUP BY region, channel;

这里不是简单删除重复行，而是将重复键的多行放进同一个分组，并对每组执行聚合。

因此：

SELECT DISTINCT region, channel

和：

SELECT region, channel
FROM sales
GROUP BY region, channel

在只选择分组列时结果通常相同，但语义不同：

• DISTINCT 表达“结果去重”；
• GROUP BY 表达“按键分组”，通常还会伴随聚合。

3. COUNT(DISTINCT x)：每个分组内对输入值去重

SELECT
    region,
    COUNT(DISTINCT channel) AS channel_count
FROM sales
GROUP BY region;

它的执行语义是：

1. 先按 region 分组；
2. 在每个区域内部取 channel 的不同值；
3. 统计不同值数量。

它不是对整个查询结果先做 DISTINCT。

例如：

WITH events(user_id, region) AS (
    VALUES
        (1, 'East'),
        (1, 'East'),
        (2, 'East'),
        (3, 'West')
)
SELECT
    region,
    COUNT(*) AS event_count,
    COUNT(DISTINCT user_id) AS user_count
FROM events
GROUP BY region;

结果：

region	event_count	user_count
East	3	2
West	1	1

event_count 统计事件行数，user_count 统计用户数。不能通过把整个输入表 DISTINCT 成 (user_id, region) 后再随意替代所有指标，因为其他指标可能需要保留事件重复次数。

4. COUNT(DISTINCT) 忽略 NULL

SELECT COUNT(DISTINCT value)
FROM (
    VALUES
        (1),
        (1),
        (2),
        (NULL),
        (NULL)
) AS t(value);

结果为：

2

不同的非 NULL 值只有 1 和 2。

如果业务要求把 NULL 作为一个“未知类别”计数，需要显式转换：

COUNT(DISTINCT COALESCE(value, -1))

但这种写法要求 -1 不会与真实值冲突。对于字符串也同理，替代值必须具有业务安全性。

5. 重复输入会影响聚合值

SQL 聚合默认不会自动删除输入重复行：

WITH t(value) AS (
    VALUES
        (10),
        (10),
        (20)
)
SELECT
    SUM(value) AS total,
    COUNT(*) AS row_count,
    COUNT(DISTINCT value) AS distinct_value_count
FROM t;

结果：

total	row_count	distinct_value_count
40	3	2

如果把 SUM(value) 错误地理解为“不同 value 的和”，就会得到错误结论。需要先去重时，应明确改变聚合输入：

WITH t(value) AS (
    VALUES
        (10),
        (10),
        (20)
)
SELECT SUM(value)
FROM (
    SELECT DISTINCT value
    FROM t
) AS d;

结果为 30。

6. DISTINCT 与 UNION 的位置不同

下面两个查询一般不等价：

SELECT DISTINCT region
FROM t1
UNION ALL
SELECT region
FROM t2;

和：

SELECT region
FROM t1
UNION
SELECT region
FROM t2;

第一个只去重 t1，然后保留 t2 的重复值；第二个对两个分支合并后的整体结果去重。

如果需要对 UNION ALL 的最终结果去重，应增加外层查询：

SELECT DISTINCT region
FROM (
    SELECT region FROM t1
    UNION ALL
    SELECT region FROM t2
) AS combined;

去重位置会改变结果，不能只看关键字是否出现。

---

八、用 UNION ALL 模拟 GROUPING SETS：什么时候合理

MySQL 8.4 支持 ROLLUP，但不提供 PostgreSQL 中这种通用的 GROUPING SETS 和 CUBE 语法。因此，在 MySQL 中常见的模拟方法是 UNION ALL。

例如，计算明细、区域小计和总计：

SELECT
    region,
    channel,
    SUM(amount) AS total_amount,
    0 AS is_region_total,
    0 AS is_grand_total
FROM sales
GROUP BY region, channel

UNION ALL

SELECT
    region,
    NULL AS channel,
    SUM(amount) AS total_amount,
    1 AS is_region_total,
    0 AS is_grand_total
FROM sales
GROUP BY region

UNION ALL

SELECT
    NULL AS region,
    NULL AS channel,
    SUM(amount) AS total_amount,
    0 AS is_region_total,
    1 AS is_grand_total
FROM sales;

这里显式增加了层级标记，避免真实 NULL 和汇总 NULL 混淆。

如果直接写：

SELECT region, channel, SUM(amount)
FROM sales
GROUP BY region, channel

UNION

SELECT region, NULL, SUM(amount)
FROM sales
GROUP BY region;

则可能出现两个问题：

1. UNION 会去重，可能误删业务上不同层级的行；
2. 无法仅根据 region、channel 的 NULL 判断该行属于哪一级汇总。

MySQL 的 ROLLUP

MySQL 8.4 常用写法是：

SELECT
    region,
    channel,
    SUM(amount) AS total_amount
FROM sales
GROUP BY region, channel WITH ROLLUP;

它产生的层级相当于：

(region, channel)
(region)
()

MySQL 中应根据具体版本文档确认 GROUPING() 的支持和使用限制；不要把 PostgreSQL 的完整 GROUPING SETS、CUBE 语法直接复制到 MySQL。

一个跨数据库更容易控制的方案是使用显式标记：

SELECT
    region,
    channel,
    SUM(amount) AS total_amount,
    CASE
        WHEN region IS NULL AND channel IS NULL THEN 'grand_total'
        WHEN channel IS NULL THEN 'region_total'
        ELSE 'detail'
    END AS level_name
FROM sales
GROUP BY region, channel WITH ROLLUP;

但这个例子仍然无法区分“真实的 NULL 区域明细”和“由 ROLLUP 产生的 NULL”，除非使用该版本支持的 GROUPING() 或预先把业务 NULL 映射成不会冲突的显示值。因此，生产查询不应仅依赖 NULL 判断层级。

---

九、把窗口函数与多级聚合结合起来

窗口函数与 GROUPING SETS 经常同时出现在报表中，但两者作用不同。

• GROUPING SETS 改变行的粒度，产生明细、小计和总计；
• 窗口函数保留当前行，并在结果行之间计算排名、累计值或比例。

例如，先计算区域和渠道明细，再计算每个区域中的渠道占比：

WITH sales(region, channel, amount) AS (
    VALUES
        ('East'::text, 'Online'::text, 100::numeric),
        ('East',        'Online',        120),
        ('East',        'Store',          80),
        ('West',        'Online',         90),
        ('West',        'Store',          70)
),
detail AS (
    SELECT
        region,
        channel,
        SUM(amount) AS channel_amount
    FROM sales
    GROUP BY region, channel
)
SELECT
    region,
    channel,
    channel_amount,
    channel_amount
        / SUM(channel_amount) OVER (PARTITION BY region)
        AS region_share
FROM detail
ORDER BY region, channel;

这里窗口函数作用于 detail 的结果，而不是原始销售行。

如果直接把窗口函数与多级汇总混在一起，需要先明确窗口函数看到的是哪一层结果。否则容易把：

• 明细金额；
• 区域小计；
• 全局总计

混到同一个窗口分区中，导致比例重复计算或分母错误。

---

十、常见误解、失败表现与诊断方法

1. 把 ROLLUP 当成 CUBE

错误理解：

ROLLUP (region, channel)

会同时得到区域小计和渠道小计。

实际只得到：

(region, channel)
(region)
()

如果需要：

(region, channel)
(region)
(channel)
()

应使用：

CUBE (region, channel)

或者明确写：

GROUPING SETS (
    (region, channel),
    (region),
    (channel),
    ()
)

2. 把结果中的 NULL 都当成总计

真实数据中的 NULL 也会参与普通分组。例如：

region = NULL, channel = Online

可能是原始业务数据，而不是渠道小计。

诊断时应始终输出：

GROUPING(region),
GROUPING(channel)

如果数据库版本或产品不支持该函数，则需要在查询中显式添加层级标识，或在进入聚合前将业务 NULL 规范化并保留独立的缺失标记。

3. 使用 UNION 而不是 UNION ALL 导致行数减少

如果两个分支产生相同的整行结果，UNION 会删除其中一行。

尤其是多级报表中，以下行的数值可能恰好相同：

East, NULL, 100, region_total
NULL, NULL, 100, grand_total

如果层级标记未被选出，或者两个分支的列完全相同，UNION 可能把它们当成重复行。

诊断方法：

1. 先分别执行每个分支；
2. 使用 UNION ALL；
3. 增加 level_name；
4. 比较合并前后的行数。

4. 认为聚合会自动去重

以下查询不会自动消除重复输入：

SELECT SUM(amount)
FROM sales;

若同一业务事件在表中重复两次，金额会被累计两次。应先判断：

• 重复是否是合法的多条事件；
• 是否需要按业务主键去重；
• 去重应发生在明细层、分组内，还是最终结果层。

这三个位置的含义不同。

5. 在 SELECT 中引用未分组列

下面的查询通常不合法：

SELECT region, channel, SUM(amount)
FROM sales
GROUP BY region;

因为同一个 region 内可能存在多个 channel，数据库无法确定应返回哪个 channel。

应选择以下之一：

GROUP BY region, channel

或者把 channel 也聚合：

STRING_AGG(DISTINCT channel, ',')

或者从结果中删除它。

不能依靠“这个查询在当前数据上恰好只有一个 channel”来规避语义约束。

6. 依赖隐含输出顺序

GROUPING SETS、ROLLUP、CUBE 都不应被理解为自动提供报表最终排序。

应显式排序：

ORDER BY
    GROUPING(region),
    region NULLS LAST,
    GROUPING(channel),
    channel NULLS LAST;

如果需要明细、小计、总计固定顺序，可以使用层级表达式：

CASE
    WHEN GROUPING(region) = 1
     AND GROUPING(channel) = 1 THEN 2
    WHEN GROUPING(channel) = 1 THEN 1
    ELSE 0
END AS level_order

然后：

ORDER BY level_order, region, channel;

---

十一、执行、并发与故障边界

1. 逻辑上的多次聚合，不代表一定物理扫描多次

GROUPING SETS 可以从逻辑上展开为多个聚合查询，但数据库可能：

• 共享一次表扫描；
• 复用排序结果；
• 使用哈希聚合；
• 使用专门的多级聚合计划；
• 在并行执行时先局部聚合，再合并结果。

这些是实现策略，不是 SQL 语义保证。必须通过执行计划验证：

EXPLAIN (ANALYZE, BUFFERS)
SELECT ...

EXPLAIN ANALYZE 会实际执行查询。对有副作用的语句或高负载生产查询，使用时应确认风险。

2. 单条查询通常使用一个语句级一致性视图

本文示例使用 CTE 和单条 SQL。对于 PostgreSQL，在默认 READ COMMITTED 下，一个语句通常使用该语句开始时的一致性快照，因此同一条 GROUPING SETS 查询中的各层聚合不会分别看到不同时间点的数据。

如果改成应用程序发起多个独立查询再在程序中合并，情况会不同：

• 每条查询可能获得不同快照；
• 中间可能有提交或回滚；
• 最终明细、小计和总计可能无法互相对应。

这不是 GROUPING SETS 与 UNION ALL 的语法差异，而是事务边界差异。

若要求多个独立查询看到同一批数据，应将它们放在明确的事务和合适的隔离级别中；具体行为还取决于 PostgreSQL、MySQL 的事务隔离和是否使用锁定读。

3. 聚合异常通常来自输入数据或连接放大

多级聚合出现总计不一致时，常见原因不是 ROLLUP 算错，而是聚合前的输入已经被连接放大。

例如：

orders
JOIN order_items

会让一笔订单对应多条明细。如果随后直接：

SUM(order_amount)

订单金额可能按明细行重复累计。

诊断方法是先检查聚合前输入：

SELECT
    order_id,
    COUNT(*) AS joined_rows
FROM joined_result
GROUP BY order_id
HAVING COUNT(*) > 1;

应先确认每个业务事实在进入聚合前的粒度，再决定是：

• 按明细行聚合；
• 先按订单去重；
• 使用半连接；
• 将订单级和明细级指标拆开计算。

DISTINCT 不能盲目修复连接放大，因为它可能同时删除本来合法的重复事实。

---

十二、与 ClickHouse 等列式分析引擎的边界

GROUPING SETS、ROLLUP 和 CUBE 描述的是 SQL 层面的分组集合语义，与存储引擎是两个层次的问题。

在 ClickHouse 等列式分析系统中，还需要额外考虑：

• 分区键是否减少了需要读取的数据；
• 排序键是否有利于聚合前的数据组织；
• 数据跳过索引是否能过滤无关块；
• 分布式查询中本地聚合和最终聚合如何合并；
• 明细数据是否因 ReplacingMergeTree 等表引擎而存在暂时重复；
• UNION ALL 分支是否分别读取相同的大表。

特别是，列式引擎中的表引擎、排序键和合并过程不会自动改变 SQL 聚合的重复语义。若底层仍有两条输入记录，SUM 默认仍会读取两条记录。是否需要去重，必须由业务事实模型和查询语义决定。

同样，不能因为某个引擎对 ROLLUP 或 CUBE 有优化，就推断所有版本、所有分布式部署都具有相同的性能或输出顺序。语义以对应版本的官方文档为准，性能以实际执行计划和数据分布验证。

---

十三、如何选择四种写法

可以用问题本身来选择语法：

需要明确指定若干粒度

GROUP BY GROUPING SETS (
    (region, channel),
    (region),
    ()
)

这是最精确的表达。

需要沿一条层级路径汇总

GROUP BY ROLLUP (year, quarter, month)

要求维度顺序符合业务层级。

需要所有维度组合

GROUP BY CUBE (region, channel, month)

适合确实需要多方向切片的分析，但要警惕 $2^n$ 个分组集合带来的结果规模。

需要合并结构不同的查询

query_a
UNION ALL
query_b

适合各分支来源、过滤条件或列计算明显不同的场景。

需要消除最终重复行

SELECT DISTINCT ...

需要统计每组内的不同值

COUNT(DISTINCT expression)

需要合并且保留重复行

UNION ALL

---

结语

这几种语法可以归纳为不同层次的问题：

• GROUPING SETS 决定要计算哪些分组集合；
• ROLLUP 将分组集合组织成有顺序的层级；
• CUBE 枚举维度的所有子集；
• UNION 合并多个查询结果，并默认去重；
• UNION ALL 合并结果但保留重复；
• DISTINCT 去除结果行；
• COUNT(DISTINCT ...) 在每个分组内部去重聚合输入；
• GROUPING() 区分真实 NULL 与汇总产生的 NULL。

最容易造成数据错误的地方通常不是语法本身，而是把这些层次混在了一起：用 ROLLUP 表达非层级维度，用 UNION 意外删除报表行，用 DISTINCT 掩盖连接放大，或者把汇总 NULL 当成业务 NULL。

只要先确定输入粒度、分组集合、去重位置和事务边界，再选择对应语法，多级聚合查询就能从“看起来能跑”变成语义可验证、结果可诊断的 SQL。

---

系列导航与关联阅读

• 系列入口：数据库完整学习路线：从关系模型、事务索引到分布式与向量检索
• 上一篇：SQL 窗口函数：分区、排序、Frame、排名、累计和间隔分析
• 下一篇：SQL 数据写入：INSERT、UPDATE、DELETE、MERGE、Upsert 与并发正确性
• 延伸：ClickHouse 列式建模：MergeTree、排序键、分区和数据跳过

官方资料

• PostgreSQL SQL Language
• MySQL Reference Manual

本文依据数据库官方文档重新梳理；正文、示例与生产检查清单由 WR BLOG 编写。