hive grouping sets聚合函数

函数说明：

grouping sets
在一个 group by 查询中，根据不同的维度组合进行聚合，等价于将不同维度的 group by 结果集进行 union all

cube

根据 group by 的维度的所有组合进行聚合

cube简称数据魔方，可以实现hive多个任意维度的查询，cube(a,b,c)则首先会对(a,b,c)进行group by，然后依次是(a,b),(a,c),(a),(b,c),(b),(c),最后在对全表进行group by，他会统计所选列中值的所有组合的聚合

rollup
是 cube 的子集，以最左侧的维度为主，从该维度进行层级聚合。

（1）grouping sets 
select 
       order_id,
       departure_date,
       count(*) as cnt
  from ord_test
 where order_id=410341346
 group by order_id,
       departure_date
 grouping sets (order_id,(order_id,departure_date))
;

等价于以下
group by order_id
union all
group by order_id,departure_date

（2）cube
select 
       order_id,
       departure_date,
       count(*) as cnt
  from ord_test
 where order_id=410341346
 group by order_id,
       departure_date
 with cube
 ;

等价于以下
select count(*) as cnt from ord_test where order_id=410341346
union all
group by order_id
union all
group by departure_date
union all
group by order_id,departure_date

（3） rollup
select 
       order_id,
       departure_date,
       count(*) as cnt
  from ord_test
 where order_id=410341346
 group by order_id,
       departure_date
 with rollup
 ;

等价于以下
select count(*) as cnt from ord_test where order_id=410341346
union all
group by order_id
union all
group by order_id,departure_date