Hive中group by还能这样玩？

之前参加校招面试的时候，遇到一个hive分组函数的题目，大致内容如下：

我们有如下的一张表（数据纯属虚构）：

表中一共有四列数据，分别是地区，员工姓名，月份和销量。目前的需求时，想同时得到各地区的销量、各员工的销量，以及每个员工在每个地区的销量。

当时我是这么回答的：使用Group by和union all，对应的代码如下：

select
 area,null as name,sum(cnt) as sum_cnt
from
 default.salerinfo2
group by area


union all


select
 null as area,name,sum(cnt) as sum_cnt
from
 default.salerinfo2
group by name


union all


select
 area,name,sum(cnt) as sum_cnt
from
 default.salerinfo2
group by area,name

这样确实可以得到对应的结果：

但是不是觉得太繁琐了，其实hive中还有一个更高阶的分组函数，可以帮我们更加简洁的实现上面的功能，即Grouping Sets函数，我们可以把想要用于分组的列全都声明在Grouping Sets函数中，其代码如下：

select
 area,name,sum(cnt) as sum_cnt
from
 default.salerinfo3
group by area,name
grouping sets ((area),(name),(area,name))

得到了同样正确的结果：

可以看到，使用grouping sets必须首先用group by声明用于分组的列，如果不使用group by声明，报错如下：

FAILED: ParseException line 5:0 cannot recognize input near 'grouping' 'sets' '(' in table source

同样，用于分组的列，必须全都在group by中声明，如果grouping sets使用了没有在group by中出现的列，如下面的代码报错如下：

select
 area,name,sum(cnt) as sum_cnt
from
 default.salerinfo3
group by area
grouping sets ((area),(name),(area,name))


SemanticException 6:23 [Error 10213]: Grouping sets expression is not in GROUP BY key. Error encountered near token 'name'

是不是又学习到了新知识了呢，嘻嘻

往期推荐

数据分析EPHS(12)-Hive中使用over()实现累积求和和滑动求和

数据分析EPHS(11)-详解Hive中的排序函数 数据分析EPHS(10)-Hive中的explode使用全解