SQL 中的 GROUPING SETS、ROLLUP 和 CUBE

Grouping Sets, Rollup and Cube in SQL

一个典型的 GROUP BY 查询只在一组列上进行聚合（aggregate）。例如：

• 按列 “A” 对数据进行分组（grouping）需要一条查询。
• 按列 “A” 和 “B” 对数据进行分组则需要单独的一条查询。
• 接下来，如果必须把两个输出合并到同一张表（table）里，我们就使用 UNION 或 UNION ALL（视需要而定）。

但这样做并不高效，因为它要扫描同一张表两次。相反，有三种方式可以只扫描一次表，就在同一张表上运行多次聚合，从而让我们的查询（query）高效得多。它们就是 GROUPING SETS、ROLLUP 和 CUBE，其用法如下图所示：

GROUPING SETS 子句（clause）允许我们在一条查询中定义多个分组。每个 grouping set 定义了一组用于对数据进行分组的列组合。

给定上面的查询，将会创建以下分组聚合：

• (A)：按 A 聚合，统计所有其他列的全部行（row）。
• (A, B)：按 (A, B) 聚合，统计所有其他列的全部行。
• (C)：按 C 聚合，统计所有其他列的全部行。

下面是一个演示：

上面的查询：

• 对 City 列（column）生成一个聚合。
• 对 Fruit 列再生成一个聚合。

ROLLUP 会创建一个结果集（result set），除了常规的分组结果之外，它还包含小计和总计。它是通过在多个聚合层级上对数据进行分组来实现的。

给定上面的查询，将会创建以下分组聚合：

• (A)：每个 A 的小计，跨所有 B 和 C 聚合。
• (A, B)：每个 A 和 B 组合的小计，跨所有 C 聚合。
• (A, B, C)：按全部三列进行常规分组。
• ()：总计，跨所有 A、B 和 C 聚合。

下面是一个演示：

上面的查询：

• 对 Fruit 列生成一个聚合。
• 对 (Fruit, City) 列再生成一个聚合。
• 生成一个总计。

与 GROUPING SETS 不同，ROLLUP 中顺序很重要。更具体地说，ROLLUP (A, B) 与 ROLLUP (B, A) 并不相同。

最后，CUBE 会创建一个结果集，其中包含指定列所有可能的聚合组合。

给定上面的查询，将会创建以下分组聚合：

• (A, B, C)：按全部三列进行常规分组。
• (A, B)：每个 A 和 B 组合的小计，跨所有 C 聚合。
• (A, C)：每个 A 和 C 组合的小计，跨所有 B 聚合。
• (B, C)：每个 B 和 C 组合的小计，跨所有 A 聚合。
• (A)：每个 A 的小计，跨所有 B 和 C 聚合。
• (B)：每个 B 的小计，跨所有 A 和 C 聚合。
• (C)：每个 C 的小计，跨所有 A 和 B 聚合。
• ()：总计，跨所有 A、B 和 C 聚合。

下面是一个演示：

上面的查询：

• 对 Fruit 列生成一个聚合。
• 对 City 列生成一个聚合。
• 对 (Fruit, City) 列再生成一个聚合。
• 生成一个总计。

下载这个 Jupyter Notebook 自己动手试试：https://bit.ly/4bx4SUX。