drop table if exists gdm.gdm_user_portrait_shop;
CREATE EXTERNAL TABLE gdm.gdm_user_portrait_shop (
user_id bigint ,--用户
shop_id bigint ,--商店ID
shop_name string ,--商店名称
brand_id bigint ,--品牌ID
brand_name string ,--品牌名称
month1_cart_cnt bigint ,--最近30天购物车次数
month1_cart_goods_cnt bigint ,--最近30天购物车商品件数
month1_cart_submit_goods_cnt bigint ,--最近30天购物车提交商品件数
month1_cart_rate double ,--最近30天购物车成功率
month1_cart_cancnt_cnt bigint ,--最近30天购物车放弃商品件数
lastst_cart_time timestamp ,--最近一次购物车时间
month3_valid_goods_cnt bigint ,--最近90天排除退拒商品件数
month3_valid_goods_amt double ,--最近90天排除退拒商品金额
month3_sales_cnt bigint ,--最近90天购买订单数(含退拒)
month3_cod_cnt bigint ,--最近90天货到付款订单数
month3_return_goods_cnt bigint ,--最近90天退货件数
month3_reject_goods_cnt bigint ,--最近90天拒收件数
month3_return_goods_amt double ,--最近90天退货商品金额
month3_reject_goods_amt double ,--最近90天拒收商品金额
latest_order_date timestamp ,--最后一次排除退拒订单时间
latest_goods_cnt bigint ,--最后一次排除退拒订单购买商品件数
latest_goods_amt double ,--最后一次排除退拒订单购买商品金额
latest_return_time timestamp ,--最后一次退货时间
latest_reject_time timestamp ,--最后一次拒收时间
dw_date timestamp ,
)partitioned by (dt string)
;
--用户画像--客户购买商店表
drop table if exists gdm.gdm_user_portrait_shop;
CREATE EXTERNAL TABLE gdm.gdm_user_portrait_shop (
user_id bigint ,--用户
shop_id bigint ,--商店ID
shop_name string ,--商店名称
brand_id bigint ,--品牌ID
brand_name string ,--品牌名称
month1_cart_cnt bigint ,--最近30天购物车次数
month1_cart_goods_cnt bigint ,--最近30天购物车商品件数
month1_cart_submit_goods_cnt bigint ,--最近30天购物车提交商品件数
month1_cart_rate double ,--最近30天购物车成功率
month1_cart_cancnt_cnt bigint ,--最近30天购物车放弃商品件数
lastst_cart_time timestamp ,--最近一次购物车时间
month3_valid_goods_cnt bigint ,--最近90天排除退拒商品件数
month3_valid_goods_amt double ,--最近90天排除退拒商品金额
month3_sales_cnt bigint ,--最近90天购买订单数(含退拒)
month3_cod_cnt bigint ,--最近90天货到付款订单数
month3_return_goods_cnt bigint ,--最近90天退货件数
month3_reject_goods_cnt bigint ,--最近90天拒收件数
month3_return_goods_amt double ,--最近90天退货商品金额
month3_reject_goods_amt double ,--最近90天拒收商品金额
latest_order_date timestamp ,--最后一次排除退拒订单时间
latest_goods_cnt bigint ,--最后一次排除退拒订单购买商品件数
latest_goods_amt double ,--最后一次排除退拒订单购买商品金额
latest_return_time timestamp ,--最后一次退货时间
latest_reject_time timestamp ,--最后一次拒收时间
dw_date timestamp ,
)partitioned by (dt string)
;
#********************************************************
#
#文件名称:gdm_user_portrait_shop.sql
#
#********************************************************
drop table if exists temp.tmp_gdm_user_portrait_shop_01;
CREATE TABLE temp.tmp_gdm_user_portrait_shop_01 as
select a.user_id,
a.shop_id,
a.shop_name,
a.brand_id,
a.brand_name,
sum(case
when a.dat_90 = 1 then
b.goods_amount
end) month3_valid_goods_cnt,
sum(case
when a.dat_90 = 1 then
coalesce(b.goods_price,0)* coalesce(b.goods_amount,0)
end) month3_valid_goods_amt,
count(distinct case
when a.dat_90 =1 then
a.order_id
end) month3_sales_cnt,
count(distinct case
when a.dat_90 =1 and order_type = 1 then
a.order_id
end) month3_cod_cnt,
sum(case
when a.dat_90 =1 and a.order_status = 3 then
b.goods_amount
end) month3_return_goods_cnt,
sum(case
when a.dat_90 =1 and a.order_status = 4 then
b.goods_amount
end) month3_reject_goods_cnt,
sum(case
when a.dat_90 = 1 and a.order_status =3 then
coalesce(b.goods_price,0)* coalesce(b.goods_amount,0)
end) month3_return_goods_amt,
sum(case
when a.dat_90 = 1 and a.order_status =4 then
coalesce(b.goods_price,0)* coalesce(b.goods_amount,0)
end) month3_reject_goods_amt,
max(case
when a.rn = 1 then
a.order_date
end) latest_order_date,
max(case
when a.rn = 1 then
b.goods_amount
end) latest_goods_cnt,
max(case
when a.rn = 1 then
coalesce(b.goods_price,0)*coalesce(b.goods_amount,0)
end) latest_goods_amt,
max(case
when a.order_status in (3) then
a.create_time
end) latest_return_time,
max(case
when a.order_status in (4) then
a.create_time
end) latest_reject_time
from(select a.*,
(case
when order_date >= date_sub('2015-01-01',29) and
order_date <= '2015-01-01' then
1
end) dat_30,
case
when order_date >= date_sub('2015-01-01',59) and
order_date <= '2015-01-01' then
1
end) dat_60,
case
when order_date >= date_sub('2015-01-01',89) and
order_date <= '2015-01-01' then
1
end) dat_90,
(case
when a.order_status in (3,4) then
1
else
0
end) jt_flag, --退货与拒收
hour(create_time) order_hour,
row_number() over(distribute by a.user_id sort by a.create_time desc)
from gdm.gdm_ord_order a
where dt = '2015-01-01'
and order_status =5) a
left join(select * from fdm.fdm_ord_order_goods where dt = '2015-01-01') b
on(a.order_id = b.order_id)
group by a.user_id,a.shop_id,a.shop_name,a.brand_id,a.brand_name;
drop table if exists temp.tmp_gdm_user_portrait_shop_02;
CREATE TABLE temp.tmp_gdm_user_portrait_shop_02 as
select a.user_id,
b.shop_id,
b.shop_name,
b.brand_id,
b.brand_name,
count(1) month1_cart_cnt, --最近30天购物车次数
sum(number) month1_cart_goods_cnt, --最近30天购物车商品件数
sum(case
when submit_time is not null then
number
end) month1_cart_submit_cnt, --最近30天购物车提交商品件数
'' month1_cart_rate, --最近30天购物车成功率
sum(case
when cancel_time is not null then
number
end) month1_cart_cancnt_cnt, --最近30天购物车放弃商品件数
max(add_time) lastst_cart_time
from (select *
from fdm.fdm_ord_cart
where dt = '2015-01-01'
and to_date(add_time) >= date_sub('2015-01-01',179)
and to_date(add_time) <= '2015-01-01') a
left join (select goods_id,shop_id,shop_name,brand_id,brand_name
from fdm.fdm_prd_goods
where dt = '2015-01-01'
group by goods_id,shop_id,shop_name,brand_id,brand_name) b
on (a.goods_id=b.goods_id)
group by a.user_id,b.shop_id,b.shop_name,b.brand_id,b.brand_name;
drop table if exists temp.tmp_gdm_user_portrait_shop_99;
CREATE TABLE temp.tmp_gdm_user_portrait_shop_99 as
select user_id,shop_id,shop_name,brand_id,brand_name
from (select user_id,shop_id,shop_name,brand_id,brand_name
from temp.tmp_gdm_user_portrait_shop_01
union all
select user_id,shop_id,shop_name,brand_id,brand_name
from temp.tmp_gdm_user_portrait_shop_02) a
group by user_id,shop_id,shop_name,brand_id,brand_name;
insert overwrite table gdm.gdm_user_portrait_shop partition(dt ='2015-01-01')
select
t.user_id,
t.shop_id,
t.shop_name,
t.brand_id,
t.brand_name,
t2.month1_cart_cnt,
t2.month1_cart_goods_cnt,
t2.month1_cart_submit_cnt month1_cart_submit_goods_cnt,
(case when t2.month1_cart_goods_cnt<> 0 then t2.month1_cart_submit_cnt/t2.month1_cart_submit_cnt else 0 end) month1_cart_rate
t2.month1_cart_cancnt_cnt,
t2.latest_cart_time,
t1.month3_valid_goods_cnt,
t1.month3_valid_goods_amt,
t1.month3_sales_cnt,
t1.month3_cod_cnt,
t1.month3_return_goods_cnt,
t1.month3_reject_goods_cnt,
t1.month3_return_goods_amt,
t1.month3_reject_goods_amt,
t1.latest_order_date,
t1.latest_goods_cnt,
t1.latest_goods_amt,
t1.latest_return_time,
t1.latest_reject_time,
from_unixtime(unix_timestamp()) dw_date
from temp.tmp_gdm_user_portrait_shop_99 t
left join temp.tmp_gdm_user_portrait_shop_01 t1
on(t.user_id =t1.user_id and t.shop_id =t1.shop_id and t.brand_id=t1.brand_id)
left join temp.tmp_gdm_user_portrait_shop_02 t2
on(t.user_id =t2.user_id and t.shop_id =t2.shop_id and t.brand_id=t2.brand_id)