题解 | #各个视频的平均完播率#简单粗暴暴力解法

先展示一波代码，再写思路

SELECT a.video_id,
ROUND(SUM(if(date_sub(end_time,interval duration second) >= start_time,1,0))/COUNT(start_time),3) as avg_comp_play_rate
FROM tb_user_video_log as a
LEFT JOIN tb_video_info as b
ON a.video_id = b.video_id
WHERE year(start_time) = '2021'
GROUP by a.video_id
ORDER BY avg_comp_play_rate desc

首先先把简单的框架写出来：

1. 计算2021年 WHERE year(start_time) = '2021'
2. 每个视频 GROUP by a.video_id
3. 完播率降序排序 ORDER BY avg_comp_play_rate desc
4. 完播率 = 完成播放次数/总播放此
5. 总播放次 = COUNT(start_time)
6. 完成播放次 = SUM(if(date_sub(end_time,interval duration second) >= start_time,1,0)
7. PS：简单介绍一下这个完成播放次的写法，首先想到的是date_sub（）函数，通过往回漂移duration对应视频所返回的时间来确认是否看完，所以只要往回漂移后的时间大于等于START_TIME的话，那就相当于是看完视频的，其次考虑的是如何统计次数，我们引出IF（）函数来判断，若返回为TRUE，则返回1，否则返回0，然后用SUM（）函数统计1一共有多少，则可以统计到完成播放次数
8. 保留三位小数则可以用round（,3）套娃完播率；
9. 左连接两个表即可