用户画像之基本属性信息

#!/bin/sh

#线上线下会员手机号
allMobileSql="
select distinct x_cellphone from
(
select x_cellphone from sourcedata.s_loy_member
union all
select mobile as x_cellphone from sourcedata.def_members 
)temp
";

#会员最近时间的极光id
jgSql="
select user_id,reg_id from(
select a.*,row_number() over(partition by a.user_id order by a.last_time desc) as rn from(
select user_id,reg_id,max(create_time) last_time from sourcedata.device_mapping group by user_id,reg_id)a)t where rn<=1
"

#线上会员信息
onlineSql="
select d.id,d.vipcard_no,d.mobile,d.regtime,s.internal,b.channel,b.device_id,b.phone_brand,b.phone_model,b.gps_adress, t.type,m.reg_id
from sourcedata.def_members d left join sourcedata.distributor t on d.id=t.user_id left join sourcedata.def_member_siebel_mem s on d.id=s.member_id
 left join 
 ($jgSql) m on d.id=m.user_id
left join 
(
 select distinct a.device_id,a.uid,a.channel,a.phone_brand,a.phone_model,a.gps_adress from tracker.base_info a inner join
 (select uid,max(client_time) ct from tracker.base_info where uid is not null group by uid) temp 
 where a.uid=temp.uid and a.client_time=temp.ct 
) b on d.id=cast(b.uid as bigint) 
";

#线下会员信息
offlineSql="
select m.x_cellphone,c.x_vipcardno,m.created,x.attrib_34,x.attrib_26,cx.mrgstatus,m.reg_channel_cd
from sourcedata.s_loy_member m left join sourcedata.s_loy_card c on m.row_id=c.member_id
left join sourcedata.s_loy_member_x x on m.row_id=x.par_row_id
left join sourcedata.cx_member_x cx on m.row_id=cx.par_row_id
";

#生成中间表-基础信息
memberBaseInfoSql="
use tmp;
drop table if exists tmp.member_base_info;
create table tmp.member_base_info as
select o.id as user_id, (case when o.vipcard_no is not null then o.vipcard_no else f.x_vipcardno end) vipcardno,
t.x_cellphone as mobile,
(case length(regexp_extract(t.x_cellphone, '^((1[358][0-9])|(14[57])|(17[0678])|(19[7]))\\d{8}$',0)) when 11 then '是' else '否' end) is_valid,
o.device_id,
o.reg_id,
from_unixtime(o.regtime) as online_regtime,from_unixtime(unix_timestamp(f.created) + 28800) as offline_regtime,
from_unixtime(least(o.regtime,unix_timestamp(f.created) + 28800)) as regtime,
f.attrib_34 as sex,f.attrib_26 as birthday,o.channel,o.phone_brand,o.phone_model,
(case o.type when 2 then 1 else 0 end) as is_distributor,
(case o.internal when 1 then 1 else 0 end) as is_internal,
f.mrgstatus,
0 as has_children,
(case when o.mobile is not null and f.x_cellphone is not null then 1 when f.x_cellphone is not null then 2 end) as member_type,
1 as buyer_type,
datediff(from_unixtime(unix_timestamp()), from_unixtime(least(o.regtime,unix_timestamp(f.created) + 28800))) as regdays,
(case 
 when f.reg_channel_cd  in('门店','潜在会员转入','手工录入' ,'扫门店码入会') then '门店' 
 when f.reg_channel_cd ='来伊份APP' then '来伊份APP'
 when f.reg_channel_cd  in('天猫','天猫旗舰店入会','微信') then '第三方'
 when f.reg_channel_cd  in('宝钢系统', '官网','后台',null) then '其他'
 end
) reg_source,
'' as reg_source_online,
(case when o.gps_adress is not null then o.gps_adress else f.reg_channel_cd end) as province
from
($allMobileSql)t
left join
($onlineSql)o
on t.x_cellphone=o.mobile
left join
($offlineSql)f on t.x_cellphone=f.x_cellphone
";

hive -e "$memberBaseInfoSql"

#还有实名认证／买家信息／省份／注册来源几个中间表信息未作处理，最后处理