基于MaxCompute构建企业用户画像（用户标签的制作）

背景：

数据说明：

基站位置信息：s_user_cell_log

CREATE TABLE s_user_cell_log (
	user_id bigint COMMENT '用户id',
	cell string COMMENT '基站id',
	phone_type string COMMENT '接入设备类型',
	latitude double COMMENT '维度',
	longitude double COMMENT '经度',
	log_time datetime COMMENT '日志时间',
	app_type bigint COMMENT 'app类型'
)
COMMENT '基站位置信息'
PARTITIONED BY (
	ds string
)
LIFECYCLE 90;

设备位置信息：s_user_device_log

CREATE TABLE s_user_device_log (
	user_id bigint COMMENT '用户id',
	imei string COMMENT 'imei',
	latitude double COMMENT '维度',
	longitude double COMMENT '经度',
	log_time datetime COMMENT '日志时间',
	app_type bigint COMMENT 'app类型'
)
COMMENT '设备位置信息'
PARTITIONED BY (
	ds string
)
LIFECYCLE 90;

网页日志信息：s_user_web_log

CREATE TABLE s_user_web_log (
	user_id bigint COMMENT '用户id',
	cookie string COMMENT 'cookieid',
	ip string COMMENT 'ip',
	url string COMMENT 'url',
	log_time datetime COMMENT '日志时间',
	app_name string COMMENT 'app类型'
)
COMMENT '网页日志信息'
PARTITIONED BY (
	ds string
)
LIFECYCLE 90;

计算逻辑：

CREATE TABLE dwd_log_addr_d (
	user_id bigint COMMENT '用户id',
	prov_id bigint COMMENT '省份id',
	city_id bigint COMMENT '城市id',
	dist_id bigint COMMENT '区域id',
	bizdate string COMMENT '日期'
)
COMMENT '用户地址基础表'
PARTITIONED BY (
	ds string
)
LIFECYCLE 90;

其中ip2region和lbs2region两个函数都需要用户自己来编写UDF来实现。

insert overwrite table dwd_log_addr_d partition (ds='${bdp.system.bizdate}') 
select 
user_id  
,prov_id 
,city_id 
,dist_id 
from (select 
       user_id
       ,ip2region(ip,'province') as prov_id
       ,ip2region(ip,'city')     as city_id
       ,ip2region(ip,'district') as dist_id
      from s_user_web_log   --用户访问网页日志信息
      where ds='${bdp.system.bizdate}'
      union all 
      select 
       user_id
       ,lbs2region(latitude,longitude,'province') as prov_id
       ,lbs2region(latitude,longitude,'city')     as city_id
       ,lbs2region(latitude,longitude,'district') as dist_id
      from s_user_cell_log   --基站信息
      where ds='${bdp.system.bizdate}'
      union all 
      select 
       user_id
       ,lbs2region(latitude,longitude,'province') as prov_id
       ,lbs2region(latitude,longitude,,'city')     as city_id
       ,lbs2region(latitude,longitude,,'district') as dist_id
      from s_user_device_log    --用户手机位置信息
      where ds='${bdp.system.bizdate}'    
     ) a 
group by user_id  
,prov_id 
,city_id 
,dist_id ;

insert overwrite table dm_tag_addr_city partition (ds='${bdp.system.bizdate}') 
select 
user_id,
city_id as addr_city
from ( select 
       user_id,
       city_id,
       ROW_NUMBER(partition by user_id order by date_cnt desc ) as rn
       from ( select 
              user_id,
              city_id,
              count(distinct dt) as date_cnt
              from dwd_log_addr_d 
              where ds<='${bdp.system.bizdate}' 
              and ds>'${bizdate_90}'
            ) t0 
      ) t1     
where t1.rn=1;

--调度参数说明
bizdate_90=${yyyymmdd-90}