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

SELECT 
    tb.video_id,
    ROUND(SUM(IF(
    TIME_TO_SEC(end_time)-TIME_TO_SEC(start_time) >= info.duration,
    1,
    0
    ))/COUNT(tb.video_id),3)AS avg_comp_play_rate
FROM tb_user_video_log tb
JOIN tb_video_info info USING(video_id) 
WHERE YEAR(start_time)='2021'
GROUP BY tb.video_id
ORDER BY avg_comp_play_rate DESC