使用Grouping Sets可按照多个不同的维度组合进行聚合,减少了繁琐的代码,提升整体计算的效率。
比如,需要对国家,省份,城市各维度进行聚合时候,可能会这么写:
select '国家' as gep_type,country as geo_name,count(*) as cnt from tbl group by country
union all
select '省份' as gep_type,province as geo_name,count(*) as cnt from tbl group by province
union all
select '城市' as gep_type,city as geo_name,count(*) as cnt from tbl group by city
;
或者这么写:
select country,province,city,count(*) as cnt from tbl group by country,province,city
union all
select country,province,'ALL' as city,count(*) as cnt from tbl group by country,province
union all
select country,'ALL' as province,'ALL' as city,count(*) as cnt from tbl group by country
;
无论哪种方式都会用到union all,而通过grouping sets,我们可以这么写:
-- 1. 先把空值替换成'-',不然在聚合的时候会被当成'ALL'
with tmp as (
select
coalesce(country, '-') as country
,coalesce(province, '-') as province
,coalesce(city, '-') as city
from tbl
)
-- 2.对于非本维度组合的字段,以'ALL'填充
select
coalesce(country, 'ALL') as country
,coalesce(province, 'ALL') as province
,coalesce(city, 'ALL') as city
,count(*) as cnt
from
tmp
group by country,province,city -- 按country,province,city聚合
grouping sets(
(country) --只按country聚合,等于country,count(*)
,(province) --只按province聚合,等于province,count(*)
,(city) --只按city聚合,等于city,count(*)
,(country,province) --按country,province聚合,等于country,province,count(*)
)
有任何疑问都可以在下方留言,我会尽量进行解答!
希望本文对你有帮助,请点个赞鼓励一下作者吧~ 谢谢!