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

爱被打了一巴掌 2023-10-04 18:32 4阅读 0赞

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

#### 内容目录 ####

*   *  近一个月发布的视频中热度最高的top3视频
     *   *  1.题目的几点注意项：
         *  2.需要计算的值：
         *  3.逻辑过程：
         *  4.代码：

**题目**：现有用户-视频互动表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