牛客sql面试题个人分析思路：近一个月发布的视频中热度最高的top3视频

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

• 描述

• 现有用户-视频互动表tb_user_video_log
  

• 短视频信息表tb_video_info
  

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

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

• 输出示例：
  示例数据的输出结果如下
  
  解释：
  最近播放日期为2021-10-03，记作当天日期；近一个月（2021-09-04及之后）发布的视频有2001、2002、2003、2004，不过2004暂时还没有播放记录；
  视频2001完播率1.0（被播放次数4次，完成播放4次），被点赞3次，评论1次，转发2次，最近无播放天数为0，因此热度为：(1001.0+53+31+22)/(0+1)=122
  同理，视频2003完播率0，被点赞数1，评论和转发均为0，最近无播放天数为3，因此热度为：(1000+51+30+20)/(3+1)=1（1.2保留为整数）

DROP TABLE IF EXISTS tb_user_video_log, tb_video_info;
CREATE TABLE tb_user_video_log (
    id INT PRIMARY KEY AUTO_INCREMENT COMMENT '自增ID',
    uid INT NOT NULL COMMENT '用户ID',
    video_id INT NOT NULL COMMENT '视频ID',
    start_time datetime COMMENT '开始观看时间',
    end_time datetime COMMENT '结束观看时间',
    if_follow TINYINT COMMENT '是否关注',
    if_like TINYINT COMMENT '是否点赞',
    if_retweet TINYINT COMMENT '是否转发',
    comment_id INT COMMENT '评论ID'
) CHARACTER SET utf8 COLLATE utf8_bin;

CREATE TABLE tb_video_info (
    id INT PRIMARY KEY AUTO_INCREMENT COMMENT '自增ID',
    video_id INT UNIQUE NOT NULL COMMENT '视频ID',
    author INT NOT NULL COMMENT '创作者ID',
    tag VARCHAR(16) NOT NULL COMMENT '类别标签',
    duration INT NOT NULL COMMENT '视频时长(秒数)',
    release_time datetime NOT NULL COMMENT '发布时间'
)CHARACTER SET utf8 COLLATE utf8_bin;

INSERT INTO tb_user_video_log(uid, video_id, start_time, end_time, if_follow, if_like, if_retweet, comment_id) VALUES
   (101, 2001, '2021-09-24 10:00:00', '2021-09-24 10:00:30', 1, 1, 1, null)
  ,(101, 2001, '2021-10-01 10:00:00', '2021-10-01 10:00:31', 1, 1, 0, null)
  ,(102, 2001, '2021-10-01 10:00:00', '2021-10-01 10:00:35', 0, 0, 1, null)
  ,(103, 2001, '2021-10-03 11:00:50', '2021-10-03 11:01:35', 1, 1, 0, 1732526)
  ,(106, 2002, '2021-10-02 10:59:05', '2021-10-02 11:00:04', 2, 0, 1, null)
  ,(107, 2002, '2021-10-02 10:59:05', '2021-10-02 11:00:06', 1, 0, 0, null)
  ,(108, 2002, '2021-10-02 10:59:05', '2021-10-02 11:00:05', 1, 1, 1, null)
  ,(109, 2002, '2021-10-03 10:59:05', '2021-10-03 11:00:01', 0, 1, 0, null)
  ,(105, 2002, '2021-09-25 11:00:00', '2021-09-25 11:00:30', 1, 0, 1, null)
  ,(101, 2003, '2021-09-26 11:00:00', '2021-09-26 11:00:30', 1, 0, 0, null)
  ,(101, 2003, '2021-09-30 11:00:00', '2021-09-30 11:00:30', 1, 1, 0, null);

INSERT INTO tb_video_info(video_id, author, tag, duration, release_time) VALUES
   (2001, 901, '旅游', 30, '2021-09-05 7:00:00')
  ,(2002, 901, '旅游', 60, '2021-09-05 7:00:00')
  ,(2003, 902, '影视', 90, '2021-09-05 7:00:00')
  ,(2004, 902, '影视', 90, '2021-09-05 8:00:00');

一、将所有问题列出
1、找出近一个月发布的视频中，热度最高的top3视频
2、热度=(100视频完播率)+（5点赞数）+（3评论数）+(2转发数)*新鲜度
3、新鲜度=1/（最近无播放天数+1）
4、最近播放日期end_time 结束观看时间为准，假设为T，则最近一个月按[T-29, T]闭区间统计。

二、将最后需要的细节列出

筛选：

1. 近一个月
2. 热度最高的top3
3. 所需字段 video_id(视频编号），hot_index(热度）
4. 热度保留为整数、按热度降序；

字段拆分

• hot_index 由视频完播率、点赞数、评论数、转发数、新鲜度构成
• 视频完播率=完播视频个数/视频总观看次数

#完播视频 ：视频结束时间与视频开始时间之差大于视频时长为完播视频
#两时间之差采取1.timestampdiff()和两时间相减再用date_format()
sum(if(timestampdiff(second,start_time,end_time)-duration>=0,1,0))/count(t1.video_id)

• 点赞数、评论数、转发数

   sum(if_like)、sum(if(comment_id is not null,1,0))、sum(if_retweet))

• 新鲜度=1/（最近无播放天数+1）
• ！！！注意这个地方的括号 是先加1 再括回来

#最近无播发天数 ：分为两种情况，一是该视频播放量=0，为当前时间-视频发行时间；
#						    二是视频有播放量不等于0，为当前时间-视频最后观看时间
#当前时间：select max(end_time) from tb_user_video_log
#视频发行时间：release_time
#视频最后观看时间：max(date(end_time)
1/
(if(count(t1.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))

#####源代码
select t1.video_id,
round(
	(100*sum(if(timestampdiff(second,start_time,end_time)-duration>=0,1,0))/count(t1.video_id)+
    5*sum(if_like)+
    3*sum(if(comment_id is not null,1,0))+
    2*sum(if_retweet))*(
	1/(if(count(t1.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)),0)
	as hot_index		
from tb_user_video_log t1 left join tb_video_info t2 using(video_id)
where end_time>=date_sub((select max(end_time) from tb_user_video_log),interval 29 day)
group by t1.video_id
order by hot_index desc;