1.按周统计
insert into xxxx.dm_sale
with dim_date as (
select
date_code,
year_code,
year_month,
month_code ,
day_month_num ,
dim_date_id ,
year_week_name_cn
from hive.bj59_yp_dwd_jiale.dim_date
),
t1 as (
select
'2022-05-10' as date_time,
'week' as time_type,
year_code ,
null as year_month,
null as month_code,
null as day_month_num ,
null as dim_date_id,
year_week_name_cn ,
-- 处理维度:
case
when grouping(d.year_week_name_cn,s.city_id ,s.trade_area_id ,s.store_id,s.brand_id ,s.max_class_id ,s.mid_class_id ,s.min_class_id ) = 15 and s.group_type = 'store'
then 'store'
when grouping(d.year_week_name_cn,s.city_id ,s.trade_area_id ,s.store_id,s.brand_id ,s.max_class_id ,s.mid_class_id ,s.min_class_id ) = 31 and s.group_type = 'trade_area'
then 'trade_area'
when grouping(d.year_week_name_cn,s.city_id ,s.trade_area_id ,s.store_id,s.brand_id ,s.max_class_id ,s.mid_class_id ,s.min_class_id ) = 63 and s.group_type = 'city'
then 'city'
when grouping(d.year_week_name_cn,s.city_id ,s.trade_area_id ,s.store_id,s.brand_id ,s.max_class_id ,s.mid_class_id ,s.min_class_id ) = 119 and s.group_type = 'brand'
then 'brand'
when grouping(d.year_week_name_cn,s.city_id ,s.trade_area_id ,s.store_id,s.brand_id ,s.max_class_id ,s.mid_class_id ,s.min_class_id ) = 120 and s.group_type = 'min_class'
then 'min_class'
when grouping(d.year_week_name_cn,s.city_id ,s.trade_area_id ,s.store_id,s.brand_id ,s.max_class_id ,s.mid_class_id ,s.min_class_id ) = 121 and s.group_type = 'mid_class'
then 'mid_class'
when grouping(d.year_week_name_cn,s.city_id ,s.trade_area_id ,s.store_id,s.brand_id ,s.max_class_id ,s.mid_class_id ,s.min_class_id ) = 123 and s.group_type = 'max_class'
then 'max_class'
when grouping(d.year_week_name_cn,s.city_id ,s.trade_area_id ,s.store_id,s.brand_id ,s.max_class_id ,s.mid_class_id ,s.min_class_id ) = 127 and s.group_type = 'all'
then 'all'
else null
end as group_type,
s.city_id ,
s.city_name ,
s.trade_area_id ,
s.trade_area_name,
s.store_id,
s.store_name ,
s.brand_id,
s.brand_name,
s.max_class_id,
s.max_class_name,
s.mid_class_id,
s.mid_class_name,
s.min_class_id,
s.min_class_name,
-- 指标:
case
when grouping(d.year_week_name_cn,s.city_id ,s.trade_area_id ,s.store_id,s.brand_id ,s.max_class_id ,s.mid_class_id ,s.min_class_id ) = 15 and s.group_type = 'store'
then sum(s.sale_amt)
when grouping(d.year_week_name_cn,s.city_id ,s.trade_area_id ,s.store_id,s.brand_id ,s.max_class_id ,s.mid_class_id ,s.min_class_id ) = 31 and s.group_type = 'trade_area'
then sum(s.sale_amt)
when grouping(d.year_week_name_cn,s.city_id ,s.trade_area_id ,s.store_id,s.brand_id ,s.max_class_id ,s.mid_class_id ,s.min_class_id ) = 63 and s.group_type = 'city'
then sum(s.sale_amt)
when grouping(d.year_week_name_cn,s.city_id ,s.trade_area_id ,s.store_id,s.brand_id ,s.max_class_id ,s.mid_class_id ,s.min_class_id ) = 119 and s.group_type = 'brand'
then sum(s.sale_amt)
when grouping(d.year_week_name_cn,s.city_id ,s.trade_area_id ,s.store_id,s.brand_id ,s.max_class_id ,s.mid_class_id ,s.min_class_id ) = 120 and s.group_type = 'min_class'
then sum(s.sale_amt)
when grouping(d.year_week_name_cn,s.city_id ,s.trade_area_id ,s.store_id,s.brand_id ,s.max_class_id ,s.mid_class_id ,s.min_class_id ) = 121 and s.group_type = 'mid_class'
then sum(s.sale_amt)
when grouping(d.year_week_name_cn,s.city_id ,s.trade_area_id ,s.store_id,s.brand_id ,s.max_class_id ,s.mid_class_id ,s.min_class_id ) = 123 and s.group_type = 'max_class'
then sum(s.sale_amt)
when grouping(d.year_week_name_cn,s.city_id ,s.trade_area_id ,s.store_id,s.brand_id ,s.max_class_id ,s.mid_class_id ,s.min_class_id ) = 127 and s.group_type = 'all'
then sum(s.sale_amt)
else null
end as sale_amt,
case
when grouping(d.year_week_name_cn,s.city_id ,s.trade_area_id ,s.store_id,s.brand_id ,s.max_class_id ,s.mid_class_id ,s.min_class_id ) = 15 and s.group_type = 'store'
then sum(s.plat_amt)
when grouping(d.year_week_name_cn,s.city_id ,s.trade_area_id ,s.store_id,s.brand_id ,s.max_class_id ,s.mid_class_id ,s.min_class_id ) = 31 and s.group_type = 'trade_area'
then sum(s.plat_amt)
when grouping(d.year_week_name_cn,s.city_id ,s.trade_area_id ,s.store_id,s.brand_id ,s.max_class_id ,s.mid_class_id ,s.min_class_id ) = 63 and s.group_type = 'city'
then sum(s.plat_amt)
when grouping(d.year_week_name_cn,s.city_id ,s.trade_area_id ,s.store_id,s.brand_id ,s.max_class_id ,s.mid_class_id ,s.min_class_id ) = 119 and s.group_type = 'brand'
then sum(s.plat_amt)
when grouping(d.year_week_name_cn,s.city_id ,s.trade_area_id ,s.store_id,s.brand_id ,s.max_class_id ,s.mid_class_id ,s.min_class_id ) = 120 and s.group_type = 'min_class'
then sum(s.plat_amt)
when grouping(d.year_week_name_cn,s.city_id ,s.trade_area_id ,s.store_id,s.brand_id ,s.max_class_id ,s.mid_class_id ,s.min_class_id ) = 121 and s.group_type = 'mid_class'
then sum(s.plat_amt)
when grouping(d.year_week_name_cn,s.city_id ,s.trade_area_id ,s.store_id,s.brand_id ,s.max_class_id ,s.mid_class_id ,s.min_class_id ) = 123 and s.group_type = 'max_class'
then sum(s.plat_amt)
when grouping(d.year_week_name_cn,s.city_id ,s.trade_area_id ,s.store_id,s.brand_id ,s.max_class_id ,s.mid_class_id ,s.min_class_id ) = 127 and s.group_type = 'all'
then sum(s.plat_amt)
else null
end as plat_amt,
case
when grouping(d.year_week_name_cn,s.city_id ,s.trade_area_id ,s.store_id,s.brand_id ,s.max_class_id ,s.mid_class_id ,s.min_class_id ) = 15 and s.group_type = 'store'
then sum(s.deliver_sale_amt)
when grouping(d.year_week_name_cn,s.city_id ,s.trade_area_id ,s.store_id,s.brand_id ,s.max_class_id ,s.mid_class_id ,s.min_class_id ) = 31 and s.group_type = 'trade_area'
then sum(s.deliver_sale_amt)
when grouping(d.year_week_name_cn,s.city_id ,s.trade_area_id ,s.store_id,s.brand_id ,s.max_class_id ,s.mid_class_id ,s.min_class_id ) = 63 and s.group_type = 'city'
then sum(s.deliver_sale_amt)
when grouping(d.year_week_name_cn,s.city_id ,s.trade_area_id ,s.store_id,s.brand_id ,s.max_class_id ,s.mid_class_id ,s.min_class_id ) = 119 and s.group_type = 'brand'
then sum(s.deliver_sale_amt)
when grouping(d.year_week_name_cn,s.city_id ,s.trade_area_id ,s.store_id,s.brand_id ,s.max_class_id ,s.mid_class_id ,s.min_class_id ) = 120 and s.group_type = 'min_class'
then sum(s.deliver_sale_amt)
when grouping(d.year_week_name_cn,s.city_id ,s.trade_area_id ,s.store_id,s.brand_id ,s.max_class_id ,s.mid_class_id ,s.min_class_id ) = 121 and s.group_type = 'mid_class'
then sum(s.deliver_sale_amt)
when grouping(d.year_week_name_cn,s.city_id ,s.trade_area_id ,s.store_id,s.brand_id ,s.max_class_id ,s.mid_class_id ,s.min_class_id ) = 123 and s.group_type = 'max_class'
then sum(s.deliver_sale_amt)
when grouping(d.year_week_name_cn,s.city_id ,s.trade_area_id ,s.store_id,s.brand_id ,s.max_class_id ,s.mid_class_id ,s.min_class_id ) = 127 and s.group_type = 'all'
then sum(s.deliver_sale_amt)
else null
end as deliver_sale_amt,
case
when grouping(d.year_week_name_cn,s.city_id ,s.trade_area_id ,s.store_id,s.brand_id ,s.max_class_id ,s.mid_class_id ,s.min_class_id ) = 15 and s.group_type = 'store'
then sum(s.mini_app_sale_amt)
when grouping(d.year_week_name_cn,s.city_id ,s.trade_area_id ,s.store_id,s.brand_id ,s.max_class_id ,s.mid_class_id ,s.min_class_id ) = 31 and s.group_type = 'trade_area'
then sum(s.mini_app_sale_amt)
when grouping(d.year_week_name_cn,s.city_id ,s.trade_area_id ,s.store_id,s.brand_id ,s.max_class_id ,s.mid_class_id ,s.min_class_id ) = 63 and s.group_type = 'city'
then sum(s.mini_app_sale_amt)
when grouping(d.year_week_name_cn,s.city_id ,s.trade_area_id ,s.store_id,s.brand_id ,s.max_class_id ,s.mid_class_id ,s.min_class_id ) = 119 and s.group_type = 'brand'
then sum(s.mini_app_sale_amt)
when grouping(d.year_week_name_cn,s.city_id ,s.trade_area_id ,s.store_id,s.brand_id ,s.max_class_id ,s.mid_class_id ,s.min_class_id ) = 120 and s.group_type = 'min_class'
then sum(s.mini_app_sale_amt)
when grouping(d.year_week_name_cn,s.city_id ,s.trade_area_id ,s.store_id,s.brand_id ,s.max_class_id ,s.mid_class_id ,s.min_class_id ) = 121 and s.group_type = 'mid_class'
then sum(s.mini_app_sale_amt)
when grouping(d.year_week_name_cn,s.city_id ,s.trade_area_id ,s.store_id,s.brand_id ,s.max_class_id ,s.mid_class_id ,s.min_class_id ) = 123 and s.group_type = 'max_class'
then sum(s.mini_app_sale_amt)
when grouping(d.year_week_name_cn,s.city_id ,s.trade_area_id ,s.store_id,s.brand_id ,s.max_class_id ,s.mid_class_id ,s.min_class_id ) = 127 and s.group_type = 'all'
then sum(s.mini_app_sale_amt)
else null
end as mini_app_sale_amt,
case
when grouping(d.year_week_name_cn,s.city_id ,s.trade_area_id ,s.store_id,s.brand_id ,s.max_class_id ,s.mid_class_id ,s.min_class_id ) = 15 and s.group_type = 'store'
then sum(s.android_sale_amt)
when grouping(d.year_week_name_cn,s.city_id ,s.trade_area_id ,s.store_id,s.brand_id ,s.max_class_id ,s.mid_class_id ,s.min_class_id ) = 31 and s.group_type = 'trade_area'
then sum(s.android_sale_amt)
when grouping(d.year_week_name_cn,s.city_id ,s.trade_area_id ,s.store_id,s.brand_id ,s.max_class_id ,s.mid_class_id ,s.min_class_id ) = 63 and s.group_type = 'city'
then sum(s.android_sale_amt)
when grouping(d.year_week_name_cn,s.city_id ,s.trade_area_id ,s.store_id,s.brand_id ,s.max_class_id ,s.mid_class_id ,s.min_class_id ) = 119 and s.group_type = 'brand'
then sum(s.android_sale_amt)
when grouping(d.year_week_name_cn,s.city_id ,s.trade_area_id ,s.store_id,s.brand_id ,s.max_class_id ,s.mid_class_id ,s.min_class_id ) = 120 and s.group_type = 'min_class'
then sum(s.android_sale_amt)
when grouping(d.year_week_name_cn,s.city_id ,s.trade_area_id ,s.store_id,s.brand_id ,s.max_class_id ,s.mid_class_id ,s.min_class_id ) = 121 and s.group_type = 'mid_class'
then sum(s.android_sale_amt)
when grouping(d.year_week_name_cn,s.city_id ,s.trade_area_id ,s.store_id,s.brand_id ,s.max_class_id ,s.mid_class_id ,s.min_class_id ) = 123 and s.group_type = 'max_class'
then sum(s.android_sale_amt)
when grouping(d.year_week_name_cn,s.city_id ,s.trade_area_id ,s.store_id,s.brand_id ,s.max_class_id ,s.mid_class_id ,s.min_class_id ) = 127 and s.group_type = 'all'
then sum(s.android_sale_amt)
else null
end as android_sale_amt,
case
when grouping(d.year_week_name_cn,s.city_id ,s.trade_area_id ,s.store_id,s.brand_id ,s.max_class_id ,s.mid_class_id ,s.min_class_id ) = 15 and s.group_type = 'store'
then sum(s.ios_sale_amt)
when grouping(d.year_week_name_cn,s.city_id ,s.trade_area_id ,s.store_id,s.brand_id ,s.max_class_id ,s.mid_class_id ,s.min_class_id ) = 31 and s.group_type = 'trade_area'
then sum(s.ios_sale_amt)
when grouping(d.year_week_name_cn,s.city_id ,s.trade_area_id ,s.store_id,s.brand_id ,s.max_class_id ,s.mid_class_id ,s.min_class_id ) = 63 and s.group_type = 'city'
then sum(s.ios_sale_amt)
when grouping(d.year_week_name_cn,s.city_id ,s.trade_area_id ,s.store_id,s.brand_id ,s.max_class_id ,s.mid_class_id ,s.min_class_id ) = 119 and s.group_type = 'brand'
then sum(s.ios_sale_amt)
when grouping(d.year_week_name_cn,s.city_id ,s.trade_area_id ,s.store_id,s.brand_id ,s.max_class_id ,s.mid_class_id ,s.min_class_id ) = 120 and s.group_type = 'min_class'
then sum(s.ios_sale_amt)
when grouping(d.year_week_name_cn,s.city_id ,s.trade_area_id ,s.store_id,s.brand_id ,s.max_class_id ,s.mid_class_id ,s.min_class_id ) = 121 and s.group_type = 'mid_class'
then sum(s.ios_sale_amt)
when grouping(d.year_week_name_cn,s.city_id ,s.trade_area_id ,s.store_id,s.brand_id ,s.max_class_id ,s.mid_class_id ,s.min_class_id ) = 123 and s.group_type = 'max_class'
then sum(s.ios_sale_amt)
when grouping(d.year_week_name_cn,s.city_id ,s.trade_area_id ,s.store_id,s.brand_id ,s.max_class_id ,s.mid_class_id ,s.min_class_id ) = 127 and s.group_type = 'all'
then sum(s.ios_sale_amt)
else null
end as ios_sale_amt,
case
when grouping(d.year_week_name_cn,s.city_id ,s.trade_area_id ,s.store_id,s.brand_id ,s.max_class_id ,s.mid_class_id ,s.min_class_id ) = 15 and s.group_type = 'store'
then sum(s.pcweb_sale_amt)
when grouping(d.year_week_name_cn,s.city_id ,s.trade_area_id ,s.store_id,s.brand_id ,s.max_class_id ,s.mid_class_id ,s.min_class_id ) = 31 and s.group_type = 'trade_area'
then sum(s.pcweb_sale_amt)
when grouping(d.year_week_name_cn,s.city_id ,s.trade_area_id ,s.store_id,s.brand_id ,s.max_class_id ,s.mid_class_id ,s.min_class_id ) = 63 and s.group_type = 'city'
then sum(s.pcweb_sale_amt)
when grouping(d.year_week_name_cn,s.city_id ,s.trade_area_id ,s.store_id,s.brand_id ,s.max_class_id ,s.mid_class_id ,s.min_class_id ) = 119 and s.group_type = 'brand'
then sum(s.pcweb_sale_amt)
when grouping(d.year_week_name_cn,s.city_id ,s.trade_area_id ,s.store_id,s.brand_id ,s.max_class_id ,s.mid_class_id ,s.min_class_id ) = 120 and s.group_type = 'min_class'
then sum(s.pcweb_sale_amt)
when grouping(d.year_week_name_cn,s.city_id ,s.trade_area_id ,s.store_id,s.brand_id ,s.max_class_id ,s.mid_class_id ,s.min_class_id ) = 121 and s.group_type = 'mid_class'
then sum(s.pcweb_sale_amt)
when grouping(d.year_week_name_cn,s.city_id ,s.trade_area_id ,s.store_id,s.brand_id ,s.max_class_id ,s.mid_class_id ,s.min_class_id ) = 123 and s.group_type = 'max_class'
then sum(s.pcweb_sale_amt)
when grouping(d.year_week_name_cn,s.city_id ,s.trade_area_id ,s.store_id,s.brand_id ,s.max_class_id ,s.mid_class_id ,s.min_class_id ) = 127 and s.group_type = 'all'
then sum(s.pcweb_sale_amt)
else null
end as pcweb_sale_amt,
case
when grouping(d.year_week_name_cn,s.city_id ,s.trade_area_id ,s.store_id,s.brand_id ,s.max_class_id ,s.mid_class_id ,s.min_class_id ) = 15 and s.group_type = 'store'
then sum(s.order_cnt)
when grouping(d.year_week_name_cn,s.city_id ,s.trade_area_id ,s.store_id,s.brand_id ,s.max_class_id ,s.mid_class_id ,s.min_class_id ) = 31 and s.group_type = 'trade_area'
then sum(s.order_cnt)
when grouping(d.year_week_name_cn,s.city_id ,s.trade_area_id ,s.store_id,s.brand_id ,s.max_class_id ,s.mid_class_id ,s.min_class_id ) = 63 and s.group_type = 'city'
then sum(s.order_cnt)
when grouping(d.year_week_name_cn,s.city_id ,s.trade_area_id ,s.store_id,s.brand_id ,s.max_class_id ,s.mid_class_id ,s.min_class_id ) = 119 and s.group_type = 'brand'
then sum(s.order_cnt)
when grouping(d.year_week_name_cn,s.city_id ,s.trade_area_id ,s.store_id,s.brand_id ,s.max_class_id ,s.mid_class_id ,s.min_class_id ) = 120 and s.group_type = 'min_class'
then sum(s.order_cnt)
when grouping(d.year_week_name_cn,s.city_id ,s.trade_area_id ,s.store_id,s.brand_id ,s.max_class_id ,s.mid_class_id ,s.min_class_id ) = 121 and s.group_type = 'mid_class'
then sum(s.order_cnt)
when grouping(d.year_week_name_cn,s.city_id ,s.trade_area_id ,s.store_id,s.brand_id ,s.max_class_id ,s.mid_class_id ,s.min_class_id ) = 123 and s.group_type = 'max_class'
then sum(s.order_cnt)
when grouping(d.year_week_name_cn,s.city_id ,s.trade_area_id ,s.store_id,s.brand_id ,s.max_class_id ,s.mid_class_id ,s.min_class_id ) = 127 and s.group_type = 'all'
then sum(s.order_cnt)
else null
end as order_cnt,
case
when grouping(d.year_week_name_cn,s.city_id ,s.trade_area_id ,s.store_id,s.brand_id ,s.max_class_id ,s.mid_class_id ,s.min_class_id ) = 15 and s.group_type = 'store'
then sum(s.eva_order_cnt)
when grouping(d.year_week_name_cn,s.city_id ,s.trade_area_id ,s.store_id,s.brand_id ,s.max_class_id ,s.mid_class_id ,s.min_class_id ) = 31 and s.group_type = 'trade_area'
then sum(s.eva_order_cnt)
when grouping(d.year_week_name_cn,s.city_id ,s.trade_area_id ,s.store_id,s.brand_id ,s.max_class_id ,s.mid_class_id ,s.min_class_id ) = 63 and s.group_type = 'city'
then sum(s.eva_order_cnt)
when grouping(d.year_week_name_cn,s.city_id ,s.trade_area_id ,s.store_id,s.brand_id ,s.max_class_id ,s.mid_class_id ,s.min_class_id ) = 119 and s.group_type = 'brand'
then sum(s.eva_order_cnt)
when grouping(d.year_week_name_cn,s.city_id ,s.trade_area_id ,s.store_id,s.brand_id ,s.max_class_id ,s.mid_class_id ,s.min_class_id ) = 120 and s.group_type = 'min_class'
then sum(s.eva_order_cnt)
when grouping(d.year_week_name_cn,s.city_id ,s.trade_area_id ,s.store_id,s.brand_id ,s.max_class_id ,s.mid_class_id ,s.min_class_id ) = 121 and s.group_type = 'mid_class'
then sum(s.eva_order_cnt)
when grouping(d.year_week_name_cn,s.city_id ,s.trade_area_id ,s.store_id,s.brand_id ,s.max_class_id ,s.mid_class_id ,s.min_class_id ) = 123 and s.group_type = 'max_class'
then sum(s.eva_order_cnt)
when grouping(d.year_week_name_cn,s.city_id ,s.trade_area_id ,s.store_id,s.brand_id ,s.max_class_id ,s.mid_class_id ,s.min_class_id ) = 127 and s.group_type = 'all'
then sum(s.eva_order_cnt)
else null
end as eva_order_cnt,
case
when grouping(d.year_week_name_cn,s.city_id ,s.trade_area_id ,s.store_id,s.brand_id ,s.max_class_id ,s.mid_class_id ,s.min_class_id ) = 15 and s.group_type = 'store'
then sum(s.bad_eva_order_cnt)
when grouping(d.year_week_name_cn,s.city_id ,s.trade_area_id ,s.store_id,s.brand_id ,s.max_class_id ,s.mid_class_id ,s.min_class_id ) = 31 and s.group_type = 'trade_area'
then sum(s.bad_eva_order_cnt)
when grouping(d.year_week_name_cn,s.city_id ,s.trade_area_id ,s.store_id,s.brand_id ,s.max_class_id ,s.mid_class_id ,s.min_class_id ) = 63 and s.group_type = 'city'
then sum(s.bad_eva_order_cnt)
when grouping(d.year_week_name_cn,s.city_id ,s.trade_area_id ,s.store_id,s.brand_id ,s.max_class_id ,s.mid_class_id ,s.min_class_id ) = 119 and s.group_type = 'brand'
then sum(s.bad_eva_order_cnt)
when grouping(d.year_week_name_cn,s.city_id ,s.trade_area_id ,s.store_id,s.brand_id ,s.max_class_id ,s.mid_class_id ,s.min_class_id ) = 120 and s.group_type = 'min_class'
then sum(s.bad_eva_order_cnt)
when grouping(d.year_week_name_cn,s.city_id ,s.trade_area_id ,s.store_id,s.brand_id ,s.max_class_id ,s.mid_class_id ,s.min_class_id ) = 121 and s.group_type = 'mid_class'
then sum(s.bad_eva_order_cnt)
when grouping(d.year_week_name_cn,s.city_id ,s.trade_area_id ,s.store_id,s.brand_id ,s.max_class_id ,s.mid_class_id ,s.min_class_id ) = 123 and s.group_type = 'max_class'
then sum(s.bad_eva_order_cnt)
when grouping(d.year_week_name_cn,s.city_id ,s.trade_area_id ,s.store_id,s.brand_id ,s.max_class_id ,s.mid_class_id ,s.min_class_id ) = 127 and s.group_type = 'all'
then sum(s.bad_eva_order_cnt)
else null
end as bad_eva_order_cnt,
case
when grouping(d.year_week_name_cn,s.city_id ,s.trade_area_id ,s.store_id,s.brand_id ,s.max_class_id ,s.mid_class_id ,s.min_class_id ) = 15 and s.group_type = 'store'
then sum(s.deliver_order_cnt)
when grouping(d.year_week_name_cn,s.city_id ,s.trade_area_id ,s.store_id,s.brand_id ,s.max_class_id ,s.mid_class_id ,s.min_class_id ) = 31 and s.group_type = 'trade_area'
then sum(s.deliver_order_cnt)
when grouping(d.year_week_name_cn,s.city_id ,s.trade_area_id ,s.store_id,s.brand_id ,s.max_class_id ,s.mid_class_id ,s.min_class_id ) = 63 and s.group_type = 'city'
then sum(s.deliver_order_cnt)
when grouping(d.year_week_name_cn,s.city_id ,s.trade_area_id ,s.store_id,s.brand_id ,s.max_class_id ,s.mid_class_id ,s.min_class_id ) = 119 and s.group_type = 'brand'
then sum(s.deliver_order_cnt)
when grouping(d.year_week_name_cn,s.city_id ,s.trade_area_id ,s.store_id,s.brand_id ,s.max_class_id ,s.mid_class_id ,s.min_class_id ) = 120 and s.group_type = 'min_class'
then sum(s.deliver_order_cnt)
when grouping(d.year_week_name_cn,s.city_id ,s.trade_area_id ,s.store_id,s.brand_id ,s.max_class_id ,s.mid_class_id ,s.min_class_id ) = 121 and s.group_type = 'mid_class'
then sum(s.deliver_order_cnt)
when grouping(d.year_week_name_cn,s.city_id ,s.trade_area_id ,s.store_id,s.brand_id ,s.max_class_id ,s.mid_class_id ,s.min_class_id ) = 123 and s.group_type = 'max_class'
then sum(s.deliver_order_cnt)
when grouping(d.year_week_name_cn,s.city_id ,s.trade_area_id ,s.store_id,s.brand_id ,s.max_class_id ,s.mid_class_id ,s.min_class_id ) = 127 and s.group_type = 'all'
then sum(s.deliver_order_cnt)
else null
end as deliver_order_cnt,
case
when grouping(d.year_week_name_cn,s.city_id ,s.trade_area_id ,s.store_id,s.brand_id ,s.max_class_id ,s.mid_class_id ,s.min_class_id ) = 15 and s.group_type = 'store'
then sum(s.refund_order_cnt)
when grouping(d.year_week_name_cn,s.city_id ,s.trade_area_id ,s.store_id,s.brand_id ,s.max_class_id ,s.mid_class_id ,s.min_class_id ) = 31 and s.group_type = 'trade_area'
then sum(s.refund_order_cnt)
when grouping(d.year_week_name_cn,s.city_id ,s.trade_area_id ,s.store_id,s.brand_id ,s.max_class_id ,s.mid_class_id ,s.min_class_id ) = 63 and s.group_type = 'city'
then sum(s.refund_order_cnt)
when grouping(d.year_week_name_cn,s.city_id ,s.trade_area_id ,s.store_id,s.brand_id ,s.max_class_id ,s.mid_class_id ,s.min_class_id ) = 119 and s.group_type = 'brand'
then sum(s.refund_order_cnt)
when grouping(d.year_week_name_cn,s.city_id ,s.trade_area_id ,s.store_id,s.brand_id ,s.max_class_id ,s.mid_class_id ,s.min_class_id ) = 120 and s.group_type = 'min_class'
then sum(s.refund_order_cnt)
when grouping(d.year_week_name_cn,s.city_id ,s.trade_area_id ,s.store_id,s.brand_id ,s.max_class_id ,s.mid_class_id ,s.min_class_id ) = 121 and s.group_type = 'mid_class'
then sum(s.refund_order_cnt)
when grouping(d.year_week_name_cn,s.city_id ,s.trade_area_id ,s.store_id,s.brand_id ,s.max_class_id ,s.mid_class_id ,s.min_class_id ) = 123 and s.group_type = 'max_class'
then sum(s.refund_order_cnt)
when grouping(d.year_week_name_cn,s.city_id ,s.trade_area_id ,s.store_id,s.brand_id ,s.max_class_id ,s.mid_class_id ,s.min_class_id ) = 127 and s.group_type = 'all'
then sum(s.refund_order_cnt)
else null
end as refund_order_cnt,
case
when grouping(d.year_week_name_cn,s.city_id ,s.trade_area_id ,s.store_id,s.brand_id ,s.max_class_id ,s.mid_class_id ,s.min_class_id ) = 15 and s.group_type = 'store'
then sum(s.miniapp_order_cnt)
when grouping(d.year_week_name_cn,s.city_id ,s.trade_area_id ,s.store_id,s.brand_id ,s.max_class_id ,s.mid_class_id ,s.min_class_id ) = 31 and s.group_type = 'trade_area'
then sum(s.miniapp_order_cnt)
when grouping(d.year_week_name_cn,s.city_id ,s.trade_area_id ,s.store_id,s.brand_id ,s.max_class_id ,s.mid_class_id ,s.min_class_id ) = 63 and s.group_type = 'city'
then sum(s.miniapp_order_cnt)
when grouping(d.year_week_name_cn,s.city_id ,s.trade_area_id ,s.store_id,s.brand_id ,s.max_class_id ,s.mid_class_id ,s.min_class_id ) = 119 and s.group_type = 'brand'
then sum(s.miniapp_order_cnt)
when grouping(d.year_week_name_cn,s.city_id ,s.trade_area_id ,s.store_id,s.brand_id ,s.max_class_id ,s.mid_class_id ,s.min_class_id ) = 120 and s.group_type = 'min_class'
then sum(s.miniapp_order_cnt)
when grouping(d.year_week_name_cn,s.city_id ,s.trade_area_id ,s.store_id,s.brand_id ,s.max_class_id ,s.mid_class_id ,s.min_class_id ) = 121 and s.group_type = 'mid_class'
then sum(s.miniapp_order_cnt)
when grouping(d.year_week_name_cn,s.city_id ,s.trade_area_id ,s.store_id,s.brand_id ,s.max_class_id ,s.mid_class_id ,s.min_class_id ) = 123 and s.group_type = 'max_class'
then sum(s.miniapp_order_cnt)
when grouping(d.year_week_name_cn,s.city_id ,s.trade_area_id ,s.store_id,s.brand_id ,s.max_class_id ,s.mid_class_id ,s.min_class_id ) = 127 and s.group_type = 'all'
then sum(s.miniapp_order_cnt)
else null
end as miniapp_order_cnt,
case
when grouping(d.year_week_name_cn,s.city_id ,s.trade_area_id ,s.store_id,s.brand_id ,s.max_class_id ,s.mid_class_id ,s.min_class_id ) = 15 and s.group_type = 'store'
then sum(s.android_order_cnt)
when grouping(d.year_week_name_cn,s.city_id ,s.trade_area_id ,s.store_id,s.brand_id ,s.max_class_id ,s.mid_class_id ,s.min_class_id ) = 31 and s.group_type = 'trade_area'
then sum(s.android_order_cnt)
when grouping(d.year_week_name_cn,s.city_id ,s.trade_area_id ,s.store_id,s.brand_id ,s.max_class_id ,s.mid_class_id ,s.min_class_id ) = 63 and s.group_type = 'city'
then sum(s.android_order_cnt)
when grouping(d.year_week_name_cn,s.city_id ,s.trade_area_id ,s.store_id,s.brand_id ,s.max_class_id ,s.mid_class_id ,s.min_class_id ) = 119 and s.group_type = 'brand'
then sum(s.android_order_cnt)
when grouping(d.year_week_name_cn,s.city_id ,s.trade_area_id ,s.store_id,s.brand_id ,s.max_class_id ,s.mid_class_id ,s.min_class_id ) = 120 and s.group_type = 'min_class'
then sum(s.android_order_cnt)
when grouping(d.year_week_name_cn,s.city_id ,s.trade_area_id ,s.store_id,s.brand_id ,s.max_class_id ,s.mid_class_id ,s.min_class_id ) = 121 and s.group_type = 'mid_class'
then sum(s.android_order_cnt)
when grouping(d.year_week_name_cn,s.city_id ,s.trade_area_id ,s.store_id,s.brand_id ,s.max_class_id ,s.mid_class_id ,s.min_class_id ) = 123 and s.group_type = 'max_class'
then sum(s.android_order_cnt)
when grouping(d.year_week_name_cn,s.city_id ,s.trade_area_id ,s.store_id,s.brand_id ,s.max_class_id ,s.mid_class_id ,s.min_class_id ) = 127 and s.group_type = 'all'
then sum(s.android_order_cnt)
else null
end as android_order_cnt,
case
when grouping(d.year_week_name_cn,s.city_id ,s.trade_area_id ,s.store_id,s.brand_id ,s.max_class_id ,s.mid_class_id ,s.min_class_id ) = 15 and s.group_type = 'store'
then sum(s.ios_order_cnt)
when grouping(d.year_week_name_cn,s.city_id ,s.trade_area_id ,s.store_id,s.brand_id ,s.max_class_id ,s.mid_class_id ,s.min_class_id ) = 31 and s.group_type = 'trade_area'
then sum(s.ios_order_cnt)
when grouping(d.year_week_name_cn,s.city_id ,s.trade_area_id ,s.store_id,s.brand_id ,s.max_class_id ,s.mid_class_id ,s.min_class_id ) = 63 and s.group_type = 'city'
then sum(s.ios_order_cnt)
when grouping(d.year_week_name_cn,s.city_id ,s.trade_area_id ,s.store_id,s.brand_id ,s.max_class_id ,s.mid_class_id ,s.min_class_id ) = 119 and s.group_type = 'brand'
then sum(s.ios_order_cnt)
when grouping(d.year_week_name_cn,s.city_id ,s.trade_area_id ,s.store_id,s.brand_id ,s.max_class_id ,s.mid_class_id ,s.min_class_id ) = 120 and s.group_type = 'min_class'
then sum(s.ios_order_cnt)
when grouping(d.year_week_name_cn,s.city_id ,s.trade_area_id ,s.store_id,s.brand_id ,s.max_class_id ,s.mid_class_id ,s.min_class_id ) = 121 and s.group_type = 'mid_class'
then sum(s.ios_order_cnt)
when grouping(d.year_week_name_cn,s.city_id ,s.trade_area_id ,s.store_id,s.brand_id ,s.max_class_id ,s.mid_class_id ,s.min_class_id ) = 123 and s.group_type = 'max_class'
then sum(s.ios_order_cnt)
when grouping(d.year_week_name_cn,s.city_id ,s.trade_area_id ,s.store_id,s.brand_id ,s.max_class_id ,s.mid_class_id ,s.min_class_id ) = 127 and s.group_type = 'all'
then sum(s.ios_order_cnt)
else null
end as ios_order_cnt,
case
when grouping(d.year_week_name_cn,s.city_id ,s.trade_area_id ,s.store_id,s.brand_id ,s.max_class_id ,s.mid_class_id ,s.min_class_id ) = 15 and s.group_type = 'store'
then sum(s.pcweb_order_cnt)
when grouping(d.year_week_name_cn,s.city_id ,s.trade_area_id ,s.store_id,s.brand_id ,s.max_class_id ,s.mid_class_id ,s.min_class_id ) = 31 and s.group_type = 'trade_area'
then sum(s.pcweb_order_cnt)
when grouping(d.year_week_name_cn,s.city_id ,s.trade_area_id ,s.store_id,s.brand_id ,s.max_class_id ,s.mid_class_id ,s.min_class_id ) = 63 and s.group_type = 'city'
then sum(s.pcweb_order_cnt)
when grouping(d.year_week_name_cn,s.city_id ,s.trade_area_id ,s.store_id,s.brand_id ,s.max_class_id ,s.mid_class_id ,s.min_class_id ) = 119 and s.group_type = 'brand'
then sum(s.pcweb_order_cnt)
when grouping(d.year_week_name_cn,s.city_id ,s.trade_area_id ,s.store_id,s.brand_id ,s.max_class_id ,s.mid_class_id ,s.min_class_id ) = 120 and s.group_type = 'min_class'
then sum(s.pcweb_order_cnt)
when grouping(d.year_week_name_cn,s.city_id ,s.trade_area_id ,s.store_id,s.brand_id ,s.max_class_id ,s.mid_class_id ,s.min_class_id ) = 121 and s.group_type = 'mid_class'
then sum(s.pcweb_order_cnt)
when grouping(d.year_week_name_cn,s.city_id ,s.trade_area_id ,s.store_id,s.brand_id ,s.max_class_id ,s.mid_class_id ,s.min_class_id ) = 123 and s.group_type = 'max_class'
then sum(s.pcweb_order_cnt)
when grouping(d.year_week_name_cn,s.city_id ,s.trade_area_id ,s.store_id,s.brand_id ,s.max_class_id ,s.mid_class_id ,s.min_class_id ) = 127 and s.group_type = 'all'
then sum(s.pcweb_order_cnt)
else null
end as pcweb_order_cnt
from xxxxx.dws_sale_daycount s left join dim_date d on s.dt = d.date_code
group by
grouping sets(
-- 日期 + group_type
(d.year_code,d.year_week_name_cn,s.group_type) ,
-- 日期 + 城市 + group_type
(d.year_code,d.year_week_name_cn,s.city_id ,s.city_name ,s.group_type),
-- 日期 + 城市 + 商圈 + group_type
(d.year_code,d.year_week_name_cn,s.city_id ,s.city_name,s.trade_area_id ,s.trade_area_name ,s.group_type),
-- 日期 + 城市 + 商圈 + 店铺 + group_type
(d.year_code,d.year_week_name_cn,s.city_id ,s.city_name,s.trade_area_id ,s.trade_area_name ,s.store_id,s.store_name ,s.group_type),
-- 日期 + 品牌 + group_type
(d.year_code,d.year_week_name_cn,s.brand_id ,s.brand_name ,s.group_type) ,
-- 日期 + 大类 + group_type
(d.year_code,d.year_week_name_cn,s.max_class_id ,s.max_class_name ,s.group_type) ,
-- 日期 + 大类 + 中类 + group_type
(d.year_code,d.year_week_name_cn,s.max_class_id ,s.max_class_name ,s.mid_class_id ,s.mid_class_name, s.group_type),
-- 日期 + 大类 + 中类 + 小类 + group_type
(d.year_code,d.year_week_name_cn,s.max_class_id ,s.max_class_name ,s.mid_class_id ,s.mid_class_name,s.min_class_id ,s.min_class_name ,s.group_type)
)
)
select * from t1 where group_type is not null;
2.按月统计
insert into xxxxx_yp_dm_jiale.dm_sale
with dim_date as (
select
date_code,
year_code,
year_month,
month_code ,
day_month_num ,
dim_date_id ,
year_week_name_cn
from xxxxx_yp_dwd_jiale.dim_date
),
t1 as (
select
'2022-05-10' as date_time,
'month' as time_type,
d.year_code ,
d.year_month,
d.month_code,
null as day_month_num ,
null as dim_date_id,
null as year_week_name_cn ,
-- 处理维度:
case
when grouping(d.year_month,s.city_id ,s.trade_area_id ,s.store_id,s.brand_id ,s.max_class_id ,s.mid_class_id ,s.min_class_id ) = 15 and s.group_type = 'store'
then 'store'
when grouping(d.year_month,s.city_id ,s.trade_area_id ,s.store_id,s.brand_id ,s.max_class_id ,s.mid_class_id ,s.min_class_id ) = 31 and s.group_type = 'trade_area'
then 'trade_area'
when grouping(d.year_month,s.city_id ,s.trade_area_id ,s.store_id,s.brand_id ,s.max_class_id ,s.mid_class_id ,s.min_class_id ) = 63 and s.group_type = 'city'
then 'city'
when grouping(d.year_month,s.city_id ,s.trade_area_id ,s.store_id,s.brand_id ,s.max_class_id ,s.mid_class_id ,s.min_class_id ) = 119 and s.group_type = 'brand'
then 'brand'
when grouping(d.year_month,s.city_id ,s.trade_area_id ,s.store_id,s.brand_id ,s.max_class_id ,s.mid_class_id ,s.min_class_id ) = 120 and s.group_type = 'min_class'
then 'min_class'
when grouping(d.year_month,s.city_id ,s.trade_area_id ,s.store_id,s.brand_id ,s.max_class_id ,s.mid_class_id ,s.min_class_id ) = 121 and s.group_type = 'mid_class'
then 'mid_class'
when grouping(d.year_month,s.city_id ,s.trade_area_id ,s.store_id,s.brand_id ,s.max_class_id ,s.mid_class_id ,s.min_class_id ) = 123 and s.group_type = 'max_class'
then 'max_class'
when grouping(d.year_month,s.city_id ,s.trade_area_id ,s.store_id,s.brand_id ,s.max_class_id ,s.mid_class_id ,s.min_class_id ) = 127 and s.group_type = 'all'
then 'all'
else null
end as group_type,
s.city_id ,
s.city_name ,
s.trade_area_id ,
s.trade_area_name,
s.store_id,
s.store_name ,
s.brand_id,
s.brand_name,
s.max_class_id,
s.max_class_name,
s.mid_class_id,
s.mid_class_name,
s.min_class_id,
s.min_class_name,
-- 指标:
case
when grouping(d.year_month,s.city_id ,s.trade_area_id ,s.store_id,s.brand_id ,s.max_class_id ,s.mid_class_id ,s.min_class_id ) = 15 and s.group_type = 'store'
then sum(s.sale_amt)
when grouping(d.year_month,s.city_id ,s.trade_area_id ,s.store_id,s.brand_id ,s.max_class_id ,s.mid_class_id ,s.min_class_id ) = 31 and s.group_type = 'trade_area'
then sum(s.sale_amt)
when grouping(d.year_month,s.city_id ,s.trade_area_id ,s.store_id,s.brand_id ,s.max_class_id ,s.mid_class_id ,s.min_class_id ) = 63 and s.group_type = 'city'
then sum(s.sale_amt)
when grouping(d.year_month,s.city_id ,s.trade_area_id ,s.store_id,s.brand_id ,s.max_class_id ,s.mid_class_id ,s.min_class_id ) = 119 and s.group_type = 'brand'
then sum(s.sale_amt)
when grouping(d.year_month,s.city_id ,s.trade_area_id ,s.store_id,s.brand_id ,s.max_class_id ,s.mid_class_id ,s.min_class_id ) = 120 and s.group_type = 'min_class'
then sum(s.sale_amt)
when grouping(d.year_month,s.city_id ,s.trade_area_id ,s.store_id,s.brand_id ,s.max_class_id ,s.mid_class_id ,s.min_class_id ) = 121 and s.group_type = 'mid_class'
then sum(s.sale_amt)
when grouping(d.year_month,s.city_id ,s.trade_area_id ,s.store_id,s.brand_id ,s.max_class_id ,s.mid_class_id ,s.min_class_id ) = 123 and s.group_type = 'max_class'
then sum(s.sale_amt)
when grouping(d.year_month,s.city_id ,s.trade_area_id ,s.store_id,s.brand_id ,s.max_class_id ,s.mid_class_id ,s.min_class_id ) = 127 and s.group_type = 'all'
then sum(s.sale_amt)
else null
end as sale_amt,
case
when grouping(d.year_month,s.city_id ,s.trade_area_id ,s.store_id,s.brand_id ,s.max_class_id ,s.mid_class_id ,s.min_class_id ) = 15 and s.group_type = 'store'
then sum(s.plat_amt)
when grouping(d.year_month,s.city_id ,s.trade_area_id ,s.store_id,s.brand_id ,s.max_class_id ,s.mid_class_id ,s.min_class_id ) = 31 and s.group_type = 'trade_area'
then sum(s.plat_amt)
when grouping(d.year_month,s.city_id ,s.trade_area_id ,s.store_id,s.brand_id ,s.max_class_id ,s.mid_class_id ,s.min_class_id ) = 63 and s.group_type = 'city'
then sum(s.plat_amt)
when grouping(d.year_month,s.city_id ,s.trade_area_id ,s.store_id,s.brand_id ,s.max_class_id ,s.mid_class_id ,s.min_class_id ) = 119 and s.group_type = 'brand'
then sum(s.plat_amt)
when grouping(d.year_month,s.city_id ,s.trade_area_id ,s.store_id,s.brand_id ,s.max_class_id ,s.mid_class_id ,s.min_class_id ) = 120 and s.group_type = 'min_class'
then sum(s.plat_amt)
when grouping(d.year_month,s.city_id ,s.trade_area_id ,s.store_id,s.brand_id ,s.max_class_id ,s.mid_class_id ,s.min_class_id ) = 121 and s.group_type = 'mid_class'
then sum(s.plat_amt)
when grouping(d.year_month,s.city_id ,s.trade_area_id ,s.store_id,s.brand_id ,s.max_class_id ,s.mid_class_id ,s.min_class_id ) = 123 and s.group_type = 'max_class'
then sum(s.plat_amt)
when grouping(d.year_month,s.city_id ,s.trade_area_id ,s.store_id,s.brand_id ,s.max_class_id ,s.mid_class_id ,s.min_class_id ) = 127 and s.group_type = 'all'
then sum(s.plat_amt)
else null
end as plat_amt,
case
when grouping(d.year_month,s.city_id ,s.trade_area_id ,s.store_id,s.brand_id ,s.max_class_id ,s.mid_class_id ,s.min_class_id ) = 15 and s.group_type = 'store'
then sum(s.deliver_sale_amt)
when grouping(d.year_month,s.city_id ,s.trade_area_id ,s.store_id,s.brand_id ,s.max_class_id ,s.mid_class_id ,s.min_class_id ) = 31 and s.group_type = 'trade_area'
then sum(s.deliver_sale_amt)
when grouping(d.year_month,s.city_id ,s.trade_area_id ,s.store_id,s.brand_id ,s.max_class_id ,s.mid_class_id ,s.min_class_id ) = 63 and s.group_type = 'city'
then sum(s.deliver_sale_amt)
when grouping(d.year_month,s.city_id ,s.trade_area_id ,s.store_id,s.brand_id ,s.max_class_id ,s.mid_class_id ,s.min_class_id ) = 119 and s.group_type = 'brand'
then sum(s.deliver_sale_amt)
when grouping(d.year_month,s.city_id ,s.trade_area_id ,s.store_id,s.brand_id ,s.max_class_id ,s.mid_class_id ,s.min_class_id ) = 120 and s.group_type = 'min_class'
then sum(s.deliver_sale_amt)
when grouping(d.year_month,s.city_id ,s.trade_area_id ,s.store_id,s.brand_id ,s.max_class_id ,s.mid_class_id ,s.min_class_id ) = 121 and s.group_type = 'mid_class'
then sum(s.deliver_sale_amt)
when grouping(d.year_month,s.city_id ,s.trade_area_id ,s.store_id,s.brand_id ,s.max_class_id ,s.mid_class_id ,s.min_class_id ) = 123 and s.group_type = 'max_class'
then sum(s.deliver_sale_amt)
when grouping(d.year_month,s.city_id ,s.trade_area_id ,s.store_id,s.brand_id ,s.max_class_id ,s.mid_class_id ,s.min_class_id ) = 127 and s.group_type = 'all'
then sum(s.deliver_sale_amt)
else null
end as deliver_sale_amt,
case
when grouping(d.year_month,s.city_id ,s.trade_area_id ,s.store_id,s.brand_id ,s.max_class_id ,s.mid_class_id ,s.min_class_id ) = 15 and s.group_type = 'store'
then sum(s.mini_app_sale_amt)
when grouping(d.year_month,s.city_id ,s.trade_area_id ,s.store_id,s.brand_id ,s.max_class_id ,s.mid_class_id ,s.min_class_id ) = 31 and s.group_type = 'trade_area'
then sum(s.mini_app_sale_amt)
when grouping(d.year_month,s.city_id ,s.trade_area_id ,s.store_id,s.brand_id ,s.max_class_id ,s.mid_class_id ,s.min_class_id ) = 63 and s.group_type = 'city'
then sum(s.mini_app_sale_amt)
when grouping(d.year_month,s.city_id ,s.trade_area_id ,s.store_id,s.brand_id ,s.max_class_id ,s.mid_class_id ,s.min_class_id ) = 119 and s.group_type = 'brand'
then sum(s.mini_app_sale_amt)
when grouping(d.year_month,s.city_id ,s.trade_area_id ,s.store_id,s.brand_id ,s.max_class_id ,s.mid_class_id ,s.min_class_id ) = 120 and s.group_type = 'min_class'
then sum(s.mini_app_sale_amt)
when grouping(d.year_month,s.city_id ,s.trade_area_id ,s.store_id,s.brand_id ,s.max_class_id ,s.mid_class_id ,s.min_class_id ) = 121 and s.group_type = 'mid_class'
then sum(s.mini_app_sale_amt)
when grouping(d.year_month,s.city_id ,s.trade_area_id ,s.store_id,s.brand_id ,s.max_class_id ,s.mid_class_id ,s.min_class_id ) = 123 and s.group_type = 'max_class'
then sum(s.mini_app_sale_amt)
when grouping(d.year_month,s.city_id ,s.trade_area_id ,s.store_id,s.brand_id ,s.max_class_id ,s.mid_class_id ,s.min_class_id ) = 127 and s.group_type = 'all'
then sum(s.mini_app_sale_amt)
else null
end as mini_app_sale_amt,
case
when grouping(d.year_month,s.city_id ,s.trade_area_id ,s.store_id,s.brand_id ,s.max_class_id ,s.mid_class_id ,s.min_class_id ) = 15 and s.group_type = 'store'
then sum(s.android_sale_amt)
when grouping(d.year_month,s.city_id ,s.trade_area_id ,s.store_id,s.brand_id ,s.max_class_id ,s.mid_class_id ,s.min_class_id ) = 31 and s.group_type = 'trade_area'
then sum(s.android_sale_amt)
when grouping(d.year_month,s.city_id ,s.trade_area_id ,s.store_id,s.brand_id ,s.max_class_id ,s.mid_class_id ,s.min_class_id ) = 63 and s.group_type = 'city'
then sum(s.android_sale_amt)
when grouping(d.year_month,s.city_id ,s.trade_area_id ,s.store_id,s.brand_id ,s.max_class_id ,s.mid_class_id ,s.min_class_id ) = 119 and s.group_type = 'brand'
then sum(s.android_sale_amt)
when grouping(d.year_month,s.city_id ,s.trade_area_id ,s.store_id,s.brand_id ,s.max_class_id ,s.mid_class_id ,s.min_class_id ) = 120 and s.group_type = 'min_class'
then sum(s.android_sale_amt)
when grouping(d.year_month,s.city_id ,s.trade_area_id ,s.store_id,s.brand_id ,s.max_class_id ,s.mid_class_id ,s.min_class_id ) = 121 and s.group_type = 'mid_class'
then sum(s.android_sale_amt)
when grouping(d.year_month,s.city_id ,s.trade_area_id ,s.store_id,s.brand_id ,s.max_class_id ,s.mid_class_id ,s.min_class_id ) = 123 and s.group_type = 'max_class'
then sum(s.android_sale_amt)
when grouping(d.year_month,s.city_id ,s.trade_area_id ,s.store_id,s.brand_id ,s.max_class_id ,s.mid_class_id ,s.min_class_id ) = 127 and s.group_type = 'all'
then sum(s.android_sale_amt)
else null
end as android_sale_amt,
case
when grouping(d.year_month,s.city_id ,s.trade_area_id ,s.store_id,s.brand_id ,s.max_class_id ,s.mid_class_id ,s.min_class_id ) = 15 and s.group_type = 'store'
then sum(s.ios_sale_amt)
when grouping(d.year_month,s.city_id ,s.trade_area_id ,s.store_id,s.brand_id ,s.max_class_id ,s.mid_class_id ,s.min_class_id ) = 31 and s.group_type = 'trade_area'
then sum(s.ios_sale_amt)
when grouping(d.year_month,s.city_id ,s.trade_area_id ,s.store_id,s.brand_id ,s.max_class_id ,s.mid_class_id ,s.min_class_id ) = 63 and s.group_type = 'city'
then sum(s.ios_sale_amt)
when grouping(d.year_month,s.city_id ,s.trade_area_id ,s.store_id,s.brand_id ,s.max_class_id ,s.mid_class_id ,s.min_class_id ) = 119 and s.group_type = 'brand'
then sum(s.ios_sale_amt)
when grouping(d.year_month,s.city_id ,s.trade_area_id ,s.store_id,s.brand_id ,s.max_class_id ,s.mid_class_id ,s.min_class_id ) = 120 and s.group_type = 'min_class'
then sum(s.ios_sale_amt)
when grouping(d.year_month,s.city_id ,s.trade_area_id ,s.store_id,s.brand_id ,s.max_class_id ,s.mid_class_id ,s.min_class_id ) = 121 and s.group_type = 'mid_class'
then sum(s.ios_sale_amt)
when grouping(d.year_month,s.city_id ,s.trade_area_id ,s.store_id,s.brand_id ,s.max_class_id ,s.mid_class_id ,s.min_class_id ) = 123 and s.group_type = 'max_class'
then sum(s.ios_sale_amt)
when grouping(d.year_month,s.city_id ,s.trade_area_id ,s.store_id,s.brand_id ,s.max_class_id ,s.mid_class_id ,s.min_class_id ) = 127 and s.group_type = 'all'
then sum(s.ios_sale_amt)
else null
end as ios_sale_amt,
case
when grouping(d.year_month,s.city_id ,s.trade_area_id ,s.store_id,s.brand_id ,s.max_class_id ,s.mid_class_id ,s.min_class_id ) = 15 and s.group_type = 'store'
then sum(s.pcweb_sale_amt)
when grouping(d.year_month,s.city_id ,s.trade_area_id ,s.store_id,s.brand_id ,s.max_class_id ,s.mid_class_id ,s.min_class_id ) = 31 and s.group_type = 'trade_area'
then sum(s.pcweb_sale_amt)
when grouping(d.year_month,s.city_id ,s.trade_area_id ,s.store_id,s.brand_id ,s.max_class_id ,s.mid_class_id ,s.min_class_id ) = 63 and s.group_type = 'city'
then sum(s.pcweb_sale_amt)
when grouping(d.year_month,s.city_id ,s.trade_area_id ,s.store_id,s.brand_id ,s.max_class_id ,s.mid_class_id ,s.min_class_id ) = 119 and s.group_type = 'brand'
then sum(s.pcweb_sale_amt)
when grouping(d.year_month,s.city_id ,s.trade_area_id ,s.store_id,s.brand_id ,s.max_class_id ,s.mid_class_id ,s.min_class_id ) = 120 and s.group_type = 'min_class'
then sum(s.pcweb_sale_amt)
when grouping(d.year_month,s.city_id ,s.trade_area_id ,s.store_id,s.brand_id ,s.max_class_id ,s.mid_class_id ,s.min_class_id ) = 121 and s.group_type = 'mid_class'
then sum(s.pcweb_sale_amt)
when grouping(d.year_month,s.city_id ,s.trade_area_id ,s.store_id,s.brand_id ,s.max_class_id ,s.mid_class_id ,s.min_class_id ) = 123 and s.group_type = 'max_class'
then sum(s.pcweb_sale_amt)
when grouping(d.year_month,s.city_id ,s.trade_area_id ,s.store_id,s.brand_id ,s.max_class_id ,s.mid_class_id ,s.min_class_id ) = 127 and s.group_type = 'all'
then sum(s.pcweb_sale_amt)
else null
end as pcweb_sale_amt,
case
when grouping(d.year_month,s.city_id ,s.trade_area_id ,s.store_id,s.brand_id ,s.max_class_id ,s.mid_class_id ,s.min_class_id ) = 15 and s.group_type = 'store'
then sum(s.order_cnt)
when grouping(d.year_month,s.city_id ,s.trade_area_id ,s.store_id,s.brand_id ,s.max_class_id ,s.mid_class_id ,s.min_class_id ) = 31 and s.group_type = 'trade_area'
then sum(s.order_cnt)
when grouping(d.year_month,s.city_id ,s.trade_area_id ,s.store_id,s.brand_id ,s.max_class_id ,s.mid_class_id ,s.min_class_id ) = 63 and s.group_type = 'city'
then sum(s.order_cnt)
when grouping(d.year_month,s.city_id ,s.trade_area_id ,s.store_id,s.brand_id ,s.max_class_id ,s.mid_class_id ,s.min_class_id ) = 119 and s.group_type = 'brand'
then sum(s.order_cnt)
when grouping(d.year_month,s.city_id ,s.trade_area_id ,s.store_id,s.brand_id ,s.max_class_id ,s.mid_class_id ,s.min_class_id ) = 120 and s.group_type = 'min_class'
then sum(s.order_cnt)
when grouping(d.year_month,s.city_id ,s.trade_area_id ,s.store_id,s.brand_id ,s.max_class_id ,s.mid_class_id ,s.min_class_id ) = 121 and s.group_type = 'mid_class'
then sum(s.order_cnt)
when grouping(d.year_month,s.city_id ,s.trade_area_id ,s.store_id,s.brand_id ,s.max_class_id ,s.mid_class_id ,s.min_class_id ) = 123 and s.group_type = 'max_class'
then sum(s.order_cnt)
when grouping(d.year_month,s.city_id ,s.trade_area_id ,s.store_id,s.brand_id ,s.max_class_id ,s.mid_class_id ,s.min_class_id ) = 127 and s.group_type = 'all'
then sum(s.order_cnt)
else null
end as order_cnt,
case
when grouping(d.year_month,s.city_id ,s.trade_area_id ,s.store_id,s.brand_id ,s.max_class_id ,s.mid_class_id ,s.min_class_id ) = 15 and s.group_type = 'store'
then sum(s.eva_order_cnt)
when grouping(d.year_month,s.city_id ,s.trade_area_id ,s.store_id,s.brand_id ,s.max_class_id ,s.mid_class_id ,s.min_class_id ) = 31 and s.group_type = 'trade_area'
then sum(s.eva_order_cnt)
when grouping(d.year_month,s.city_id ,s.trade_area_id ,s.store_id,s.brand_id ,s.max_class_id ,s.mid_class_id ,s.min_class_id ) = 63 and s.group_type = 'city'
then sum(s.eva_order_cnt)
when grouping(d.year_month,s.city_id ,s.trade_area_id ,s.store_id,s.brand_id ,s.max_class_id ,s.mid_class_id ,s.min_class_id ) = 119 and s.group_type = 'brand'
then sum(s.eva_order_cnt)
when grouping(d.year_month,s.city_id ,s.trade_area_id ,s.store_id,s.brand_id ,s.max_class_id ,s.mid_class_id ,s.min_class_id ) = 120 and s.group_type = 'min_class'
then sum(s.eva_order_cnt)
when grouping(d.year_month,s.city_id ,s.trade_area_id ,s.store_id,s.brand_id ,s.max_class_id ,s.mid_class_id ,s.min_class_id ) = 121 and s.group_type = 'mid_class'
then sum(s.eva_order_cnt)
when grouping(d.year_month,s.city_id ,s.trade_area_id ,s.store_id,s.brand_id ,s.max_class_id ,s.mid_class_id ,s.min_class_id ) = 123 and s.group_type = 'max_class'
then sum(s.eva_order_cnt)
when grouping(d.year_month,s.city_id ,s.trade_area_id ,s.store_id,s.brand_id ,s.max_class_id ,s.mid_class_id ,s.min_class_id ) = 127 and s.group_type = 'all'
then sum(s.eva_order_cnt)
else null
end as eva_order_cnt,
case
when grouping(d.year_month,s.city_id ,s.trade_area_id ,s.store_id,s.brand_id ,s.max_class_id ,s.mid_class_id ,s.min_class_id ) = 15 and s.group_type = 'store'
then sum(s.bad_eva_order_cnt)
when grouping(d.year_month,s.city_id ,s.trade_area_id ,s.store_id,s.brand_id ,s.max_class_id ,s.mid_class_id ,s.min_class_id ) = 31 and s.group_type = 'trade_area'
then sum(s.bad_eva_order_cnt)
when grouping(d.year_month,s.city_id ,s.trade_area_id ,s.store_id,s.brand_id ,s.max_class_id ,s.mid_class_id ,s.min_class_id ) = 63 and s.group_type = 'city'
then sum(s.bad_eva_order_cnt)
when grouping(d.year_month,s.city_id ,s.trade_area_id ,s.store_id,s.brand_id ,s.max_class_id ,s.mid_class_id ,s.min_class_id ) = 119 and s.group_type = 'brand'
then sum(s.bad_eva_order_cnt)
when grouping(d.year_month,s.city_id ,s.trade_area_id ,s.store_id,s.brand_id ,s.max_class_id ,s.mid_class_id ,s.min_class_id ) = 120 and s.group_type = 'min_class'
then sum(s.bad_eva_order_cnt)
when grouping(d.year_month,s.city_id ,s.trade_area_id ,s.store_id,s.brand_id ,s.max_class_id ,s.mid_class_id ,s.min_class_id ) = 121 and s.group_type = 'mid_class'
then sum(s.bad_eva_order_cnt)
when grouping(d.year_month,s.city_id ,s.trade_area_id ,s.store_id,s.brand_id ,s.max_class_id ,s.mid_class_id ,s.min_class_id ) = 123 and s.group_type = 'max_class'
then sum(s.bad_eva_order_cnt)
when grouping(d.year_month,s.city_id ,s.trade_area_id ,s.store_id,s.brand_id ,s.max_class_id ,s.mid_class_id ,s.min_class_id ) = 127 and s.group_type = 'all'
then sum(s.bad_eva_order_cnt)
else null
end as bad_eva_order_cnt,
case
when grouping(d.year_month,s.city_id ,s.trade_area_id ,s.store_id,s.brand_id ,s.max_class_id ,s.mid_class_id ,s.min_class_id ) = 15 and s.group_type = 'store'
then sum(s.deliver_order_cnt)
when grouping(d.year_month,s.city_id ,s.trade_area_id ,s.store_id,s.brand_id ,s.max_class_id ,s.mid_class_id ,s.min_class_id ) = 31 and s.group_type = 'trade_area'
then sum(s.deliver_order_cnt)
when grouping(d.year_month,s.city_id ,s.trade_area_id ,s.store_id,s.brand_id ,s.max_class_id ,s.mid_class_id ,s.min_class_id ) = 63 and s.group_type = 'city'
then sum(s.deliver_order_cnt)
when grouping(d.year_month,s.city_id ,s.trade_area_id ,s.store_id,s.brand_id ,s.max_class_id ,s.mid_class_id ,s.min_class_id ) = 119 and s.group_type = 'brand'
then sum(s.deliver_order_cnt)
when grouping(d.year_month,s.city_id ,s.trade_area_id ,s.store_id,s.brand_id ,s.max_class_id ,s.mid_class_id ,s.min_class_id ) = 120 and s.group_type = 'min_class'
then sum(s.deliver_order_cnt)
when grouping(d.year_month,s.city_id ,s.trade_area_id ,s.store_id,s.brand_id ,s.max_class_id ,s.mid_class_id ,s.min_class_id ) = 121 and s.group_type = 'mid_class'
then sum(s.deliver_order_cnt)
when grouping(d.year_month,s.city_id ,s.trade_area_id ,s.store_id,s.brand_id ,s.max_class_id ,s.mid_class_id ,s.min_class_id ) = 123 and s.group_type = 'max_class'
then sum(s.deliver_order_cnt)
when grouping(d.year_month,s.city_id ,s.trade_area_id ,s.store_id,s.brand_id ,s.max_class_id ,s.mid_class_id ,s.min_class_id ) = 127 and s.group_type = 'all'
then sum(s.deliver_order_cnt)
else null
end as deliver_order_cnt,
case
when grouping(d.year_month,s.city_id ,s.trade_area_id ,s.store_id,s.brand_id ,s.max_class_id ,s.mid_class_id ,s.min_class_id ) = 15 and s.group_type = 'store'
then sum(s.refund_order_cnt)
when grouping(d.year_month,s.city_id ,s.trade_area_id ,s.store_id,s.brand_id ,s.max_class_id ,s.mid_class_id ,s.min_class_id ) = 31 and s.group_type = 'trade_area'
then sum(s.refund_order_cnt)
when grouping(d.year_month,s.city_id ,s.trade_area_id ,s.store_id,s.brand_id ,s.max_class_id ,s.mid_class_id ,s.min_class_id ) = 63 and s.group_type = 'city'
then sum(s.refund_order_cnt)
when grouping(d.year_month,s.city_id ,s.trade_area_id ,s.store_id,s.brand_id ,s.max_class_id ,s.mid_class_id ,s.min_class_id ) = 119 and s.group_type = 'brand'
then sum(s.refund_order_cnt)
when grouping(d.year_month,s.city_id ,s.trade_area_id ,s.store_id,s.brand_id ,s.max_class_id ,s.mid_class_id ,s.min_class_id ) = 120 and s.group_type = 'min_class'
then sum(s.refund_order_cnt)
when grouping(d.year_month,s.city_id ,s.trade_area_id ,s.store_id,s.brand_id ,s.max_class_id ,s.mid_class_id ,s.min_class_id ) = 121 and s.group_type = 'mid_class'
then sum(s.refund_order_cnt)
when grouping(d.year_month,s.city_id ,s.trade_area_id ,s.store_id,s.brand_id ,s.max_class_id ,s.mid_class_id ,s.min_class_id ) = 123 and s.group_type = 'max_class'
then sum(s.refund_order_cnt)
when grouping(d.year_month,s.city_id ,s.trade_area_id ,s.store_id,s.brand_id ,s.max_class_id ,s.mid_class_id ,s.min_class_id ) = 127 and s.group_type = 'all'
then sum(s.refund_order_cnt)
else null
end as refund_order_cnt,
case
when grouping(d.year_month,s.city_id ,s.trade_area_id ,s.store_id,s.brand_id ,s.max_class_id ,s.mid_class_id ,s.min_class_id ) = 15 and s.group_type = 'store'
then sum(s.miniapp_order_cnt)
when grouping(d.year_month,s.city_id ,s.trade_area_id ,s.store_id,s.brand_id ,s.max_class_id ,s.mid_class_id ,s.min_class_id ) = 31 and s.group_type = 'trade_area'
then sum(s.miniapp_order_cnt)
when grouping(d.year_month,s.city_id ,s.trade_area_id ,s.store_id,s.brand_id ,s.max_class_id ,s.mid_class_id ,s.min_class_id ) = 63 and s.group_type = 'city'
then sum(s.miniapp_order_cnt)
when grouping(d.year_month,s.city_id ,s.trade_area_id ,s.store_id,s.brand_id ,s.max_class_id ,s.mid_class_id ,s.min_class_id ) = 119 and s.group_type = 'brand'
then sum(s.miniapp_order_cnt)
when grouping(d.year_month,s.city_id ,s.trade_area_id ,s.store_id,s.brand_id ,s.max_class_id ,s.mid_class_id ,s.min_class_id ) = 120 and s.group_type = 'min_class'
then sum(s.miniapp_order_cnt)
when grouping(d.year_month,s.city_id ,s.trade_area_id ,s.store_id,s.brand_id ,s.max_class_id ,s.mid_class_id ,s.min_class_id ) = 121 and s.group_type = 'mid_class'
then sum(s.miniapp_order_cnt)
when grouping(d.year_month,s.city_id ,s.trade_area_id ,s.store_id,s.brand_id ,s.max_class_id ,s.mid_class_id ,s.min_class_id ) = 123 and s.group_type = 'max_class'
then sum(s.miniapp_order_cnt)
when grouping(d.year_month,s.city_id ,s.trade_area_id ,s.store_id,s.brand_id ,s.max_class_id ,s.mid_class_id ,s.min_class_id ) = 127 and s.group_type = 'all'
then sum(s.miniapp_order_cnt)
else null
end as miniapp_order_cnt,
case
when grouping(d.year_month,s.city_id ,s.trade_area_id ,s.store_id,s.brand_id ,s.max_class_id ,s.mid_class_id ,s.min_class_id ) = 15 and s.group_type = 'store'
then sum(s.android_order_cnt)
when grouping(d.year_month,s.city_id ,s.trade_area_id ,s.store_id,s.brand_id ,s.max_class_id ,s.mid_class_id ,s.min_class_id ) = 31 and s.group_type = 'trade_area'
then sum(s.android_order_cnt)
when grouping(d.year_month,s.city_id ,s.trade_area_id ,s.store_id,s.brand_id ,s.max_class_id ,s.mid_class_id ,s.min_class_id ) = 63 and s.group_type = 'city'
then sum(s.android_order_cnt)
when grouping(d.year_month,s.city_id ,s.trade_area_id ,s.store_id,s.brand_id ,s.max_class_id ,s.mid_class_id ,s.min_class_id ) = 119 and s.group_type = 'brand'
then sum(s.android_order_cnt)
when grouping(d.year_month,s.city_id ,s.trade_area_id ,s.store_id,s.brand_id ,s.max_class_id ,s.mid_class_id ,s.min_class_id ) = 120 and s.group_type = 'min_class'
then sum(s.android_order_cnt)
when grouping(d.year_month,s.city_id ,s.trade_area_id ,s.store_id,s.brand_id ,s.max_class_id ,s.mid_class_id ,s.min_class_id ) = 121 and s.group_type = 'mid_class'
then sum(s.android_order_cnt)
when grouping(d.year_month,s.city_id ,s.trade_area_id ,s.store_id,s.brand_id ,s.max_class_id ,s.mid_class_id ,s.min_class_id ) = 123 and s.group_type = 'max_class'
then sum(s.android_order_cnt)
when grouping(d.year_month,s.city_id ,s.trade_area_id ,s.store_id,s.brand_id ,s.max_class_id ,s.mid_class_id ,s.min_class_id ) = 127 and s.group_type = 'all'
then sum(s.android_order_cnt)
else null
end as android_order_cnt,
case
when grouping(d.year_month,s.city_id ,s.trade_area_id ,s.store_id,s.brand_id ,s.max_class_id ,s.mid_class_id ,s.min_class_id ) = 15 and s.group_type = 'store'
then sum(s.ios_order_cnt)
when grouping(d.year_month,s.city_id ,s.trade_area_id ,s.store_id,s.brand_id ,s.max_class_id ,s.mid_class_id ,s.min_class_id ) = 31 and s.group_type = 'trade_area'
then sum(s.ios_order_cnt)
when grouping(d.year_month,s.city_id ,s.trade_area_id ,s.store_id,s.brand_id ,s.max_class_id ,s.mid_class_id ,s.min_class_id ) = 63 and s.group_type = 'city'
then sum(s.ios_order_cnt)
when grouping(d.year_month,s.city_id ,s.trade_area_id ,s.store_id,s.brand_id ,s.max_class_id ,s.mid_class_id ,s.min_class_id ) = 119 and s.group_type = 'brand'
then sum(s.ios_order_cnt)
when grouping(d.year_month,s.city_id ,s.trade_area_id ,s.store_id,s.brand_id ,s.max_class_id ,s.mid_class_id ,s.min_class_id ) = 120 and s.group_type = 'min_class'
then sum(s.ios_order_cnt)
when grouping(d.year_month,s.city_id ,s.trade_area_id ,s.store_id,s.brand_id ,s.max_class_id ,s.mid_class_id ,s.min_class_id ) = 121 and s.group_type = 'mid_class'
then sum(s.ios_order_cnt)
when grouping(d.year_month,s.city_id ,s.trade_area_id ,s.store_id,s.brand_id ,s.max_class_id ,s.mid_class_id ,s.min_class_id ) = 123 and s.group_type = 'max_class'
then sum(s.ios_order_cnt)
when grouping(d.year_month,s.city_id ,s.trade_area_id ,s.store_id,s.brand_id ,s.max_class_id ,s.mid_class_id ,s.min_class_id ) = 127 and s.group_type = 'all'
then sum(s.ios_order_cnt)
else null
end as ios_order_cnt,
case
when grouping(d.year_month,s.city_id ,s.trade_area_id ,s.store_id,s.brand_id ,s.max_class_id ,s.mid_class_id ,s.min_class_id ) = 15 and s.group_type = 'store'
then sum(s.pcweb_order_cnt)
when grouping(d.year_month,s.city_id ,s.trade_area_id ,s.store_id,s.brand_id ,s.max_class_id ,s.mid_class_id ,s.min_class_id ) = 31 and s.group_type = 'trade_area'
then sum(s.pcweb_order_cnt)
when grouping(d.year_month,s.city_id ,s.trade_area_id ,s.store_id,s.brand_id ,s.max_class_id ,s.mid_class_id ,s.min_class_id ) = 63 and s.group_type = 'city'
then sum(s.pcweb_order_cnt)
when grouping(d.year_month,s.city_id ,s.trade_area_id ,s.store_id,s.brand_id ,s.max_class_id ,s.mid_class_id ,s.min_class_id ) = 119 and s.group_type = 'brand'
then sum(s.pcweb_order_cnt)
when grouping(d.year_month,s.city_id ,s.trade_area_id ,s.store_id,s.brand_id ,s.max_class_id ,s.mid_class_id ,s.min_class_id ) = 120 and s.group_type = 'min_class'
then sum(s.pcweb_order_cnt)
when grouping(d.year_month,s.city_id ,s.trade_area_id ,s.store_id,s.brand_id ,s.max_class_id ,s.mid_class_id ,s.min_class_id ) = 121 and s.group_type = 'mid_class'
then sum(s.pcweb_order_cnt)
when grouping(d.year_month,s.city_id ,s.trade_area_id ,s.store_id,s.brand_id ,s.max_class_id ,s.mid_class_id ,s.min_class_id ) = 123 and s.group_type = 'max_class'
then sum(s.pcweb_order_cnt)
when grouping(d.year_month,s.city_id ,s.trade_area_id ,s.store_id,s.brand_id ,s.max_class_id ,s.mid_class_id ,s.min_class_id ) = 127 and s.group_type = 'all'
then sum(s.pcweb_order_cnt)
else null
end as pcweb_order_cnt
from xxxxx.dws_sale_daycount s left join dim_date d on s.dt = d.date_code
group by
grouping sets(
-- 日期 + group_type
(d.year_code,d.year_month,d.month_code,s.group_type) ,
-- 日期 + 城市 + group_type
(d.year_code,d.year_month,d.month_code,s.city_id ,s.city_name ,s.group_type),
-- 日期 + 城市 + 商圈 + group_type
(d.year_code,d.year_month,d.month_code,s.city_id ,s.city_name,s.trade_area_id ,s.trade_area_name ,s.group_type),
-- 日期 + 城市 + 商圈 + 店铺 + group_type
(d.year_code,d.year_month,d.month_code,s.city_id ,s.city_name,s.trade_area_id ,s.trade_area_name ,s.store_id,s.store_name ,s.group_type),
-- 日期 + 品牌 + group_type
(d.year_code,d.year_month,d.month_code,s.brand_id ,s.brand_name ,s.group_type) ,
-- 日期 + 大类 + group_type
(d.year_code,d.year_month,d.month_code,s.max_class_id ,s.max_class_name ,s.group_type) ,
-- 日期 + 大类 + 中类 + group_type
(d.year_code,d.year_month,d.month_code,s.max_class_id ,s.max_class_name ,s.mid_class_id ,s.mid_class_name, s.group_type),
-- 日期 + 大类 + 中类 + 小类 + group_type
(d.year_code,d.year_month,d.month_code,s.max_class_id ,s.max_class_name ,s.mid_class_id ,s.mid_class_name,s.min_class_id ,s.min_class_name ,s.group_type)
)
)
select * from t1 where group_type is not null;
3.按年来统计
insert into xxxxx_yp_dm_jiale.dm_sale
with dim_date as (
select
date_code,
year_code,
year_month,
month_code ,
day_month_num ,
dim_date_id ,
year_week_name_cn
from xxxxx_yp_dwd_jiale.dim_date
),
t1 as (
select
'2022-05-10' as date_time,
'year' as time_type,
d.year_code ,
null as year_month,
null as month_code,
null as day_month_num ,
null as dim_date_id,
null as year_week_name_cn ,
-- 处理维度:
case
when grouping(d.year_code,s.city_id ,s.trade_area_id ,s.store_id,s.brand_id ,s.max_class_id ,s.mid_class_id ,s.min_class_id ) = 15 and s.group_type = 'store'
then 'store'
when grouping(d.year_code,s.city_id ,s.trade_area_id ,s.store_id,s.brand_id ,s.max_class_id ,s.mid_class_id ,s.min_class_id ) = 31 and s.group_type = 'trade_area'
then 'trade_area'
when grouping(d.year_code,s.city_id ,s.trade_area_id ,s.store_id,s.brand_id ,s.max_class_id ,s.mid_class_id ,s.min_class_id ) = 63 and s.group_type = 'city'
then 'city'
when grouping(d.year_code,s.city_id ,s.trade_area_id ,s.store_id,s.brand_id ,s.max_class_id ,s.mid_class_id ,s.min_class_id ) = 119 and s.group_type = 'brand'
then 'brand'
when grouping(d.year_code,s.city_id ,s.trade_area_id ,s.store_id,s.brand_id ,s.max_class_id ,s.mid_class_id ,s.min_class_id ) = 120 and s.group_type = 'min_class'
then 'min_class'
when grouping(d.year_code,s.city_id ,s.trade_area_id ,s.store_id,s.brand_id ,s.max_class_id ,s.mid_class_id ,s.min_class_id ) = 121 and s.group_type = 'mid_class'
then 'mid_class'
when grouping(d.year_code,s.city_id ,s.trade_area_id ,s.store_id,s.brand_id ,s.max_class_id ,s.mid_class_id ,s.min_class_id ) = 123 and s.group_type = 'max_class'
then 'max_class'
when grouping(d.year_code,s.city_id ,s.trade_area_id ,s.store_id,s.brand_id ,s.max_class_id ,s.mid_class_id ,s.min_class_id ) = 127 and s.group_type = 'all'
then 'all'
else null
end as group_type,
s.city_id ,
s.city_name ,
s.trade_area_id ,
s.trade_area_name,
s.store_id,
s.store_name ,
s.brand_id,
s.brand_name,
s.max_class_id,
s.max_class_name,
s.mid_class_id,
s.mid_class_name,
s.min_class_id,
s.min_class_name,
-- 指标:
case
when grouping(d.year_code,s.city_id ,s.trade_area_id ,s.store_id,s.brand_id ,s.max_class_id ,s.mid_class_id ,s.min_class_id ) = 15 and s.group_type = 'store'
then sum(s.sale_amt)
when grouping(d.year_code,s.city_id ,s.trade_area_id ,s.store_id,s.brand_id ,s.max_class_id ,s.mid_class_id ,s.min_class_id ) = 31 and s.group_type = 'trade_area'
then sum(s.sale_amt)
when grouping(d.year_code,s.city_id ,s.trade_area_id ,s.store_id,s.brand_id ,s.max_class_id ,s.mid_class_id ,s.min_class_id ) = 63 and s.group_type = 'city'
then sum(s.sale_amt)
when grouping(d.year_code,s.city_id ,s.trade_area_id ,s.store_id,s.brand_id ,s.max_class_id ,s.mid_class_id ,s.min_class_id ) = 119 and s.group_type = 'brand'
then sum(s.sale_amt)
when grouping(d.year_code,s.city_id ,s.trade_area_id ,s.store_id,s.brand_id ,s.max_class_id ,s.mid_class_id ,s.min_class_id ) = 120 and s.group_type = 'min_class'
then sum(s.sale_amt)
when grouping(d.year_code,s.city_id ,s.trade_area_id ,s.store_id,s.brand_id ,s.max_class_id ,s.mid_class_id ,s.min_class_id ) = 121 and s.group_type = 'mid_class'
then sum(s.sale_amt)
when grouping(d.year_code,s.city_id ,s.trade_area_id ,s.store_id,s.brand_id ,s.max_class_id ,s.mid_class_id ,s.min_class_id ) = 123 and s.group_type = 'max_class'
then sum(s.sale_amt)
when grouping(d.year_code,s.city_id ,s.trade_area_id ,s.store_id,s.brand_id ,s.max_class_id ,s.mid_class_id ,s.min_class_id ) = 127 and s.group_type = 'all'
then sum(s.sale_amt)
else null
end as sale_amt,
case
when grouping(d.year_code,s.city_id ,s.trade_area_id ,s.store_id,s.brand_id ,s.max_class_id ,s.mid_class_id ,s.min_class_id ) = 15 and s.group_type = 'store'
then sum(s.plat_amt)
when grouping(d.year_code,s.city_id ,s.trade_area_id ,s.store_id,s.brand_id ,s.max_class_id ,s.mid_class_id ,s.min_class_id ) = 31 and s.group_type = 'trade_area'
then sum(s.plat_amt)
when grouping(d.year_code,s.city_id ,s.trade_area_id ,s.store_id,s.brand_id ,s.max_class_id ,s.mid_class_id ,s.min_class_id ) = 63 and s.group_type = 'city'
then sum(s.plat_amt)
when grouping(d.year_code,s.city_id ,s.trade_area_id ,s.store_id,s.brand_id ,s.max_class_id ,s.mid_class_id ,s.min_class_id ) = 119 and s.group_type = 'brand'
then sum(s.plat_amt)
when grouping(d.year_code,s.city_id ,s.trade_area_id ,s.store_id,s.brand_id ,s.max_class_id ,s.mid_class_id ,s.min_class_id ) = 120 and s.group_type = 'min_class'
then sum(s.plat_amt)
when grouping(d.year_code,s.city_id ,s.trade_area_id ,s.store_id,s.brand_id ,s.max_class_id ,s.mid_class_id ,s.min_class_id ) = 121 and s.group_type = 'mid_class'
then sum(s.plat_amt)
when grouping(d.year_code,s.city_id ,s.trade_area_id ,s.store_id,s.brand_id ,s.max_class_id ,s.mid_class_id ,s.min_class_id ) = 123 and s.group_type = 'max_class'
then sum(s.plat_amt)
when grouping(d.year_code,s.city_id ,s.trade_area_id ,s.store_id,s.brand_id ,s.max_class_id ,s.mid_class_id ,s.min_class_id ) = 127 and s.group_type = 'all'
then sum(s.plat_amt)
else null
end as plat_amt,
case
when grouping(d.year_code,s.city_id ,s.trade_area_id ,s.store_id,s.brand_id ,s.max_class_id ,s.mid_class_id ,s.min_class_id ) = 15 and s.group_type = 'store'
then sum(s.deliver_sale_amt)
when grouping(d.year_code,s.city_id ,s.trade_area_id ,s.store_id,s.brand_id ,s.max_class_id ,s.mid_class_id ,s.min_class_id ) = 31 and s.group_type = 'trade_area'
then sum(s.deliver_sale_amt)
when grouping(d.year_code,s.city_id ,s.trade_area_id ,s.store_id,s.brand_id ,s.max_class_id ,s.mid_class_id ,s.min_class_id ) = 63 and s.group_type = 'city'
then sum(s.deliver_sale_amt)
when grouping(d.year_code,s.city_id ,s.trade_area_id ,s.store_id,s.brand_id ,s.max_class_id ,s.mid_class_id ,s.min_class_id ) = 119 and s.group_type = 'brand'
then sum(s.deliver_sale_amt)
when grouping(d.year_code,s.city_id ,s.trade_area_id ,s.store_id,s.brand_id ,s.max_class_id ,s.mid_class_id ,s.min_class_id ) = 120 and s.group_type = 'min_class'
then sum(s.deliver_sale_amt)
when grouping(d.year_code,s.city_id ,s.trade_area_id ,s.store_id,s.brand_id ,s.max_class_id ,s.mid_class_id ,s.min_class_id ) = 121 and s.group_type = 'mid_class'
then sum(s.deliver_sale_amt)
when grouping(d.year_code,s.city_id ,s.trade_area_id ,s.store_id,s.brand_id ,s.max_class_id ,s.mid_class_id ,s.min_class_id ) = 123 and s.group_type = 'max_class'
then sum(s.deliver_sale_amt)
when grouping(d.year_code,s.city_id ,s.trade_area_id ,s.store_id,s.brand_id ,s.max_class_id ,s.mid_class_id ,s.min_class_id ) = 127 and s.group_type = 'all'
then sum(s.deliver_sale_amt)
else null
end as deliver_sale_amt,
case
when grouping(d.year_code,s.city_id ,s.trade_area_id ,s.store_id,s.brand_id ,s.max_class_id ,s.mid_class_id ,s.min_class_id ) = 15 and s.group_type = 'store'
then sum(s.mini_app_sale_amt)
when grouping(d.year_code,s.city_id ,s.trade_area_id ,s.store_id,s.brand_id ,s.max_class_id ,s.mid_class_id ,s.min_class_id ) = 31 and s.group_type = 'trade_area'
then sum(s.mini_app_sale_amt)
when grouping(d.year_code,s.city_id ,s.trade_area_id ,s.store_id,s.brand_id ,s.max_class_id ,s.mid_class_id ,s.min_class_id ) = 63 and s.group_type = 'city'
then sum(s.mini_app_sale_amt)
when grouping(d.year_code,s.city_id ,s.trade_area_id ,s.store_id,s.brand_id ,s.max_class_id ,s.mid_class_id ,s.min_class_id ) = 119 and s.group_type = 'brand'
then sum(s.mini_app_sale_amt)
when grouping(d.year_code,s.city_id ,s.trade_area_id ,s.store_id,s.brand_id ,s.max_class_id ,s.mid_class_id ,s.min_class_id ) = 120 and s.group_type = 'min_class'
then sum(s.mini_app_sale_amt)
when grouping(d.year_code,s.city_id ,s.trade_area_id ,s.store_id,s.brand_id ,s.max_class_id ,s.mid_class_id ,s.min_class_id ) = 121 and s.group_type = 'mid_class'
then sum(s.mini_app_sale_amt)
when grouping(d.year_code,s.city_id ,s.trade_area_id ,s.store_id,s.brand_id ,s.max_class_id ,s.mid_class_id ,s.min_class_id ) = 123 and s.group_type = 'max_class'
then sum(s.mini_app_sale_amt)
when grouping(d.year_code,s.city_id ,s.trade_area_id ,s.store_id,s.brand_id ,s.max_class_id ,s.mid_class_id ,s.min_class_id ) = 127 and s.group_type = 'all'
then sum(s.mini_app_sale_amt)
else null
end as mini_app_sale_amt,
case
when grouping(d.year_code,s.city_id ,s.trade_area_id ,s.store_id,s.brand_id ,s.max_class_id ,s.mid_class_id ,s.min_class_id ) = 15 and s.group_type = 'store'
then sum(s.android_sale_amt)
when grouping(d.year_code,s.city_id ,s.trade_area_id ,s.store_id,s.brand_id ,s.max_class_id ,s.mid_class_id ,s.min_class_id ) = 31 and s.group_type = 'trade_area'
then sum(s.android_sale_amt)
when grouping(d.year_code,s.city_id ,s.trade_area_id ,s.store_id,s.brand_id ,s.max_class_id ,s.mid_class_id ,s.min_class_id ) = 63 and s.group_type = 'city'
then sum(s.android_sale_amt)
when grouping(d.year_code,s.city_id ,s.trade_area_id ,s.store_id,s.brand_id ,s.max_class_id ,s.mid_class_id ,s.min_class_id ) = 119 and s.group_type = 'brand'
then sum(s.android_sale_amt)
when grouping(d.year_code,s.city_id ,s.trade_area_id ,s.store_id,s.brand_id ,s.max_class_id ,s.mid_class_id ,s.min_class_id ) = 120 and s.group_type = 'min_class'
then sum(s.android_sale_amt)
when grouping(d.year_code,s.city_id ,s.trade_area_id ,s.store_id,s.brand_id ,s.max_class_id ,s.mid_class_id ,s.min_class_id ) = 121 and s.group_type = 'mid_class'
then sum(s.android_sale_amt)
when grouping(d.year_code,s.city_id ,s.trade_area_id ,s.store_id,s.brand_id ,s.max_class_id ,s.mid_class_id ,s.min_class_id ) = 123 and s.group_type = 'max_class'
then sum(s.android_sale_amt)
when grouping(d.year_code,s.city_id ,s.trade_area_id ,s.store_id,s.brand_id ,s.max_class_id ,s.mid_class_id ,s.min_class_id ) = 127 and s.group_type = 'all'
then sum(s.android_sale_amt)
else null
end as android_sale_amt,
case
when grouping(d.year_code,s.city_id ,s.trade_area_id ,s.store_id,s.brand_id ,s.max_class_id ,s.mid_class_id ,s.min_class_id ) = 15 and s.group_type = 'store'
then sum(s.ios_sale_amt)
when grouping(d.year_code,s.city_id ,s.trade_area_id ,s.store_id,s.brand_id ,s.max_class_id ,s.mid_class_id ,s.min_class_id ) = 31 and s.group_type = 'trade_area'
then sum(s.ios_sale_amt)
when grouping(d.year_code,s.city_id ,s.trade_area_id ,s.store_id,s.brand_id ,s.max_class_id ,s.mid_class_id ,s.min_class_id ) = 63 and s.group_type = 'city'
then sum(s.ios_sale_amt)
when grouping(d.year_code,s.city_id ,s.trade_area_id ,s.store_id,s.brand_id ,s.max_class_id ,s.mid_class_id ,s.min_class_id ) = 119 and s.group_type = 'brand'
then sum(s.ios_sale_amt)
when grouping(d.year_code,s.city_id ,s.trade_area_id ,s.store_id,s.brand_id ,s.max_class_id ,s.mid_class_id ,s.min_class_id ) = 120 and s.group_type = 'min_class'
then sum(s.ios_sale_amt)
when grouping(d.year_code,s.city_id ,s.trade_area_id ,s.store_id,s.brand_id ,s.max_class_id ,s.mid_class_id ,s.min_class_id ) = 121 and s.group_type = 'mid_class'
then sum(s.ios_sale_amt)
when grouping(d.year_code,s.city_id ,s.trade_area_id ,s.store_id,s.brand_id ,s.max_class_id ,s.mid_class_id ,s.min_class_id ) = 123 and s.group_type = 'max_class'
then sum(s.ios_sale_amt)
when grouping(d.year_code,s.city_id ,s.trade_area_id ,s.store_id,s.brand_id ,s.max_class_id ,s.mid_class_id ,s.min_class_id ) = 127 and s.group_type = 'all'
then sum(s.ios_sale_amt)
else null
end as ios_sale_amt,
case
when grouping(d.year_code,s.city_id ,s.trade_area_id ,s.store_id,s.brand_id ,s.max_class_id ,s.mid_class_id ,s.min_class_id ) = 15 and s.group_type = 'store'
then sum(s.pcweb_sale_amt)
when grouping(d.year_code,s.city_id ,s.trade_area_id ,s.store_id,s.brand_id ,s.max_class_id ,s.mid_class_id ,s.min_class_id ) = 31 and s.group_type = 'trade_area'
then sum(s.pcweb_sale_amt)
when grouping(d.year_code,s.city_id ,s.trade_area_id ,s.store_id,s.brand_id ,s.max_class_id ,s.mid_class_id ,s.min_class_id ) = 63 and s.group_type = 'city'
then sum(s.pcweb_sale_amt)
when grouping(d.year_code,s.city_id ,s.trade_area_id ,s.store_id,s.brand_id ,s.max_class_id ,s.mid_class_id ,s.min_class_id ) = 119 and s.group_type = 'brand'
then sum(s.pcweb_sale_amt)
when grouping(d.year_code,s.city_id ,s.trade_area_id ,s.store_id,s.brand_id ,s.max_class_id ,s.mid_class_id ,s.min_class_id ) = 120 and s.group_type = 'min_class'
then sum(s.pcweb_sale_amt)
when grouping(d.year_code,s.city_id ,s.trade_area_id ,s.store_id,s.brand_id ,s.max_class_id ,s.mid_class_id ,s.min_class_id ) = 121 and s.group_type = 'mid_class'
then sum(s.pcweb_sale_amt)
when grouping(d.year_code,s.city_id ,s.trade_area_id ,s.store_id,s.brand_id ,s.max_class_id ,s.mid_class_id ,s.min_class_id ) = 123 and s.group_type = 'max_class'
then sum(s.pcweb_sale_amt)
when grouping(d.year_code,s.city_id ,s.trade_area_id ,s.store_id,s.brand_id ,s.max_class_id ,s.mid_class_id ,s.min_class_id ) = 127 and s.group_type = 'all'
then sum(s.pcweb_sale_amt)
else null
end as pcweb_sale_amt,
case
when grouping(d.year_code,s.city_id ,s.trade_area_id ,s.store_id,s.brand_id ,s.max_class_id ,s.mid_class_id ,s.min_class_id ) = 15 and s.group_type = 'store'
then sum(s.order_cnt)
when grouping(d.year_code,s.city_id ,s.trade_area_id ,s.store_id,s.brand_id ,s.max_class_id ,s.mid_class_id ,s.min_class_id ) = 31 and s.group_type = 'trade_area'
then sum(s.order_cnt)
when grouping(d.year_code,s.city_id ,s.trade_area_id ,s.store_id,s.brand_id ,s.max_class_id ,s.mid_class_id ,s.min_class_id ) = 63 and s.group_type = 'city'
then sum(s.order_cnt)
when grouping(d.year_code,s.city_id ,s.trade_area_id ,s.store_id,s.brand_id ,s.max_class_id ,s.mid_class_id ,s.min_class_id ) = 119 and s.group_type = 'brand'
then sum(s.order_cnt)
when grouping(d.year_code,s.city_id ,s.trade_area_id ,s.store_id,s.brand_id ,s.max_class_id ,s.mid_class_id ,s.min_class_id ) = 120 and s.group_type = 'min_class'
then sum(s.order_cnt)
when grouping(d.year_code,s.city_id ,s.trade_area_id ,s.store_id,s.brand_id ,s.max_class_id ,s.mid_class_id ,s.min_class_id ) = 121 and s.group_type = 'mid_class'
then sum(s.order_cnt)
when grouping(d.year_code,s.city_id ,s.trade_area_id ,s.store_id,s.brand_id ,s.max_class_id ,s.mid_class_id ,s.min_class_id ) = 123 and s.group_type = 'max_class'
then sum(s.order_cnt)
when grouping(d.year_code,s.city_id ,s.trade_area_id ,s.store_id,s.brand_id ,s.max_class_id ,s.mid_class_id ,s.min_class_id ) = 127 and s.group_type = 'all'
then sum(s.order_cnt)
else null
end as order_cnt,
case
when grouping(d.year_code,s.city_id ,s.trade_area_id ,s.store_id,s.brand_id ,s.max_class_id ,s.mid_class_id ,s.min_class_id ) = 15 and s.group_type = 'store'
then sum(s.eva_order_cnt)
when grouping(d.year_code,s.city_id ,s.trade_area_id ,s.store_id,s.brand_id ,s.max_class_id ,s.mid_class_id ,s.min_class_id ) = 31 and s.group_type = 'trade_area'
then sum(s.eva_order_cnt)
when grouping(d.year_code,s.city_id ,s.trade_area_id ,s.store_id,s.brand_id ,s.max_class_id ,s.mid_class_id ,s.min_class_id ) = 63 and s.group_type = 'city'
then sum(s.eva_order_cnt)
when grouping(d.year_code,s.city_id ,s.trade_area_id ,s.store_id,s.brand_id ,s.max_class_id ,s.mid_class_id ,s.min_class_id ) = 119 and s.group_type = 'brand'
then sum(s.eva_order_cnt)
when grouping(d.year_code,s.city_id ,s.trade_area_id ,s.store_id,s.brand_id ,s.max_class_id ,s.mid_class_id ,s.min_class_id ) = 120 and s.group_type = 'min_class'
then sum(s.eva_order_cnt)
when grouping(d.year_code,s.city_id ,s.trade_area_id ,s.store_id,s.brand_id ,s.max_class_id ,s.mid_class_id ,s.min_class_id ) = 121 and s.group_type = 'mid_class'
then sum(s.eva_order_cnt)
when grouping(d.year_code,s.city_id ,s.trade_area_id ,s.store_id,s.brand_id ,s.max_class_id ,s.mid_class_id ,s.min_class_id ) = 123 and s.group_type = 'max_class'
then sum(s.eva_order_cnt)
when grouping(d.year_code,s.city_id ,s.trade_area_id ,s.store_id,s.brand_id ,s.max_class_id ,s.mid_class_id ,s.min_class_id ) = 127 and s.group_type = 'all'
then sum(s.eva_order_cnt)
else null
end as eva_order_cnt,
case
when grouping(d.year_code,s.city_id ,s.trade_area_id ,s.store_id,s.brand_id ,s.max_class_id ,s.mid_class_id ,s.min_class_id ) = 15 and s.group_type = 'store'
then sum(s.bad_eva_order_cnt)
when grouping(d.year_code,s.city_id ,s.trade_area_id ,s.store_id,s.brand_id ,s.max_class_id ,s.mid_class_id ,s.min_class_id ) = 31 and s.group_type = 'trade_area'
then sum(s.bad_eva_order_cnt)
when grouping(d.year_code,s.city_id ,s.trade_area_id ,s.store_id,s.brand_id ,s.max_class_id ,s.mid_class_id ,s.min_class_id ) = 63 and s.group_type = 'city'
then sum(s.bad_eva_order_cnt)
when grouping(d.year_code,s.city_id ,s.trade_area_id ,s.store_id,s.brand_id ,s.max_class_id ,s.mid_class_id ,s.min_class_id ) = 119 and s.group_type = 'brand'
then sum(s.bad_eva_order_cnt)
when grouping(d.year_code,s.city_id ,s.trade_area_id ,s.store_id,s.brand_id ,s.max_class_id ,s.mid_class_id ,s.min_class_id ) = 120 and s.group_type = 'min_class'
then sum(s.bad_eva_order_cnt)
when grouping(d.year_code,s.city_id ,s.trade_area_id ,s.store_id,s.brand_id ,s.max_class_id ,s.mid_class_id ,s.min_class_id ) = 121 and s.group_type = 'mid_class'
then sum(s.bad_eva_order_cnt)
when grouping(d.year_code,s.city_id ,s.trade_area_id ,s.store_id,s.brand_id ,s.max_class_id ,s.mid_class_id ,s.min_class_id ) = 123 and s.group_type = 'max_class'
then sum(s.bad_eva_order_cnt)
when grouping(d.year_code,s.city_id ,s.trade_area_id ,s.store_id,s.brand_id ,s.max_class_id ,s.mid_class_id ,s.min_class_id ) = 127 and s.group_type = 'all'
then sum(s.bad_eva_order_cnt)
else null
end as bad_eva_order_cnt,
case
when grouping(d.year_code,s.city_id ,s.trade_area_id ,s.store_id,s.brand_id ,s.max_class_id ,s.mid_class_id ,s.min_class_id ) = 15 and s.group_type = 'store'
then sum(s.deliver_order_cnt)
when grouping(d.year_code,s.city_id ,s.trade_area_id ,s.store_id,s.brand_id ,s.max_class_id ,s.mid_class_id ,s.min_class_id ) = 31 and s.group_type = 'trade_area'
then sum(s.deliver_order_cnt)
when grouping(d.year_code,s.city_id ,s.trade_area_id ,s.store_id,s.brand_id ,s.max_class_id ,s.mid_class_id ,s.min_class_id ) = 63 and s.group_type = 'city'
then sum(s.deliver_order_cnt)
when grouping(d.year_code,s.city_id ,s.trade_area_id ,s.store_id,s.brand_id ,s.max_class_id ,s.mid_class_id ,s.min_class_id ) = 119 and s.group_type = 'brand'
then sum(s.deliver_order_cnt)
when grouping(d.year_code,s.city_id ,s.trade_area_id ,s.store_id,s.brand_id ,s.max_class_id ,s.mid_class_id ,s.min_class_id ) = 120 and s.group_type = 'min_class'
then sum(s.deliver_order_cnt)
when grouping(d.year_code,s.city_id ,s.trade_area_id ,s.store_id,s.brand_id ,s.max_class_id ,s.mid_class_id ,s.min_class_id ) = 121 and s.group_type = 'mid_class'
then sum(s.deliver_order_cnt)
when grouping(d.year_code,s.city_id ,s.trade_area_id ,s.store_id,s.brand_id ,s.max_class_id ,s.mid_class_id ,s.min_class_id ) = 123 and s.group_type = 'max_class'
then sum(s.deliver_order_cnt)
when grouping(d.year_code,s.city_id ,s.trade_area_id ,s.store_id,s.brand_id ,s.max_class_id ,s.mid_class_id ,s.min_class_id ) = 127 and s.group_type = 'all'
then sum(s.deliver_order_cnt)
else null
end as deliver_order_cnt,
case
when grouping(d.year_code,s.city_id ,s.trade_area_id ,s.store_id,s.brand_id ,s.max_class_id ,s.mid_class_id ,s.min_class_id ) = 15 and s.group_type = 'store'
then sum(s.refund_order_cnt)
when grouping(d.year_code,s.city_id ,s.trade_area_id ,s.store_id,s.brand_id ,s.max_class_id ,s.mid_class_id ,s.min_class_id ) = 31 and s.group_type = 'trade_area'
then sum(s.refund_order_cnt)
when grouping(d.year_code,s.city_id ,s.trade_area_id ,s.store_id,s.brand_id ,s.max_class_id ,s.mid_class_id ,s.min_class_id ) = 63 and s.group_type = 'city'
then sum(s.refund_order_cnt)
when grouping(d.year_code,s.city_id ,s.trade_area_id ,s.store_id,s.brand_id ,s.max_class_id ,s.mid_class_id ,s.min_class_id ) = 119 and s.group_type = 'brand'
then sum(s.refund_order_cnt)
when grouping(d.year_code,s.city_id ,s.trade_area_id ,s.store_id,s.brand_id ,s.max_class_id ,s.mid_class_id ,s.min_class_id ) = 120 and s.group_type = 'min_class'
then sum(s.refund_order_cnt)
when grouping(d.year_code,s.city_id ,s.trade_area_id ,s.store_id,s.brand_id ,s.max_class_id ,s.mid_class_id ,s.min_class_id ) = 121 and s.group_type = 'mid_class'
then sum(s.refund_order_cnt)
when grouping(d.year_code,s.city_id ,s.trade_area_id ,s.store_id,s.brand_id ,s.max_class_id ,s.mid_class_id ,s.min_class_id ) = 123 and s.group_type = 'max_class'
then sum(s.refund_order_cnt)
when grouping(d.year_code,s.city_id ,s.trade_area_id ,s.store_id,s.brand_id ,s.max_class_id ,s.mid_class_id ,s.min_class_id ) = 127 and s.group_type = 'all'
then sum(s.refund_order_cnt)
else null
end as refund_order_cnt,
case
when grouping(d.year_code,s.city_id ,s.trade_area_id ,s.store_id,s.brand_id ,s.max_class_id ,s.mid_class_id ,s.min_class_id ) = 15 and s.group_type = 'store'
then sum(s.miniapp_order_cnt)
when grouping(d.year_code,s.city_id ,s.trade_area_id ,s.store_id,s.brand_id ,s.max_class_id ,s.mid_class_id ,s.min_class_id ) = 31 and s.group_type = 'trade_area'
then sum(s.miniapp_order_cnt)
when grouping(d.year_code,s.city_id ,s.trade_area_id ,s.store_id,s.brand_id ,s.max_class_id ,s.mid_class_id ,s.min_class_id ) = 63 and s.group_type = 'city'
then sum(s.miniapp_order_cnt)
when grouping(d.year_code,s.city_id ,s.trade_area_id ,s.store_id,s.brand_id ,s.max_class_id ,s.mid_class_id ,s.min_class_id ) = 119 and s.group_type = 'brand'
then sum(s.miniapp_order_cnt)
when grouping(d.year_code,s.city_id ,s.trade_area_id ,s.store_id,s.brand_id ,s.max_class_id ,s.mid_class_id ,s.min_class_id ) = 120 and s.group_type = 'min_class'
then sum(s.miniapp_order_cnt)
when grouping(d.year_code,s.city_id ,s.trade_area_id ,s.store_id,s.brand_id ,s.max_class_id ,s.mid_class_id ,s.min_class_id ) = 121 and s.group_type = 'mid_class'
then sum(s.miniapp_order_cnt)
when grouping(d.year_code,s.city_id ,s.trade_area_id ,s.store_id,s.brand_id ,s.max_class_id ,s.mid_class_id ,s.min_class_id ) = 123 and s.group_type = 'max_class'
then sum(s.miniapp_order_cnt)
when grouping(d.year_code,s.city_id ,s.trade_area_id ,s.store_id,s.brand_id ,s.max_class_id ,s.mid_class_id ,s.min_class_id ) = 127 and s.group_type = 'all'
then sum(s.miniapp_order_cnt)
else null
end as miniapp_order_cnt,
case
when grouping(d.year_code,s.city_id ,s.trade_area_id ,s.store_id,s.brand_id ,s.max_class_id ,s.mid_class_id ,s.min_class_id ) = 15 and s.group_type = 'store'
then sum(s.android_order_cnt)
when grouping(d.year_code,s.city_id ,s.trade_area_id ,s.store_id,s.brand_id ,s.max_class_id ,s.mid_class_id ,s.min_class_id ) = 31 and s.group_type = 'trade_area'
then sum(s.android_order_cnt)
when grouping(d.year_code,s.city_id ,s.trade_area_id ,s.store_id,s.brand_id ,s.max_class_id ,s.mid_class_id ,s.min_class_id ) = 63 and s.group_type = 'city'
then sum(s.android_order_cnt)
when grouping(d.year_code,s.city_id ,s.trade_area_id ,s.store_id,s.brand_id ,s.max_class_id ,s.mid_class_id ,s.min_class_id ) = 119 and s.group_type = 'brand'
then sum(s.android_order_cnt)
when grouping(d.year_code,s.city_id ,s.trade_area_id ,s.store_id,s.brand_id ,s.max_class_id ,s.mid_class_id ,s.min_class_id ) = 120 and s.group_type = 'min_class'
then sum(s.android_order_cnt)
when grouping(d.year_code,s.city_id ,s.trade_area_id ,s.store_id,s.brand_id ,s.max_class_id ,s.mid_class_id ,s.min_class_id ) = 121 and s.group_type = 'mid_class'
then sum(s.android_order_cnt)
when grouping(d.year_code,s.city_id ,s.trade_area_id ,s.store_id,s.brand_id ,s.max_class_id ,s.mid_class_id ,s.min_class_id ) = 123 and s.group_type = 'max_class'
then sum(s.android_order_cnt)
when grouping(d.year_code,s.city_id ,s.trade_area_id ,s.store_id,s.brand_id ,s.max_class_id ,s.mid_class_id ,s.min_class_id ) = 127 and s.group_type = 'all'
then sum(s.android_order_cnt)
else null
end as android_order_cnt,
case
when grouping(d.year_code,s.city_id ,s.trade_area_id ,s.store_id,s.brand_id ,s.max_class_id ,s.mid_class_id ,s.min_class_id ) = 15 and s.group_type = 'store'
then sum(s.ios_order_cnt)
when grouping(d.year_code,s.city_id ,s.trade_area_id ,s.store_id,s.brand_id ,s.max_class_id ,s.mid_class_id ,s.min_class_id ) = 31 and s.group_type = 'trade_area'
then sum(s.ios_order_cnt)
when grouping(d.year_code,s.city_id ,s.trade_area_id ,s.store_id,s.brand_id ,s.max_class_id ,s.mid_class_id ,s.min_class_id ) = 63 and s.group_type = 'city'
then sum(s.ios_order_cnt)
when grouping(d.year_code,s.city_id ,s.trade_area_id ,s.store_id,s.brand_id ,s.max_class_id ,s.mid_class_id ,s.min_class_id ) = 119 and s.group_type = 'brand'
then sum(s.ios_order_cnt)
when grouping(d.year_code,s.city_id ,s.trade_area_id ,s.store_id,s.brand_id ,s.max_class_id ,s.mid_class_id ,s.min_class_id ) = 120 and s.group_type = 'min_class'
then sum(s.ios_order_cnt)
when grouping(d.year_code,s.city_id ,s.trade_area_id ,s.store_id,s.brand_id ,s.max_class_id ,s.mid_class_id ,s.min_class_id ) = 121 and s.group_type = 'mid_class'
then sum(s.ios_order_cnt)
when grouping(d.year_code,s.city_id ,s.trade_area_id ,s.store_id,s.brand_id ,s.max_class_id ,s.mid_class_id ,s.min_class_id ) = 123 and s.group_type = 'max_class'
then sum(s.ios_order_cnt)
when grouping(d.year_code,s.city_id ,s.trade_area_id ,s.store_id,s.brand_id ,s.max_class_id ,s.mid_class_id ,s.min_class_id ) = 127 and s.group_type = 'all'
then sum(s.ios_order_cnt)
else null
end as ios_order_cnt,
case
when grouping(d.year_code,s.city_id ,s.trade_area_id ,s.store_id,s.brand_id ,s.max_class_id ,s.mid_class_id ,s.min_class_id ) = 15 and s.group_type = 'store'
then sum(s.pcweb_order_cnt)
when grouping(d.year_code,s.city_id ,s.trade_area_id ,s.store_id,s.brand_id ,s.max_class_id ,s.mid_class_id ,s.min_class_id ) = 31 and s.group_type = 'trade_area'
then sum(s.pcweb_order_cnt)
when grouping(d.year_code,s.city_id ,s.trade_area_id ,s.store_id,s.brand_id ,s.max_class_id ,s.mid_class_id ,s.min_class_id ) = 63 and s.group_type = 'city'
then sum(s.pcweb_order_cnt)
when grouping(d.year_code,s.city_id ,s.trade_area_id ,s.store_id,s.brand_id ,s.max_class_id ,s.mid_class_id ,s.min_class_id ) = 119 and s.group_type = 'brand'
then sum(s.pcweb_order_cnt)
when grouping(d.year_code,s.city_id ,s.trade_area_id ,s.store_id,s.brand_id ,s.max_class_id ,s.mid_class_id ,s.min_class_id ) = 120 and s.group_type = 'min_class'
then sum(s.pcweb_order_cnt)
when grouping(d.year_code,s.city_id ,s.trade_area_id ,s.store_id,s.brand_id ,s.max_class_id ,s.mid_class_id ,s.min_class_id ) = 121 and s.group_type = 'mid_class'
then sum(s.pcweb_order_cnt)
when grouping(d.year_code,s.city_id ,s.trade_area_id ,s.store_id,s.brand_id ,s.max_class_id ,s.mid_class_id ,s.min_class_id ) = 123 and s.group_type = 'max_class'
then sum(s.pcweb_order_cnt)
when grouping(d.year_code,s.city_id ,s.trade_area_id ,s.store_id,s.brand_id ,s.max_class_id ,s.mid_class_id ,s.min_class_id ) = 127 and s.group_type = 'all'
then sum(s.pcweb_order_cnt)
else null
end as pcweb_order_cnt
from xxxxx_yp_dws_jiale.dws_sale_daycount s left join dim_date d on s.dt = d.date_code
group by
grouping sets(
-- 日期 + group_type
(d.year_code,s.group_type) ,
-- 日期 + 城市 + group_type
(d.year_code,s.city_id ,s.city_name ,s.group_type),
-- 日期 + 城市 + 商圈 + group_type
(d.year_code,s.city_id ,s.city_name,s.trade_area_id ,s.trade_area_name ,s.group_type),
-- 日期 + 城市 + 商圈 + 店铺 + group_type
(d.year_code,s.city_id ,s.city_name,s.trade_area_id ,s.trade_area_name ,s.store_id,s.store_name ,s.group_type),
-- 日期 + 品牌 + group_type
(d.year_code,s.brand_id ,s.brand_name ,s.group_type) ,
-- 日期 + 大类 + group_type
(d.year_code,s.max_class_id ,s.max_class_name ,s.group_type) ,
-- 日期 + 大类 + 中类 + group_type
(d.year_code,s.max_class_id ,s.max_class_name ,s.mid_class_id ,s.mid_class_name, s.group_type),
-- 日期 + 大类 + 中类 + 小类 + group_type
(d.year_code,s.max_class_id ,s.max_class_name ,s.mid_class_id ,s.mid_class_name,s.min_class_id ,s.min_class_name ,s.group_type)
)
)
select * from t1 where group_type is not null;
4.商品主题统计宽表
主要指标有：下单次数、下单件数、下单金额、被支付次数、被支付件数、被支付金额、被退款次数、被退款件数、被退款金额、被加入购物车次数、被加入购物车件数、被收藏次数、好评数、中评数、差评数。
维度有：商品+日期(总累计值,近30天的数据)。
- 构建DM层商品主题统计宽表(hive中执行):
create table xxx.dm_sku
(
sku_id string comment 'sku_id',
sku_name string comment '商品名称',
order_last_30d_count bigint comment '最近30日被下单次数',
order_last_30d_num bigint comment '最近30日被下单件数',
order_last_30d_amount decimal(38,2) comment '最近30日被下单金额',
order_count bigint comment '累积被下单次数',
order_num bigint comment '累积被下单件数',
order_amount decimal(38,2) comment '累积被下单金额',
payment_last_30d_count bigint comment '最近30日被支付次数',
payment_last_30d_num bigint comment '最近30日被支付件数',
payment_last_30d_amount decimal(38,2) comment '最近30日被支付金额',
payment_count bigint comment '累积被支付次数',
payment_num bigint comment '累积被支付件数',
payment_amount decimal(38,2) comment '累积被支付金额',
refund_last_30d_count bigint comment '最近三十日退款次数',
refund_last_30d_num bigint comment '最近三十日退款件数',
refund_last_30d_amount decimal(38,2) comment '最近三十日退款金额',
refund_count bigint comment '累积退款次数',
refund_num bigint comment '累积退款件数',
refund_amount decimal(38,2) comment '累积退款金额',
cart_last_30d_count bigint comment '最近30日被加入购物车次数',
cart_last_30d_num bigint comment '最近30日被加入购物车件数',
cart_count bigint comment '累积被加入购物车次数',
cart_num bigint comment '累积被加入购物车件数',
favor_last_30d_count bigint comment '最近30日被收藏次数',
favor_count bigint comment '累积被收藏次数',
evaluation_last_30d_good_count bigint comment '最近30日好评数',
evaluation_last_30d_mid_count bigint comment '最近30日中评数',
evaluation_last_30d_bad_count bigint comment '最近30日差评数',
evaluation_good_count bigint comment '累积好评数',
evaluation_mid_count bigint comment '累积中评数',
evaluation_bad_count bigint comment '累积差评数'
)
COMMENT '商品主题宽表'
ROW format delimited fields terminated BY '\t'
stored AS orc tblproperties ('orc.compress' = 'SNAPPY');
2.计算累计值
-- 计算总累计值
select
sku_id,
sku_name,
sum(order_count) as order_count,
sum(order_num) as order_num,
sum(order_amount) as order_amount,
sum(payment_count) as payment_count,
sum(payment_num) as payment_num,
sum(payment_amount) as payment_amount,
sum(refund_count) as refund_count,
sum(refund_num) as refund_num,
sum(refund_amount) as refund_amount,
sum(cart_count) as cart_count,
sum(cart_num) as cart_num,
sum(favor_count) as favor_count,
sum(evaluation_good_count) as evaluation_good_count,
sum(evaluation_mid_count) as evaluation_mid_count,
sum(evaluation_bad_count) as evaluation_bad_count
from xxxx.dws_sku_daycount
group by sku_id ,sku_name ;
3.计算近30天
-- 计算总累计值
select
sku_id,
sku_name,
sum(order_count) as order_last_30d_count,
sum(order_num) as order_last_30d_num,
sum(order_amount) as order_last_30d_amount,
sum(payment_count) as payment_last_30d_count,
sum(payment_num) as payment_last_30d_num,
sum(payment_amount) as payment_last_30d_amount,
sum(refund_count) as refund_last_30d_count,
sum(refund_num) as refund_last_30d_num,
sum(refund_amount) as refund_last_30d_amount,
sum(cart_count) as cart_last_30d_count,
sum(cart_num) as cart_last_30d_num,
sum(favor_count) as favor_last_30d_count,
sum(evaluation_good_count) as evaluation_last_30d_good_count,
sum(evaluation_mid_count) as evaluation_last_30d_mid_count,
sum(evaluation_bad_count) as evaluation_last_30d_bad_count
from xxxx.dws_sku_daycount where dt between '2021-07-01' and '2021-07-31'
group by sku_id ,sku_name ;
-- 如果动态获取最近30天的数据呢? 最近30天范围: 上一天日期 ~ 上一天的前30天
select * from hive.bj59_yp_dws_jiale.dws_sku_daycount where dt
between date_format(date_add('day',-30,date_add('day',-1,now())), '%Y-%m-%d') and date_format(date_add('day',-1,now()), '%Y-%m-%d')
-- 思考: 如何获取当前的时间呢?
select NOW();
-- 第二个思考: 如果让这个时间 -30天呢?
select date_add('day',-30,date_add('day',-1,now()))
-- 或者
select date_add('day',-31,now())
-- 第三个: 如何获取时间的 年 月 日部分
select date_format(date_add('day',-31,now()), '%Y-%m-%d');
4.合并处理
insert into xxxx.dm_sku
with total_cnt as(
-- 计算总累计值
select
sku_id,
sku_name,
sum(order_count) as order_count,
sum(order_num) as order_num,
sum(order_amount) as order_amount,
sum(payment_count) as payment_count,
sum(payment_num) as payment_num,
sum(payment_amount) as payment_amount,
sum(refund_count) as refund_count,
sum(refund_num) as refund_num,
sum(refund_amount) as refund_amount,
sum(cart_count) as cart_count,
sum(cart_num) as cart_num,
sum(favor_count) as favor_count,
sum(evaluation_good_count) as evaluation_good_count,
sum(evaluation_mid_count) as evaluation_mid_count,
sum(evaluation_bad_count) as evaluation_bad_count
from xxxx.dws_sku_daycount
group by sku_id ,sku_name
),
l30_cnt as (
select
sku_id,
sku_name,
sum(order_count) as order_last_30d_count,
sum(order_num) as order_last_30d_num,
sum(order_amount) as order_last_30d_amount,
sum(payment_count) as payment_last_30d_count,
sum(payment_num) as payment_last_30d_num,
sum(payment_amount) as payment_last_30d_amount,
sum(refund_count) as refund_last_30d_count,
sum(refund_num) as refund_last_30d_num,
sum(refund_amount) as refund_last_30d_amount,
sum(cart_count) as cart_last_30d_count,
sum(cart_num) as cart_last_30d_num,
sum(favor_count) as favor_last_30d_count,
sum(evaluation_good_count) as evaluation_last_30d_good_count,
sum(evaluation_mid_count) as evaluation_last_30d_mid_count,
sum(evaluation_bad_count) as evaluation_last_30d_bad_count
from xxxx.dws_sku_daycount where dt between '2021-07-01' and '2021-07-31'
group by sku_id ,sku_name
)
select
total_cnt.sku_id,
total_cnt.sku_name,
coalesce(l30_cnt.order_last_30d_count,0) as order_last_30d_count,
coalesce(l30_cnt.order_last_30d_num,0) as order_last_30d_num,
coalesce(l30_cnt.order_last_30d_amount,0) as order_last_30d_amount,
total_cnt.order_count ,
total_cnt.order_num ,
total_cnt.order_amount ,
coalesce(l30_cnt.payment_last_30d_count, 0 ) as payment_last_30d_count,
coalesce(l30_cnt.payment_last_30d_num , 0 ) as payment_last_30d_num,
coalesce(l30_cnt.payment_last_30d_amount, 0 ) as payment_last_30d_amount,
total_cnt.payment_count,
total_cnt.payment_num ,
total_cnt.payment_amount ,
coalesce(l30_cnt.refund_last_30d_count, 0 ) as refund_last_30d_count,
coalesce(l30_cnt.refund_last_30d_num, 0 ) as refund_last_30d_num,
coalesce(l30_cnt.refund_last_30d_amount, 0 ) as refund_last_30d_amount,
total_cnt.refund_count,
total_cnt.refund_num,
total_cnt.refund_amount ,
coalesce(l30_cnt.cart_last_30d_count, 0 ) as cart_last_30d_count,
coalesce(l30_cnt.cart_last_30d_num , 0 ) as cart_last_30d_num,
total_cnt.cart_count,
total_cnt.cart_num ,
coalesce(l30_cnt.favor_last_30d_count , 0 ) as favor_last_30d_count,
total_cnt.favor_count,
coalesce(l30_cnt.evaluation_last_30d_good_count, 0 ) as evaluation_last_30d_good_count,
coalesce(l30_cnt.evaluation_last_30d_mid_count, 0 ) as evaluation_last_30d_mid_count,
coalesce(l30_cnt.evaluation_last_30d_bad_count, 0 ) as evaluation_last_30d_bad_count,
total_cnt.evaluation_good_count,
total_cnt.evaluation_mid_count ,
total_cnt.evaluation_bad_count
from total_cnt left join l30_cnt on total_cnt.sku_id = l30_cnt.sku_id;
5.增量sql的实现
-- 第一步: 重新计算最近30天的数据, 和昨天的结果数据
with t1 as (
select
sku_id,
sku_name,
sum(order_count) as order_last_30d_count,
sum(order_num) as order_last_30d_num,
sum(order_amount) as order_last_30d_amount,
sum(
if(
dt = date_format(date_add('day',-1,now()), '%Y-%m-%d') ,
order_count,
0
)
) as order_count,
sum(
if(
dt = date_format(date_add('day',-1,now()), '%Y-%m-%d') ,
order_num,
0
)
) as order_num,
sum(
if(
dt = date_format(date_add('day',-1,now()), '%Y-%m-%d') ,
order_amount,
0
)
) as order_amount,
sum(payment_count) as payment_last_30d_count,
sum(payment_num) as payment_last_30d_num,
sum(payment_amount) as payment_last_30d_amount,
sum(
if(
dt = date_format(date_add('day',-1,now()), '%Y-%m-%d') ,
payment_count,
0
)
) as payment_count,
sum(
if(
dt = date_format(date_add('day',-1,now()), '%Y-%m-%d') ,
payment_num,
0
)
) as payment_num,
sum(
if(
dt = date_format(date_add('day',-1,now()), '%Y-%m-%d') ,
payment_amount,
0
)
) as payment_amount,
sum(refund_count) as refund_last_30d_count,
sum(refund_num) as refund_last_30d_num,
sum(refund_amount) as refund_last_30d_amount,
sum(
if(
dt = date_format(date_add('day',-1,now()), '%Y-%m-%d') ,
refund_count,
0
)
) as refund_count,
sum(
if(
dt = date_format(date_add('day',-1,now()), '%Y-%m-%d') ,
refund_num,
0
)
) as refund_num,
sum(
if(
dt = date_format(date_add('day',-1,now()), '%Y-%m-%d') ,
refund_amount,
0
)
) as refund_amount,
sum(cart_count) as cart_last_30d_count,
sum(cart_num) as cart_last_30d_num,
sum(
if(
dt = date_format(date_add('day',-1,now()), '%Y-%m-%d') ,
cart_count,
0
)
) as cart_count,
sum(
if(
dt = date_format(date_add('day',-1,now()), '%Y-%m-%d') ,
cart_num,
0
)
) as cart_num,
sum(favor_count) as favor_last_30d_count,
sum(
if(
dt = date_format(date_add('day',-1,now()), '%Y-%m-%d') ,
favor_count,
0
)
) as favor_count,
sum(evaluation_good_count) as evaluation_last_30d_good_count,
sum(evaluation_mid_count) as evaluation_last_30d_mid_count,
sum(evaluation_bad_count) as evaluation_last_30d_bad_count,
sum(
if(
dt = date_format(date_add('day',-1,now()), '%Y-%m-%d') ,
evaluation_good_count,
0
)
) as evaluation_good_count,
sum(
if(
dt = date_format(date_add('day',-1,now()), '%Y-%m-%d') ,
evaluation_mid_count,
0
)
) as evaluation_mid_count,
sum(
if(
dt = date_format(date_add('day',-1,now()), '%Y-%m-%d') ,
evaluation_bad_count,
0
)
) as evaluation_bad_count
from xxxx.dws_sku_daycount where dt between date_format(date_add('day',-30,date_add('day',-1,now())), '%Y-%m-%d') and date_format(date_add('day',-1,now()), '%Y-%m-%d')
group by sku_id ,sku_name
)
-- 第二步: 直接覆盖为新的最近30天的数据, 将累计的数据和昨日的数据加在一起
select
coalesce(new.sku_id,old.sku_id) as sku_id,
coalesce(new.sku_name,old.sku_name) as sku_name,
coalesce(new.order_last_30d_count,0) as order_last_30d_count,
coalesce(new.order_last_30d_num,0) as order_last_30d_num,
coalesce(new.order_last_30d_amount,0) as order_last_30d_amount,
coalesce(new.order_count,0) + coalesce(old.order_count, 0) as order_count,
coalesce(new.order_num,0) + coalesce(old.order_num, 0) as order_num,
coalesce(new.order_amount,0) + coalesce(old.order_amount, 0) as order_amount,
coalesce(new.payment_last_30d_count, 0 ) as payment_last_30d_count,
coalesce(new.payment_last_30d_num , 0 ) as payment_last_30d_num,
coalesce(new.payment_last_30d_amount, 0 ) as payment_last_30d_amount,
coalesce(new.payment_count,0) + coalesce(old.payment_count, 0) as payment_count,
coalesce(new.payment_num,0) + coalesce(old.payment_num, 0) as payment_num,
coalesce(new.payment_amount,0) + coalesce(old.payment_amount, 0) as payment_amount,
coalesce(new.refund_last_30d_count, 0 ) as refund_last_30d_count,
coalesce(new.refund_last_30d_num, 0 ) as refund_last_30d_num,
coalesce(new.refund_last_30d_amount, 0 ) as refund_last_30d_amount,
coalesce(new.refund_count,0) + coalesce(old.refund_count, 0) as refund_count,
coalesce(new.refund_num,0) + coalesce(old.refund_num, 0) as refund_num,
coalesce(new.refund_amount,0) + coalesce(old.refund_amount, 0) as refund_amount,
coalesce(new.cart_last_30d_count, 0 ) as cart_last_30d_count,
coalesce(new.cart_last_30d_num , 0 ) as cart_last_30d_num,
coalesce(new.cart_count,0) + coalesce(old.cart_count, 0) as cart_count,
coalesce(new.cart_num,0) + coalesce(old.cart_num, 0) as cart_num,
coalesce(new.favor_last_30d_count , 0 ) as favor_last_30d_count,
coalesce(new.favor_count,0) + coalesce(old.favor_count, 0) as favor_count,
coalesce(new.evaluation_last_30d_good_count, 0 ) as evaluation_last_30d_good_count,
coalesce(new.evaluation_last_30d_mid_count, 0 ) as evaluation_last_30d_mid_count,
coalesce(new.evaluation_last_30d_bad_count, 0 ) as evaluation_last_30d_bad_count,
coalesce(new.evaluation_good_count,0) + coalesce(old.evaluation_good_count, 0) as evaluation_good_count,
coalesce(new.evaluation_mid_count,0) + coalesce(old.evaluation_mid_count, 0) as evaluation_mid_count,
coalesce(new.evaluation_bad_count,0) + coalesce(old.evaluation_bad_count, 0) as evaluation_bad_count
from t1 new full join hive.bj59_yp_dm_jiale.dm_sku old on new.sku_id = old.sku_id
6.如何将最终结果覆盖到目标表中
persto来说, 不支持 insert overwrite 操作, 无法直接覆盖, 替代操作, 建议使用delete from 的语法. 将数据删除, 删除后, 通过insert into 方式将数据导入即可
主要: 不能先将旧删除数据, 因为一旦删除了, 上述的SQL无法执行
解决方案: 都需要清楚, 因为在实际中, 可能都会存在
方案一: 采用临时表方案
1) 创建一张临时表(需要提前在HIVE中创建好)
2) 将结果数据导入到临时表
3) 删除旧表中数据(persto: delete from 表)
4) 将临时表数据通过 insert into 方式导入到目标表表
5) 将临时表数据清空掉(persto: delete from 表)
方案二: 采用分区表方式
1) 改造DM层商品主题统计宽表, 将其变更为一个分区表, 分区字段为统计数据的时间字段
2) 在全量导入的时候, 将数据导入到指定的分区中
insert into dm.商品统计宽表
select *, '2022-05-09' as dt from .....
3) 进行增量的数据统计操作, 统计后, 将数据直接导入到DM层商品主题统计宽表, 放置到新的分区中:
insert into dm.商品统计宽表
select *, '2022-05-10' as dt from .....
4) 删除掉旧的分区的数据即可: (是否立即删除, 取决于业务是否需要保留前几天的统计结果)
presto: delete from 表 where dt ='2022-05-09'
这两种方案, 我们是以当前我们项目的表情况来说明的. 如果在实际生产中, 大家需要提前想好后续增量使用什么方案, 直接提前对应建好相关方案的表即可
5.RPT层实现
作用: 根据后续对接的业务需求, 从DM层中抽取出相关的数据灌入到PRT层
-
创建RPT库: hive执行
create database IF NOT EXISTS bj59_yp_rpt_xxxx;
需求一: 按月统计，各个门店的月销售单量。
-- 步骤一: 创建RPT层结果表 (HIVE中执行)
CREATE TABLE bj59_yp_rpt_xxxx.rpt_sale_store_cnt_month(
year_code string COMMENT '年code',
year_month string COMMENT '年月',
city_id string COMMENT '城市id',
city_name string COMMENT '城市name',
trade_area_id string COMMENT '商圈id',
trade_area_name string COMMENT '商圈名称',
store_id string COMMENT '店铺的id',
store_name string COMMENT '店铺名称',
order_store_cnt BIGINT COMMENT '店铺销售单量',
miniapp_order_store_cnt BIGINT COMMENT '店铺小程序销售单量',
android_order_store_cnt BIGINT COMMENT '店铺android销售单量',
ios_order_store_cnt BIGINT COMMENT '店铺ios销售单量',
pcweb_order_store_cnt BIGINT COMMENT '店铺pcweb销售单量',
sale_amt DECIMAL(38,4) COMMENT '销售收入',
mini_app_sale_amt DECIMAL(38,4) COMMENT '小程序成交额',
android_sale_amt DECIMAL(38,4) COMMENT '安卓APP成交额',
ios_sale_amt DECIMAL(38,4) COMMENT '苹果APP成交额',
pcweb_sale_amt DECIMAL(38,4) COMMENT 'PC商城成交额'
)
COMMENT '门店月销售单量排行'
-- 统计日期,不能用来分组统计
PARTITIONED BY(date_time STRING)
ROW format delimited fields terminated BY '\t'
stored AS orc tblproperties ('orc.compress' = 'SNAPPY');
-- SQL实现:
insert into hive.bj59_yp_rpt_xxx.rpt_sale_store_cnt_month
select
year_code ,
year_month ,
city_id ,
city_name ,
trade_area_id ,
trade_area_name ,
store_id ,
store_name ,
order_cnt as order_store_cnt,
miniapp_order_cnt as miniapp_order_store_cnt,
android_order_cnt as android_order_store_cnt,
ios_order_cnt as ios_order_store_cnt,
pcweb_order_cnt as pcweb_order_store_cnt,
sale_amt ,
mini_app_sale_amt ,
android_sale_amt ,
ios_sale_amt ,
pcweb_sale_amt ,
'2022-05-10' as date_time
from hive.bj59_yp_dm_xxxx.dm_sale where time_type ='month' and group_type ='store'
order by year_month desc,order_cnt desc;
需求二: 统计出总退款率最高的Top10商品。
-- 退款率: 退款的订单数量 / 总的支付的数量 = 退款率
-- 步骤一: 构建目标表
create table if not exists bj59_yp_rpt_xxxx.rpt_goods_refund_topN(
sku_id string comment '商品id',
sku_name string comment '商品名称',
refund_radio decimal(38,2) comment '最近30天各个商品的退款率'
)
comment '各商品的TOP100退款率'
-- 统计时间
PARTITIONED BY(date_time STRING)
row format delimited fields terminated by '\t'
stored as orc tblproperties('orc.compress' = 'SNAPPY');
-- 步骤二: 编写SQL 灌入目标表
insert into hive.bj59_yp_rpt_xxxx.rpt_goods_refund_topn
select
sku_id,
sku_name ,
-- 最近30天的累计的退款率
if(
payment_last_30d_count > 0,
cast(refund_last_30d_count as decimal(38,2) ) / cast(payment_last_30d_count as decimal(38,2)) ,
0
) as refund_radio,
'2022-05-10' as date_time
from hive.bj59_yp_dm_xxx.dm_sku
order by refund_radio desc limit 10;
6.数据导出操作
1.第一步: 在MySQL中创建一个用于存储结果表的库 (本地需要创建, 云端不需要构建)
CREATE DATABASE yp_olap DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
思考: 如何将HIVE中数据导出到MySQL中呢?
方案一: 通过 SQOOP 解决
方案二: 使用presto解决
presto可以对接多个数据源, 一条SQL语言可以跨越多个数据源进行操作
2.Presto整合Mysql (本地Presto集成)
第一步: 在hadoop01的presto的etc/catalog的目录下, 创建一个 mysql.properties, 并添加以下内容:
cd /export/server/presto/etc/catalog/
vim mysql.properties
输入: i
内容如下:
connector.name=mysql
connection-url=jdbc:mysql://192.168.88.80:3306?enabledTLSProtocols=TLSv1.2&useUnicode=true&characterEncoding=utf8
connection-user=root
connection-password=123456
第二步: 将hadoop01中mysql.properties发送给其他的presto节点
cd /export/server/presto/etc/catalog/
scp -r mysql.properties hadoop02:$PWD
第三步: 重启presto所有的节点
cd /export/server/presto
./bin/launcher restart
7.使用presto完成数据导出
需要通过presto在mysql中创建目标表
-- 通过presto 创建mysql的表:
CREATE TABLE .rpt_sale_store_cnt_month(
year_code varchar COMMENT '年code',
year_month varchar COMMENT '年月',
city_id varchar COMMENT '城市id',
city_name varchar COMMENT '城市name',
trade_area_id varchar COMMENT '商圈id',
trade_area_name varchar COMMENT '商圈名称',
store_id varchar COMMENT '店铺的id',
store_name varchar COMMENT '店铺名称',
order_store_cnt BIGINT COMMENT '店铺销售单量',
miniapp_order_store_cnt BIGINT COMMENT '店铺小程序销售单量',
android_order_store_cnt BIGINT COMMENT '店铺android销售单量',
ios_order_store_cnt BIGINT COMMENT '店铺ios销售单量',
pcweb_order_store_cnt BIGINT COMMENT '店铺pcweb销售单量',
sale_amt DECIMAL(38,4) COMMENT '销售收入',
mini_app_sale_amt DECIMAL(38,4) COMMENT '小程序成交额',
android_sale_amt DECIMAL(38,4) COMMENT '安卓APP成交额',
ios_sale_amt DECIMAL(38,4) COMMENT '苹果APP成交额',
pcweb_sale_amt DECIMAL(38,4) COMMENT 'PC商城成交额' ,
date_time varchar
)
COMMENT '门店月销售单量排行' ;
2- 执行灌入操作:
- 云端, 可能无法灌入, 但是本地是OK
insert into mysql_class9.class9.rpt_sale_store_cnt_month
select
*
from hive.bj59_yp_rpt_jiale.rpt_sale_store_cnt_month;
8.展示
图表是一个Javaweb的项目,采用 spring cloud, spring boot 和 mybatis 相关后台框架,构建的后台图表系统, 前端是采用 VUE和 node.js 构建前端服务, 前后端分离架构 构建的项目
此项工作, 一般来说 是不需要大数据开发工程师来处理, 主要是由JAVAWEB工程师 或者 BI工程师来实现, 我们仅仅将需要的数据导出对应数据源
当然图表也可以使用fineBI来实现
项目成果展示界面:
pc端地址: [https://yp-bigscreen-dev.itheima.net/](https://yp-bigscreen-dev.itheima.net/)
手机端: [https://yp-h5-dev.itheima.net/#/home](
体现到以下这么几个点:
项目的基本介绍(项目背景. 主要目的是什么), 项目的架构 , 项目的流转流程 , 我主要负责了那部分内容
项目架构 :
基于cloudera manager构建的大数据分析平台, 在此平台上, 搭建有 zookeeper, HDFS YARN ,HIVE, OOIZE SQOOP
同时还使用presto加快hive数据分析操作
项目的流转流程:
通过sqoop将mysql中数据导入到hive中, 在hive中对数据进行清洗转换处理工作,构建为集团数据中心, 将处理后的数据对接presto, 进行数据分析操作, 将分析后的结果, 通过presto导出到mysql, 最终通过图表进行数据展示操作, 整个统计过程是周而复始, 不断干, 所以加入oozie完成定时化自动调度工作
负责点: 只需要描述出在这一部分做的一些大致事情即可, 不需要详细描述具体流程 (主要将自己熟悉点,说出来, 便于面试官去问这一部分内容)
先说整个项目大致分为两个部分, 一个是构建集团数据中心, 一个基于主题各个主题数据集市
在集团数据中心部分: 选择起一个业务模块 或者其中两个讲解
在数据集市部分: 选择一个主题模块或者两个情况
重点关注:
DWD层:
DWB层:
DWS层:
在讲述的时候, 一定要紧密贴合项目, 具体描述出项目整个实施过程 , 万万不可描述非常空, 仅在描述层次作用
一般会问一到二个层次
此部分, 在回答的时候, 一定要往特殊的问题上说, 万万不能将一些语法性错误, 表选择性错误,比较低级错误
提供可参考问题点:
1- DWB 或者 DWS层的 Join的数据倾斜
2- DWS层 group by 数据倾斜
3- DWB层 Join 优化方案
4- DWS层 多表进行full join情况
5- DWS层 当需要多表进行多次分组情况
扫一扫
6642
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
