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

SELECT
    video_id,
    ROUND(AVG(IF(play_time >= duration, 1, 0)), 3) AS avg_comp_play_rate
FROM (
    SELECT
        a.video_id,
        TIMESTAMPDIFF(SECOND, a.start_time, a.end_time) AS play_time,
        b.duration
    FROM tb_user_video_log AS a
    LEFT JOIN tb_video_info AS b
    ON a.video_id = b.video_id
    WHERE YEAR(a.start_time) = 2021) AS t
GROUP BY video_id
ORDER BY avg_comp_play_rate DESC;

WHERE要放在JOIN的后面