【SQL 01 计算播放视频的平均完播率】

题目描述

用户-视频互动表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	1	1	NULL
3	102	2001	2021-10-01 11:00:00	2021-10-01 11:00:34	0	0	0	1732526
4	103	2002	2021-09-01 10:00:00	2021-9-01 10:00:42	0	0	1	NULL
5	101	2002	2021-10-01 11:00:00	2021-10-01 10: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-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.718

解题思路

1.取数范围：

• 年份 = 2001
• 有播放记录的每个视频

2.取数字段：

• video_id (有播放记录的视频号）
• avg_comp_play_rate（完播率）

3.解题关键是拆解完播率的计算：

SQL字段1：

SELECT a.video_id ,
       round(sum(if(end_time - start_time >= duration, 1, 0))/count(start_time ),3) as avg_comp_play_rate
FROM tb_user_video_log AS tuvl
#左连接的目的是确保：统计有播放记录的视频号
LEFT JOIN tb_video_info AS tvi
on tuvl.video_id = tvi.video_id
# 限制统计范围：年份 = 2021
WHERE year(start_time) = 2021
# 按视频号（video_id）分类汇总统计 
GROUP BY a.video_id
# 根据完播率降序排列
ORDER BY avg_comp_play_rate DESC;

SQL字段2：

SELECT a.video_id ,
       round(sum(IF(TIMESTAMPDIFF(SECOND, start_time, end_time)>=duration, 1, 0),3) as avg_comp_play_rate
FROM tb_user_video_log AS tuvl
#左连接的目的是确保：统计有播放记录的视频号
LEFT JOIN tb_video_info AS tvi
on tuvl.video_id = tvi.video_id
# 限制统计范围：年份 = 2021
WHERE year(start_time) = 2021
# 按视频号（video_id）分类汇总统计 
GROUP BY a.video_id
# 根据完播率降序排列
ORDER BY avg_comp_play_rate DESC;

Knowleage Point 知识点

TIMESTAMPDIFF

根据单位返回时间差,对于传入的begin和end不需要相同的数据结构,可以存在一个为Date一个DateTime

语法：

• TIMESTAMPDIFF(unit,begin,end)

Unit

• MICROSECOND 毫秒
• SECOND 秒
• MINUTE 分钟
• HOUR 小时
• DAY 天
• WEEK 星期
• MONTH 月
• QUARTER 季度
• YEAR 年

TIMESTAMPDIFF函数容易疏忽的地方

select timestampdiff(day,"2012-08-22 15:15:16","2012-08-23 15:15:16");

1

select timestampdiff(day,"2012-08-22 15:15:15","2012-08-23 15:15:16");

1

select timestampdiff(day,"2012-08-22 15:15:17","2012-08-23 15:15:16");

0

天数的时间差是以246060（86400）为依据的。 假如相差间隔小于86400秒，则为0，如果等于86400秒或大于86400秒且小于86400*2秒，则为1。

如果单位为分钟，那么计算间隔分钟是以60秒为依据的。 假如相差间隔小于60秒，则为0，如果等于60秒或大于60秒且小于60*2秒，则为1。

select timestampdiff(minute,"2012-08-22 15:15:16","2012-08-22 15:16:15");

0

select timestampdiff(minute,"2012-08-22 15:15:16","2012-08-22 15:16:16");

1

select timestampdiff(minute,"2012-08-22 15:15:16","2012-08-22 15:16:56");

1

select timestampdiff(minute,"2012-08-22 15:15","2012-08-22 15:16");

1

Reference

戴杭林的博客园
牛客网练习
https://blog.csdn.net/zm2714/article/details/7896381