题解 | #各个视频的平均完播率#

SELECT a.video_id
     , ROUND(COUNT(IF(TIMESTAMPDIFF(SECOND, start_time, end_time) >= duration, 1, NULL)) / COUNT(*),
             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 a.video_id
ORDER BY avg_comp_play_rate DESC
;

思路:因为是有播放记录的每个视频完播率，但是要用到tb_video_info里的duration，所以 from tb_user_video_log a left join tb_video_info b on a.video_id = b.video_id

时间在2021年 = where year(start_time) = 2021

完播率 = 完成播放的视频数量 / 总播放数量

完成播放的视频数量 = count(if(timestampdiff(second,start_time,end_time) >= duration,1,null))

总播放量 = count(*)

保留三位小数 round(count(if(timestampdiff(second,start_time,end_time) >= duration,1,null)) / count(*),3) as avg_comp_play_rate