MySQL面试题：近一个月发布的视频中热度最高的top3视频

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

题目：现有用户-视频互动表tb_user_video_loguid-用户ID, video_id-视频ID, start_time-开始观看时间, end_time-结束观看时间, if_follow-是否关注, if_like-是否点赞, if_retweet-是否转发, comment_id-评论ID）

短视频信息表tb_video_info（video_id-视频ID, author-创作者ID, tag-类别标签, duration-视频时长, release_time-发布时间）

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

注：

• 热度=(a视频完播率+b点赞数+c评论数+d转发数)*新鲜度；
• 新鲜度=1/(最近无播放天数+1)；
• 当前配置的参数a,b,c,d分别为100、5、3、2。
• 最近播放日期以end_time-结束观看时间为准，假设为T，则最近一个月按[T-29, T]闭区间统计。
• 结果中热度保留为整数，并按热度降序排序。

这是牛客网上的一道sql题

1.题目的几点注意项：

1. 日期的计算

   因为是近一个月，所以要有日期的计算得到近一个月，说明也给出了，T-29，其方法是：datediff(date1,date2)

2. 时间的计算

   因为需要判断是否把视频看完了，要比较播放的时间是否大于或等于视频时长，其方法是：timestampdiff(time1,time2)

3. 分组找到top3

   需要找到近一个月热度高的top3，所以要按照video_id分组，热度排序，取前三

4. 评论的内容如果有则是有评论，如果没有就不会有评论，所以使用方法：if(comment_id is not null,1,0)。这里注意is not null 和 <> null 的区别

2.需要计算的值：

1. 整个表中最早的日期

   select max(end_time) from tb_user_video_log
   

2. 每个video_id的最近日期

   select max(end_time) from tb_user_video_log group by video_id
   

3. 最近无播放日期

   datediff(select max(end_time) from tb_user_video_log, end_time) from tb_user_video_log group by video_id
   

4. 近一个月

   datediff(select max(end_time) from tb_user_video_log, release_time)<=29
   

3.逻辑过程：

1. 首先划定范围，where 语句应该是在近一个月的视频里

2. 然后求得计算热度需要的各个数值，点赞，评论，转发，观看完整度，新鲜度

3. 根据上一步的数值，计算出热度，然后倒叙取前三

4.代码：

SELECT
  video_id,
  ROUND((100 * finished_rate 
   + 5 * like_cnt 
   + 3 * comment_count 
   + 2 * retweet_cnt) / (unfinished_day_cnt + 1)) hot_index
FROM (
  SELECT
    u.video_id,
    SUM(TIMESTAMPDIFF(second, start_time, end_time) >= duration) / COUNT(1) finished_rate,
    SUM(if_like) like_cnt,
    SUM(IF(comment_id IS NOT NULL, 1, 0)) comment_count,
    SUM(if_retweet) retweet_cnt,
    DATEDIFF((SELECT MAX(end_time) FROM tb_user_video_log), MAX(end_time)) unfinished_day_cnt
  FROM tb_video_info u
  JOIN tb_user_video_log v
  on u.video_id=v.video_id
  WHERE DATEDIFF((SELECT MAX(end_time) FROM tb_user_video_log), release_time) <= 29 
  GROUP BY 1
) t
ORDER BY 2 DESC LIMIT 3