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

id	uid	video_id	start_time	end_time	if_follow	if_like	if_retweet	comment_id
1	101	2001	2021-09-24 10:00:00	2021-09-24 10:00:30	1	1	1	NULL
2	101	2001	2021-10-01 10:00:00	2021-10-01 10:00:31	1	1	0	NULL
3	102	2001	2021-10-01 10:00:00	2021-10-01 10:00:35	0	0	1	NULL
4	103	2001	2021-10-03 11:00:50	2021-10-03 10:00:35	1	1	0	1732526
5	106	2002	2021-10-02 11:00:05	2021-10-02 11:01:04	2	0	1	NULL
6	107	2002	2021-10-02 10:59:05	2021-10-02 11:00:06	1	0	0	NULL
7	108	2002	2021-10-02 10:59:05	2021-10-02 11:00:05	1	1	1	NULL
8	109	2002	2021-10-03 10:59:05	2021-10-03 11:00:01	0	1	0	NULL
9	105	2002	2021-09-25 11:00:00	2021-09-25 11:00:30	1	0	1	NULL
10	101	2003	2021-09-26 11:00:00	2021-09-26 11:00:30	1	0	0	NULL
11	101	2003	2021-09-30 11:00:00	2021-09-30 11:00:30	1	1	0	NULL

（uid-用户ID, video_id-视频ID, start_time-开始观看时间, end_time-结束观看时间, if_follow-是否关注, if_like-是否点赞, if_retweet-是否转发, comment_id-评论ID）

短视频信息表tb_video_info

id	video_id	author	tag	duration	release_time
1	2001	901	旅游	30	2021-09-05 07:00:00
2	2002	901	旅游	60	2021-09-05 07:00:00
3	2003	902	影视	90	2021-09-05 07:00:00
4	2004	902	影视	90	2021-09-05 08:00:00

（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]闭区间统计。
结果中热度保留为整数，并按热度降序排序。

输出示例：

示例数据的输出结果如下

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，因此热度为：(100*1.0+5*3+3*1+2*2)/(0+1)=122

同理，视频2003完播率0，被点赞数1，评论和转发均为0，最近无播放天数为3，因此热度为：(100*0+5*1+3*0+2*0)/(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

原站题解

上次编辑到这里，代码来自缓存点击恢复默认模板

Mysql 解法, 执行用时: 37ms, 内存消耗: 6392KB, 提交时间: 2022-02-10

select t1.video_id
,round(
((sum(case when timestampdiff(second,start_time,end_time)>=duration then 1 else 0 end ))/count(*)*100
+sum(if_like)*5
+count(comment_id)*3
+sum(if_retweet)*2)
*
1/(DATEDIFF((select date(max(end_time)) from tb_user_video_log),date(max(end_time)))+1)
) as hot_index
from tb_user_video_log t1,tb_video_info t2
where t1.video_id=t2.video_id
and DATEDIFF(DATE((SELECT MAX(end_time) FROM tb_user_video_log)),DATE(release_time)) <= 29
group by t1.video_id
order by hot_index desc
limiT 3

Mysql 解法, 执行用时: 37ms, 内存消耗: 6392KB, 提交时间: 2022-02-08

select x.video_id,
round((x.avg_com_rate*100+x.like_num*5+x.comment_num*3+x.retweet*2)*(1/(x.daydiff+1)),0) hot
from(
select f.video_id,min(f.daydiff) daydiff,
AVG((if(timestampdiff(second,f.start_time,f.end_time)>=f.duration,1,0))) avg_com_rate,
count(comment_id) comment_num,
sum(f.if_retweet) retweet,
sum(f.if_like) like_num
from(
select d.video_id,d.start_time,d.end_time,d.if_like,d.if_retweet,d.comment_id,c.daydiff,e.duration,e.release_time
from (select b.id,
datediff((select max(date(a.end_time))
from tb_user_video_log a
),date(b.end_time)) daydiff
from tb_user_video_log b) c
left join tb_user_video_log d on d.id=c.id 
left join tb_video_info e on e.video_id=d.video_id) f
where datediff((select max(date(a.end_time))from tb_user_video_log a),date(f.release_time))<=29
group by f.video_id) x
order by hot desc limit 3

Mysql 解法, 执行用时: 37ms, 内存消耗: 6392KB, 提交时间: 2021-12-31

select video_id,
round((100*fin_rate + 5*like_sum + 3*comment_sum + 2*retweet_sum)
      /(unfin_day_cnt+1),0) hot_index
from(
  select 
    video_id,duration,
    avg(if(timestampdiff(second,start_time,end_time)>=duration,1,0)) fin_rate,
    sum(if_like) like_sum,
    count(comment_id) comment_sum,
    sum(if_retweet) retweet_sum,
    datediff(date((select max(end_time) from tb_user_video_log)), max(date(end_time))) unfin_day_cnt
  from tb_video_info
  join tb_user_video_log using(video_id)
  where datediff(date((select max(end_time) from tb_user_video_log)), date(release_time)) <= 29
  group by video_id
) t
order by hot_index DESC
limit 3

Mysql 解法, 执行用时: 37ms, 内存消耗: 6396KB, 提交时间: 2021-12-27

select video_id,round((100*sc+5*like1+3*comment1+retweet1*2)/(dt+1),0) a1
from(select video_id,duration,sum(if_like) like1,
        sum(if_retweet) retweet1,count(comment_id) comment1
        ,sum(TIMESTAMPDIFF(second,start_time,end_time)>=duration)/count(*) sc,
        datediff(date((select max(end_time) from tb_user_video_log)),max(date(end_time))) dt
        from tb_user_video_log t1
        left join tb_video_info t2
        using(video_id)
        where datediff(date((select max(end_time) from tb_user_video_log))
        ,date(release_time))<=29
        group by video_id
)t
order by a1 desc
limit 3

Mysql 解法, 执行用时: 37ms, 内存消耗: 6396KB, 提交时间: 2021-12-02

with tmp_table as(
select
  a.video_id
 ,a.end_time
 ,a.if_retweet*2 as if_retweet
 ,a.if_like*5 as if_like
 ,if(a.comment_id IS NOT NULL,3,0) as comment_id
 ,if(unix_timestamp(a.end_time)-unix_timestamp(a.start_time)>=b.duration,100,0) as see_time
from tb_user_video_log a
join tb_video_info b
 on a.video_id = b.video_id
 where DATEDIFF(DATE((SELECT MAX(end_time) FROM tb_user_video_log)), DATE(b.release_time)) <= 29 
 
)
select
  a.video_id
 ,round(
 
 ((sum(a.see_time)/count(1))+sum(a.if_like)+sum(a.if_retweet)+sum(a.comment_id))
 /
 (datediff(date((select max(end_time) from tb_user_video_log)),max(date(end_time)))+1)
 
 ) as hot_index
from tmp_table a
group by 
a.video_id
order by hot_index desc limit 3
;

上一题

下一题

详情