hive语句练习—用户订单分布

用户订单分布
按照用户id进行分组，统计订单时间以及地域的分布情况
订单指标:

第一次下单时间，
最近一次下单时间，
首单距今时间，
尾单距今时间，

近30天订单数量（不含退拒
近30天订单金额（不含退拒)
近60天订单数量（不含退拒)
近60天订单金额（不含退拒)
近90天订单数量（不含退拒)
近90天订单金额(不含退拒)
近30天订单数量（含退拒)
近30天订单金额(含退拒)
近60天购买次数(含退拒)
近60天购买金额(含退拒)
近90天购买次数（含退拒)
近90天购买金额(含退拒)
近90天的客单价(含退拒)

最大消费金额
最小消费金额
累计消费次数(不含退拒)
累计消费金额（不含退拒)
客单价(含退拒)
最常用收货地址
最常用支付方式
退货商品数量
退货商品金额
拒收商品数量
拒收商品金额
最近一次退货时间

create table snbap_dwd.order_analyze as with
r1 as (
select
user_id,
min(order_date) first_order,--第一次下单时间
max(order_date) last_order,--最近一次下单时间
datediff(current_date(),min(order_date)) first_timediff,--首单距今时间
datediff(current_date(),max(order_date)) last_timediff--尾单距今时间
from snbap_ods.us_order group by user_id
),
r2 as (
select
user_id,
count(case when order_date between date_sub(current_date(),30) and date_sub(current_date(),1) then 1 end) order_30_cnt,--近30天订单数量
sum(case when order_date between date_sub(current_date(),30) and date_sub(current_date(),1) then order_money else 0 end) order_30_money,--近30天订单金额
count(case when order_date between date_sub(current_date(),60) and date_sub(current_date(),1) then 1 end) order_60_cnt,--近60天订单数量
sum(case when order_date between date_sub(current_date(),60) and date_sub(current_date(),1) then order_money else 0 end) order_60_money,--近60天订单金额
count(case when order_date between date_sub(current_date(),90) and date_sub(current_date(),1) then 1 end) order_90_cnt,--近90天订单数量
sum(case when order_date between date_sub(current_date(),90) and date_sub(current_date(),1) then order_money else 0 end) order_90_money,--近90天订单金额
sum(case when order_date between date_sub(current_date(),90) and date_sub(current_date(),1) then order_money else 0 end) order_90_avg,--近90天客单价
max(order_money) max_money,--最大消费金额
min(order_money) min_money,--最小消费金额
count(order_money) cnt_order,--累计消费次数
sum(order_money) sum_order,--累计消费金额
avg(order_money) avg_order--客单价
from snbap_ods.us_order group by user_id
),
r3 as (
select user_id,order_addr favor_addr from (--最常用收货地址
select user_id,order_addr,row_number() over(partition by user_id order by cnt desc) rn from (
select u.user_id user_id,a.order_addr order_addr,count(1) cnt
from snbap_ods.us_order u join snbap_ods.user_addr a on u.order_id=a.arear_id group by u.user_id,a.order_addr) tab1) tab2 where rn=1
),
r4 as (
select user_id,pay_type favor_pay from (--最常用支付方式
select user_id,pay_type,row_number() over(partition by user_id order by cnt desc) rn from (
select user_id,pay_type,count(1) cnt
from snbap_ods.us_order group by user_id,pay_type) tab1) tab2 where rn=1
),
r5 as (
select
user_id,
count(order_money) back_cnt,--退货商品数量
sum(order_money) back_money--退货商品金额
from snbap_ods.us_order where order_status=3 group by user_id
),
r6 as (
select
user_id,
count(order_money) refuse_cnt,--拒收商品数量
sum(order_money) refuse_money--拒收商品金额
from snbap_ods.us_order where order_status=4 group by user_id
),
r7 as (
select u.user_id user_id,
count(case when a.user_order_flag=3 then 1 end) home_cnt,
count(case when a.user_order_flag=2 then 1 end) dep_cnt,
count(case when a.user_order_flag=1 then 1 end) school_cnt,
count(case when hour(order_date) between 0 and 5 then 1 end) morning_cnt,
count(case when hour(order_date) between 6 and 12 then 1 end) am_cnt,
count(case when hour(order_date) between 13 and 15 then 1 end) noon_cnt,
count(case when hour(order_date) between 16 and 20 then 1 end) pm_cnt,
count(case when hour(order_date) between 21 and 24 then 1 end) eve_cnt
from snbap_ods.us_order u join snbap_ods.user_addr a on u.order_id=a.arear_id group by u.user_id
)
select
r1.user_id,first_order,last_order,first_timediff,last_timediff,order_30_cnt,order_30_money,order_60_cnt,order_60_money,order_90_cnt,order_90_money,order_90_avg,
max_money,min_money,cnt_order,sum_order,avg_order,favor_addr,favor_pay,back_cnt,back_money,refuse_cnt,refuse_money
from r1 left join r2 on r1.user_id=r2.user_id left join r3 on r1.user_id=r3.user_id left join r4 on r1.user_id=r4.user_id 
left join r5 on r1.user_id=r5.user_id left join r6 on r1.user_id=r6.user_id left join r7 on r1.user_id=r7.user_id;