【Hive】多维函数 『grouping sets() | with cube | with rollup 』

1. 多维函数说明

1. 多维函数作用：是一种将多个group by逻辑写在一个sql语句中的便利写法。等价于将不同维度的group by结果集进行 union all
2. 与union all 效率对比：Grouping Sets 版本的 SQL 不仅在表达上更加简洁，在性能上也更加高效
3. Hive中多维函数有哪些？
   1. grouping sets()
   2. with cube
   3. with rollup

2. grouping sets()

(1) 语法

假设现在有如下3个SQL：

-- 第一个sql：以a维度计算num
select a, sum(num) from t group by a

-- 第二个sql：以b维度计算num
select b, sum(num) from t group by b

-- 第三个sql：以a, b维度计算num
select a, b, sum(num) from t group by a, b

如何把这三个SQL写到一个SQL中？

1. 方式一：用 union all

   select 
   	a,			 -- 只有a维度
   	null as b,   -- 没有b维度 
   	sum(num) as num
   from t group by a
   union all
   select 
   	null,     -- 没有a维度
   	b,        -- 只有b维度
   	sum(num) as num 
   from t group by b
   union all
   select 
   	a,   -- 有a维度
   	b,   -- 也有维度
   	sum(num) as num 
   from t group by a, b
   

2. 方式二：用 grouping sets() 函数

   -- 将这两个合在一起的更简单的写法：使用 grouping sets
   select 
   	a,
   	b,
   	sum(num) as num 
   from t
   group by a, b
   grouping sets (a, b, (a, b))  -- sets后面的括号指名了要取出现哪些维度
   order by grouping__id   -- grouping__id表示结果属于哪一个分组集合，以二进制的形式表达