【SQL】各个视频的平均完播率

最新推荐文章于 2024-08-16 22:07:20 发布

_StarryNight_

最新推荐文章于 2024-08-16 22:07:20 发布

阅读量1.1k

点赞数

CC 4.0 BY-SA版权

分类专栏： SQL 文章标签： sql 数据库

本文链接：https://blog.youkuaiyun.com/codelady_g/article/details/131371282

SQL 专栏收录该内容

1 篇文章

订阅专栏

【问题】

计算2021年里有播放记录的每个视频的完播率(结果保留三位小数)，并按完播率降序排序。

【数据】

用户-视频互动表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-发布时间）

输出示例数据：video_id，avg_comp_play_rate

【要点】

$完播率=视频完成播放的次数(end.time−start.time>=duration)视频总的播放次数完播率=\dfrac{视频完成播放的次数(end.time- start_.time>=duration)}{视频总的播放次数}$

保留三位小数 round(x,3)
时间相减 timestampdiff(second,a.start_time,a.end_time)
左连接 left join ，相当于将视频信息添加到互动表中，方便比较播放时长和视频时长
完播率是对同一个视频拥有的多条记录进行计算，因此分组计算 group by video_id
分组要对应聚合函数，可以使用sum，avg 等，实现这个计算逻辑即可
降序排列 order by 列名 desc ，不加 desc 默认升序
注意题目中有关时间的限定（2021年）

【解答】

select b.video_id,
       round(avg(timestampdiff(second,a.start_time,a.end_time)>=b.duration),3) as avg_comp_play_rate
from tb_user_video_log a left join tb_video_info b on a.video_id=b.video_id
where year(a.start_time)=2021
group by b.video_id
order by avg_comp_play_rate desc

完播率的以下几种计算都是可以的，但是第一种最为简洁：

avg(timestampdiff(second,a.start_time,a.end_time)>=b.duration)
sum(timestampdiff(second,a.start_time,a.end_time)>=b.duration)/count(*)
sum(if(timestampdiff(second,a.start_time,a.end_time)>=b.duration,1,0))/count(*)