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

计算时间跨度不能简单地使用减法，要用TIMESTAMPDIFF (SECOND, start_time, end_time) / duration来计算

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