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

SELECT
    video_id,
    ROUND(SUM(IF(play_time >= duration, 1, 0)) / COUNT(play_time), 3) AS avg_comp_play_rate #播放记录且大于时常
FROM(
    SELECT
        tu.video_id,
        TIMESTAMPDIFF(SECOND, start_time, end_time) AS play_time,
        duration
    FROM tb_user_video_log tu
    JOIN tb_video_info tv
    ON tu.video_id = tv.video_id
    WHERE YEAR(start_time) = 2021 # 条件1: 2021年
) AS tb1
GROUP BY video_id
ORDER BY avg_comp_play_rate DESC