CUBE 函数的强大功能

原创于 2025-09-24 10:56:00 发布 · 295 阅读

9 ·

CC 4.0 BY-SA版权

文章标签：

#hive

大数据开发-数据仓库-Hive 专栏收录该内容

24 篇文章

订阅专栏

好的，CUBE 是另一个强大的 OLAP（在线分析处理）函数，它比 ROLLUP 更进一步，用于生成所有可能的组合聚合（即幂集）。
它是 GROUP BY 子句的扩展。
核心概念
CUBE 会根据你在 GROUP BY CUBE() 中指定的字段，生成所有可能的组合分组的小计，最后生成一个总计。
假设你使用 GROUP BY CUBE(a, b, c)，它将生成 2^n 种分组组合（n 是字段数）。对于三个字段，它将生成以下 8 种分组：

(a, b, c) - 最细粒度的分组
(a, b) - 对 a 和 b 的小计
(a, c) - 对 a 和 c 的小计
(b, c) - 对 b 和 c 的小计
(a) - 对 a 的小计
(b) - 对 b 的小计
(c) - 对 c 的小计
() - 总计（Grand Total）

简单记忆：CUBE 生成的是一个多维数据集的"立方体"，你可以在任意维度上进行切片和汇总。

语法
sql
SELECT
column1,
column2,
...,
aggregate_function(column)
FROM table_name
GROUP BY CUBE (column1, column2, ...);

#示例详解
#我们继续使用 sales 表，并增加一个 year 字段，使其更符合 CUBE 的应用场景。

year   country   category   sales
2023   USA   Electronics   1000
2023   USA   Clothing   200
2023   China   Electronics   800
2023   China   Clothing   150
2024   USA   Electronics   1200
2024   USA   Clothing   250
2024   China   Electronics   950
2024   China   Clothing   180
#示例：使用 CUBE(year, country, category)
#我们想生成一个报表，包含所有年份、国家、产品类别组合的销售额小计和总计。

sql
SELECT
COALESCE(CAST(year AS STRING), 'All Years') AS year,
COALESCE(country, 'All Countries') AS country,
COALESCE(category, 'All Categories') AS category,
SUM(sales) AS total_sales
FROM sales
GROUP BY CUBE (year, country, category)
ORDER BY year, country, category;

#部分关键结果与解释：
#由于 CUBE 会生成 2^3 = 8 种组合，结果集较大，下表展示了部分有代表性的行：

year   country   category   total_sales   说明
2023   China   Clothing   150   细粒度：2023年中国服装的销售额
2023   China   All Categories   950   小计：2023年中国的总销售额 (150+800)
2023   All Countries   Electronics   1800   小计：2023年全球电子产品的销售额 (1000+800)
2023   All Countries   All Categories   2150   小计：2023年全球的总销售额
All Years   China   Electronics   1750   小计：所有年份中国电子产品的销售额 (800+950)
All Years   China   All Categories   2180   小计：所有年份中国的总销售额 (950+1230)
All Years   All Countries   Clothing   780   小计：所有年份全球服装的销售额 (200+150+250+180)
All Years   All Countries   All Categories   4930   总计：所有年份所有国家的总销售额

关键点：
CUBE 生成了 ROLLUP 无法生成的交叉组合小计。例如：
(year, category)：(2023, Electronics) - 某年所有国家的某类产品小计。
(country, category)：(China, Electronics) - 某国所有年份的某类产品小计。
(category)：(Electronics) - 所有年份所有国家的某类产品小计。
这些交叉维度的汇总对于多维数据分析至关重要。

使用 GROUPING() 和 GROUPING_ID() 进行精确识别
当维度很多时，CUBE 会产生大量汇总行。使用 GROUPING() 函数可以精确识别出当前行是在哪个或哪些维度上进行的汇总。
GROUPING_ID() 函数尤其有用，它将所有字段的 GROUPING() 结果组合成一个位图（bitmap），形成一个唯一的数字标识符，便于过滤和排序。

sql
SELECT
year,
country,
category,
SUM(sales) AS total_sales,
GROUPING(year) AS g_year,
GROUPING(country) AS g_country,
GROUPING(category) AS g_category,
GROUPING_ID(year, country, category) AS grouping_id
FROM sales
GROUP BY CUBE (year, country, category)
ORDER BY grouping_id, year, country, category;
结果示例：

year   country   category   total_sales   g_year   g_country   g_category   grouping_id   解释
2023   China   Clothing   150   0   0   0   0 (0b000)   最细粒度数据，无汇总
NULL   NULL   NULL   4930   1   1   1   7 (0b111)   总计，所有维度都已汇总
2023   NULL   NULL   2150   0   1   1   3 (0b011)   按 year 汇总 (g_year=0)
NULL   China   NULL   2180   1   0   1   5 (0b101)   按 country 汇总 (g_country=0)
NULL   NULL   Electronics   3950   1   1   0   6 (0b110)   按 category 汇总 (g_category=0)
2023   NULL   Electronics   1800   0   1   0   2 (0b010)   按 year, category 汇总
通过 GROUPING_ID，你可以轻松地过滤出你想要的特定汇总级别。例如，WHERE grouping_id = 6 可以只查看按产品类别汇总的结果。

最佳实践与建议
谨慎使用：CUBE 会生成巨大的结果集，尤其是当维度很多或基数很大时。务必先评估数据量。
使用 GROUPING_ID()：这是管理和解释 CUBE 庞大结果集的最有效工具，用于过滤、排序和标识。
性能考虑：CUBE 操作计算成本很高，在大型数据集上可能很慢。通常在预处理或构建摘要表时使用。
应用场景：CUBE 是生成交叉表（Pivot Table）和多维报表的终极SQL工具，非常适合支持商业智能（BI）和即席查询（Ad-hoc Query）需求。
总之，CUBE 提供了无与伦比的灵活性，让你能够从数据的每一个可能的角度进行观察和汇总，是OLAP分析中的一把利器。