新零售数仓项目实战（二）

由于数据不足，向us_order表中插入数据

INSERT INTO snbap_ods.us_order(order_no,user_id,user_name,order_money,order_type,order_status,pay_status,pay_type,update_time) 
SELECT order_no,user_id,user_name,ROUND(RAND()*100,2),ROUND(RAND()),ROUND(RAND()*2),ROUND(RAND()),ROUND(RAND()*2),CURRENT_TIMESTAMP() 
FROM snbap_ods.us_order;

order_type 0线下订单 1线上订单
order_status 0已成交 1已收货 2已取消
pay_type 0现金支付 1第三方支付 2银行卡/网银支付
pay_status 0未支付 1已支付
查看支付状态（其他状态修改相应sql语句即可）

select count(1),pay_status from us_order group by pay_status;

–首单、末单及距今时间

select user_id,min(order_date) first_order_date,max(order_date) last_order_date,
datediff(current_date(),min(order_date)) first_order_days,
datediff(current_date(),max(order_date)) last_order_days
from snbap_ods.us_order group by user_id;

–首单、末单时间及距今时间及近30天-90天含退拒的订单数量、金额不含退拒的订单数量、金额

create table snbap_dw.user_order_info as
select user_id,min(order_date) first_order_date,max(order_date) last_order_date,
datediff(current_date(),min(order_date)) first_order_days,
datediff(current_date(),max(order_date)) last_order_days,
sum(case when order_status <> 2 and order_date between date_sub(current_date(),30) 
and current_date() then 1 else 0 end) order_cnt_30,
sum(case when order_status <> 2 and order_date between date_sub(current_date(),30) 
and current_date(