SQL实战（1）——计算短视频完播率

shubrewer

于 2022-04-24 23:40:41 发布

阅读量1.2k

点赞数

分类专栏： SQL实战文章标签： mysql

本文链接：https://blog.csdn.net/shubrewer/article/details/124393406

版权

本文通过一个实际问题介绍如何使用SQL计算2021年每个视频的完播率，并按完播率降序排序。完播率定义为完成播放次数占总播放次数的比例，当结束观看时间与开始播放时间的差大于等于视频时长时视为完成播放。文中涉及UNIX_TIMESTAMP()函数、timestampdiff()函数以及日期提取等SQL知识点。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

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

SELECT t1.video_id vid,
round(sum(IF(t2.duration <= (UNIX_TIMESTAMP(t1.end_time)-UNIX_TIMESTAMP(t1.start_time)),1,0))/count(1),3) rate
from tb_user_video_log t1
left join tb_video_info t2
on t1.video_id = t2.video_id
where SUBSTR(t1.start_time,1,4) = 2021
GROUP BY vid
order by rate desc

知识点总结：

1. UNIX_TIMESTAMP()函数可以把date或datetime格式的数据变成时间戳，时间戳可以相减，得到以秒为单位的时间差（不能直接用 date格式的数据相减），时间差还能用timestampdiff()函数计算：

UNIX_TIMESTAMP(end_tim