企业官网建设流程全解析

先讲一个我印象特别深的场景。去年我们在做一个用户行为数据的多维统计项目，业务方一次性提出三个聚合维度：按城市统计、按渠道统计、按城市加渠道联合统计，输出访问量和独立访客数。团队里第一个反应就是拼三条 GROUP BY，再用 UNION ALL 连起来。结果在真实数据量上跑了一次，底表扫了三遍，触发三串 MapReduce 任务，作业跑了一个多小时，下游报表直接超时。后来我把这个聚合改成 Hive 的 GROUP SETS（严格说是 GROUPING SETS 语法族）重写，一条 SQL 就完成了三个维度的聚合，跑完不到原来的三分之一时间，代码反而短了一半。

这篇东西把我这次优化里涉及到的知识点完整拆开讲：GROUP SETS 解决什么痛点、底层执行原理是什么、怎么从 UNION ALL 方案平稳迁移、以及我在实战中踩过的坑。适合正在做 Hive SQL 优化的数据开发、分析工程师参考，也适合刚接触多维聚合想少走弯路的同学。下面直接进正题。

1. 先看一段让人头疼的多维聚合SQL

1.1 报表需求与原始SQL

需求本身很简单。有一张用户行为日志表 user_behavior_log，关键字段是 log_date（日期）、city（城市）、channel（渠道）、user_id（用户ID）、page_url（访问页面）。每天几百上千个文件，几十亿行数据。我们需要输出三张维度完全不同的统计：

1. 按城市：每个城市当天的 PV（访问次数）和 UV（独立用户数）
2. 按渠道：每个渠道当天的 PV 和 UV
3. 按城市加渠道：每个城市下每个渠道当天的 PV 和 UV

新人第一时间写出来的 SQL 长这样：

SELECT city, NULL AS channel, COUNT(*) AS pv, COUNT(DISTINCT user_id) AS uv FROM user_behavior_log WHERE log_date = '2024-06-01' GROUP BY city UNION ALL SELECT NULL AS city, channel, COUNT(*) AS pv, COUNT(DISTINCT user_id) AS uv FROM user_behavior_log WHERE log_date = '2024-06-01' GROUP BY channel UNION ALL SELECT city, channel, COUNT(*) AS pv, COUNT(DISTINCT user_id) AS uv FROM user_behavior_log WHERE log_date = '2024-06-01' GROUP BY city, channel

这段 SQL 在逻辑上完全正确，甚至很容易读懂，但它的问题不在正确性，而在执行效率。那三个 UNION ALL 分支看起来只是列出了三种维度组合，实际上每个分支都是一次完整的“扫描 + Shuffle + 聚合”，这是彻头彻尾的重复劳动。

1.2 UNION ALL方案的三宗罪

第一宗罪：底表扫描三遍。三个分支各自独立触发一次全表扫描，几十亿行数据读三遍，光 IO 就把任务拖垮了。对于日志类大表，扫描是所有环节里的第一大成本，三遍几乎等于三倍的基础耗时。

第二宗罪：Shuffle 三轮。city、channel、city+channel 的聚合键各不相同，每一遍都要单独做一次全局 Shuffle，Reducer 端再聚合一次。也就是说，我们为了三个统计结果，把最重的三个执行环节都重复了三遍。

第三宗罪是维护层面的。维度一旦增加，比如再加一个“按小时统计”，你就要在 UNION ALL 里加一大段几乎一样的 SQL。而且每个分支都要写同样的 WHERE 条件，改一个过滤逻辑可能要同时动三四处，很容易漏掉一两处，导致各分支口径不一致。

这三点合在一起，就是“能跑但不想跑”的典型代表。接下来的 GROUP SETS，就是专门解决这类问题的。

2. GROUPING SETS / ROLLUP / CUBE 语法与执行原理拆解

2.1 三兄弟怎么选

GROUP SETS 在 Hive 里其实是三个语法家族的统称：

• GROUPING SETS(...)：最灵活，你要哪几种组合就列哪几种
• WITH ROLLUP：沿着维度从细到粗逐级上卷，比如 (city, channel) -> (city) -> 总计
• WITH CUBE：给出所有维度组合，n 个维度就是 2^n 种组合

用同一个需求对比一下：

-- GROUPING SETS：手动指定组合 SELECT city, channel, COUNT(*) AS pv FROM user_behavior_log GROUP BY city, channel GROUPING SETS ((city), (channel), (city, channel), ()); -- ROLLUP：层级上卷 SELECT city, channel, COUNT(*) AS pv FROM user_behavior_log GROUP BY city, channel WITH ROLLUP; -- CUBE：全排列 SELECT city, channel, COUNT(*) AS pv FROM user_behavior_log GROUP BY city, channel WITH CUBE;

这里有几个细节要特别说清楚。GROUP BY 后面跟的是“可能参与聚合的全部维度”，GROUPING SETS 每组括号内写一种维度组合，空括号 () 表示不按任何维度聚合，也就是全表总计。ROLLUP 不用你写组合，它是按 GROUP BY 里字段从后往前逐级消减的固定套路：先按 (city, channel)，再按 (city)，最后是 ()。CUBE 则是把 (city)、(channel)、(city, channel)、() 全部生成一遍。

三兄弟的适用场景很清晰：要精确控制聚合粒度用 GROUPING SETS，做层级汇总报表用 ROLLUP，做多维分析探索用 CUBE。大多数业务报表其实用前两个就够，CUBE 在维度多的时候组合数量爆炸，要慎用。

2.2 GROUPING__ID 与 GROUPING()：读懂汇总行

用上 GROUPING SETS，你会看到输出行里有一些列是 NULL。这个 NULL 不是数据本身为空，而是“这一维度被聚合掉了”。比如按城市分组那组结果，channel 列全是 NULL。问题是，当数据里本身就可能出现 NULL 时，光靠肉眼和 NULL 判断行属于哪个分组是不可靠的。

所以 Hive 提供了两个专用工具：

• GROUPING__ID：整数，用位掩码标识当前行属于哪个分组
• GROUPING(列名)：返回 0 或 1，用于判断指定列在当前行是否被聚合掉

看这个例子：

SELECT city, channel, COUNT(*) AS pv, GROUPING__ID FROM user_behavior_log GROUP BY city, channel GROUPING SETS ((city), (channel), (city, channel), ()) ORDER BY GROUPING__ID;

GROUPING__ID 的计算规则是：GROUP BY 后面的字段从左到右对应二进制位，当前行如果聚合掉了某个字段，对应位就置 1。所以：

对应关系就是这么来的。注意位序：GROUP BY 里越靠后的字段，对应二进制位越靠低位（值越小）。如果维度多，你就按“后列低位、前列高位”的规则去组合判断，不要硬背数值。

GROUPING() 函数更直观，它接收一个列名，返回 1 表示这行在该列维度上是一个汇总行：

SELECT CASE WHEN GROUPING(city) = 1 THEN '全部城市' ELSE city END AS city, CASE WHEN GROUPING(channel) = 1 THEN '全部渠道' ELSE channel END AS channel, COUNT(*) AS pv FROM user_behavior_log GROUP BY city, channel GROUPING SETS ((city), (channel), (city, channel), ());

输出结果里“全部城市”“全部渠道”这样的标记，一眼就能看懂行属于哪个粒度。有一点要提醒：GROUPING() 函数在比较老的 Hive 版本里不可用，如果你的环境还在 2.3 之前，老老实实用 GROUPING__ID。

2.3 一条 SQL 为什么比三条 SQL 快

从执行层面解释一下快在哪。UNION ALL 方案里，每个分支都是一个独立的 MapReduce 过程：Map 端读数据、Shuffle 到对应 Reduce、Reduce 聚合。三组维度就是三遍独立流程。

GROUPING SETS 方案则把三个分组逻辑合并到一次作业里。数据只扫描一遍，Map 端会同时生成携带不同 GROUPING__ID 标记的聚合结果，Shuffle 阶段按“(GROUPING__ID, 分组键)”路由到 Reducer，Reducer 端根据 GROUPING__ID 区分当前数据属于哪个分组，一次输出所有组合的结果。简化后的执行计划大概长这样：

Stage-1 Map Group By Operator keys: GROUPING__ID, city, channel Reduce Group By Operator keys: GROUPING__ID, city, channel aggregations: count, count-distinct

这就能把最贵的两件事——全表扫描和全局 Shuffle——从三次降为一次。我用实际项目做了对比：同样的三组维度，同样几十亿行数据，UNION ALL 跑了一个多小时，GROUPING SETS 单作业 20 分钟左右搞定。后面的章节我会把整个迁移过程完整还原。

3. 实操改造：从UNION ALL到GROUPING SETS的完整迁移

3.1 迁移前：完整还原原始任务

原任务除了聚合，还要把结果同步到下游报表。数据集市表结构大概是：

CREATE TABLE agg_report_daily ( stat_date STRING COMMENT '统计日期', agg_level STRING COMMENT '聚合粒度', city STRING COMMENT '城市', channel STRING COMMENT '渠道', pv BIGINT, uv BIGINT );

原始 SQL 直接往这张表里写三份结果，用 agg_level 字段区分粒度：'city-only'、'channel-only'、'city-channel'。当时这个任务挂在调度上每天准时跑，但耗时越来越长，已经成了整个数仓链路里最不稳定的一个环节。

3.2 迁移后：一条 SQL 重写多维聚合

改造后的核心 SQL 长这样：

INSERT OVERWRITE TABLE agg_report_daily SELECT '2024-06-01' AS stat_date, CASE GROUPING__ID WHEN 1 THEN 'city-only' WHEN 2 THEN 'channel-only' WHEN 0 THEN 'city-channel' WHEN 3 THEN 'total' END AS agg_level, CASE WHEN GROUPING(city) = 1 THEN NULL ELSE city END AS city, CASE WHEN GROUPING(channel) = 1 THEN NULL ELSE channel END AS channel, COUNT(*) AS pv, COUNT(DISTINCT user_id) AS uv FROM user_behavior_log WHERE log_date = '2024-06-01' GROUP BY city, channel GROUPING SETS ((city), (channel), (city, channel), ());

这里我多说几个小细节，都是实战经验。

第一，CASE WHEN 里把汇总行对应的字段写成 NULL，是为了保持和原表清理逻辑一致，底层报表不用改。如果你希望看到的是字符串“全部”，直接写 'ALL' 也行，但要确认下游有没有针对这个字段做 is null 判断，别把统计口径改乱了。

第二，GROUPING__ID 的值不是固定的，它跟随 GROUP BY 字段数量和顺序变化。所以 CASE 里写死了四个值，不要觉得写完就万事大吉。后面加了一个维度，所有分支都要重新推导。

第三，COUNT(DISTINCT user_id) 这个写法在 GROUPING SETS 下依然有效，但要注意它是开销大户。底下我会单独讲怎么处理。

另外，如果只需要 (city, channel)、(city)、总计 这三种，也可以直接用 WITH ROLLUP，效果等价。但 ROLLUP 无法生成“只按 channel”这个分组。所以到底用哪个，取决于需求粒度。

3.3 结果校验三板斧

改造完之后不要急着上线，先做结果校验。我通常做三件事。

第一，行数校验。把 GROUPING SETS 结果和旧 SQL 结果分别查出来，按 GROUPING__ID 分组数行数，必须一一对应。比如 city 分组有多少城市，channel 分组的行数就应该等于旧方案里按渠道分组的结果行数。一个很小的验证 SQL 就够：

SELECT GROUPING__ID, COUNT(*) AS row_cnt FROM agg_report_daily WHERE stat_date = '2024-06-01' GROUP BY GROUPING__ID; -- 实际用你自己的粒度标识字段

第二，交叉验证。拿按 city 分组的总 PV，和原方案第一个分支的 SUM 比对；总计行的 PV，应该等于全表扫描 COUNT(*) 的精确值。这里推荐直接在同一个会话里把新旧两版结果都查出来，用 FULL OUTER JOIN 按维度键对账，差值必须为 0。

第三，抽查极端值。挑一个 PV 特别高的城市、一个主流渠道，单独跑一条 GROUP BY 去对账。大数对的概率高，小数和边界情况才容易出问题。

这三板斧做完，我才敢说改造成功。再补充一个排查小技巧：如果对不上，多数情况不是 GROUPING SETS 语法错了，而是数据类型不一致，比如 city 字段里混了 NULL 和空字符串，导致同一维度下分组数量变多。把数据洗一下再对账。

4. 踩坑记录与排查技巧

4.1 NULL 值陷阱

这个坑出现频率最高。GROUPING SETS 的输出会把被聚合掉的列置为 NULL，而业务表里本身也可能存在 NULL 值，两者混在一起。分析报告里如果直接用 COALESCE(city, '未知') 处理，会把“按渠道聚合”产生的汇总行错误地归类到“未知城市”里，报表里的“未知”维度会莫名其妙暴增。

正确做法：要么用 GROUPING() 函数显式判断，要么在 GROUP BY 前对可能为空的维度字段做统一清洗。比如把空字符串统一处理成 'NULL_VALUE' 这样的占位符，让数据和汇总标记不再混淆。

我见过不止一次线上报表出现“未知”维度暴增的问题，最后定位下来都是这个原因，排查起来还挺费劲，因为单独看任何一行数据都像是正常的。

4.2 维度基数差距大与 COUNT DISTINCT

GROUPING SETS 虽然只扫一遍表，但输出结果是所有组合之和。当 GROUP BY 字段里有一个维度基数特别低（比如渠道只有三五个），另一个基数特别高（城市几十个），并且两者组合时，Reducer 端容易出现数据倾斜：高基数的组合拖慢整体进度，低基数组合却早早跑完干等。

另外，COUNT(DISTINCT user_id) 是个重操作。在多个分组组合里都要算 UV 时，它的负担会近似乘上组合数。我那次优化里，UV 部分反而成了耗时大头，光是这一个算子就占了近一半资源。

碰到这种情况，我的套路是：

• 在 ETL 上游先把 user_id 去重，生成中间明细表，再用 GROUPING SETS 做常规聚合
• 或者用近似 UV 函数替换精确 COUNT(DISTINCT)，接受一定的误差
• 任务开启 map 端聚合，避免太多等值键直接落到 Reduce

4.3 两个关键参数

Hive 上直接影响 GROUP SETS 执行计划的，一个是 hive.new.job.grouping.set.cardinality，默认 30。它规定了一个阈值：当 grouping sets 的组合数量超过这个值时，Hive 会把作业拆成多个 job 来跑，本质上是把原查询重写成 UNION ALL 的形式，以避免单作业 Map 端输出爆炸。

另一个是 hive.exec.parallel，默认 false。如果你的环境里 GROUPING SETS 被拆成了多个 job，把这个参数打开，作业之间可以并行执行，能明显减少整体耗时。

这两个参数没有放之四海而皆准的配置，最好是结合 EXPLAIN 看执行计划再调。组合数少（比如三五个维度组合）就保持默认，单作业执行；组合数很多（比如 CUBE 的 8 种组合以上）就要评估拆 job 的内存风险，避免单点 OOM。

4.4 高频问题速查表

5. 一个忠告：别迷信 GROUP SETS

5.1 我的两个判断标准

讲完优点和坑，最后说点泼冷水的话。GROUPING SETS 并不是在所有场景下都最优。

当维度字段非常多、组合数量爆炸时（比如 CUBE 在 6 个维度下就是 64 种组合），单作业的 Map 端输出和 Reduce 端内存可能都扛不住。这种情况反而适合退回 UNION ALL，每个分支拆成独立作业，失败还能单独重跑。

另外，如果你的聚合任务本身就是按日增量跑，数据量不大（几百万行以内），三条 GROUP BY 加 UNION ALL 跑起来也就几秒钟，没必要为了“高级语法”去改。优化要讲究投入产出比。

我个人的判断标准有两个：一是看表扫描是否成为瓶颈，底表超过千万行级别才值得动；二是看维度组合数，小于等于 4 个维度的聚合，GROUPING SETS 是首选，再往上就要权衡内存。

5.2 一个实战小技巧

最后再分享一个小技巧：迁移 GROUPING SETS 这类查询时，先在小表上跑出结果，确认 GROUPING__ID 的位序、行数、汇总值三层都对了，再换成大表。我当时就是用 10 万行的抽样表反复验证，避免在几十亿数据的全量任务上反复试错。

按照这个流程走，GROUPING SETS 能成为你手里一个非常顺手的多维聚合工具，而不是一个光鲜但看不懂的语法糖。每次优化聚合任务之前，先花十分钟跑一遍成本估算，再决定用哪套方案，这比记住任何固定模板都重要。