SELECT v.video_id,
       ROUND(SUM(CASE WHEN TIMESTAMPDIFF(SECOND, uv.start_time, uv.end_time) >= v.duration THEN 1 ELSE 0 END) / COUNT(*), 3) AS completion_rate
FROM tb_user_video_log uv
JOIN tb_video_info v ON uv.video_id = v.video_id
WHERE YEAR(uv.start_time) = 2021
GROUP BY v.video_id
ORDER BY completion_rate DESC;

1. 總播放次數(shù)：這個數(shù)值可以通過統(tǒng)計tb_user_video_log表中每個video_id的記錄數(shù)量來得到
2. 完成播放次數(shù)：我們可以通過比較tb_user_video_log表中的end_time與start_time的差值和tb_video_info表中的duration來確定是否播放完成。如果差值大于等于duration，則視為完成播放