Hive - grouping sets 示例与详解_BIT_666_grouping sets 详解

网络投稿 02-07 7279

一.引言

现有超市用户购物数据表一张，其字段与数据如下：

用户 id 为连续数字编号，性别分别用 0、1表示，年龄使用 xxs 表示，cost 代表用户在超市的消费总额：

1 0 00s 100 20220505 2 1 90s 200 20220505 3 1 00s 300 20220505 4 1 70s 400 20220505 5 0 60s 500 20220505 6 0 80s 600 20220505 7 1 80s 700 20220505 8 0 60s 800 20220505 9 0 70s 900 20220505 10 1 10s 1000 20220505

下面使用 group by 和 grouping sets 对表中数据进行提取。

二.Group By

对 hive 表中的数据进行分组汇总时常用 group by 语法

1.按年龄分组 select user_age,sum(buy_cost) from user_purchasing_info group by user_age; 00s 400.0 10s 1000.0 60s 1300.0 70s 1300.0 80s 1300.0 90s 200.0

2.按性别分组 select user_gender,user_age,sum(buy_cost) from user_purchasing_info group by user_gender,user_age; 0 2900.0 1 2600.0

3.按性别、年龄分组

?按照多个变量进行 group by 时需要注意 group by 后的字段也需要全部添加：

select user_gender,user_age,sum(buy_cost) from user_purchasing_info group by user_gender,user_age; 0 00s 100.0 0 60s 1300.0 0 70s 900.0 0 80s 600.0 1 00s 300.0 1 10s 1000.0 1 70s 400.0 1 80s 700.0 1 90s 200.0

三.Grouping Sets 1.Grouping Sets 示例

grouping sets 方法提供一个 () sets，内部支持添加 group by 字段的不同组合，按不同维度分组聚合后，最后将数据合并，类似于根据 sets 内的字段进行多次 group by 操作再最后执行 union all。下面对超市购买表进行聚合，一次性查询按年龄、性别、年龄&性别的聚合结果：

select user_gender,user_age,sum(buy_cost) from user_purchasing_info where dt='20220505' group by user_gender,user_age grouping sets((user_gender),(user_age),(user_gender, user_age));

可以与上述查询结果相对应，其中蓝框内的结果为按年龄 user_age 进行分组的结果，红框内为按性别 user_gender + 年龄 user_age 进行分组的结果，剩余两行为按性别 user_gender 进行分组的结果。

2.Grouping Sets 实战

上面示例展示了一次获取不同分组的数据方法，实战场景下用法例如给定数据求按照某个维度的分数据和汇总总数据，对应到上述超市数据即求当天营业额按性别 + 年龄分组和总和数据：

select concat_ws('_',nvl(user_gender,'total'),nvl(user_age,'total')),sum(buy_cost) from user_purchasing_info where dt='20220505' group by user_gender,user_age grouping sets((user_gender),(user_gender, user_age),());

通过 concat_ws 和 nvl 函数连接了分组字段并使用 nvl 函数排除 NULL 的数据，非常的实用 👍?

total_total 5500.0 0_total 2900.0 0_00s 100.0 0_60s 1300.0 0_70s 900.0 0_80s 600.0 1_total 2600.0 1_00s 300.0 1_10s 1000.0 1_70s 400.0 1_80s 700.0 1_90s 200.0

四.总结

简言之 Grouping Sets = Group By + Union，有几个点需要注意：

A.NULL 值

上面 Grouping Sets 中分别采用 A、B、A+B 的字段聚合，但是 Select 时 A，B 都添加了，所以对应单独 GroupBy A 或者 B 的数据，最终结果会默认补 NULL，所以上面结果中会有很多 NULL 值。

B.空 Set ()

Grouping Sets 实战中，我们添加了一个空 Sets()，其等价于：

select sum(buy_cost) from user_purchasing_info where dt='20220505';

即对整个表计算 sum，此时 user_age、user_gender 都为 NULL，通过 nvl 函数转换为 total_total，所以最终数据得到 total_total: 5500.0 的数据。

C.分组函数

除了 Grouping Sets 这个聚合功能函数外，类似的函数还有 Grouping_Id、CUBE、RollUp，可以参考：Hive - Cube, Rollup, GroupingId 示例与详解。