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

# SELECT lo.video_id, 
#     ROUND(SUM(IF(TIMESTAMPDIFF(second, start_time, end_time)>=duration,1,0))/COUNT(start_time),3) AS avg_comp_play_rate
# FROM tb_user_video_log lo
# LEFT JOIN tb_video_info info ON lo.video_id = info.video_id
# WHERE YEAR(start_time)= '2021'
# GROUP BY video_id
# ORDER BY avg_comp_play_rate DESC
SELECT 
    lo.video_id,
    ROUND(SUM(IF(end_time - start_time >= duration,1,0))/COUNT(start_time),3) AS avg_comp_play_rate
FROM tb_user_video_log lo
LEFT JOIN tb_video_info info ON lo.video_id = info.video_id
WHERE YEAR(start_time) = 2021
GROUP BY lo.video_id
ORDER BY avg_comp_play_rate DESC;