本文一次性说清HIVE的with cube & with rollup & grouping sets用法

表结构

CREATE TABLE test (f1 string,  
                   f2 string,  
                   f3 string,  
                   cnt int) ROW FORMAT delimited FIELDS TERMINATED BY '\t' stored AS textfile;  
LOAD DATA LOCAL inpath '/data/logs/suiyingli/tmp/test.data' overwrite INTO TABLE test;  

原始数据
•A A B 1
•B B A 1
•A A A 2

1、with cube
查询语句

SELECT f1,  
       f2,  
       f3,  
       sum(cnt),  
       GROUPING__ID,  
       rpad(reverse(bin(cast(GROUPING__ID AS bigint))),3,'0')  
FROM test  
GROUP BY f1,  
         f2,  
         f3 WITH CUBE; 

结果

with cube查询结果

多维数据集
CUBE 运算符可用于生成 n 维的多维数据集，即具有任意数目维度的多维数据集。只有一个维度的多维数据集可用于生成合计，例如：

-- 此 SELECT 语句返回的结果集既显示了 Item 中每个值的小计，也显示了 Item 中所有值的总计：

SELECT CASE WHEN (GROUPING(Item) = 1) THEN 'ALL'
ELSE ISNULL(Item, 'UNKNOWN')
END AS Item,
SUM(Quantity) AS QtySum
FROM Inventory
GROUP BY Item WITH CUBE;

2、with rollup
查询语句

SELECT f1,  
       f2,  
       f3,  
       sum(cnt),  
       GROUPING__ID,  
       rpad(reverse(bin(cast(GROUPING__ID AS bigint))),3,'0')  
FROM test  
GROUP BY f1,  
         f2,  
         f3 WITH ROLLUP;  

结果

with rollup查询结果

 

3、grouping sets
查询语句

SELECT f1,  
       f2,  
       f3,  
       sum(cnt),  
       GROUPING__ID,  
       rpad(reverse(bin(cast(GROUPING__ID AS bigint))),3,'0')  
FROM test  
GROUP BY f1,  
         f2,  
         f3  
GROUPING SETS((f1),(f1,f2))  

结果

grouping sets查询结果

总结
cube的分组组合最全，是各个维度值的笛卡尔（包含null）组合，
rollup的各维度组合应满足，前一维度为null后一位维度必须为null，前一维度取非null时，下一维度随意，
grouping sets则为自定义维度，根据需要分组即可。
ps:通过grouping sets的使用可以简化SQL，比group by单维度进行union性能更好。

重点-： 结果分析函数 GROUPING() 和 GROUPING__ID：
1、结果集中的NULL值处理一 GROUPING() 函数：
注意：GROUPING() 函数 不能在hive中使用

CUBE 或 ROLLUP 等操作所生成的空值带来一个问题：如何区分 CUBE 或 ROLLUP 等操作所生成的 NULL 值和从实际数据中返回的 NULL 值？这个问题可用 GROUPING 函数解决。当行由 CUBE 或 ROLLUP 等运算符添加时，该函数将导致附加列的输出值为 1；当行不由 CUBE 或 ROLLUP 等运算符添加时，该函数将导致附加列的输出值为 0。

在 CUBE 或 ROLLUP 等操作中，所生成的 NULL 代表全体值。可将 SELECT 语句写成使用 GROUPING 函数将所生成的 NULL 替换为字符串 ALL 或 SUM。因为事实数据中的 NULL 表明数据值未知，所以 SELECT 语句还可译码为返回字符串 UNKNOWN 替代来自事实数据的 NULL。例如：

SELECT CASE WHEN (GROUPING(Item) = 1) THEN 'ALL'
ELSE ISNULL(Item, 'UNKNOWN')
END AS Item,
CASE WHEN (GROUPING(Color) = 1) THEN 'ALL'
ELSE ISNULL(Color, 'UNKNOWN')
END AS Color,
SUM(Quantity) AS QtySum
FROM Inventory
GROUP BY Item, Color WITH CUBE;

2、结果集中的NULL值处理二 GROUPING__ID：
GROUPING_ID 解释

1、GROUPING_ID 是hive生成的，是标识维度组合的十进制数字。

2、Grouping_ID  的生成规则：在 grouping sets 的组合中存在的字段使用 1 表示，否则使用 0 表示。（对称赋值）

例如1：
grouping sets 中有3组字段组合：grouping sets(month,day,(month,day))，分析如下：

（1）month          =>  month,null  : 0 1 换算成十进制 1

（2）day              =>   null,day      :1,0 换算成十进制 2

（3）(month,day) =>  (month,day):1,1 换算成十进制 3

例如2：

如果有 group by platform, position, title, bg 

grouping sets(

 platform, position, title, bg,(platform, position),(platform, position, title),(platform, position, title, bg)

)
组合为 ：

（1）platform,null,null,null:0001 换算成十进制为 1

（2）null,null,titile,null ：0100 换算成十进制为 4

（3）(platform, position, title, bg)：1111 换算成十进制 15

其他的就不具体细说了。

有了Grouping_ID 维度组合也就可以更加方便组合。

比如  使用hive语句：

       if(cast(GROUPING__ID as int) & 4 = 0, 'all', title) as title

表示若model参与group by操作，则使用model原有值，若model不参与group by操作，则将其置为'ALL'

3、通用型方法：NVL() 函数处理null值
注意：前提是处理好原本字段中的null值

NVL(Item,'ALL') as Item

SELECT 
nvl(Item,'ALL') Item,
nvl(Color,'ALL') Color,
SUM(Quantity) AS QtySum
FROM Inventory
GROUP BY Item, Color