1、场景描述
在进行多维度的数据查询时,有时候需要将维度按较细的颗粒度拆分,同时也需要按照较粗的颗粒度聚合,比如我们查询分城市的dau,同时也想知道所有城市的dau,通常来说多数人会这么写:
select
city_name,
count(distinct uid) as dau
from
xgchen_dw.dws_xgc_app_uid_topic
where
dp = '2021-04-05'
group by
city_name
union all
select
'全部城市' as city_name,
count(distinct uid) as dau
from
xgchen_dw.dws_xgc_app_uid_topic
where
dp = '2021-04-05'
group by
'全部城市';
2、改善与实现:
如果查询的代码很长,不止一个指标,那么会造成代码冗余,可以用lateral view explode()内人造维度进行简写,如下:
select
v1.city_name,
count(distinct uid) as dau
from
xgchen_dw.dws_xgc_app_uid_topic
lateral view explode(array(nvl(city_name,'其他'),'全部城市')) v1 as city_name
where
dp = '2021-04-05'
group by
v1.city_name
上述2种写法输出的结果一样,如有错误望批评斧正!