互联网电商大数据环境 ——大数飓数据分析实践培训精华笔记(八)——电商核心业务知识之订单商品模块三

订单指标表计算

#!/bin/bash

#======

#dm_b2c_orders_goods.sh    这里用shell来调用SQL

#订单指标表计算

#======

DT=`date  -d  '-1 day'  "+%Y-%m-%d" ` 注意date  -d ‘-1 day’表示取前一天

或者sysdate=`date "+%Y-%m-%d"`脚本传参数法

if  [$1]; then

   DT=$1

fi

SQL="

drop table if exists temp.tmp_dm_user_order_tag_1_"${DT}" ;

create table temp.tmp_dm_user_order_tag_1_"${DT}" as

select

a.user_id,

min(a.order_date)first_order_time,-第一次消费时间

max(a.order_date)last_order_time,-最近一次消费时间

sum(a.dat_30) month1_order_cnt, -近一个月订单数

sum(case

    when dat_30=1 then a.order_money --近一个月订金额

end) month1_order_amt,

sum(a.dat_60)month2_order_cnt,

sum(case

    when dat_60=1 then a.order_money

end) month2_order_amt,

sum(a.dat_90)month3_order_cnt,

sum(case

    when dat_90=1 then a.order_money

end) month3_order_amt,

max(a.order_money)max_order_amt,

min(a.order_money)min_order_amt,

count(case

     when jt_flag=0 then

     order_id

     end) total_order_cnt,

sum(case

     when jt_flag=0 then

     a.order_money

     end) total_order_amt,  --排除拒退累计金额

count(a.order_id)total_order_cnt_1,

sum(a.order_money)total_order_amt_1,

sum(coupon_money)total_coupon_amt,

from(

select

user_id,

order_date,

order_money,

(case

when order_date>=date_sub(‘"${DT}"’,29) and

    order_date<=’ "${DT}"’ then

     1

end) dat_30,

(case

when order_date>=date_sub(‘"${DT}"’,59) and

    order_date<=’ "${DT}"’ then

     1

end) dat_60,

(case

when order_date>=date_sub(‘"${DT}"’,89) and

    order_date<=’ "${DT}"’ then

     1

end) dat_90,

(case

when a.pay_type in (‘退货’,’拒收’) then

     1

     else

     0

end)  jt_flag

from itqsc.dm_b2c_orders a

where dt=’"${DT}"’)

groupby user_id;

 

drop table if exists temp.tmp_dm_user_order_tag_2_"${DT}" ;

create table temp.tmp_dm_user_order_tag_2_"${DT}" as

select t.user_id, t.con, t.type, t.cnt

from(select

     b.user_id,

     b.con,

     b.type,

     b.cnt,

     row_number() over(distribute by b.user_id, b.type sort by b.cnt, b.type desc) rn

     from (select

a.user_id,

coalesce(area_name, ‘ ’)|| coalesce(address, ‘’) con,

‘address’ type,

count(1) cnt

from itqsc.dm_b2c_orders a

where dt=’ "${DT}"’

group by a.user_id

union all

select a.user_id,

a.pay_type con,

‘pay_type’ type,

count(1) cnt

          from itqsc.dm_b2c_orders a

where dt=’ "${DT}"’

group by a.user_id) b) t

where t.rn=1;

 

drop table if exists temp.tmp_dm_user_order_tag_3_"${DT}" ;-加时间是为并行的

create table temp.tmp_dm_user_order_tag_3_"${DT}" as

selectuser_id

     count(1) month1_cart_cnt, -近30天购物车次数

     sum(number) month1_cart_goods_cnt, -近30天购物商品件数

     sum(case

         when submit_time is not null thennumber

         end) month1_cart_submit_cnt, -近30天购物车提交商品件数

     ‘’ month1_cart_rate, -近30天购物车成功率

     sum(case

         when cancel_time is not null then

         number

         end) month1_cart_cancel_cnt, -近30天购物车放弃商品件数

fromitqsc.ods_b2c_cart

wheredt=’ "${DT}"’

andto_date(add_time) >= date_sub(’"${DT}"’,29)

andto_date(add_time) <=’"${DT}"’

groupby user_id;

)

 

 

drop table if exists temp.tmp_dm_user_order_tag_4_"${DT}" ;

create table temp.tmp_dm_user_order_tag_4_"${DT}" as

 

selecta.user_id

 from (select user_id

from temp.tmp_dm_user_order_tag_1_"${DT}")

union all

select user_id from temp.tmp_dm_user_order_tag_3_"${DT}") a

group by a.user_id;

 

insert overwrite tableitqsc.dm_b2c_orders partition (dt=' "${DT}" ')

select

a.user_id,--用户

a.first_order_time,-第一次消费时间

a.last_order_time,-最近一次消费时间

date_diff(‘"${DT}"’, a. first_order_time) first_order_ago, --首单距今时间

date_diff(‘"${DT}"’, a. last_order_time) last_order_ago, --尾单距今时间

a.month1_order_cnt,-近30天购买次数

a.month1_order_amt,-近30天购买金额

a.month2_order_cnt,-近60天购买次数

a.month2_order_amt,-近60天购买金额

a.month3_order_cnt,-近90天购买次数

a.month3_order_amt,-近90天购买金额

a.max_order_amt,-最大消费金额

a.min_order_amt,-最小消费金额

a.total_order_cnt,-累计消费次数（不含退拒）

a.total_order_amt,-累计消费金额（不含退拒）

a.total_coupon_amt,-累计使用代金券金额

(case

 when a.total_order_cnt_1=0 ora.total_order_cnt_1 is null then

 0

 else

a.total_order_amt_1/a.total_order_cnt_1

end) user_avg_amt, -客单价（含退拒）

(case

 when a.month3_order_cnt_1=0 then

 0

 else

a.month3_order_amt/ a.month3_order_cnt

end) month3_user_avg_amt,-近90天客单价（含退拒）

b.concommon_address, --常用收货地区

c.concommon_paytype, --常用支付方式

d.month1_cart_cnt,--最近30天购物车次数

d.month1_cart_goods_cnt,--最近30天购物车商品件数

d.month1_cart_submit_cnt,--最近30天购物车提交商品件数

(case

 when d.month1_cart_goods_cnt=0 ord.month1_cart_goods_cnt is null then

 0

 else

d.month1_cart_submit_cnt /d.month1_cart_goods_cnt

end)month1_cart_rate, --最近30天购物车成功率

d.month1_cart_cancel_cnt,--最近30天购物车放弃件数

sysdate()dw_date

from temp.tmp_dm_user_order_tag_4_”${DT}” t

join temp.tmp_dm_user_order_tag_1_”${DT}” a

on(t.user_id=b.user_id)

join(select user_id, con

fromtemp.tmp_dm_user_order_tag_2_”${DT}”

where type=’address’) b

on(t.user_id=b.user_id)

join(select user_id, con

fromtemp.tmp_dm_user_order_tag_2_”${DT}”

where type=’pay_type’) c

on(t.user_id=c.user_id)

jointemp.tmp_dm_user_order_tag_3_”${DT}” d

on(t.user_id=b.user_id);

"

echo  "${SQL}"

hive –e  "$SQL"