用御膳房的大数据处理平台提供的Sql处理
我负责统计的特征:
用户id,总点击量,总收藏量,总添加购物车量,总购买量,用户活跃天数/抽样时间长度,发生购买天数/抽样时间长度,总点击量/总购买,总收藏/总购买,总购物车/总购买,发生购买天数/用户活跃天数,用户活跃天数,发生购买天数
初步没有限定统计窗口,选取全部30天的数据
Sql代码如下:
create table user_All4_actday as
select user_id,
sum(case when behavior_type='1' then 1 else 0 end) as click,
sum(case when behavior_type='2' then 1 else 0 end) as SC,
sum(case when behavior_type='3' then 1 else 0 end) as Shop_cart,
sum(case when behavior_type='4' then 1 else 0 end) as pur,
count(distinct case when behavior_type='1' then substr(time,1,10) else NULL end) as Actday,
count(distinct case when behavior_type='4' then substr(time,1,10) else NULL end) as Purday
from tianchi_lbs.tianchi_mobile_recommend_train_user
group by user_id