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

原创已于 2022-06-01 20:22:34 修改 · 988 阅读

2 ·

CC 4.0 BY-SA版权

文章标签：

#hive #hadoop #数据仓库

于 2022-02-13 15:13:53 首次发布

本文介绍了Hive中用于聚合查询的高级特性——GroupingSets、Cube和Rollup，详细解释了它们的语法和应用场景，并通过示例展示了它们如何简化复杂的SQL查询。这些功能在特定的数据分析任务中非常有用，但使用场景相对较少。

日常使用较少，主要记录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 ,())
;

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