首页 > 试题广场 >

平均播放进度大于60%的视频类别

[编程题]平均播放进度大于60%的视频类别

热度指数：230253 时间限制：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-10-01 10:00:00	2021-10-01 10:00:30	0	1	1	NULL
2	102	2001	2021-10-01 10:00:00	2021-10-01 10:00:21	0	0	1	NULL
3	103	2001	2021-10-01 11:00:50	2021-10-01 11:01:20	0	1	0	1732526
4	102	2002	2021-10-01 11:00:00	2021-10-01 11:00:30	1	0	1	NULL
5	103	2002	2021-10-01 10:59:05	2021-10-01 11:00:05	1	0	1	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-01-01 07:00:00
2	2002	901	美食	60	2021-01-01 07:00:00
3	2003	902	旅游	90	2021-01-01 07:00:00

（video_id-视频ID, author-创作者ID, tag-类别标签, duration-视频时长, release_time-发布时间）

问题：计算各类视频的平均播放进度，将进度大于60%的类别输出。

注：

播放进度=播放时长÷视频时长*100%，当播放时长大于视频时长时，播放进度均记为100%。
结果保留两位小数，并按播放进度倒序排序。

输出示例：

示例数据的输出结果如下：

tag	avg_play_progress
影视	90.00%
美食	75.00%

解释：

影视类视频2001被用户101、102、103看过，播放进度分别为：30秒（100%）、21秒（70%）、30秒（100%），平均播放进度为90.00%（保留两位小数）；

美食类视频2002被用户102、103看过，播放进度分别为：30秒（50%）、60秒（100%），平均播放进度为75.00%（保留两位小数）；

示例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-10-01 10:00:00', '2021-10-01 10:00:30', 0, 1, 1, null),
  (102, 2001, '2021-10-01 10:00:00', '2021-10-01 10:00:21', 0, 0, 1, null),
  (103, 2001, '2021-10-01 11:00:50', '2021-10-01 11:01:20', 0, 1, 0, 1732526),
  (102, 2002, '2021-10-01 11:00:00', '2021-10-01 11:00:30', 1, 0, 1, null),
  (103, 2002, '2021-10-01 10:59:05', '2021-10-01 11:00:05', 1, 0, 1, null);

INSERT INTO tb_video_info(video_id, author, tag, duration, release_time) VALUES
  (2001, 901, '影视', 30, '2021-01-01 7:00:00'),
  (2002, 901, '美食', 60, '2021-01-01 7:00:00'),
  (2003, 902, '旅游', 90, '2020-01-01 7:00:00');

输出

影视|90.00%
美食|75.00%

算法知识视频讲解

牛客473122382号

你管这种题叫简单？

发表于 2022-06-13 21:04:06 回复(10)

Mysql

牛客416285415号

SELECT tag,
       CONCAT(ROUND(AVG(
           CASE WHEN end_time-start_time>duration
                THEN 100
                ELSE 100*(end_time-start_time)/duration
           END),2),'%') AS avg_play_progress
FROM (tb_user_video_log AS uv) JOIN (tb_video_info AS vi) ON uv.video_id=vi.video_id 
GROUP BY tag
HAVING SUBSTRING_INDEX(avg_play_progress,'%',1)>60
ORDER BY avg_play_progress DESC

求各位大佬帮我看看我哪里写错了，自测运行可以正常通过，但是保存并提交就报错。

会比答案多查询到一个旅游类

影视|90.00%

美食|75.00%
旅游|73.33%

发表于 2022-01-16 16:56:43 回复(13)

Mysql

thosedays

select tag ,
concat(round(avg(if(TIMESTAMPDIFF(SECOND,a.start_time,a.end_time)>b.duration,1,TIMESTAMPDIFF(SECOND,a.start_time,a.end_time)/b.duration)) * 100,2) ,'%') as avg_play_progress
from
tb_user_video_log a
left join tb_video_info b on a.video_id = b.video_id
group by tag having replace(avg_play_progress,'%','') > 60
order by avg_play_progress desc

发表于 2021-11-30 18:40:19 回复(16)

Mysql

牛客174807539号

这道题保存并提交时候的2001年第三行播放结束时间和原表中的不一样

2001

2021-10-01 11:00:50

2021-10-01 11:01:25

影视

结果也不一样：

...	实际输出期望输出
1	影视\|95.56%	1	影视\|90.00%
2	美食\|75.00%	2	美食\|75.00%

代码

select tag,
concat(round(avg(timestampdiff(second,start_time,end_time)/duration)*100,2),'%') avg_play_progress
from tb_user_video_log tvl right join tb_video_info tvi on tvl.video_id=tvi.video_id
where year(start_time)=2021 and year(end_time)=2021 and tag in('影视','美食')
group by tag,duration
having avg(timestampdiff(second,start_time,end_time)/duration)*100>60
order by avg_play_progress desc

1 .播放时长>=视频时长的相关语句没有写

发表于 2022-06-27 10:20:28 回复(5)

Mysql

牛客740745719号

#第一步 取出需要的数据，主要是取出每条视频的播放时间
with t as (select tag,TIMESTAMPDIFF(second,start_time,end_time) times,duration d
from tb_user_video_log tl left join tb_video_info ti on tl.video_id = ti.video_id)
#第二步，利用if函数计算每条视频的播放率，在利用avg()函数计算各类视频的平均完播率
select tag, concat(round(avg(if(times <= d,times/d*100,100)),2),'%') avg_play_progress
from t
group by tag
having avg(if(times <= d,times/d*100,100)) > 60
order by avg_play_progress desc

发表于 2021-12-03 18:00:00 回复(6)

PythonNoo6

一定要先*100再保留两位小数

发表于 2021-12-30 11:18:07 回复(6)

Tiffany_tang

select a.tag, avg_play_progress
from (select tag, 
   concat(round(avg(case when timestampdiff(second, start_time, end_time)>=duration then 1 else 
   timestampdiff(second, start_time, end_time)/duration end)*100,2),'%') avg_play_progress
   from tb_user_video_log t1
   join tb_video_info t2
   on t1.video_id=t2.video_id
   group by tag) a
where replace(avg_play_progress,'%','') > 60
order by avg_play_progress DESC

发表于 2021-12-01 11:08:30 回复(4)

Mysql

颠仔

select t2.tag,
concat(round(avg(if(timestampdiff(second,t1.start_time,t1.end_time)>t2.duration,1,timestampdiff(second,t1.start_time,t1.end_time)/t2.duration))*100,2),'%') avg_play_progress 
from tb_user_video_log t1
inner join tb_video_info t2 on t1.video_id = t2.video_id
group by t2.tag
having replace(avg_play_progress,'%','') > 60
order by avg_play_progress desc;

发表于 2022-03-19 11:28:10 回复(0)

Mysql

katelin

select tag,
concat(round(avg(if(timestampdiff(second,start_time,end_time)<duration,timestampdiff(second,start_time,end_time)/duration*100,100)),2),'%') avg_play_progress
from tb_user_video_log u
left join tb_video_info v on u.video_id=v.video_id
group by tag
having avg_play_progress>60
order by avg_play_progress desc;

发表于 2022-02-17 14:31:26 回复(0)

Mysql

Stzolo

select tag, 
case when sum(t_time) / sum(duration)>=1 then '100.00%'
else concat(round(sum(t_time) / sum(duration)*100,2),'%') end as avg_play_progress
from (
    select a.video_id,tag,if(TIME_TO_SEC(timediff(end_time, start_time))>duration, duration,TIME_TO_SEC(timediff(end_time, start_time))) as t_time, duration
    from tb_user_video_log a
    left join tb_video_info b on a.video_id = b.video_id 
) a
group by tag having avg_play_progress>'60.00%'
order by avg_play_progress desc

# 这题可太细了，一不小心就错了

发表于 2022-04-25 20:30:21 回复(0)

Mysql

牛客122287615号

奇了怪了，为啥时间相减就不对，多一个73.33%的旅游，而用timestampdiff就是对的

发表于 2022-04-18 12:52:23 回复(5)

Mysql

phXu

SELECT tag, CONCAT(avg_play_progress, "%") as avg_play_progress
FROM (
SELECT tag,
ROUND(AVG(
IF(TIMESTAMPDIFF(SECOND, start_time, end_time) > duration, 1,
TIMESTAMPDIFF(SECOND, start_time, end_time) / duration)
) * 100, 2) as avg_play_progress
FROM tb_user_video_log
JOIN tb_video_info USING(video_id)
GROUP BY tag
HAVING avg_play_progress > 60
ORDER BY avg_play_progress DESC
) as t_progress;
# 一些知识点
# contact()
# round()
# avg()
# if()
# timestampdiff()

发表于 2022-03-13 20:28:49 回复(1)

牛客724814758号

select tag, concat(round(sum(if(timestampdiff(second, start_time, end_time)>=duration, duration, timestampdiff(second, start_time, end_time))/duration)/count(distinct uid)*100, 2), '%') as avg_play_progress from tb_user_video_log join tb_video_info using(video_id) group by tag 
having avg_play_progress > '60.00%' order by avg_play_progress desc;

发表于 2022-08-11 16:37:21 回复(0)

Mysql

oclass

-- 各类视频 的平均播放进度
select
    tag,
    concat(round(avg(if(timestampdiff(second,start_time,end_time) >= duration,duration,timestampdiff(second,start_time,end_time))/duration*100),2),'%') as avg_play_progress
from tb_user_video_log u
join tb_video_info v using(video_id)
group by tag
-- 筛选进度大于60%
having replace(avg_play_progress,'%','') > 60
order by avg_play_progress desc;

发表于 2022-07-16 14:48:09 回复(1)

Mysql

牛客176569499号

代码：

select 
  aa.tag
  ,concat(aa.rate,'%') as rate
from (  
  select 
    b.tag
    ,round(
      sum(if(timestampdiff(SECOND,a.start_time,a.end_time) - b.duration = 0,
             (timestampdiff(SECOND,a.start_time,a.end_time) / b.duration) ,
             if(timestampdiff(SECOND,a.start_time,a.end_time) - b.duration < 0,
                (timestampdiff(SECOND,a.start_time,a.end_time) / b.duration),1))
         )/count(1) * 100
      ,2)
          as rate
  from tb_user_video_log as a
  join tb_video_info as b 
  on a.video_id = b.video_id 
  group by b.tag
  having rate > 60.00
  order by rate desc
)  as aa
; --

发表于 2022-07-06 16:05:17 回复(0)

戴子安

自己测试能出来结果，但是不行，有没有大神看看啥原因

select

b.tag
,concat(format((sum(if((a.end_time - a.start_time)/b.duration >=1,1,(a.end_time - a.start_time)/b.duration))/count(*)*100),2),'%') as avg_play_progress
from tb_user_video_log a
join tb_video_info b
on a.video_id = b.video_id
group by b.tag
having replace(avg_play_progress,'%','') > 60
order by avg_play_progress desc

发表于 2022-04-27 18:41:00 回复(1)

Mysql

7发明家6极客2个3星5费

select
tag
,concat(round(avg(if(timestampdiff(second,start_time,end_time)/duration>1,1,timestampdiff(second,start_time,end_time)/duration))*100,2),'%') avg_play_progress
from tb_user_video_log tl
left join tb_video_info ti using(video_id)
group by tag
having avg(if(timestampdiff(second,start_time,end_time)/duration>1,1,timestampdiff(second,start_time,end_time)/duration))>0.6
order by avg_play_progress desc

发表于 2022-04-24 22:47:26 回复(0)

Mysql

宝宝别生气啊！

select tag,
concat(round(avg(case when timestampdiff(second,start_time,end_time)>=duration then 1
else timestampdiff(second,start_time,end_time)/duration end)*100,2),'%') as avg_play_progress
from tb_user_video_log t
inner join tb_video_info f
on t.video_id = f.video_id
group by tag
having avg_play_progress > 60
order by avg_play_progress desc

发表于 2021-12-08 10:25:02 回复(0)

牛客145702195号

select
    tag,
    concat (
        round(
            avg(
                case
                    when timestampdiff (second, a.start_time, a.end_time) < b.duration 
                    then 100 * (timestampdiff (second, a.start_time, a.end_time) / b.duration)
                    else 100
                end
            ),
            2
        ),
        '%'
    ) avg_play_progress
from
    tb_user_video_log a
    inner join tb_video_info b on a.video_id = b.video_id
group by
    tag
having
    substring(avg_play_progress, 1, 4) > 60
order by
    avg_play_progress desc;

发表于 2024-05-10 08:35:57 回复(0)

Mysql

勇敢的灰太狼在学习

select   
     tag,
     concat(round(avg(Completion_Rate),2),'%') as avg_play_progress
from 
     (select 
     tb_user_video_log.video_id,
     tag,
     case 
     when (end_time - start_time) >= duration
     then 100.00
     else (end_time - start_time)/duration * 100
     end
     as Completion_Rate
     from tb_user_video_log
     left join tb_video_info
     on tb_video_info.video_id = tb_user_video_log.video_id) A
group by A.tag
having avg(Completion_Rate) > 60;

大佬们，自测可以通过，提交不能通过，为什么呢？

发表于 2024-04-14 21:36:42 回复(0)