多思考多总结，困难问题拆分

问题：找出近一个月 发布的视频中热度最高的top3视频。

热度=(a视频完播率+b点赞数+c评论数+d转发数)*新鲜度；
新鲜度=1/(最近无播放天数+1)；
当前配置的参数a,b,c,d分别为100、5、3、2。
最近播放日期以end_time-结束观看时间为准，假设为T，则最近一个月按[T-29, T]闭区间统计。
结果中热度保留为整数，并按热度降序排序。

解题思路：

根据用户的记录求出每条记录

是否完播
是否点赞
是否评论
是否转发
当前记录的时间离最近播放（就是max(end_time)）的时间差

注意：选择近一个月发布的记录，就是max(end_time)【最近播放日期】与release_time【发布时间】的间隔不超过30天

    select 
        video_id,
        if(timestampdiff(second,start_time,end_time)>=duration,1,0) as is_wanbo,
        if_like,
        if(comment_id is null,0,1) as is_comment,
        if_retweet,
        DATEDIFF((select max(end_time) from tb_user_video_log),end_time) diff_time
    from tb_user_video_log join tb_video_info USING (video_id)
    where DATEDIFF((select max(end_time) from tb_user_video_log),release_time)<30

可以得到：

alt

根据上面的信息可以得到完播率
热度保留为整数，并按热度降序排序
选择热度前3的记录

select video_id,
    round(((sum(is_wanbo)/count(*))*100
           +5*sum(if_like)
           +3*sum(is_comment)
           +2*sum(if_retweet)
          )*(1/(1+min(diff_time)))) as hot_index
from (
    select 
        video_id,
        if(timestampdiff(second,start_time,end_time)>=duration,1,0) as is_wanbo,
        if_like,
        if(comment_id is null,0,1) as is_comment,
        if_retweet,
        DATEDIFF((select max(end_time) from tb_user_video_log),end_time) diff_time
    from tb_user_video_log join tb_video_info USING (video_id)
    where DATEDIFF((select max(end_time) from tb_user_video_log),release_time)<30
)t
group by video_id
order by hot_index desc limit 3

题解 | #近一个月发布的视频中热度最高的top3视频#

多思考多总结，困难问题拆分