hive高级分组聚合grouping sets,cube,rollup

 

        日常使用较少，主要记录grouping sets,cube,rollup这三个分组聚合。

        首先，使用高级分组聚合的语法时，要注意hive是否开启了向量模式。

        set hive.verctorized.execution.enabled = true;

1、grouping sets

select  prov_id
        ,deep
        ,count(1) as num
from    dim.dim_city
where   prov_id = 110000
and     deep = 1
group by prov_id
        ,deep
grouping sets( (prov_id,deep) ,prov_id ,deep )
;

--等同于
select  prov_id
        ,deep
        ,count(1) as num
from    dim.dim_city
where   prov_id = 110000
and     deep = 1
group by prov_id
        ,deep
union all
select  prov_id
        ,null
        ,count(1) as num
from    dim.dim_city
where   prov_id = 110000
and     deep = 1
group by prov_id
union all
select  null
        ,deep
        ,count(1) as num
from    dim.dim_city
where   prov_id = 110000
and     deep = 1
group by deep

2、cube

select  prov_id
        ,deep
        ,count(1) as num
from    dim.dim_city
where   prov_id = 110000
and     deep = 1
group by prov_id
        ,deep
with cube
;

--等同于
select  prov_id
        ,deep
        ,count(1) as num
from    dim.dim_city
where   prov_id = 110000
and     deep = 1
group by prov_id
        ,deep
grouping sets ((prov_id,deep) ,prov_id ,deep ,())
;

3、rollup

select  prov_id
        ,deep
        ,count(1) as num
from    dim.dim_city
where   prov_id = 110000
and     deep = 1
group by prov_id
        ,deep
with rollup
;

--等同于
select  prov_id
        ,deep
        ,count(1) as num
from    dim.dim_city
where   prov_id = 110000
and     deep = 1
group by prov_id
        ,deep
grouping sets ((prov_id,deep) ,prov_id ,())
;

如果确实有比较契合的需求用起来还是比较方便的，但是使用场景确实也少