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

视频中热度最高的top3视频"/>

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

题目

源自牛客
描述

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

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

输出示例：
示例数据的输出结果如下
video_id hot_index
2001 122
2002 56
2003 1
解释：
最近播放日期为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保留为整数）。
示例1
输入：
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’);
复制
输出：
2001|122
2002|56
2003|1

思路

先定义tb_user_video_log表为log表，tb_video_info表为info表

显然需要计算热度，而热度需要5个值：视频完播率、点赞数、评论数、转发数、最近无播放天数。需要一一求解。再根据5个值计算热度。大致的sql应该有思路：

// XXX为具体计算热度值，表应是提供了5个值的中间表
select video_id video_id, round(XXX) hot_index from 表 order by hot_index desc limit 3;

• 视频完播率
  需要计算被播放的次数，和完成播放的次数。
  如何判断是否有完成播放呢？需要判断每条播放记录结束时间(log.end_time)减去开始时间(log.start_time)，是否大于等于本条视频的时长(info.duration)，求时间差使用时间差函数：timestampdiff，timestampdiff(second, log.start_time, log.end_time)，second代表确定（end-begin）时间差的单位，计算出相差的秒数再与视频时长比较：if (timestampdiff(second, log.start_time, log.end_time) >= info.duration, 1, 0)，如果比时长长，则结果是1代表看完视频，否则是0，没有看完视频。再去平均值：avg(if (timestampdiff(second, log.start_time, log.end_time) >= info.duration, 1, 0))，结果为视频完播率video_completion_rate
• 点赞数
  计算总的log.if_like，sum(log.if_like)，结果为点赞数like_cnt
• 评论数
  计算总的logment_id，count(logment_id)，结果为评论数comment_cnt，注意count和sum，sum是求值的和，count是求条数和
• 转发数
  计算总的log.if_retweet，sum(log.if_retweet)，结果为转发数retweet_cnt
• 最近无播放天数
  最近无播放天数其实是求记录表中最后播放的时间和本条记录的播放结束时间相差的天数，求相差天数，使用DATEDIFF() 函数：DATEDIFF((select max(end_time) from tb_user_video_log), max(info.end_time))，结果为转发数last_no_record_days

计算完上述5个值，即可算出热度值。

同时还需要筛选出“近一个月发布的视频”，仍使用DATEDIFF() :DATEDIFF((select max(end_time) from tb_user_video_log), info.release_time) <= 29

最终把sql串起来即可。

注意在组建临时表时，肯定是需要连表查询的，不能使用left join，会导致info表里2004的视频left join后的log里的数据都是null，应使用join或者right join

解法

selecttmp.video_id video_id,round((100 * tmp.video_completion_rate + 5 * tmp.like_cnt + 3 * tmpment_cnt + 2 * tmp.retween_cnt) / (tmp.last_no_record_days + 1), 0) hot_index
from(selectinfo.video_id,avg(if (timestampdiff(second, log.start_time, log.end_time) >= info.duration, 1, 0)) video_completion_rate,sum(log.if_like) like_cnt,count(logment_id) comment_cnt,sum(log.if_retweet) retween_cnt,DATEDIFF( (select max(end_time) from tb_user_video_log), max(log.end_time)) last_no_record_daysfromtb_video_info inforight join tb_user_video_log log on info.video_id = log.video_idwhereDATEDIFF((select max(end_time) from tb_user_video_log), info.release_time) <= 29group byinfo.video_id) tmp
order byhot_index desc
limit 3;