产品指南
查询与优化
Grouping Sets

GROUPING SET, ROLLUP 与 CUBE

在数据分析与报表生成（OLAP）场景中，我们经常需要对数据进行多维度、多层次的分组聚合计算。ProtonBase 提供了强大的 GROUP BY 扩展功能：RROUPING SET、ROLLUP 和 CUBE，能够高效地生成小计、总计和多维交叉报表，极大地简化了复杂聚合查询的编写。

基本概念与前置知识

在深入了解这三个功能之前，请确保你已理解基本的 GROUP BY 和聚合函数（如 SUM(), COUNT(), AVG()）的使用。

示例表 sales： 为了后续说明，我们假设有一个销售记录表 sales。

CREATE TABLE sales (
    region VARCHAR(50),
    country VARCHAR(50),
    product_category VARCHAR(50),
    sales_amount NUMERIC
);
 
-- 示例数据
INSERT INTO sales VALUES
('Asia', 'China', 'Electronics', 10000),
('Asia', 'China', 'Clothing', 5000),
('Asia', 'Japan', 'Electronics', 8000),
('Asia', 'Japan', 'Clothing', 3000),
('Europe', 'UK', 'Electronics', 7000),
('Europe', 'UK', 'Clothing', 2000),
('Europe', 'Germany', 'Electronics', 9000),
('Europe', 'Germany', 'Clothing', 4000);

GROUPING SETS：自定义分组集

GROUPING SETS 是最基础也是最灵活的原语。它允许你指定多个你希望同时计算的分组组合。ROLLUP 和 CUBE 本质上都是 GROUPING SETS 的特定语法糖。

语法：

SELECT
    column1,
    column2,
    aggregate_function(column3)
FROM
    table_name
GROUP BY
    GROUPING SETS (
        (column1, column2),
        (column1),
        (column2),
        () -- 空括号表示总计
    );

功能： 对 GROUPING SETS 子句中列出的每一个分组集合，分别执行一次 GROUP BY 操作，然后将所有结果联合（UNION ALL）在一起。

示例： 我们想同时获取：

  1. 按 region 和 country 的细粒度分组合计。
  2. 仅按 region 的分组合计。
  3. 仅按 country 的分组合计。
  4. 全局总计。

使用 GROUPING SETS 可以一步到位：

SELECT
    region,
    country,
    SUM(sales_amount) AS total_sales
FROM
    sales
GROUP BY
    GROUPING SETS (
        (region, country), -- 组合1：细粒度
        (region),          -- 组合2：仅大区
        (country),         -- 组合3：仅国家
        ()                 -- 组合4：总计
    )
ORDER BY
    region, country;
 region | country | total_sales
--------+---------+-------------
 Asia   | China   |       15000
 Asia   | Japan   |       11000
 Asia   |         |       26000  <- 这是 Asia 地区的小计
 Europe | Germany |       13000
 Europe | UK      |        9000
 Europe |         |       22000  <- 这是 Europe 地区的小计
        | China   |       15000  <- 所有地区下中国的合计
        | Germany |       13000
        | Japan   |       11000
        | UK      |        9000
        |         |       48000  <- 这是全局总计
(11 rows)

ROLLUP：分层级小计与总计

ROLLUP 用于生成分层级或分维度的小计和总计。它假设分组列之间存在层次结构（如 年 > 月 > 日，或 大区 > 国家 > 城市）。

语法：

GROUP BY ROLLUP (column1, column2, ..., columnN)

它等价于：

GROUP BY GROUPING SETS (
    (column1, column2, ..., columnN), -- 最细粒度
    (column1, column2, ...),
    ...,
    (column1), -- 第一层小计
    ()         -- 总计
)

功能： 从最细的粒度开始，从右向左逐级减少分组列，并生成小计，最后生成一个总计。

示例： 我们想分析 region > country > product_category 的层级销售情况，并得到每个层级的小计。

SELECT
    region,
    country,
    product_category,
    SUM(sales_amount) AS total_sales
FROM
    sales
GROUP BY
    ROLLUP (region, country, product_category)
ORDER BY
    region, country, product_category;

结果解读：

 region | country | product_category | total_sales
--------+---------+------------------+-------------
 Asia   | China   | Clothing         |        5000  -> 5000 (最细粒度)
 Asia   | China   | Electronics      |       10000  -> 10000 (最细粒度)
 Asia   | China   |                  |       15000  -> **15000** (中国的小计)
 Asia   | Japan   | Clothing         |        3000  -> 3000
 Asia   | Japan   | Electronics      |        8000  -> 8000
 Asia   | Japan   |                  |       11000  -> **11000** (日本的小计)
 Asia   |         |                  |       26000  -> **26000** (Asia 地区的小计)
 Europe | Germany | Clothing         |        4000
 Europe | Germany | Electronics      |        9000
 Europe | Germany |                  |       13000
 Europe | UK      | Clothing         |        2000
 Europe | UK      | Electronics      |        7000
 Europe | UK      |                  |        9000
 Europe |         |                  |       22000
        |         |                  |       48000  -> **48000** (全局总计)

CUBE：生成所有组合的小计

CUBE 的功能最强大，它会生成所有可能的分组列组合。如果你的维度之间是平级的，没有明确的层次关系，并且你想分析所有维度的交叉情况，CUBE 是最佳选择。

语法：

GROUP BY CUBE (column1, column2, ..., columnN)

对于 N 列，CUBE 会生成 2^N 种分组组合。例如 CUBE(a, b, c) 等价于：

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

功能： 生成所有可能的子集组合的分组聚合。

示例： 我们想对 region、country、product_category 三个维度进行全交叉分析。

SELECT
    region,
    country,
    product_category,
    SUM(sales_amount) AS total_sales
FROM
    sales
GROUP BY
    CUBE (region, country, product_category)
ORDER BY
    region, country, product_category;

结果解读（除了ROLLUP的结果，还会多出）：

 region | country | product_category | total_sales
--------+---------+------------------+-------------
 Asia   | China   | Clothing         |        5000
 Asia   | China   | Electronics      |       10000
 Asia   | China   |                  |       15000  -> 所有产品类别下中国的销售额 (15000)
 Asia   | Japan   | Clothing         |        3000
 Asia   | Japan   | Electronics      |        8000
 Asia   | Japan   |                  |       11000
 Asia   |         | Clothing         |        8000
 Asia   |         | Electronics      |       18000
 Asia   |         |                  |       26000
 Europe | Germany | Clothing         |        4000
 Europe | Germany | Electronics      |        9000
 Europe | Germany |                  |       13000  -> 所有产品类别下德国的销售额 (13000)
 Europe | UK      | Clothing         |        2000
 Europe | UK      | Electronics      |        7000
 Europe | UK      |                  |        9000
 Europe |         | Clothing         |        6000
 Europe |         | Electronics      |       16000
 Europe |         |                  |       22000
        | China   | Clothing         |        5000
        | China   | Electronics      |       10000
        | China   |                  |       15000
        | Germany | Clothing         |        4000
        | Germany | Electronics      |        9000
        | Germany |                  |       13000
        | Japan   | Clothing         |        3000
        | Japan   | Electronics      |        8000
        | Japan   |                  |       11000
        | UK      | Clothing         |        2000
        | UK      | Electronics      |        7000
        | UK      |                  |        9000
        |         | Clothing         |       14000  -> 所有地区所有国家的服装总额 (5000+3000+2000+4000=14000)
        |         | Electronics      |       34000  -> 所有地区所有国家的电子产品总额
        |         |                  |       48000

区分原始NULL与分组产生的NULL：GROUPING() 函数

当数据本身存在 NULL 值时，我们无法区分结果中的 NULL 是来自原始数据还是分组功能生成的。ProtonBase 提供了 GROUPING() 函数来解决这个问题。

语法： GROUPING(column_name)

  • 返回 0：表示该列是当前分组的一部分（其中的 NULL 是原始数据）。
  • 返回 1：表示该列因分组操作被聚合掉了（其中的 NULL 是小计/总计生成的）。

高级示例： 使用 GROUPING() 美化输出。

SELECT
    CASE
        WHEN GROUPING(region) = 1 THEN 'All Regions'
        ELSE COALESCE(region, 'N/A')
    END AS region,
    CASE
        WHEN GROUPING(country) = 1 THEN 'All Countries'
        ELSE COALESCE(country, 'N/A')
    END AS country,
    SUM(sales_amount) AS total_sales,
    -- 这是一个方便的位掩码，表示分组状态
    GROUPING(region, country) AS grouping_mask
FROM
    sales
GROUP BY
    ROLLUP (region, country)
ORDER BY
    region, country;

结果：

   region    |    country    | total_sales | grouping_mask
-------------+---------------+-------------+---------------
 All Regions | All Countries |       48000 |             3
 Asia        | All Countries |       26000 |             1
 Asia        | China         |       15000 |             0
 Asia        | Japan         |       11000 |             0
 Europe      | All Countries |       22000 |             1
 Europe      | Germany       |       13000 |             0
 Europe      | UK            |        9000 |             0

GROUPING(region, country) 返回一个整数，其二进制位表示每个字段的分组状态。例如 3 的二进制是 11，表示第一列 (region) 和第二列 (country) 都被聚合了。

总结与选择指南

特性GROUPING SETSROLLUPCUBE
灵活性极高，可指定任意组合中等，遵循层次结构高，生成所有组合
输出行数自定义较少 (N+1层)最多 (2^N 种组合)
适用场景需要特定、非连续的聚合组合有明确层次结构的报表（如地理、时间层级）多维交叉分析，探索所有维度关系
性能取决于指定的集合数量通常优于CUBE最耗资源，维度多时需谨慎

选择建议：

  • 需要总计和分层小计（如每个大区、每个国家的合计）时，用 ROLLUP。
  • 需要所有维度的所有可能组合的聚合时（如同时看大区合计、产品合计、大区与产品的交叉合计），用 CUBE。
  • 只需要其中某几个特定组合的聚合时（如同时看按大区聚合和按产品聚合，但不需要它们的交叉组合），用 GROUPING SETS，性能最好。
地理信息检索自定义函数(Beta)