hive高级分组聚合groupingsets,cube,rollup

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

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

        set hive.verctorized.execution.enabled = true;

1、grouping sets

select prov_id ,deep ,count(1) as numfrom dim.dim_citywhere prov_id = 110000and deep = 1group by prov_id ,deepgrouping sets( (prov_id,deep) ,prov_id ,deep );--等同于select prov_id ,deep ,count(1) as numfrom dim.dim_citywhere prov_id = 110000and deep = 1group by prov_id ,deepunion allselect prov_id ,null ,count(1) as numfrom dim.dim_citywhere prov_id = 110000and deep = 1group by prov_idunion allselect null ,deep ,count(1) as numfrom dim.dim_citywhere prov_id = 110000and deep = 1group by deep

2、cube

select prov_id ,deep ,count(1) as numfrom dim.dim_citywhere prov_id = 110000and deep = 1group by prov_id ,deepwith cube;--等同于select prov_id ,deep ,count(1) as numfrom dim.dim_citywhere prov_id = 110000and deep = 1group by prov_id ,deepgrouping sets ((prov_id,deep) ,prov_id ,deep ,());

3、rollup

select prov_id ,deep ,count(1) as numfrom dim.dim_citywhere prov_id = 110000and deep = 1group by prov_id ,deepwith rollup;--等同于select prov_id ,deep ,count(1) as numfrom dim.dim_citywhere prov_id = 110000and deep = 1group by prov_id ,deepgrouping sets ((prov_id,deep) ,prov_id ,());

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