用户画像系列——推荐相关核心标签(偏好类)

select userid,content_tag,sum(play_time) as total_time,max(last_play_time) as last_play_time from dws_user_play_info_tag group by userid,content_tag

INSERT OVERWRITE TABLE dws_user_play_weight_info PARTITION(dt='${current_date}')
select userid, id, sum(feature_value) as feature_value
from (
select
userid,   --用户id
id,    --电视剧或者电影id
exp(-1 * cast(datediff('${current_date}', dt) / 7 as int) * 7 / (28 * 3 / 2.0))
* 1.0 / ( 1.0 + exp(-0.01 * ( playtime - 400 ))) as weight
from dwd_user_play_info
where dt between '${current_date-28}' and '${current_date}'   ---28天一个周期
union all
-- 4周之前
select userid, id,
exp(-1 * 28 / (28 * 3 / 2.0))* weight as weight --衰减
from dws_user_play_weight_info
where dt='${current_date-29}'
)T2
group by userid, id
having sum(weight) > 0.001
distribute by userid;

select userid, tag, weight
from (
select userid, id, weight, row_number() over(partition by userid order by weight desc) as rn
from (
select
userid,
id,
2 / ( 1 + exp(-0.5 * (sum(s1) + sum(s2)) )) - 1 as weight
from (
-- 2. 近期观影
select
A.account_id,
B.tag,
0 as s1,
A.weight * B.weight as s2,
1 as s3
from (
select id, tag, weight  ---weight都为1，后续可给标签加上权重代表该标签重要性较强
from dim_content_tag_info ---内容标签打平之后的表
)B
inner join (select * from dws_user_play_weight_info  ---用户观影权重表
where dt='${current_date}') A
on B.id =A.id
)T1
group by userid, tag
)T2
)T3
where rn <= 50 and weight > 0.01;