PostgreSQL / 系统课时

分组集与多维聚合

分组集与多维聚合

多维报表常常既要明细维度组合,也要地区小计、商品小计和总计。分别写多条 GROUP BY 再 UNION ALL 可以实现,但维护成本高。PostgreSQL 的 GROUPING SETS、ROLLUP 和 CUBE 能在一条聚合语句里声明这些层级。

学习目标

  • 能用 GROUPING SETS 一次生成多个分组层级
  • 能判断何时使用 ROLLUP、何时使用 CUBE
  • 能用 GROUPING() 区分真实 NULL 与汇总层产生的空维度

从报表层级反推分组集

假设销售数据有 region 和 product 两个维度。报表可能同时需要四类结果:地区加商品明细、地区小计、商品小计、全部总计。GROUPING SETS 的核心就是直接列出这些“希望保留的维度组合”,数据库负责在一次聚合计划中计算它们。

用 GROUPING SETS 生成多层汇总

下面的示例完全自包含,四条销售记录会被汇总成明细、两个方向的小计和总计。

sql
WITH sales(region, product, amount) AS (
  VALUES
    ('E', 'A', 100),
    ('E', 'B', 80),
    ('N', 'A', 60),
    ('N', 'B', 40)
)
SELECT
  region,
  product,
  SUM(amount) AS total_amount,
  GROUPING(region) AS g_region,
  GROUPING(product) AS g_product
FROM sales
GROUP BY GROUPING SETS (
  (region, product),
  (region),
  (product),
  ()
)
ORDER BY
  GROUPING(region),
  GROUPING(product),
  region NULLS LAST,
  product NULLS LAST;

预期输出

text
 region | product | total_amount | g_region | g_product
--------+---------+--------------+----------+----------
 E      | A       |          100 |        0 |        0
 E      | B       |           80 |        0 |        0
 N      | A       |           60 |        0 |        0
 N      | B       |           40 |        0 |        0
 E      |         |          180 |        0 |        1
 N      |         |          100 |        0 |        1
        | A       |          160 |        1 |        0
        | B       |          120 |        1 |        0
        |         |          280 |        1 |        1

GROUPING(column) 返回 1 表示该列在当前汇总层被折叠,返回 0 表示该列仍是本层分组键。它比简单判断 column IS NULL 更可靠,因为源数据本身也可能真的包含 NULL。

ROLLUP 与 CUBE 的展开关系

ROLLUP(region, product) 表达的是有方向的层级链:先按地区和商品,再按地区,最后到总计。CUBE(region, product) 则生成两个维度的全部组合,因此还会多出“按商品”的横向小计。

ROLLUP 与 CUBE 的分组层级关系

写法 等价分组集 典型用途
ROLLUP(region, product) (region, product), (region), () 有父子顺序的小计与总计
CUBE(region, product) (region, product), (region), (product), () 维度交叉分析
GROUPING SETS (...) 只计算显式列出的组合 精确控制报表层级

当维度增加时,CUBE 的组合数按 2n2^n 增长。三个维度会产生 8 个分组集,六个维度就是 64 个,因此生产报表更常用显式 GROUPING SETS 或有业务层级的 ROLLUP,避免计算大量没人读取的组合。

让汇总结果可安全消费

展示层通常会把被汇总的维度显示为“全部地区”或“全部商品”,但不要直接用 COALESCE(region, '全部地区') 判断汇总层,因为真实数据中的 NULL 会与汇总空值混在一起。应先用 GROUPING() 确认层级,再决定显示文案或排序权重。

多维聚合的关键不是“把所有组合都算出来”,而是让分组集与报表真正需要的层级一一对应:固定层级优先 ROLLUP,完整交叉才用 CUBE,不规则组合则直接写 GROUPING SETS。

按自己的节奏,确认真正理解。

目录与阅读设置

阅读设置

17

记录一个想法

分组集与多维聚合

0 / 10000 · 笔记保存到账号。未提交的修改会在关闭、离开或刷新后丢失。

登录哪都通