Hive中GROUPING SETS功能详解

我们来详细讲解一下 Hive 中的 GROUPING SETS 功能。这是一个非常强大且实用的特性，用于实现高级的多维数据分析。

1. 核心概念：它解决了什么问题？

想象一个场景：你有一张销售数据表，包含 地区（region）、 产品类别（category） 和 销售额（sales） 三个字段。

你现在需要生成一个报表，要求同时包含：

1. 每个地区的总销售额。
2. 每个产品类别的总销售额。
3. 所有地区的总销售额（全局总计）。
4. 每个地区下每个产品类别的总销售额。

传统的方法是写多个 SQL 查询，然后用 UNION ALL 把它们合并起来。这种方法有明显的缺点：

• 代码冗长：需要重复编写类似的 GROUP BY 语句。
• 性能低下：表会被多次扫描，计算效率低。
• 维护困难：如果需要修改逻辑，需要在多个地方进行更改。

GROUPING SETS 就是为了解决这个问题而生的。它允许你在一个单一的 GROUP BY 查询中，指定多个不同的分组组合。Hive 会在一次表扫描中，并行地计算出所有分组组合的结果，极大地提高了效率。

2. 语法和基本用法

GROUPING SETS 是 GROUP BY 子句的扩展。其基本语法如下：

SELECT
    column1,
    column2,
    ...,
    aggregate_function(column)
FROM
    table_name
GROUP BY
    column1,
    column2,
    ...
    GROUPING SETS (
        (group1_1, group1_2), -- 分组组合1
        (group2_1, group2_2), -- 分组组合2
        ...,
        ()                    -- 空括号表示全局分组（总计）
    );

关键点：

• 在 GROUPING SETS 的括号内，每个小括号 () 代表一个你想要的分组维度集合。
• 你可以列出任意数量的分组组合。
• 空的分组组合 () 表示对任何维度都不分组，即计算全局总计。

3. 实战示例

我们使用开头的销售数据例子。假设有一张表 sales_data：

region	category	sales
North	Electronics	100
North	Books	50
South	Electronics	200
South	Books	75
West	Electronics	150

现在我们用 GROUPING SETS 来实现需求：

SELECT
    region,
    category,
    SUM(sales) AS total_sales
FROM
    sales_data
GROUP BY
    region,
    category
    GROUPING SETS (
        (region, category), -- 每个地区每个类别
        (region),           -- 每个地区
        (category),         -- 每个类别
        ()                  -- 全局总计
    )
ORDER BY
    region,
    category;

查询结果会是什么样？

region	category	total_sales	说明
NULL	NULL	575	全局总计：所有地区所有类别的销售额之和
NULL	Books	125	每个类别总计：所有地区的Books销售额
NULL	Electronics	450	每个类别总计：所有地区的Electronics销售额
North	NULL	150	每个地区总计：North地区的所有类别销售额
North	Books	50	每个地区每个类别：North地区的Books
North	Electronics	100	每个地区每个类别：North地区的Electronics
South	NULL	275	每个地区总计：South地区的所有类别销售额
South	Books	75	每个地区每个类别：South地区的Books
South	Electronics	200	每个地区每个类别：South地区的Electronics
West	NULL	150	每个地区总计：West地区的所有类别销售额
West	Electronics	150	每个地区每个类别：West地区的Electronics
West	Books	NULL	注意：这一行不会出现，因为原数据中没有这个组合

注意结果中的 NULL：

• 在 region 列为 NULL 的行，表示这个结果是在该行上对所有地区进行了聚合（即每个类别总计和全局总计）。
• 在 category 列为 NULL 的行，表示这个结果是在该行上对所有类别进行了聚合（即每个地区总计和全局总计）。
• 这引入了歧义：这个 NULL 到底是数据中本来就存在的 NULL 值，还是由于聚合产生的 NULL？

4. 识别分组来源：

4. 识别分组来源：

GROUPING__ID 和 grouping 函数

为了解决上述 NULL 值的歧义问题，Hive 提供了两个重要的工具。

a)

a)

GROUPING__ID （注意是双下划线）

这是一个特殊的虚拟列，它返回一个整数（位图）。这个整数的二进制表示标识了哪些列参与了当前行的分组。

• 规则：对于 GROUP BY 中的每一列，如果该列在当前分组组合中被聚合了（即不在分组键中），则对应的二进制位为 1，否则为 0。
• 顺序与 GROUP BY 后列的顺序一致。

在我们的例子中，GROUP BY (region, category)，顺序是 1. region, 2. category。

分组组合	二进制（region, category）	GROUPING__ID	含义
(region, category)	(0, 0)	0	两列都参与了分组，都不是聚合产生的 NULL。
(region)	(0, 1)	1	只有 region 参与分组，category 列的 NULL 是聚合产生的。
(category)	(1, 0)	2	只有 category 参与分组，region 列的 NULL 是聚合产生的。
()	(1, 1)	3	两列都未参与分组，它们的 NULL 都是聚合产生的。

使用 GROUPING__ID 过滤：

SELECT
    region,
    category,
    SUM(sales) AS total_sales,
    GROUPING__ID -- 显示分组ID
FROM
    sales_data
GROUP BY
    region,
    category
    GROUPING SETS (
        (region, category),
        (region),
        (category),
        ()
    )
HAVING GROUPING__ID = 0; -- 例如，只查看最细粒度的（地区-类别）数据

b)

b)

grouping(column) 函数（推荐）

这是一个更直观的函数，它接受一个列名作为参数。

• 如果当前行的该列 NULL 是由于聚合产生的，则返回 1。
• 如果该列是正常的分组键或数据中的 NULL，则返回 0。

这比 GROUPING__ID 更容易理解和维护。

SELECT
    region,
    category,
    SUM(sales) AS total_sales,
    grouping(region) AS r_null_flag,   -- 1表示region的NULL是聚合产生的
    grouping(category) AS c_null_flag -- 1表示category的NULL是聚合产生的
FROM
    sales_data
GROUP BY
    region,
    category
    GROUPING SETS (
        (region, category),
        (region),
        (category),
        ()
    );

结果示例：

region	category	total_sales	r_null_flag	c_null_flag
NULL	NULL	575	1	1
NULL	Books	125	1	0
North	NULL	150	0	1
North	Books	50	0	0

5. 简写语法：

5. 简写语法：

CUBE 和 ROLLUP

GROUPING SETS 还有两个更强大的“语法糖”：

1. WITH ROLLUP：
   它会生成一种层次结构的聚合，从最精细的级别到最粗的全局总计。
   GROUP BY a, b, c WITH ROLLUP 等价于
   GROUPING SETS ( (a,b,c), (a,b), (a), () )。

2. WITH CUBE：
   它会生成所有可能的分组组合（笛卡尔积）。
   GROUP BY a, b, c WITH CUBE 等价于
   GROUPING SETS ( (a,b,c), (a,b), (a,c), (b,c), (a), (b), (c), () )。

总结

特性	优点	适用场景
GROUPING SETS	一次查询，多维度结果；性能高（单次表扫描）；代码简洁。	需要生成包含不同粒度汇总数据的报表（如数据立方体）。
GROUPING__ID / grouping()	消除 NULL 歧义，明确标识每一行数据的聚合维度。	在查询结果中需要清晰区分原始数据 NULL 和聚合产生的 NULL。
WITH ROLLUP	生成层次化的聚合（上卷操作）。	数据有自然层次结构时，如“国家-省-城市”。
WITH CUBE	生成所有可能组合的聚合。	需要进行全面的多维分析，探索所有维度之间的关系。

掌握 GROUPING SETS 及其相关功能，是进行高效、复杂 OLAP（联机分析处理）查询的关键，能让你在 Hive 中的数据分析和报表生成能力大大提升。