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

select t.video_id,
    round(((sum(case when timestampdiff(second,start_time,end_time)>=duration then 1 else 0 end)/count(*))*100+ -- 视频完播率
        5*sum(if_like) + 3* count(comment_id) + 2*sum(if_retweet))* -- 各互动值
        (1/(datediff(@max_date,max(end_time))+1))) as hot_index -- 新鲜度
from tb_video_info t join tb_user_video_log t1 on t.video_id=t1.video_id 
,(select @max_date:=max(end_time) from tb_user_video_log)t2 -- 变量使用得到最近播放日
    where datediff(@max_date,release_time) <= 29
    group by t.video_id
    order by hot_index desc
    limit 3 -- 记得是求top3 如果严谨想使用窗口函数也可以，不过题目没有说明top3的具体规则

变量的使用
1、set @max_date=(select max(end_time) from tb_user_video_log);
2、在表连接后加入 ,(select @max_date:=max(end_time) from tb_user_video_log)t2
逗号和后面的表命名都需要，位置可以放到on后面

这个题本身不难，也是实际中常常会出现的场景，主要注意近期播放日期是动态的不是 ‘2021-10-03’ 。然后认真计算即可。

为了能更正确的计算，也可以将每个计算部分单独列出来，这样方便核算

  select t.video_id,

sum(case when timestampdiff(second,start_time,end_time)>=duration then 1 else 0 end)/count(*) as rate,

    5*sum(if_like),  3* count(comment_id) , 2*sum(if_retweet) as sum_all,

    datediff(@max_date,max(end_time)) as hot_index

from tb_video_info t join tb_user_video_log t1 using(video_id),(select @max_date:=max(end_time) from tb_user_video_log)t2

where datediff(@max_date,release_time) <= 29

group by t.video_id

order by hot_index;