基於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}