今天在开发数据的时候，发现数据不对，排查后发现是原来用写的 presto 脚本改造成 sparksql 后没有问题，放到 hive 中却有问题，最后，放到测试机器中的 hive 又正确。由此做一次实验：
先来说结论：
Hive 进行过更新，在旧版本中（如1.2）的计算方法和新版本的（hive 2.3.0之后）的结果，两者是不一致的。
而 spark、presto 则和 hive 新版本的结果一致。
创建 hive 表
create table region (
province string COMMENT '省份',
city string COMMENT '地市',
county string COMMENT '区县'
)
ROW FORMAT DELIMITED
FIELDS TERMINATED BY '|'
STORED AS TEXTFILE;
准备数据
福建省|福州市|鼓楼区
计算思路
grouping_id 的计算基本思路是，是把 group by 中的字段顺序，组成一个二进制数，根据是否参与聚合填充 0 和 1，最后转为十进制数。
Hive
执行语句
select
grouping__id as id,
province,
city,
county
from region
group by province,city,county
grouping sets (
(),
(province),
(province,city),
(province,city,county)
)
order by id
Hive 1.2 版本
运行结果
# Hive1.2版本
id province city county
0 NULL NULL NULL
1 福建省 NULL NULL
3 福建省 福州市 NULL
7 福建省 福州市 鼓楼区
在这个版本中的计算方式，需要先 倒叙 group by 的字段顺序，然后参与聚合的位置为 1，不参与的为 0。即：
先将 province,city,county 转为 county,city,province，然后根据以下表中所示进行计算
| 聚合情况 | county,city,province（二进制表示） | grouping_id（十进制结果） |
|---|---|---|
| () | 000 | 0 |
| (province) | 001 | 1 |
| (province,city) | 011 | 3 |
| (province,city,county) | 111 | 7 |
Hive 3.1 版本
运行结果
# Hive3.1版本
id province city county
0 福建省 福州市 鼓楼区
1 福建省 福州市 NULL
3 福建省 NULL NULL
7 NULL NULL NULL
在 3.1 中，不需要进行倒叙，同时参与聚合的为 0，不参与的为 1。即：
| 聚合情况 | province,city,county（二进制表示） | grouping_id（十进制结果） |
|---|---|---|
| () | 111 | 7 |
| (province) | 011 | 3 |
| (province,city) | 001 | 1 |
| (province,city,county) | 000 | 0 |
SparkSQL
执行语句
select
grouping__id as id,
province,
city,
county
from region
group by province,city,county
grouping sets (
(),
(province),
(province,city),
(province,city,county)
)
order by id
有些版本会让你用
grouping_id()Error in query: grouping__id is deprecated; use grouping_id() instead;
运行结果
0 福建省 福州市 鼓楼区
1 福建省 福州市 NULL
3 福建省 NULL NULL
7 NULL NULL NULL
改版本结果与计算方法和 Hive 3.1 一致。
Presto
执行语句
select
grouping(province,city,county) as grouping_id,
province,
city,
county
from region
group by grouping sets (
(),
(province),
(province,city),
(province,city,county)
)
order by grouping_id
运行结果
grouping_id | province | city | county
-------------+----------+--------+--------
0 | 福建省 | 福州市 | 鼓楼区
1 | 福建省 | 福州市 | NULL
3 | 福建省 | NULL | NULL
7 | NULL | NULL | NULL
结果与 Hive 3.1 一致。
总结
Hive 进行过更新，在旧版本中（如1.2）的计算方法和新版本的（hive 2.3.0之后）的结果，两者是不一致的。
而 spark、presto 则和 hive 新版本的结果一致。
