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

1.问题：

找出近一个月发布的视频中热度最高的top3视频。结果中热度保留为整数，并按热度降序排序。

2.思路：

（1）热度最高 粒度：视频id+最近一个月

热度=(100视频完播率+5点赞数+3评论数+2转发数)*新鲜度

视频完播率：

sum(if(end_time-start_time-duration>=0,1,0))/count(video_id)

点赞数：

sum(if_like)

评论数：

sum(if(comment_id is not null,1,0))

转发数：

sum(if_retweet)

新鲜度=1/(最近无播放天数+1)

以2004为例，9-5发布， 当前日期

 select max(end_time) from tb_user_video_log

当播放次数为0时，最近无播放天数=当前日期-发布日期；

当播放次数不为0时，最近无播放天数=当前日期-最近一次播放日期

 if(count(video_id)=0,date(select max(end_time) from tb_user_video_log)-release_time,
    date(select max(end_time) from tb_user_video_log)-max(date(end_time)))

（2）top3视频

limit函数：limit 0,3

（3）近一个月 排序窗口函数 detediff(最新日期-发布日期)<=29

3.解题思路

SELECT video_id,
       round((100*finish_rate+5*like_index+3*comment_index+2*retweet_index)/(fresh_index+1),0) hot_index
FROM(
SELECT a.video_id,
       sum(if(timestampdiff(second,a.start_time,a.end_time)-b.duration>=0,1,0))/count(a.video_id) finish_rate,
       sum(a.if_like) like_index,
       sum(if(a.comment_id is not null,1,0)) comment_index,
       sum(a.if_retweet) retweet_index,
       if(count(a.video_id)=0,datediff(date((select max(end_time) from tb_user_video_log)),date(b.release_time)),
          datediff(date((select max(end_time) from tb_user_video_log)),max(date(a.end_time)))) fresh_index
FROM tb_user_video_log a
    left join tb_video_info b
    on a.video_id=b.video_id
where DATEDIFF(date((select max(end_time) from tb_user_video_log)),DATE(b.release_time))<=29
group by a.video_id) fir_sheet
order by hot_index DESC
limit 0,3