PHP小码农

Appearance

CUBE ​

CUBE 是 GROUPING SETS 的全组合缩写：GROUP BY CUBE (a, b, c) 会一次性算出 a、b、c 三列的所有 2³ = 8 种维度组合。它适合「任意维度都能下钻/上卷」的交叉分析场景，比如城市 × 品类 × 渠道的销量-利润矩阵。代价是结果集行数与分组集个数都随列数指数增长，务必控制列数。学完本节，你将能正确区分 ROLLUP 与 CUBE 的适用边界，并用 CUBE 一次产出全维度汇总。

前置知识

阅读本节前，建议先了解：ROLLUP

基础概念 ​

数据立方体（Data Cube） 是 OLAP 的经典模型：把 N 个维度上的所有聚合组合预先算出来，形成可在任意维度切片（Slice）、切块（Dice）、下钻（Drill Down）的多维结构。PostgreSQL 的 CUBE 关键字就是它的 SQL 语法糖。

概念术语说明
数据立方体Data CubeN 个维度全部组合的聚合结果集合
全组合Full Combination每个维度「参与 / 不参与」的 2^N 种可能
分组集Grouping Set一组参与分组的列，CUBE 会展开出 2^N 个
切片Slice固定某个维度值后观察子立方体
下钻 / 上卷Drill Down / Roll Up沿层级增/减维度细粒度
行膨胀Row Explosion分组集个数指数增长导致结果行数激增
汇总行标记Grouping FlagGROUPING() 返回值，标识某列是否被汇总

核心语义：CUBE 生成的是 ROLLUP 的超集——它既包含 ROLLUP 的前缀阶梯，也包含非前缀的组合（如只按 b、只按 c、按 b,c）。

text
GROUP BY CUBE (a, b)
        ≡ GROUP BY GROUPING SETS ((a, b), (a), (b), ())
        共 2² = 4 个分组集（含空集）

GROUP BY CUBE (a, b, c)
        ≡ GROUP BY GROUPING SETS (
            (a, b, c),  (a, b),  (a, c),  (b, c),
            (a),        (b),     (c),     ()
          )
        共 2³ = 8 个分组集

完整展开对照表（务必记牢这张表，它是理解 CUBE 与 ROLLUP 差异的关键）：

CUBE 写法展开后的分组集个数
CUBE ()()1
CUBE (a)(a), ()2
CUBE (a, b)(a, b), (a), (b), ()4
CUBE (a, b, c)(a,b,c), (a,b), (a,c), (b,c), (a), (b), (c), ()8
CUBE (a, b, c, d)上表每个分组集各自再分裂出「含 d」与「不含 d」两版16
CUBE (a, b, c, d, e)同上递归32

CUBE vs ROLLUP 的差异：

对比项ROLLUP (a, b, c)CUBE (a, b, c)
分组集个数N + 1 = 42^N = 8
是否含 ()是是
是否含 (a, c)、(b, c) 等非前缀组合否是
语义模型层级上卷（有父子关系）任意维度正交组合（无层级关系）
适合场景时间/组织层级报表交叉分析、多维切片
行数增长线性 O(N)指数 O(2^N)

组合嵌套：CUBE 内部可以再嵌 ROLLUP，形成「立方体中的层级轴」：

写法展开结果个数
CUBE (a, ROLLUP (b, c))(a,b,c), (a,b), (a), (b,c), (b), ()6
ROLLUP (a), CUBE (b, c)(a,b,c), (a,b), (a,c), (a), (b,c), (b), (c), ()8

规则是：把内层每个分组集与外层的每种「是否加入」组合相乘。CUBE (a, ROLLUP (b, c)) 中内层 ROLLUP (b, c) 给出 {(b,c), (b), ()} 三个分组集，外层 a 可加入或不加入，但内层的 () 加入 a 后变成 (a) 与内层原有的 (b,c) 等合并去重，最终得到 6 个。

语法与用法 ​

sql
-- 基本形态：对 N 列求全组合
SELECT 维度列..., 聚合函数(...)
FROM 表
GROUP BY CUBE (维度列1, 维度列2, 维度列3)
ORDER BY ...;

准备本节贯穿使用的示例表（在 demo 库中执行）：

sql
-- 销售明细表：城市 × 品类 × 渠道
CREATE TABLE sales (
  id         INTEGER GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  city       TEXT           NOT NULL,
  category   TEXT           NOT NULL,
  channel    TEXT           NOT NULL,
  qty        INTEGER        NOT NULL CHECK (qty > 0),
  amount     NUMERIC(12, 2) NOT NULL CHECK (amount >= 0),
  cost       NUMERIC(12, 2) NOT NULL CHECK (cost >= 0),
  sold_at    DATE           NOT NULL
);

-- 造数：5 城市 × 4 品类 × 3 渠道，共 3 万行
INSERT INTO sales (city, category, channel, qty, amount, cost, sold_at)
SELECT
  (ARRAY['北京', '上海', '广州', '深圳', '杭州'])[1 + (i % 5)],
  (ARRAY['手机', '电脑', '家电', '配件'])[1 + (i % 4)],
  (ARRAY['线上', '门店', '分销'])[1 + (i % 3)],
  1 + (i % 20),
  round((random() * 2000 + 100)::numeric, 2),
  round((random() * 1000 + 50)::numeric, 2),
  DATE '2026-01-01' + (i % 180)
FROM generate_series(1, 30000) AS i;

CREATE INDEX idx_sales_city_cat_ch ON sales (city, category, channel);
ANALYZE sales;

全维度矩阵的最小可运行示例：

sql
-- 一条 SQL 产出城市×品类×渠道的 8 种维度组合
SELECT
  city,
  category,
  channel,
  sum(amount)          AS 销售额,
  sum(amount - cost)   AS 利润,
  GROUPING(city)       AS g_city,
  GROUPING(category)   AS g_category,
  GROUPING(channel)    AS g_channel
FROM sales
GROUP BY CUBE (city, category, channel)
ORDER BY GROUPING(city), city,
         GROUPING(category), category,
         GROUPING(channel), channel;

预期输出（截取，8 个层级各出现若干行）：

text
  city | category | channel |  销售额  |   利润   | g_city | g_category | g_channel
-------+----------+---------+----------+----------+--------+------------+-----------
 北京  | 手机     | 门店    |  8821.40 |  4210.33 |      0 |          0 |         0
 北京  | 手机     | 线上    |  9133.02 |  4501.88 |      0 |          0 |         0
 北京  | 手机     | 分销    |  8890.11 |  4322.09 |      0 |          0 |         0
 北京  | 手机     |         | 26844.53 | 13034.30 |      0 |          0 |         1  <- 城市×品类小计
 北京  |          | 门店    | 35201.77 | 17002.11 |      0 |          1 |         0  <- 城市×渠道小计
 北京  |          |         |105430.20 | 51003.55 |      0 |          1 |         1  <- 城市小计
 上海  | 手机     | 门店    |  8712.99 |  4188.42 |      0 |          0 |         0
 ...   | ...      | ...     |      ... |      ... |    ... |        ... |       ...
 北京  |          |         |105430.20 | 51003.55 |      0 |          1 |         1
       |          | 门店    |491233.10 |240118.44 |      1 |          1 |         0  <- 渠道小计
       |          |         |1489002.55|721330.12 |      1 |          1 |         1  <- 全表总计

注意最后几行：g_category = 1 的行是「城市 × 渠道」组合，这在 ROLLUP 中是不会出现的——ROLLUP (city, category, channel) 只有 (city,category,channel)、(city,category)、(city)、() 四层。

详细说明 ​

与 GROUPING SETS 的等价改写 ​

CUBE 只是语法糖，任何 CUBE 都能手写成 GROUPING SETS，两者计划形态一致：

sql
-- CUBE 写法
SELECT city, category, sum(amount)
FROM sales
GROUP BY CUBE (city, category);

-- 完全等价的 GROUPING SETS 写法
SELECT city, category, sum(amount)
FROM sales
GROUP BY GROUPING SETS ((city, category), (city), (category), ());

当只需要部分组合时，请直接用 GROUPING SETS——CUBE 会算全部 2^N 个，多算的部分既耗时又占内存：

sql
-- 不推荐：CUBE 会多算 (category, channel) 等不需要的组合
SELECT city, category, channel, sum(amount)
FROM sales
GROUP BY CUBE (city, category, channel);

-- 推荐：明确列出真正需要的 4 个分组集
SELECT city, category, channel, sum(amount)
FROM sales
GROUP BY GROUPING SETS (
  (city, category, channel),
  (city, category),
  (city),
  ()
);

行数膨胀风险 ​

分组集个数是 2 的幂，列数每加 1，聚合工作量翻倍：

列数 N分组集个数 2^N单分组集最坏行数结果总行数量级
24明细明细 + 3
38明细明细 + 7
416明细明细 + 15
532明细明细 + 31
664明细明细 + 63

实际影响有两层：

  1. 输出行数：CUBE (a, b, c) 会输出「明细行 + 7 类汇总行」，若明细本身有 100 万行，结果轻易突破百万级；
  2. 聚合中间状态：每个分组集都要维护独立的哈希表或排序缓冲，CUBE (a,b,c,d) 意味着 16 份中间状态同时存活，极易超出 work_mem。
sql
-- 查看是否因 work_mem 不足而落盘
EXPLAIN (ANALYZE, BUFFERS)
SELECT city, category, channel, sum(amount)
FROM sales
GROUP BY CUBE (city, category, channel);
text
 HashAggregate  (cost=1892.00..2103.44 rows=1544 width=...) (actual time=33.221..40.118 rows=231 loops=1)
   Group Key: city, category, channel
   Group Key: city, category
   Group Key: city, channel
   Group Key: category, channel
   Group Key: city
   Group Key: category
   Group Key: channel
   Group Key: ()
   Batches: 1  Memory Usage: 481kB      -- Batches > 1 表示溢出落盘
   Buffers: shared hit=214
 Execution Time: 40.118 ms

出现 Batches: 2（或更大）即说明哈希表溢出。解决方式：临时提高 work_mem（会话级）或减少 CUBE 列数：

sql
-- 会话级临时调大，仅对当前连接生效
SET work_mem = '256MB';

高基数列是禁忌 ​

CUBE 的分组集是笛卡尔式全组合，高基数（重复值少）的列会让「非前缀组合」的行数逼近明细行数：

sql
-- 错误！user_id / order_no 基数极高，CUBE 后结果行数几乎等于明细行数
SELECT user_id, order_no, city, sum(amount)
FROM sales
GROUP BY CUBE (user_id, order_no, city);

经验规则：CUBE 的列应是低基数维度（城市、品类、渠道、状态），基数建议每个都在 1000 以内。

用 GROUPING() 标记汇总层级 ​

CUBE 的结果中，多列可能同时为 NULL，仅凭列值无法判断当前行是哪个层级。用 GROUPING() 拼接出可读标签：

sql
SELECT
  CASE WHEN GROUPING(city) = 1 THEN '全部城市' ELSE city END             AS 城市,
  CASE WHEN GROUPING(category) = 1 THEN '全部品类' ELSE category END      AS 品类,
  CASE WHEN GROUPING(channel) = 1 THEN '全部渠道' ELSE channel END        AS 渠道,
  sum(amount) AS 销售额
FROM sales
GROUP BY CUBE (city, category, channel)
HAVING GROUPING(city) = 1 AND GROUPING(category) = 1   -- 只看「按渠道」的汇总层
ORDER BY GROUPING(channel), channel;

还可以用多参数形式一次性拿到位图整数，便于排序与分组：

sql
-- 多参数 GROUPING() 返回位图整数，替代其他数据库的 GROUPING_ID()
SELECT
  city, category, channel,
  GROUPING(city, category, channel) AS gid,   -- 0~7，越小层级越细
  sum(amount)                        AS 销售额
FROM sales
GROUP BY CUBE (city, category, channel)
ORDER BY gid, city, category, channel;

gid = 0 是明细行，gid = 7 是全表总计。详见 GROUPING / GROUPING_ID。

执行计划：一次扫描，多分组集聚合 ​

PostgreSQL 对 GROUPING SETS 家族（含 CUBE）有两种实现路径：

计划节点触发条件特征
MixedAggregate部分分组集可复用哈希、部分可复用排序同时出现 Hash Key: 与 Group Key:
HashAggregate所有分组集都走哈希多个 Group Key: 行，关注 Batches
GroupAggregate分组集可按索引/排序顺序复用多个 Group Key: 行，输入已排序

关键点是：无论多少个分组集，基表只扫描一次，各分组集在一次遍历中「同时喂给各自的聚合状态」。

text
 MixedAggregate  (actual time=0.031..42.117 rows=231 loops=1)
   Hash Key: city
   Hash Key: category
   Hash Key: channel
   Hash Key: <>
   Group Key: city, category, channel
   Group Key: city, category
   Group Key: city, channel
   Group Key: category, channel
   ->  Seq Scan on sales  (actual time=0.012..12.884 rows=30000 loops=1)
         Buffers: shared hit=214

若改用 UNION ALL 手写 8 个 GROUP BY，计划会变成 Append 下挂 8 个聚合节点、扫描 8 次。

与 UNION ALL 的性能对比 ​

sql
-- CUBE：单次扫描
EXPLAIN (ANALYZE, BUFFERS)
SELECT city, category, sum(amount)
FROM sales
GROUP BY CUBE (city, category);
text
 MixedAggregate  (actual time=0.030..41.552 rows=33 loops=1)
   Buffers: shared hit=214
 Execution Time: 41.552 ms
sql
-- UNION ALL：四次扫描
EXPLAIN (ANALYZE, BUFFERS)
SELECT city, category, sum(amount) FROM sales GROUP BY city, category
UNION ALL SELECT city, NULL, sum(amount) FROM sales GROUP BY city
UNION ALL SELECT NULL, category, sum(amount) FROM sales GROUP BY category
UNION ALL SELECT NULL, NULL, sum(amount) FROM sales;
text
 Append  (actual time=35.001..140.221 rows=33 loops=1)
   ->  GroupAggregate  (actual time=0.028..35.001 rows=20 loops=1)
   ->  GroupAggregate  (actual time=0.022..34.882 rows=5  loops=1)
   ->  GroupAggregate  (actual time=0.024..35.110 rows=4  loops=1)
   ->  Aggregate       (actual time=0.019..33.447 rows=1  loops=1)
 Buffers: shared hit=856
 Execution Time: 140.221 ms
指标CUBE (city, category)四个 GROUP BY + UNION ALL相对提升
执行时间41.6 ms140.2 ms3.4×
共享缓冲命中2148564.0×
基表扫描次数14—

注意：CUBE 更快是相对同样的分组集而言。若 UNION ALL 只写 2 个分组集而 CUBE 算了 4 个，那就不是同口径比较。

慎用于生产的关键约束 ​

风险说明缓解手段
结果集过大2^N 个分组集，输出行数激增减少列数、外层加 LIMIT、改 GROUPING SETS 只列所需组合
内存落盘多套聚合状态挤爆 work_mem调大 work_mem、加复合索引走排序聚合
明细行混杂明细与 7 类汇总混在一起，应用侧易误算用 GROUPING() 打标签并显式过滤
排序成本结果需按层级排序，Sort 可能落盘用 ORDER BY GROUPING(...), 列 并配合索引
高基数列组合爆炸且业务无意义只放低基数维度列

实战示例 ​

步骤 1：城市 / 品类 / 渠道全维度销量-利润矩阵 ​

sql
-- 完整 8 层矩阵，带中文层级标签
SELECT
  CASE WHEN GROUPING(city)     = 1 THEN '全部城市' ELSE city     END AS 城市,
  CASE WHEN GROUPING(category) = 1 THEN '全部品类' ELSE category END AS 品类,
  CASE WHEN GROUPING(channel)  = 1 THEN '全部渠道' ELSE channel  END AS 渠道,
  sum(qty)             AS 销量,
  sum(amount)          AS 销售额,
  sum(amount - cost)   AS 利润,
  round(100.0 * sum(amount - cost) / NULLIF(sum(amount), 0), 2) AS 利润率
FROM sales
GROUP BY CUBE (city, category, channel)
ORDER BY GROUPING(city), city,
         GROUPING(category), category,
         GROUPING(channel), channel;
text
   城市    |   品类   |   渠道   | 销量 |  销售额  |   利润   | 利润率
-----------+----------+----------+------+----------+----------+--------
 上海      | 手机     | 门店     |  201 |  8812.40 |  4211.30 |  47.79
 上海      | 手机     | 线上     |  198 |  9033.02 |  4501.88 |  49.84
 上海      | 手机     |          |  399 | 17845.42 |  8713.18 |  48.83
 上海      |          | 门店     |  811 | 35201.77 | 17002.11 |  48.30
 上海      |          |          | 1633 | 70332.20 | 33911.55 |  48.22
 全部城市  |          | 门店     | 4011 |491233.10 |240118.44 |  48.88
 全部城市  |          |          | 8001 |1489002.55|721330.12 |  48.44

步骤 2：与等价的 GROUPING SETS 自检一致性 ​

sql
-- CUBE 与手写 GROUPING SETS 双向差集应为空
WITH c AS (
  SELECT city, category, channel, sum(amount) AS amt
  FROM sales
  GROUP BY CUBE (city, category)
  -- 注意：这里 CUBE 只两列，便于与下方 4 个分组集对照
),
g AS (
  SELECT city, category, NULL::text AS channel, sum(amount) AS amt
  FROM sales
  GROUP BY GROUPING SETS ((city, category), (city), (category), ())
)
SELECT '仅 CUBE' AS 差异, * FROM (SELECT * FROM c EXCEPT ALL SELECT * FROM g) x
UNION ALL
SELECT '仅 GROUPING SETS', * FROM (SELECT * FROM g EXCEPT ALL SELECT * FROM c) y;
text
 差异 | city | category | channel | amt
------+------+----------+---------+-----
(0 rows)     -- 完全等价

步骤 3：只交付「按渠道 × 品类」的汇总层 ​

sql
-- 先用 CUBE 算全组合，再用 GROUPING() 精确筛出两层
SELECT * FROM (
  SELECT
    category,
    channel,
    GROUPING(category) AS g_cat,
    GROUPING(channel)  AS g_ch,
    sum(qty)           AS 销量,
    sum(amount)        AS 销售额
  FROM sales
  GROUP BY CUBE (category, channel)
) t
WHERE (g_cat = 0 AND g_ch = 0)   -- 品类 × 渠道明细
   OR (g_cat = 0 AND g_ch = 1)   -- 品类小计
ORDER BY g_cat, category, g_ch, channel;
text
 category | channel | g_cat | g_ch | 销量 |  销售额
----------+---------+-------+------+------+----------
 电脑     | 分销    |     0 |    0 |  661 | 176334.20
 电脑     | 门店    |     0 |    0 |  670 | 179012.55
 电脑     | 线上    |     0 |    0 |  663 | 177890.11
 电脑     |         |     0 |    1 | 1994 | 533236.86
 手机     | 分销    |     0 |    0 |  655 | 175221.09
 ...

步骤 4：组合嵌套 CUBE (city, ROLLUP (category, channel)) ​

sql
-- 只对 city 做全组合，对 category/channel 做层级上卷，避免 3 列全组合
SELECT
  city,
  category,
  channel,
  GROUPING(city)     AS g_city,
  GROUPING(category) AS g_cat,
  GROUPING(channel)  AS g_ch,
  sum(amount)        AS 销售额
FROM sales
GROUP BY CUBE (city, ROLLUP (category, channel))
ORDER BY GROUPING(category), category,
         GROUPING(channel), channel,
         GROUPING(city), city;
text
  city | category | channel | g_city | g_cat | g_ch |  销售额
-------+----------+---------+--------+-------+------+----------
 北京  | 手机     | 门店    |      0 |     0 |    0 |  8821.40
 北京  | 手机     | 线上    |      0 |     0 |    0 |  9133.02
 北京  | 手机     |         |      0 |     0 |    1 | 26844.53
 北京  |          |         |      0 |     1 |    1 |105430.20
 上海  | 手机     | 门店    |      0 |     0 |    0 |  8812.40
       | 手机     | 门店    |      1 |     0 |    0 | 44106.99
       | 手机     |         |      1 |     0 |    1 |133412.55
       |          |         |      1 |     1 |    1 |1489002.55

共 6 个分组集：(city,category,channel)、(city,category)、(city)、(category,channel)、(category)、()。相比 CUBE (city, category, channel) 的 8 个，省掉了 (city, channel) 与 (channel) 两个通常无意义的组合。

步骤 5：验证内存占用与 work_mem 影响 ​

sql
-- 默认 work_mem 下观察 Batches
SET work_mem = '4MB';
EXPLAIN (ANALYZE, BUFFERS)
SELECT city, category, channel, sum(amount)
FROM sales
GROUP BY CUBE (city, category, channel);
text
 HashAggregate  (cost=1892.00..2103.44 rows=1544 width=...) (actual time=48.221..52.118 rows=231 loops=1)
   ...
   Batches: 1  Memory Usage: 481kB
 Execution Time: 52.118 ms
sql
-- 换成高基数维度后，Batches 立即上升
EXPLAIN (ANALYZE, BUFFERS)
SELECT sold_at, city, category, sum(amount)
FROM sales
GROUP BY CUBE (sold_at, city, category);   -- sold_at 基数 180，组合行数暴增
text
 HashAggregate  (actual time=121.441..188.903 rows=18422 loops=1)
   ...
   Batches: 4  Memory Usage: 4225kB   -- 已溢出一部分到磁盘
   Disk Usage: 1344kB
 Execution Time: 188.903 ms

一个 sold_at 列的加入，把结果从 231 行推到 18422 行、耗时涨了 3.6 倍，并且开始落盘。这就是「CUBE 列数每加一，代价翻倍」的直接体感。

注意事项 ​

  1. CUBE (a, b) 已包含 ()，无需再手动补总计层；而嵌套写法 CUBE (a, ROLLUP (b, c)) 也含 ()，但 ROLLUP (a, ROLLUP (b, c)) 不含。
  2. CUBE 生成 2^N 个分组集，ROLLUP 只有 N+1 个，别把两者当等价语法糖混用：
    sql
    -- 错误！需要层级上卷却用了 CUBE，多算了 (category, channel) 等组合
SELECT city, category, channel, sum(amount)
FROM sales
GROUP BY CUBE (city, category, channel);
  3. 不要在 CUBE 里放高基数列（user_id、order_no、sold_at 细粒度日期），结果行数与内存会失控。
  4. GROUPING() 不能写在 WHERE 里——WHERE 在聚合前求值，必须用 HAVING 或外层查询：
    sql
    -- 错误！聚合尚未产生，GROUPING() 无意义
SELECT city, sum(amount) FROM sales
WHERE GROUPING(city) = 1
GROUP BY CUBE (city, category);

-- 正确
SELECT city, sum(amount) FROM sales
GROUP BY CUBE (city, category)
HAVING GROUPING(city) = 1;
  5. 不要用 IS NULL 判断汇总行，CUBE 下多个列可能同时为 NULL，且真实数据也可能含空值，用 GROUPING()。
  6. Batches > 1 或 Disk Usage 非零要立刻警觉，说明哈希聚合已落盘，应先降列数再考虑调 work_mem。
  7. CUBE 结果集大，应用层务必按 GROUPING() 区分层级，否则会把汇总行当明细行二次累加。
  8. 不要用 CUBE 替代多次精确查询。只需要 2~3 个特定组合时，GROUPING SETS 明确列举更省资源。
  9. CUBE 需要 PostgreSQL 9.5+。

最佳实践 ​

  1. 列数控制在 3 列以内作为硬性经验值：CUBE (a, b, c) 已是 8 个分组集，第 4 列就变 16 个。
  2. 优先用 GROUPING SETS 显式列举所需组合，只有当真的需要「任意维度切片」时才用 CUBE。
  3. 层级维度用 ROLLUP、正交维度用 CUBE，混用时优先 CUBE (a, ROLLUP (b, c)) 这类嵌套形式而非全 CUBE。
  4. 统一用 CASE WHEN GROUPING(x) = 1 THEN '全部xx' ELSE x END 输出中文标签，让结果可直接交付。
  5. 排序固定为 ORDER BY GROUPING(x), x 逐列展开，层级结构清晰、汇总行紧跟明细。
  6. 筛选汇总层一律用 HAVING GROUPING(...) = 1，或包一层子查询在外层过滤。
  7. 上线前用 EXPLAIN (ANALYZE, BUFFERS) 复核：确认只扫描一次基表、Batches = 1、Disk Usage 为空。
  8. 大表上先建覆盖所有分组键的复合索引（如 (city, category, channel)），让优化器有机会选 GroupAggregate 复用索引顺序。
  9. 会话级临时调大 work_mem（SET work_mem = '256MB'）跑报表，跑完即随连接释放，不要全局调大。

下一节

继续学习：GROUPING / GROUPING_ID

参考链接 ​