首页 > 试题广场 >

各个视频的平均完播率

[编程题]各个视频的平均完播率
  • 热度指数：212966 时间限制：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:24
 0 0 1 NULL
3 103
 2001
 2021-10-01  11:00:00
 2021-10-01  11:00:34
 0 1 0 1732526
4 101
 2002
 2021-09-01  10:00:00
 2021-9-01  10:00:42
 1 0 1 NULL
5 102
 2002
 2021-10-01  11:00:00
 2021-10-01  10:00:30
 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-发布时间）

问题：计算2021年里有播放记录的每个视频的完播率(结果保留三位小数)，并按完播率降序排序
注：视频完播率是指完成播放次数占总播放次数的比例。简单起见，结束观看时间与开始播放时间的差>=视频时长时，视为完成播放。

输出示例：
示例数据的结果如下：
video_id avg_comp_play_rate
2001 0.667
2002 0.000
解释：
视频2001在2021年10月有3次播放记录，观看时长分别为30秒、24秒、34秒，视频时长30秒，因此有两次是被认为完成播放了的，故完播率为0.667；
视频2002在2021年9月和10月共2次播放记录，观看时长分别为42秒、30秒，视频时长60秒，故完播率为0.000。
示例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:24', 0, 0, 1, null),
  (103, 2001, '2021-10-01 11:00:00', '2021-10-01 11:00:34', 0, 1, 0, 1732526),
  (101, 2002, '2021-09-01 10:00:00', '2021-09-01 10:00:42', 1, 0, 1, null),
  (102, 2002, '2021-10-01 11:00:00', '2021-10-01 11:00:30', 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, '2021-01-01 7:00:00');

输出

2001|0.667
2002|0.000
算法知识视频讲解
添加回答
NEU_MARIO头像 NEU_MARIO
我是废物

发表于 2022-03-24 21:11:11 回复(29)
Mysql
不想睡觉zzz头像 不想睡觉zzz 
select u.video_id,
round(avg(case when timestampdiff(second,u.start_time,u.end_time)>= v.duration then 1 else 0 end),3) avg_comp_play_rate
from tb_user_video_log u inner join tb_video_info v on v.video_id=u.video_id
where date_format(start_time,'%Y')=2021
and date_format(end_time,'%Y')=2021
group by u.video_id
order by avg_comp_play_rate desc
avg真的是求某种率的神器，yyds!
发表于 2021-12-03 08:28:23 回复(9)
Mysql
香菜代言人头像 香菜代言人
万一有人倍速了怎么算完播率呢
发表于 2022-03-25 01:08:34 回复(16)
phXu头像 phXu 
select u.video_id,
round(avg(case when timestampdiff(second,u.start_time,u.end_time)>= v.duration then 1 else 0 end),3) avg_comp_play_rate
from tb_user_video_log u inner join tb_video_info v on v.video_id=u.video_id
where date_format(start_time,'%Y')=2021
and date_format(end_time,'%Y')=2021
group by u.video_id
order by avg_comp_play_rate desc

# 这道题可以学到的东西
# round（）
# case when...then...else...end
# date_format()
# avg()在这道题的用法
# 熟能生巧 一起加油

发表于 2021-12-19 19:55:07 回复(8)
Mysql
Tiffany_tang头像 Tiffany_tang
select l.video_id, 
round(AVG(case when timestampdiff(second, start_time, end_time)>= duration then 1 else 0 end), 3) avg_comp_play_rate
from tb_user_video_log l
left join tb_video_info i 
on l.video_id=i.video_id
where year(start_time)=2021
group by l.video_id
order by avg_comp_play_rate DESC

发表于 2021-12-01 10:14:09 回复(0)
Mysql
月光死磕英语头像 月光死磕英语
select g.video_id,
 round(sum(if((end_time-start_time-n.duration) >=0,1,0))/count(g.video_id),3)
as avg_comp_play_rate
from tb_video_info n inner join tb_user_video_log g 
on g.video_id=n.video_id
where release_time between '2021-01-01' and '2021-12-31'
group by g.video_id
order by avg_comp_play_rate desc

为什么自测运行通过了，但是保存提交会失败呢,自测运行预期输出两行，保存提交就出现第三行了我不理解。。
发表于 2021-12-15 17:09:08 回复(3)
小螺号嘎嘎嘎头像 小螺号嘎嘎嘎 
with t as (
    SELECT 
      t1.video_id,
      CASE WHEN 
         end_time-start_time >= duration
      THEN 1 ELSE 0 END as flag
    FROM tb_user_video_log t1
    LEFT JOIN tb_video_info t2
    ON t1.video_id = t2.video_id
    WHERE YEAR(t1.start_time) = 2021
) 
SELECT 
    video_id, ROUND(avg(flag), 3) as avg_comp_play_rate
from t
GROUP BY video_id
ORDER BY avg_comp_play_rate DESC

发表于 2021-12-02 20:23:53 回复(3)
Mysql
zyiiiiiii头像 zyiiiiiii
/*
完播率降序 order by avg_comp_play_rate desc
2021播放记录 TIMESTAMP(second,start_time,end_time) != 0 and year(a.start_time) = '2021'
完播率 sum(if(timestamp(second,start_time,end_time) >= duration,1,0)) / COUNT(video_id)
每个视频 group by video_id
连接 inner join
*/
当我把这些要点都列出来后，运行结果才发现0.667和0.6667的区别
所以这题的考点还有一个就是 round函数
没想到取三位小数也是个考点，我一直以为返回的结果就是那样的哈哈哈😂
发表于 2022-01-02 16:01:43 回复(0)
牛客176569499号头像 牛客176569499号
代码：
自己写的，注意按照完播率倒序排序，我因为这个原因一直错误。 
select 
  a.video_id
  ,round(sum(if(timestampdiff(SECOND,a.start_time,a.end_time) - b.duration >= 0,1,0)) 
  / count(if(timestampdiff(SECOND,a.start_time,a.end_time) - b.duration >= 0,1,0)) ,3) as rate1
from tb_user_video_log as a
join tb_video_info as b 
on a.video_id = b.video_id 
where year(a.start_time) = 2021 and year(a.end_time) = 2021
group by a.video_id
order by rate1 desc
;

方法二：
用avg、case when  
select u.video_id,
round(
    avg(
        case when timestampdiff(second,u.start_time,u.end_time)>= v.duration then 1 else 0 end
    )
 ,3) avg_comp_play_rate
from tb_user_video_log u inner join tb_video_info v on v.video_id=u.video_id
where date_format(start_time,'%Y')=2021
and date_format(end_time,'%Y')=2021
group by u.video_id
order by avg_comp_play_rate desc
;--



发表于 2022-07-06 14:37:45 回复(1)
Mysql
牛客897228611号头像 牛客897228611号 
select t1.video_id as video_id,
round(count(if(timestampdiff(second,start_time,end_time) >= duration,1,null)) / count(start_time),3) as avg_comp_play_rate
from tb_user_video_log t1
join tb_video_info t2 using(video_id)
where year(start_time) = '2021'
group by t1.video_id
order by avg_comp_play_rate desc

发表于 2021-12-17 20:59:51 回复(3)
Mysql
行百里者半九十哉头像 行百里者半九十哉
第一次做题，编辑器测试和运行规则不太懂，摸索了一会儿，总算搞定了。总结：审题不仔细，看似简单却容易忽略限制条件，分别为：完播率要降序、完播率为0的项也要保留、题目只要2021年的数据、保留3个小数位。
操作方法：1.点击“自测输入”，点击“载入示例1”，在“自测输入”按钮下面的白色框拉到末尾，加上自己写的SQL（与黑色框写的SQL相同），点击“自测运行”；
2.自测运行结果通过后，点击“保存并提交”，没问题的话就会弹出通过的提示（注：黑色框自己写的SQL应该是在保存提交的时候自动加到用例末尾的，最开始不熟悉容易晕乎）
220415
SQL如下：

SELECT t1.video_id,IFNULL(ROUND(t2.playtime/t1.playtime,3),0) avg_comp_play_rate
FROM 
( SELECT video_id,COUNT(1) playtime FROM tb_user_video_log WHERE YEAR(start_time) = 2021 GROUP BY video_id ) t1
LEFT JOIN ( SELECT tu.video_id,COUNT(1) playtime
FROM tb_user_video_log tu
JOIN tb_video_info ti ON tu.video_id = ti.video_id AND TIMESTAMPDIFF(SECOND,start_time,end_time) >= ti.duration
WHERE YEAR(start_time) = 2021
GROUP BY tu.video_id ) AS t2 ON t1.video_id = t2.video_id
ORDER BY avg_comp_play_rate DESC
发表于 2022-04-15 13:11:45 回复(2)
牛客563312411号头像 牛客563312411号 
select u.video_id,
round(sum(if(timestampdiff(second,start_time,end_time)>=duration,1,0))/count(*),3) avg_comp_play_rate 
from tb_user_video_log u
join tb_video_info 
using(video_id)
where year(start_time)='2021'
group by 1
order by 2 desc

select u.video_id,
round(count(if(timestampdiff(second,start_time,end_time)>=duration,1,null))/count(*),3) avg_comp_play_rate 
from tb_user_video_log u
join tb_video_info 
using(video_id)
where year(start_time)='2021'
group by 1
order by 2 desc
还可以用avg 
select u.video_id,
round(avg(if(timestampdiff(second,start_time,end_time)>=duration,1,0)),3) avg_comp_play_rate 
from tb_user_video_log u
join tb_video_info 
using(video_id)
where year(start_time)='2021'
group by 1
order by 2 desc

编辑于 2024-01-27 14:56:49 回复(0)
dragonxz头像 dragonxz

今天不是废物（微笑

SELECT
    tb.video_id,
    round( count( CASE WHEN tb.done >= 0 THEN 1 END )/ COUNT( 1 ), 3 ) AS avg_comp_play_rate 
FROM
    (
    SELECT
        a.video_id,
        end_time - start_time - duration AS done 
    FROM
        tb_user_video_log a,
        tb_video_info b 
    WHERE
        a.video_id = b.video_id 
        AND YEAR ( end_time ) = '2021' 
    ) AS tb 
GROUP BY
    tb.video_id 
ORDER BY
    avg_comp_play_rate DESC
发表于 2023-04-17 15:16:36 回复(0)
Mysql
牛牛太空喜之郎头像 牛牛太空喜之郎 
select video_id,round(sum(if(TIMESTAMPDIFF(second,start_time,end_time)>=duration,1,0))/count(a.video_id),3) avg_comp_play_rate
from tb_user_video_log a
join tb_video_info b using(video_id)
where year(start_time)='2021'
group by a.video_id
order by 2 desc 

发表于 2022-08-30 23:16:16 回复(0)
Mysql
汤圆侠头像 汤圆侠
select video_id, 
round(sum(case when vt>=duration then 1 else 0 end)/count(video_id),3) avg_comp_play_rate
from (
SELECT video_id,duration,timestampdiff(second, start_time, end_time) as vt
FROM tb_user_video_log
join tb_video_info using (video_id) where year(start_time)=2021) t 
group by video_id
order by avg_comp_play_rate desc
发表于 2022-07-11 23:50:41 回复(0)
Mysql
andycat头像 andycat
好多题解都有错误。time不能直接相减，如果将案例2的video_id =2002的时间加长1s，就通过不了。因为mysql time相减没有将time转换成时间戳而是拼接成了整数。
 s.video_id,round(sum(if(s.duration>=i.duration,1,0))/count(i.duration),3) as avg_comp_play_rate
from(
select video_id,unix_timestamp(end_time)-unix_timestamp(start_time) as duration 
from tb_user_video_log
    where year(end_time)=2021
    )s 
inner join tb_video_info i on s.video_id=i.video_id 
group by s.video_id
order by avg_comp_play_rate desc
发表于 2022-06-30 13:56:50 回复(0)
Mysql
戴子安头像 戴子安
select
    a.video_id
    ,format(sum(if((a.end_time - a.start_time) >= b.duration,1,0))/count(*),3) as avg_comp_play_rate
from tb_user_video_log a
join tb_video_info b
on a.video_id = b.video_id and year(a.start_time) = 2021 and a.start_time is not null
group by a.video_id
order by avg_comp_play_rate desc
;
发表于 2022-04-27 16:26:31 回复(0)
pein531头像 pein531
先把2021年的播放记录过滤出来，再在这张2021年的子表上对视频id进行分组，计算每个视频的“完播率=(完播时间-开播时间)>=视频时长的纪录条数/该视频的记录条数”。 
SELECT
    temp.`video_id`,
    ROUND(AVG(CASE WHEN TIMESTAMPDIFF(SECOND, temp.start_time, temp.end_time)>=duration THEN 1 ELSE 0 END), 3) AS `avg_comp_play_rate`
FROM (SELECT * FROM tb_user_video_log WHERE YEAR(`end_time`)=2021) AS temp
LEFT JOIN tb_video_info ON temp.video_id=tb_video_info.video_id
GROUP BY temp.`video_id`
ORDER BY `avg_comp_play_rate` DESC;

发表于 2021-12-15 13:17:33 回复(0)
Mysql
thosedays头像 thosedays
select uv.video_id, round(sum(if(end_time - start_time >= duration,1,0))/count(uv.video_id),3) as 
avg_comp_play_rate from  
tb_user_video_log as uv
left join tb_video_info as vi 
on uv.video_id = vi.video_id
where 
year(start_time) = 2021 
group by  uv.video_id
order by avg_comp_play_rate desc
发表于 2021-11-30 16:55:19 回复(0)
牛客121305693号头像 牛客121305693号
select
    t1.video_id,
    round(
        sum(
            if (
                datediff (minute, start_time, end_time) >= t1.duration,
                1,
                0
            )
        ) / count(t2.video_id),
        3
    ) as avg_comp_play_rate
from
    tb_video_info t1
    join tb_user_video_log t2 on t1.video_id = t2.video_id
where
    start_time like '2021%'
group by
    t1.video_id
order by
    avg_comp_play_rate desc
有大神能帮我看看哪里不对吗
发表于 2024-01-21 17:54:44 回复(0)
提交观点

问题信息

难度：
504条回答 2020浏览

热门推荐

通过挑战的用户

查看代码

相关试题

扫一扫，把题目装进口袋

求职之前，先上牛客

扫描二维码，进入QQ群

扫描二维码，关注牛客公众号