题解 | #平均播放进度大于60%的视频类别#

SELECT
    i.tag,
    CONCAT (
        ROUND(
            AVG(
                IF (
            TIMESTAMPDIFF (SECOND, v.start_time, v.end_time) > i.duration,1,
            TIMESTAMPDIFF (SECOND, v.start_time, v.end_time) / i.duration
            )
        ) * 100,
        2
        ),
        '%'
    ) AS playrate
FROM
    tb_user_video_log AS v
    JOIN tb_video_info AS i ON i.video_id = v.video_id
GROUP BY
    i.tag
HAVING
    SUBSTRING_INDEX (playrate, '%', 1) > 60
ORDER BY
    SUBSTRING_INDEX (playrate, '%', 1) DESC

1. 观看时长>视频时长，完播率应该是100%，不能大于100%,否则会出现影视的观看完播率=95.66%，实际情况则是只有90%