数据准备
方法1:使用grouping sets
select province
,city
,area
,sum(person_num) person_num
from tmp.tmp_zb_leaarn
group by province
,city
,area
grouping sets(
(province,city,area),(province,city),(province),()
)
order by province,city,area
效果如图:
方法2:使用with rollup
select province
,city
,area
,sum(person_num) person_num
from tmp.tmp_zb_leaarn
group by province
,city
,area with rollup
order by province,city,area
效果如图:
with rollup使用起来比grouping sets更为方便,但是并没有grouping sets灵活 ,并不能控制那些多维度
方法3:使用with cube[全维度的组合]
select province
,city
,area
,sum(person_num) person_num
from tmp.tmp_zb_leaarn
group by province
,city
,area with cube
order by province,city,area
效果如下:
方法4:使用union all(下下策)
如上示例,使用4个select进行多个维度的聚合,最后进行union all