首页 > 试题广场 >

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

[编程题]近一个月发布的视频中热度最高的top3视频
  • 热度指数:103038 时间限制:C/C++ 1秒,其他语言2秒 空间限制:C/C++ 256M,其他语言512M
  • 算法知识视频讲解

现有用户-视频互动表tb_user_video_log

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
1. 首先算出每个视频每天的完播,点赞数,评论数,转发数,然后计算每个视频的热度,再取TOP3
2. 评论数 count(comment_id) comment_cnt  ,不用判断null
3. 最近无播放天数 :DATEDIFF((select max(end_time) from tb_user_video_log), max(end_time))  
select max(end_time) from tb_user_video_log  是取整个表格中最近的日期,而max(end_time)后是有group by video_id的,取的是每个视频的最近日期。
4. 近一个月发布视频
    where DATEDIFF((select max(end_time) from tb_user_video_log), vi.release_time) <= 29
同样单独去除最大日期

select t.video_id
, round((100*t.all_rate + 5*t.like_cnt + 3*t.comment_cnt + 2*t.retween_cnt)
        /(t.f+1), 0) hot_index
from 
(
    select vi.video_id
    , avg(if (timestampdiff(second, uv.start_time, uv.end_time) >= vi.duration, 1, 0)) all_rate
    , sum(if_like) like_cnt
    , sum(if_retweet) retween_cnt
    , count(comment_id) comment_cnt
    , DATEDIFF((select max(end_time) from tb_user_video_log), max(end_time)) f
    from tb_video_info vi
    join tb_user_video_log uv
    on vi.video_id = uv.video_id
    where DATEDIFF((select max(end_time) from tb_user_video_log), vi.release_time) <= 29
    group by vi.video_id
) t
order by hot_index desc
limit 3;




发表于 2021-12-22 15:00:14 回复(8)
坑爹的 TIMESTAMPDIFF 和 DATEDIFF 在 精度上的区别
发表于 2022-04-09 05:01:56 回复(13)
题目本身不复杂,但是理解题目好难 … 比如,最近无播放天数,一直理解成最近两次播放天数间的间隔天数,实际要简单很多,就是整体最近的播放日期减每个视频的最近播放日期。
参考评论里正确的答案,才发现了问题。重新写了SQL语句,看看还有没有更简单的方案。
SELECT
    video_id,
    ROUND(((a/e)*100 + b*5 + c*3 + d*2) * (1/(f+1)), 0) AS hot_index
FROM
    (SELECT
        t2.video_id,
        SUM(IF(TIMESTAMPDIFF(SECOND, start_time, end_time) >= duration, 1, 0)) AS a,
        SUM(if_like) AS b,
        COUNT(comment_id) AS c,
        SUM(if_retweet) AS d,
        COUNT(start_time) AS e,
        DATEDIFF((SELECT MAX(DATE(end_time)) FROM tb_user_video_log), MAX(DATE(end_time))) AS f
    FROM tb_user_video_log AS t1
    JOIN tb_video_info AS t2
    ON t1.video_id = t2.video_id
    WHERE t2.video_id IN
        (SELECT video_id
        FROM tb_video_info
        WHERE DATE(release_time) > DATE_SUB((SELECT MAX(DATE(end_time)) FROM tb_user_video_log), INTERVAL 30 DAY))
    GROUP BY t2.video_id) AS t3
ORDER BY hot_index DESC
LIMIT 3;

发表于 2021-12-10 16:52:34 回复(5)
select a.video_id,
round((100*play_rate + 5*likes + 3*comments + 2*retweets)*(1/(day_new+1)),0) hot_index
from
(select t1.video_id, 
 avg(case when timestampdiff(second, start_time, end_time)>=duration then 1 else 0 end) play_rate,
 sum(if_like) likes,
 count(comment_id) comments,
 sum(if_retweet) retweets,
 DATEDIFF((select max(end_time) from tb_user_video_log), max(t1.end_time)) day_new 
 from tb_user_video_log t1
 join tb_video_info t2
 on t1.video_id=t2.video_id
 where datediff(date((select max(end_time)from tb_user_video_log)), date(t2.release_time))<=29
 group by t1.video_id
) a
ORDER BY hot_index DESC 
LIMIT 3

发表于 2021-12-01 21:32:14 回复(7)
select video_id, 
round((100 * comp_play_rate + 5 * num_like + 3 * num_comment + 2 * num_retweet)
      * fresh_rate, 0) as hot_index
from(
    select V.video_id,
    # 1 / (datediff(date_format(max(U.end_time), '%Y-%m-%d'), '2021-10-03') + 1) as fresh_rate,
    1 / (datediff((select max(end_time) from tb_user_video_log), max(U.end_time)) + 1) as fresh_rate,
    avg(if(timestampdiff(second, U.start_time, U.end_time) >= duration, 1, 0)) as comp_play_rate,
    # U.end_time - U.start_time 会报错
    sum(if_like) as num_like,
    count(comment_id) as num_comment,
    sum(if_retweet) as num_retweet
    from tb_user_video_log as U
    left join tb_video_info as V
    on U.video_id = V.video_id
    where datediff((select max(end_time) from tb_user_video_log), V.release_time) <= 29
    group by V.video_id
) as play_info
order by hot_index desc
limit 3

没什么新东西,不知道为什么是困难提,可能是题意叙述很不清楚吧。

发表于 2022-02-20 15:46:08 回复(2)
这个题用到两个函数 timestampdiff和datediff
timestampdiff要把小的时间(更早的时间)放在前面
datediff要把大的时间(更靠后的时间)放在前面
如果顺序不对,会输出负值
发表于 2022-05-30 17:18:06 回复(2)
select t.video_id,
    round(((sum(case when timestampdiff(second,start_time,end_time)>=duration then 1 else 0 end)/count(*))*100+ -- 视频完播率
        5*sum(if_like) + 3* count(comment_id) + 2*sum(if_retweet))* -- 各互动值
        (1/(datediff(@max_date,max(end_time))+1))) as hot_index -- 新鲜度
from tb_video_info t join tb_user_video_log t1 on t.video_id=t1.video_id 
,(select @max_date:=max(end_time) from tb_user_video_log)t2 -- 变量使用得到最近播放日
    where datediff(@max_date,release_time) <= 29
    group by t.video_id
    order by hot_index desc
    limit 3 -- 记得是求top3 如果严谨想使用窗口函数也可以,不过题目没有说明top3的具体规则

变量的使用
1、set @max_date=(select max(end_time) from tb_user_video_log);
2、在表连接后加入 ,(select @max_date:=max(end_time) from tb_user_video_log)t2
逗号和后面的表命名都需要,位置可以放到on后面

这个题本身不难,也是实际中常常会出现的场景,主要注意近期播放日期是动态的不是 ‘2021-10-03’ 。然后认真计算即可。

为了能更正确的计算,也可以将每个计算部分单独列出来,这样方便核算

  select t.video_id,

sum(case when timestampdiff(second,start_time,end_time)>=duration then 1 else 0 end)/count(*) as rate,

    5*sum(if_like),  3* count(comment_id) , 2*sum(if_retweet) as sum_all,

    datediff(@max_date,max(end_time)) as hot_index

from tb_video_info t join tb_user_video_log t1 using(video_id),(select @max_date:=max(end_time) from tb_user_video_log)t2

where datediff(@max_date,release_time) <= 29

group by t.video_id

order by hot_index;

发表于 2022-08-25 13:57:49 回复(0)
SELECT
	t1.video_id,
	round((100 * t1.play_rate + 5 * t1.likes + 3 * t1.comments + 2 * t1.retweets )*(1 /(t1.days + 1 )),0 ) AS hot_index 
from (
select a.video_id,sum(if(TIMESTAMPDIFF(SECOND,start_time,end_time)>= duration,1,0))/count(a.video_id) play_rate, sum(if_like) likes,count(comment_id) comments,sum(if_retweet) retweets,DATEDIFF((select max(end_time) from tb_user_video_log), max(end_time)) days
	from tb_user_video_log a
	inner 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(release_time)) <= 29
	group by a.video_id) t1
ORDER BY hot_index DESC LIMIT 3

发表于 2021-12-01 20:56:25 回复(1)
1. 首先算出每个视频每天的完播,点赞数,评论数,转发数,然后计算每个视频的热度,再取TOP3
2. 评论数 count(comment_id) comment_cnt  ,不用判断null
3. 最近无播放天数 :DATEDIFF((select max(end_time) from tb_user_video_log), max(end_time))  
select max(end_time) from tb_user_video_log  是取整个表格中最近的日期,而max(end_time)后是有group by video_id的,取的是每个视频的最近日期。
4. 近一个月发布视频
    where DATEDIFF((select max(end_time) from tb_user_video_log), vi.release_time) <= 29
同样单独去除最大日期
with t as (
select vi.video_id
    , avg(if (timestampdiff(second, uv.start_time, uv.end_time) >= vi.duration, 1, 0)) all_rate
    , sum(if_like) like_cnt
    , sum(if_retweet) retween_cnt
    , count(comment_id) comment_cnt
    , DATEDIFF((select max(end_time) from tb_user_video_log), max(end_time)) f
    from tb_video_info vi
    join tb_user_video_log uv
    on vi.video_id = uv.video_id
    where DATEDIFF((select max(end_time) from tb_user_video_log), vi.release_time) <= 29
    group by vi.video_id
)
select t.video_id,
       round((100*t.all_rate + 5*t.like_cnt + 3*t.comment_cnt + 2*t.retween_cnt)
        /(t.f+1), 0) hot_index
from t 
order by hot_index desc
limit 3;


发表于 2022-08-13 22:24:38 回复(1)
有哪位大神知道哪错了么。。。实在不知道咋错了。。我的实际输出三个结果,但是预期输出就俩结果,是我的问题还是题的问题?
select 
b.video_id
,(
    round((100*(sum((case when timestampdiff(second,a.start_time,a.end_time)>=b.duration then 1 else 0 end))/count(*)) 
    + 5* sum(a.if_like)
    + 3* count(a.comment_id)
    + 2*sum(a.if_retweet))
    /(datediff(date(a.max_time),max(a.end_time)) + 1),0)
 )  hot_index
from 
(
    select 
    * 
    ,(select max(date(end_time)) from tb_user_video_log) max_time
    from 
    tb_user_video_log
    where 
    date(end_time) >= date_add((select max(date(end_time)) from tb_user_video_log),interval -29 day)
) a 
join 
tb_video_info b 
on a.video_id=b.video_id
group by 
b.video_id
order by 
hot_index desc 
limit 3


发表于 2021-11-30 20:33:51 回复(4)
想问下各位大佬这是什么情况呢,我明明输出的是正确答案,但是不给我通过,对比看预期输出少了2002年?一脸懵逼,我的代码怎么还让预期输出少了一行呢

发表于 2023-07-20 23:58:27 回复(1)
select video_id
     ,round(
         (100*(sum(if(timestampdiff(second,start_time,end_time)<duration,0,1))/count(end_time))
     +5*sum(if_like)+3*count(comment_id)+2*sum(if_retweet))
        /(datediff((select max(end_time) from tb_user_video_log),max(end_time))+1),0) hot_index
from tb_user_video_log
join tb_video_info
using(video_id)
where datediff((select max(end_time) from tb_user_video_log),release_time)<=29
group by video_id
order by hot_index desc
limit 3;
发表于 2022-10-17 19:02:49 回复(0)
select video_id,round((100*wbl+5*dz+3*pl+2*zf)*fresh,0) hot_index
from
(select video_id,avg(wbflag) wbl,sum(if_like) dz,sum(if_retweet) zf,count(comment_id) pl,1/(datediff((select date(max(end_time))
from tb_user_video_log),(date(max(end_time))))+1) fresh
from
(select video_id,end_time,if_like,if_retweet,comment_id,if(timestampdiff(second,start_time,end_time)>=duration,1,0) wbflag
from tb_user_video_log join tb_video_info using(video_id)
where date(end_time) between date_sub((select date(max(end_time))
from tb_user_video_log), interval 29 day) and (select date(max(end_time))
from tb_user_video_log)) t1
group by video_id) t2
order by hot_index desc
limit 3
不是,我不加round取整,他的预期输出就是前三,说我结果没取整数,我一加上round取整,他预期输出变两行???你搁着反复横跳??救救孩子吧,题目不难,我被他搞蒙了
发表于 2022-08-14 10:45:16 回复(3)
# 字段:每个视频、热度
# 行级别筛选:近一个月发布的视频、
# 格式:顶部3
SELECT 
    tb_video_info.video_id
    #,timestampdiff(day,max(date(end_time)),(select max(date(end_time)) from tb_user_video_log))
    ,ROUND((100*avg(IF(timestampdiff(second,start_time,end_time) >= duration, 1,0)) # 完播率
        + 5*SUM(if_like)
        + 3*COUNT(comment_id)
        + 2*SUM(if_retweet)) 
      * (1/(timestampdiff(day,max(date(end_time)),(select max(date(end_time)) from tb_user_video_log))+1))
            ,0) AS hot_index
FROM tb_user_video_log
    inner join tb_video_info on tb_user_video_log.video_id = tb_video_info.video_id
WHERE timestampdiff(day,date(release_time),(select max(date(end_time)) from tb_user_video_log)) <=29 # 最近30天,计算天粒度,一定得用date函数后再做加减计算
GROUP BY tb_video_info.video_id
ORDER BY hot_index desc
LIMIT 3
        
发表于 2022-08-06 23:45:10 回复(1)
SELECT v.video_id,
       round((100 * sum(
           if(
               (unix_timestamp(u1.end_time) - unix_timestamp(u1.start_time)) >= v.duration, v.duration, 0)
           ) / (v.duration * count(1)) +
       sum(u1.if_like) * 5 +
       count(u1.comment_id) * 3 + 2 * sum(if_retweet))/(DATEDIFF((select max(end_time) from tb_user_video_log), max(u1.end_time))+1),0) hot_index
from tb_user_video_log u1
         join tb_video_info v on v.video_id = u1.video_id
where DATEDIFF((select max(end_time) from tb_user_video_log), v.release_time) <= 29
group by v.video_id
order by hot_index desc
limit 3

发表于 2022-07-14 15:10:37 回复(0)
SELECT
	video_id,
	round(t_num * t_new, 0) AS hot_index
FROM
	(
		SELECT
			video_id,
			sum(if_end) / count(*) * 100 + sum(if_like) * 5 + sum(if_comment) * 3 + sum(if_retweet) * 2 AS t_num,
			1 / (datediff(max(max_date),max(end_date)) + 1) AS t_new
		FROM
			(
				SELECT
					a.*, b.release_time, date_format(end_time, '%Y-%m-%d') AS end_date,
					IF (TIMESTAMPDIFF(SECOND, start_time, end_time) >= b.duration,1,0) AS if_end,
					IF (comment_id IS NULL, 0, 1) AS if_comment,
					(
					SELECT
						max(date_format(end_time, '%Y-%m-%d'))
					FROM tb_user_video_log
					) AS max_date
				FROM
					tb_user_video_log a
				JOIN tb_video_info b ON a.video_id = b.video_id
			) a
		WHERE date_format(end_time, '%Y-%m-%d') BETWEEN adddate(max_date, - 29) AND max_date
		AND date_format(release_time, '%Y-%m-%d') BETWEEN adddate(max_date, - 29) AND max_date
		GROUP BY video_id
	) a
ORDER BY hot_index DESC LIMIT 3
# 俄罗斯套娃系列, 看清题目看清题目看清题目。自己都快成盲僧了
发表于 2022-04-30 21:30:35 回复(0)
select
    video_id
    ,round((100 * finish_rate + 5 * likes_cnt + 3 * comments_cnt + 2 * retweets_cnt)
    /(1 + days_cnt),0) hot_index
from(
    select
        t1.video_id
        ,timestampdiff(day,max(end_time),(select max(end_time) from tb_user_video_log)) days_cnt
        ,avg(case when timestampdiff(second,start_time,end_time) >= duration then 1
            else 0
            end) finish_rate
        ,sum(if_like) likes_cnt
        ,count(comment_id) comments_cnt
        ,sum(if_retweet) retweets_cnt
    from
        tb_user_video_log t1
    inner join tb_video_info t2
    on t1.video_id = t2.video_id
    where timestampdiff(day,release_time,(select max(end_time) from tb_user_video_log)) < 30
    group by t1.video_id
)t3
order by hot_index desc
limit 3
不知道为啥我用timestampdiff只能跑通demo感觉也没写错啊...用datediff就能过
发表于 2022-01-26 15:33:40 回复(2)
主要坑在于TIMESTAMPDIFF 和 DATEDIFF 的差异,网上找的例子,感谢作者。



发表于 2024-01-16 11:24:32 回复(0)
select
    vi.video_id,
    round((100*avg(if(timestampdiff(second,start_time,end_time) >= duration,1,0)) + 
           5*sum(if_like) + 
           3*count(comment_id) + 
           2*sum(if_retweet)) / (datediff(max(date(max(end_time))) over(),date(max(end_time))) + 1)) hot_index
from tb_user_video_log uvl
join tb_video_info vi
on uvl.video_id = vi.video_id
where date(release_time) > (select date_sub(date(max(end_time)),interval 30 day) from tb_user_video_log)
group by vi.video_id
order by hot_index desc
limit 3;

发表于 2023-07-19 17:14:48 回复(0)
-- 取最近日期
with lastset_day as (
select max(end_time) as latest
from tb_user_video_log
)

-- 计算指标
,indexs as(
select
     a.video_id
    ,datediff(c.latest , a.end_time) as time
    ,if(b.duration <= TIMESTAMPDIFF(second , a.start_time , a.end_time),1,0) as if_complete
    ,a.if_like
    ,a.if_retweet
    ,if(a.comment_id is not null , 1 , 0) as if_comment
from
    tb_user_video_log a
join
    tb_video_info b
on a.video_id = b.video_id
cross join
    lastset_day c
on datediff(latest , b.release_time) <= 29
)

-- 聚合计算
select
     video_id
    ,round((avg(if_complete) * 100 + sum(if_like) * 5 + sum(if_comment) * 3 + sum(if_retweet) * 2) / (min(time)+1),0) as hot_index
from indexs
group by video_id
order by hot_index desc
limit 3

发表于 2023-07-19 13:04:22 回复(0)
  • 二维码

    扫描二维码,关注牛客网

  • 二维码

    下载牛客APP,随时随地刷题