与数仓-ADS-各地区订单统计结构基本一致
GMV(商品交易总额):在电商网站定义里面是网站成交金额。这个实际指的是拍下订单金额, 包含付款和未付款的部分。
insert overwrite table ads_order_total
select * from ads_order_total
union
select
'2021-06-14',
recent_days,
sum(order_count) order_count,
sum(order_final_amount) order_final_amount,
sum(if(order_count>0,1,0)) order_user_count
from
(
select
recent_days,
user_id,
case
when recent_days=0 then order_count
when recent_days=1 then order_last_1d_count
when recent_days=7 then order_last_7d_count
when recent_days=30 then order_last_30d_count
end order_count,
case
when recent_days=0 then order_final_amount
when recent_days=1 then order_last_1d_final_amount
when recent_days=7 then order_last_7d_final_amount
when recent_days=30 then order_last_30d_final_amount
end order_final_amount
from dwt_user_topic
lateral view explode(Array(1,7,30)) tmp as recent_days
where dt='2021-06-14'
)t1
group by recent_days;