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

select video_id,
#        sum(if(timestampdiff(second, start_time, end_time) >= duration, 1, 0)) / count(1) '视频完播率',
#        sum(if_like) '点赞数',
#        count(comment_id) '评论数',
#        sum(if_retweet) '转发数',
#        if(count(video_id) = 0,
#           datediff(date((select max(end_time) from tb_user_video_log)), date(release_time)),
#           datediff(date((select max(end_time) from tb_user_video_log)), max(date(end_time)))) '最近无播放天数',
        round(
      ( sum(if(timestampdiff(second, start_time, end_time) >= duration, 1, 0)) / count(1) * 100         -- 视频完播率 * a (a=100)
      + sum(if_like) * 5                                                                                -- 点赞数 * b (b=5)
      + count(comment_id) * 3                                                                           -- 评论数 * c (c=3)
      + sum(if_retweet) * 2                                                                             -- 转发数 * d (d=2)
      ) * ( 1 / ( if(count(video_id) = 0,                                                               -- 出题人埋坑,考虑最近无播放记录的,以上传日期截止计算天数
          datediff(date((select max(end_time) from tb_user_video_log)), date(release_time)),
          datediff(date((select max(end_time) from tb_user_video_log)), max(date(end_time)))) + 1 ) )   -- 新鲜度 = 1 / (最近无播放天数 + 1)
      , 0)  as hot_index                                                                                -- 热度=(a*视频完播率+b*点赞数+c*评论数+d*转发数)*新鲜度
from tb_user_video_log t1
    left join tb_video_info using (video_id)
where date(release_time) >= (select date_sub(date(max(end_time)), interval 29 day) today_dt from tb_user_video_log)
group by video_id
order by hot_index desc
limit 3;