grouping sets不需要把所有的列名进行组合,把需要的进行组合就可以了;(dt),(dt, lead_pro_name),(dt, lead_city_name),这里加了三种情况,分别表示的是每天全国维度,每天省份维度,每天城市维度,如果再加(dt, lead_pro_name,lead_city_name),这个就是和(dt, lead_city_name)一样的情况了,会造成重复,所以写的时候先考虑清楚,是需要将哪几种维度的放在一起。
select
dt,
lead_pro_name,
lead_city_name,
case
when lead_pro_name is null
and lead_city_name is null then '全国'
when lead_pro_name is not null
and lead_city_name is null then '省份'
when lead_city_name is not null then '城市'
end as `维度`,
sum(sale_cnt) as `销量`
from
wujie.dm_cx_trd_multi_order_di
where
dt >= date_sub(current_date, 2)
and is_td_valid_pay = 1
group by
dt,
lead_pro_name,
lead_city_name grouping sets(
(dt),
(dt, lead_pro_name),
(dt, lead_city_name)
);