17370845950

GROUPING SETS可在一次查询中实现多维聚合，等价于多个GROUP BY通过UNION ALL合并，适用于销售分析等场景；结合GROUPING函数可区分分组层级，提升OLAP查询效率。

PostgreSQL 中的 GROUPING SETS 是多维聚合分析的重要工具，它允许你在一次查询中对多个分组维度进行聚合，而无需使用多个 UNION ALL 查询。这种技术在生成报表、数据立方体（Data Cube）和交叉汇总时非常高效。

理解 GROUPING SETS 的基本语法

GROUPING SETS 是 GROUP BY 子句的一种扩展形式。你可以指定多个分组组合，PostgreSQL 会分别按这些组合进行聚合，并将结果合并返回。

基本语法如下：

SELECT 
    column1, column2, 
    aggregate_function(column3)
FROM table_name
GROUP BY GROUPING SETS (
    (column1, column2),
    (column1),
    (column2),
    ()
);

上述语句等价于将四个不同 GROUP BY 的查询用 UNION ALL 连接起来：

• 按 column1 和 column2 分组
• 只按 column1 分组
• 只按 column2 分组
• 不分组（全局聚合）

实际应用场景：销售数据分析

假设你有一个销售表 sales，包含以下字段：

sales(store, region, product, amount, sale_date)

你想一次性获取以下聚合结果：

• 按门店和地区汇总销售额
• 仅按地区汇总
• 仅按产品汇总
• 总销售额

使用 GROUPING SETS 可以这样写：

SELECT 
    store,
    region,
    product,
    SUM(amount) AS total_sales
FROM sales
GROUP BY GROUPING SETS (
    (store, region),
    (region),
    (product),
    ()
);

结果中会出现：

• store 和 region 有值，product 为 NULL：表示按门店和地区聚合
• store 为 NULL，region 有值：表示只按地区聚合
• product 有值，其他为 NULL：表示只按产品聚合
• 所有维度都为 NULL：表示总销售额

结合 ROLLUP 和 CUBE 理解更高级聚合

GROUPING SETS 更灵活，ROLLUP 和 CUBE 实际上是它的特例。

• ROLLUP (A, B, C) 等价于 GROUPING SETS ((A,B,C), (A,B), (A), ())
• CUBE (A, B) 等价于 GROUPING SETS ((A,B), (A), (B), ())

如果你需要自定义组合，比如跳过某些层级，GROUPING SETS 就体现出优势。例如，只想看“按地区+产品”和“按门店”的聚合，而不生成中间层级：

GROUP BY GROUPING SETS (
    (region, product),
    (store)
);

识别聚合层级：使用 GROUPING 函数

由于结果中 NULL 可能代表“未分组”，容易与原始数据中的 NULL 混淆。PostgreSQL 提供 GROUPING() 函数来区分。

该函数返回一个位图，标识某列是否参与当前行的分组。例如：

SELECT 
    store,
    region,
    product,
    GROUPING(store, region, product) AS grp_mask,
    SUM(amount) AS total_sales
FROM sales
GROUP BY GROUPING SETS (
    (store, region),
    (region),
    (product),
    ()
);

返回的 grp_mask 是一个整数：

• 0：三列都参与分组（如 store+region 行，product 列为 NULL，但 grp=4）
• 4（二进制 100）：product 未参与
• 6（110）：store 和 region 未参与

你可以用它来添加标签：

CASE 
  WHEN GROUPING(store, region) = 0 THEN 'Store + Region'
  WHEN GROUPING(store) = 1 AND GROUPING(region) = 0 THEN 'By Region'
  WHEN GROUPING(product) = 0 THEN 'By Product'
  ELSE 'Total'
END AS level

基本上就这些。GROUPING SETS 让你在单条 SQL 中完成复杂聚合，减少扫描次数，提升性能，特别适合 BI 报表和 OLAP 分析场景。关键是理清你需要的分组组合，并用 GROUPING 函数辅助结果解读。不复杂但容易忽略细节。