近一个月发布的视频中热度最高的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]闭区间统计。
二、将最后需要的细节列出
筛选:
- 近一个月
- 热度最高的top3
- 所需字段 video_id(视频编号),hot_index(热度)
- 热度保留为整数、按热度降序;
字段拆分
- 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;