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

#第二步：计算热度
select video_id,
round(hot_cnt*1/(date_diff+1),0) as hot_index
from
    #第一步：找出每个视频最近一次播放日期，和所有视频的最后播放日期作差，构造一个新的列;同时计算热度的分子作为一个新的列
    (select a.video_id,
    sum(if(timestampdiff(second,a.start_time,a.end_time) >= duration,1,0))/count(a.video_id)*100+5*sum(if_like)+3*count(comment_id)+2*sum(if_retweet) as hot_cnt,
    datediff((select max(end_time) from tb_user_video_log),max(a.end_time)) as date_diff
    from tb_user_video_log a
    left join tb_video_info b
    on a.video_id=b.video_id
    where datediff((select max(end_time) from tb_user_video_log),b.release_time)<=29#记得限制播放时间相对于最近播放时间在一个月内
    group by a.video_id) t
order by hot_index desc
limit 3