首页 > 试题广场 >

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

[编程题]近一个月发布的视频中热度最高的top3视频

热度指数：145025 时间限制：C/C++ 1秒，其他语言2秒空间限制：C/C++ 256M，其他语言512M
算法知识视频讲解

现有用户-视频互动表tb_user_video_log

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

短视频信息表tb_video_info

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

输出示例：

示例数据的输出结果如下

解释：

最近播放日期为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

牛客742969403号

with b as (select a.*, if(timestampdiff(second,start_time,end_time)>=duration,1,0) as finish from tb_user_video_log a join tb_video_info using (video_id)
where a.end_time>=date_sub((select max(end_time) from tb_user_video_log), interval 29 day) and release_time>=date_sub((select max(end_time) from tb_user_video_log), interval 29 day)),

c as (select video_id, datediff(date(max(max(end_time)) over ()),date(max(end_time))) as noplay from b
group by video_id),

d as (select video_id, sum(finish)/count(finish) as finishrate,sum(if_like) as likenum, sum(if(comment_id is not null, 1, 0)) as comment, sum(if_retweet) as retweet from b
group by video_id)

select d.video_id, round((100*finishrate+5*likenum+3*comment+2*retweet)*1/(noplay+1),0) as hot_index from d join c using (video_id)
order by 2 desc
limit 3

发表于 2025-06-27 10:29:21 回复(0)

Mysql

牛客636580694号

select b.video_id,round((100*wbl+5*like_cnt+3*comment_cnt+2*retweet_cnt)/(l_d+1),0) hot_index

from

(select a.video_id,avg(if(timestampdiff(second,start_time,end_time)>=duration,1,0)) wbl,sum(if_like) like_cnt,count(comment_id) comment_cnt,sum(if_retweet) retweet_cnt,datediff(date(late_day),max(start_time)) l_d

from

(select *,max(end_time)over() late_day

from tb_user_video_log) a

left join tb_video_info b

on a.video_id=b.video_id

where datediff(late_day,release_time)<=29

group by a.video_id,late_day

) b

order by hot_index desc

limit 3

发表于 2025-05-22 22:10:51 回复(1)

Mysql

黄鱼香

select
video_id,
round(((100*sum(case
when timestampdiff(second,start_time,end_time)>=duration then 1
else 0 end))/count(start_time)+5*sum(if_like)+3*sum(case when comment_id is not null then 1 
else 0 end)+2*sum(if_retweet))/(timestampdiff(day,max(end_time),(select max(end_time) from tb_user_video_log))+1),0) hot_index
from
(select release_time,tu.video_id,start_time,end_time,comment_id,if_retweet,if_like,duration,(select
max(end_time) from tb_user_video_log) the_max
from tb_user_video_log tu left join tb_video_info tv
on tu.video_id = tv.video_id
)t1
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

为什么我答案不一样，是逻辑不一样吗

发表于 2025-04-23 14:00:06 回复(0)

Mysql

卡冈图雅的猫猫

select video_id,
round((100*finish_rate+5*like_num+3*comment_num+2*retweet_num)/(no_watch_days+1), 0) hot_index
from
(
    select video_id,
    avg(if_finish) finish_rate, 
    sum(if_like) like_num,
    sum(if_comment) comment_num,
    sum(if_retweet) retweet_num,
    # timestampdiff(day, max(end_time), (select max(end_time) from tb_user_video_log)) no_watch_days
    datediff(date((select max(end_time) from tb_user_video_log)),  max(date(end_time))) no_watch_days
    from
    (
        select
        t1.video_id, t1.if_like, t1.if_retweet, 
        if(t1.comment_id is null, 0, 1) if_comment,
        if(timestampdiff(second ,t1.start_time, t1.end_time)>=t2.duration, 1, 0) if_finish,
        t1.end_time
        from tb_user_video_log t1 left 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))<30
    ) as t12
    group by video_id
) as tt12
order by hot_index desc

求大佬看哪错了，提交就是不通过

发表于 2025-04-03 16:42:21 回复(3)

Mysql

yeye要努力

select c.video_id,
round(((wbl*100+dzs*5+pls*3+zfs*2)/(xxd+1)),0) hot_index
from (select a.video_id,
avg(if(timestampdiff(second,end_time,start_time)>= duration,1,0)) wbl,
sum(if_like) dzs,
count(comment_id) pls,
sum(if_retweet) zfs,
datediff((select max(end_time) from tb_user_video_log),max(end_time)) xxd
from tb_user_video_log a
left join tb_video_info b on a.video_id = b.video_id
where datediff((select max(end_time) from tb_user_video_log),end_time) <= 29
group by a.video_id) c
order by hot_index desc
limit 3

发表于 2025-03-12 09:14:40 回复(2)

Mysql

内向的华夫饼

with t01 as (
    select 
           a.video_id as video_id,
           a.end_time as end_time,
           (select max(end_time)  from tb_user_video_log) as last_time,
           TIMESTAMPDIFF(SECOND, a.start_time, a.end_time) as view_time,
           a.if_follow as if_follow,
           a.if_like as if_like,
           a.if_retweet as if_retweet,
           case when a.comment_id is null then 0 else 1 end as if_comment
        from tb_user_video_log as a      
),
t02 as (
    select 
           a.video_id as video_id,
           a.if_follow as if_follow,
           a.if_like as if_like,
           a.if_retweet as if_retweet,
           a.if_comment as if_comment,
           case when b.duration <= a.view_time then 1 else 0 end as if_vl,
           datediff(a.last_time,COALESCE(a.end_time,b.release_time)) as fresh,
           ROW_NUMBER() OVER (partition by a.video_id order by end_time desc) as rk
        from t01 as a
        left join tb_video_info b 
        on a.video_id = b.video_id
        where b.release_time > date_sub(last_time, interval 29 day) 
)
select a.video_id as video_id,
     round((100*a.vl_rate + 5 *a.sum_like + 3*a.sum_comment + 2*a.sum_retweet)/(b.fresh+1),0) as hot_index 
from (
    select video_id,
           sum(if_vl)/count(if_vl) as vl_rate,
           sum(if_like) as sum_like,
           sum(if_comment) as sum_comment,
           sum(if_retweet) as sum_retweet
        from t02
        group by video_id
) a
left join (
    select fresh,
           video_id
        from t02
    where rk =1
) b
on a.video_id = b.video_id
order by hot_index desc
limit 3;

发表于 2025-03-03 18:13:25 回复(0)

Mysql

在收藏面经的查理斯很纠结

SELECT 
    t1.video_id,
    ROUND(
        (
            100 * AVG(TIMESTAMPDIFF(SECOND, start_time, end_time) >= duration) + 
            5 * SUM(if_like = 1) + 
            3 * SUM(comment_id IS NOT NULL) + 
            2 * SUM(if_retweet = 1)
        ) 
        / 
        (1 + TIMESTAMPDIFF(DAY, MAX(end_time), (SELECT MAX(end_time) FROM tb_user_video_log))),
        0
    ) AS hot_index
FROM 
    tb_user_video_log t1
LEFT JOIN 
    tb_video_info t2 
ON 
    t1.video_id = t2.video_id
WHERE 
    TIMESTAMPDIFF(DAY, t1.start_time, (SELECT MAX(end_time) FROM tb_user_video_log)) BETWEEN 0 AND 29
GROUP BY 
    t1.video_id
ORDER BY 
    hot_index DESC
LIMIT 3

为啥我这个运行出来2022年和2023年的不正确咧，我看好久都没看出问题，特发帖求助下感谢

发表于 2025-01-29 11:24:39 回复(0)

Mysql

王延德

select video_id,round((100*wanbolv+5*like_cnt+3*comment_cnt+2*retweet_cnt)/(1+days),0) as hot_index

from (select u.video_id,

count(if(timestampdiff(second,start_time,end_time)>=duration,1,null))/count(*) as wanbolv,

sum(if_like) as like_cnt,

sum(if_retweet) as retweet_cnt,

count(comment_id) as comment_cnt,

datediff((select max(end_time) from tb_user_video_log),max(end_time)) as days

from tb_user_video_log u left join tb_video_info i on u.video_id=i.video_id where u.video_id in(select video_id from tb_video_info where release_time >= (select date_sub(max(date_format(end_time,'%Y-%m-%d')),interval 29 day) from tb_user_video_log)) group by u.video_id) t1

order by hot_index desc

limit 3;

发表于 2025-01-20 22:09:25 回复(0)

Mysql

风也温柔_

WITH A AS(
    SELECT
        video_id,
        AVG(IF(TIMESTAMPDIFF(SECOND,start_time,end_time)>=duration,1,0)) AS play_rate,
        DATEDIFF((SELECT MAX(DATE(end_time)) FROM tb_user_video_log),MAX(end_time)) AS no_play_days,
        SUM(if_like) AS b_like, 
        COUNT(comment_id) AS c_comment,
        SUM(if_retweet) AS d_retweet
    FROM tb_user_video_log u
    JOIN  tb_video_info v USING(video_id)
    WHERE DATE(release_time) 
        BETWEEN ( SELECT DATE_ADD( MAX(DATE(end_time)), INTERVAL -29 DAY) AS min_time FROM tb_user_video_log)  
        AND (SELECT MAX(DATE(end_time)) AS max_time FROM tb_user_video_log)
    GROUP BY video_id
)
    SELECT
    video_id,
    CAST((play_rate*100+b_like*5+c_comment*3+d_retweet*2)/(no_play_days+1) AS SIGNED) AS hot_index
FROM A 
ORDER BY hot_index DESC
LIMIT 3

发表于 2024-12-31 10:53:52 回复(0)

Mysql

牛客722647121号

SELECT a.video_id,ROUND((100*视频完播率+5*点赞数+3*评论数+2*转发数)*1/(新鲜度+1),0) AS hot_index

FROM (SELECT video_id, tag, SUM(CASE WHEN wat_duration>=duration THEN 1 ELSE 0 END)/ COUNT(*) AS 视频完播率

FROM (SELECT l.uid,l.video_id,i.tag,TIMESTAMPDIFF(second,l.start_time, l.end_time) AS wat_duration,i.duration

FROM tb_user_video_log l

LEFT JOIN tb_video_info i

ON l.video_id=i.video_id

WHERE DATEDIFF((SELECT MAX(end_time) from tb_user_video_log), i.release_time) <= 29) AS t

GROUP BY video_id) AS a

LEFT JOIN

(SELECT l.video_id,i.tag,SUM(IFNULL(if_like,0)) AS 点赞数, SUM(IFNULL(if_retweet,0)) AS 转发数, COUNT(comment_id) AS 评论数,DATEDIFF((SELECT MAX(end_time) FROM tb_user_video_log),MAX(end_time)) AS 新鲜度

FROM tb_user_video_log l

LEFT JOIN tb_video_info i

ON l.video_id=i.video_id

WHERE DATEDIFF((SELECT MAX(end_time) from tb_user_video_log), i.release_time) <= 29

GROUP BY l.video_id,i.tag) AS b

ON a.video_id=b.video_id

ORDER BY hot_index DESC

LIMIT 3

发表于 2024-11-18 08:11:29 回复(0)

Mysql

已run的羚羊很纯真

人都麻了。。。。

select video_id,round((100*play+5*like_cnt+3*com_cnt+2*re_cnt)/(tdiff+1),0) hot_index

from

(select t1.video_id,avg(TIMESTAMPDIFF(second, start_time, end_time) >= duration) play,sum(if_like) like_cnt,count(comment_id)com_cnt,sum(if_retweet) re_cnt,datediff(date((select max(end_time) from tb_user_video_log)),date(max(end_time))) tdiff

from tb_user_video_log t1

left 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(release_time))<=29

group by t1.video_id) t3

order by hot_index desc limit 3

发表于 2024-11-05 18:05:44 回复(0)

Mysql

sys_

SELECT tuvl.video_id, 
       ROUND((100 * sum(if(timestampdiff(SECOND, start_time, end_time) >= tvi.duration, 1, 0)) / count(1) 
        + 5 * sum(if_like) + 3 * count(comment_id) + 2 * sum(if_retweet)) /
        (datediff(max(t.today), max(date(end_time))) + 1)) AS hot_index
FROM tb_user_video_log tuvl, tb_video_info tvi,
     (SELECT max(date(end_time)) AS today FROM tb_user_video_log tuvl) t
WHERE tuvl.video_id = tvi.video_id 
  AND datediff(t.today, date(tvi.release_time)) BETWEEN 0 AND 29
GROUP BY tuvl.video_id
ORDER BY hot_index DESC
LIMIT 3;

发表于 2024-10-15 15:17:46 回复(0)

Mysql

灿灿灿灿灿_

-- 救命，有没有哪位大佬可以告诉我这里到底哪里错了，第三个实例一直没跑出来，我感觉好像没错啊...

WITH t2 AS

(WITH t1 AS

(SELECT

u.video_id,

u.if_like,

u.comment_id,

u.if_retweet,

u.start_time,

IF(DATE_ADD(u.start_time, INTERVAL duration SECOND) <= u.end_time, 1, 0) AS if_finished,

DATEDIFF(DATE_FORMAT((SELECT MAX(end_time) FROM tb_user_video_log), '%Y-%m-%d'),

DATE_FORMAT(end_time, '%Y-%m-%d')) AS dt

FROM tb_user_video_log u

JOIN tb_video_info v USING(video_id)

WHERE DATEDIFF(

DATE_FORMAT((SELECT MAX(end_time) FROM tb_user_video_log), '%Y-%m-%d'),

DATE_FORMAT(end_time, '%Y-%m-%d')

) <= 29)

SELECT

t1.video_id,

SUM(t1.if_finished) / COUNT(t1.start_time) AS finished_rate,

SUM(t1.if_like) AS like_cnt,

COUNT(t1.comment_id) AS comment_cnt,

SUM(t1.if_retweet) AS retweet_cnt,

MIN(t1.dt) AS dt

FROM t1

GROUP BY t1.video_id)

SELECT

t2.video_id,

ROUND((100*t2.finished_rate + 5* t2.like_cnt + 3* t2.comment_cnt + 2*t2.retweet_cnt) / (1+dt), 0) AS hot_index

FROM t2

ORDER BY hot_index DESC

LIMIT 3;

发表于 2024-10-12 17:27:02 回复(0)

Mysql

无边无量

其实还是有点BUG的，完播率并不是，结束时间-开始时间》视频时长就表示完播了，

因为用户在看视频的时候中间可能会暂停，如果暂停的时间足够久，会超过视频时间

发表于 2024-09-18 11:28:45 回复(0)

Mysql

牛客138192226号

不够简便，但还是弄出来了。


select
video_id,round((100*wanbo+5*dianzan+3*pinglun+2*zhuanfa)/(zuijin+1),0)
from
(select
a.video_id,
sum(if(timestampdiff(second,start_time,end_time)>=duration,1,0))/count(start_time) wanbo,
sum(if_like) dianzan,
count(comment_id) pinglun,
sum(if_retweet) zhuanfa,
datediff((select max(end_time) from tb_user_video_log),max(end_time)) zuijin
from tb_user_video_log a
join tb_video_info b
on a.video_id=b.video_id
where release_time between date_sub((select max(end_time) from tb_user_video_log),interval 29 day) and (select max(end_time) from tb_user_video_log)
group by a.video_id) c
group by video_id
order by 2 desc
limit 3
;

发表于 2024-09-09 03:48:21 回复(1)

Mysql

牛客712695039号

with main as ( select video_id,tag,start_time, end_time, if_follow, if_like, if_retweet, comment_id,release_time,

max(date(end_time))over( ) as 最大时间,

1/(datediff(max(date(end_time))over( ),max((date(end_time)))over(partition by video_id)) +1) as 新鲜度,

if((timestampdiff(second,start_time,end_time)-duration)>=0,1,0) as 完播

from tb_user_video_log

join tb_video_info using(video_id))

select video_id,

round((100*avg(完播)+5*sum(if_like)+3*count(comment_id)+2*sum(if_retweet))*any_value(新鲜度),0) as hot_index

from main

where date(release_time) between (date_sub(最大时间,interval 29 day)) and (date_add(最大时间,interval 1 day))

group by video_id

order by hot_index desc

limit 3

发表于 2024-08-27 14:18:08 回复(0)

Mysql

七号公园的街灯

select distinct t1.video_id,round((100*(play_over_times/play_times) + 5*like_num+3*comment_num+2*retweet_num)/(not_play_days+1),0) as "hot_index"
from(
select tu.video_id, count(1) as "play_times",sum(case when timestampdiff(second,start_time, end_time) >= tv.duration then 1
                                   else 0 end
                                 ) as "play_over_times", sum(if_like) as "like_num", 
                                    sum(
                                    case when comment_id is not null then 1
                                    else 0 end) as "comment_num", sum(if_retweet) as "retweet_num",
                                                            datediff((
                                                            select max(date(end_time))
                                                            from tb_user_video_log
                                                            ), max(date(end_time))) as "not_play_days"
from tb_user_video_log tu join tb_video_info tv
on tu.video_id = tv.video_id
where datediff((select max(end_time) from tb_user_video_log), release_time)<=29 
group by tu.video_id
)t1
order by hot_index desc
limit 0,3

发表于 2024-08-23 01:15:55 回复(0)

Mysql

准备进厂的马后炮炮手很会编程

WITH
    t1 AS (SELECT
               tb_video_info.video_id,
               #完播率，直接用TIMESTAMPDIFF（）然后筛选出大于视频时长的数据，利用sum（）聚合求出总数据量，最后除以每类视频总数
               SUM(TIMESTAMPDIFF(SECOND, start_time, end_time) >= duration) / COUNT(*) AS video_completion_rate,
              #点赞数
               SUM(if_like)                                                            AS likes,
                #转发数
               SUM(if_retweet)                                                         AS total_forwarding,
                #评论如果是null则为0，否则为1，便于sum（）聚合统计评论数
               SUM(IF(comment_id IS NULL, 0, 1))                                       AS number_of_comments,
                #！！！重点！！！（我觉得是重点）
                                #最近无播放天数，即在最大的end_time减去tb_user_video_log表中的最大值
               DATEDIFF(DATE((SELECT MAX(end_time) FROM tb_user_video_log)),
                        MAX(DATE(end_time)))                                           AS no_view_days
           FROM
               tb_user_video_log
                   JOIN tb_video_info ON tb_user_video_log.video_id = tb_video_info.video_id
           WHERE    #！！！重点重点！！！（我觉得是重点）
                               #筛选条件根据题目可知要用最大的结束日期减去发布日期（release_time）
               DATEDIFF(DATE((SELECT MAX(end_time) FROM tb_user_video_log)), DATE(release_time)) <= 29
           GROUP BY
               tb_video_info.video_id)
SELECT
    t1.video_id                  AS video_id,
    ROUND((t1.video_completion_rate * 100 + t1.likes * 5 + t1.number_of_comments * 3 + t1.total_forwarding * 2) /
          (t1.no_view_days + 1)) AS hot_index
FROM
    t1
ORDER BY
    hot_index DESC
LIMIT 3;

发表于 2024-07-25 18:32:42 回复(0)

Mysql

240623