通过代码

DATE_SUBselect video_id,
       round((100 * avg_rate + 5 * likes + 3 * comments + 2 * retweets) * (1 / (end_time + 1)), 0) hot_index
from (SELECT u.video_id                                                                                   video_id,
             (sum(if((TIMESTAMPDIFF(SECOND, start_time, end_time) >= duration), 1, 0)) / COUNT(*))        avg_rate,
             sum(if_like)                                                                                 likes,
             count(comment_id)                                                                            comments,
             sum(if_retweet)                                                                              retweets,
             timestampdiff(day, date(max(end_time)), (select date(max(end_time)) from tb_user_video_log)) end_time
      FROM tb_user_video_log u
               JOIN
           tb_video_info i
           ON
               u.video_id = i.video_id
      WHERE date(release_time) between
                DATE_SUB((select max(end_time) from tb_user_video_log), INTERVAL 30 day)
                and (select max(end_time) from tb_user_video_log)
      GROUP BY u.video_id
     ) t
order by hot_index desc
limit 3

思路

先上题目要求

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

注：

热度=(a视频完播率+b点赞数+c评论数+d转发数)新鲜度；

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

当前配置的参数a,b,c,d分别为100、5、3、2。

最近播放日期以end_time-结束观看时间为准，假设为T，则最近一个月按[T-29, T]闭区间统计。

结果中热度保留为整数，并按热度降序排序。

首先是视频完播率，这个我们之前做过就是这一类的第一题，这里就不重复了，

然后点赞数、转发数都很简单

问题是评论数：这里给出的数据是comment_id ，后来想想发现完全没问题：一条记录是一次播放，所以最多一个评论，没毛病。

然后是新鲜度：1/整个表最后一个播放时间，距离该视频最后一次播放时间天数+1

但是这里有问题，而且以前我也遇到过这个问题，连续两次作答试卷的最大时间窗

就是timestampdiff或者出题人的锅！

最近30天，我用的函数DATE_SUB

DATE_SUB() 函数从日期减去指定的时间间隔。

语法

DATE_SUB(date,INTERVAL expr type)

date 参数是合法的日期表达式。expr 参数是希望添加的时间间隔。