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

WITH t1 AS (
    SELECT tag,
           ROUND(AVG(IF(
                       TIMESTAMPDIFF(SECOND, start_time, end_time) / duration > 1,
                       100,
                       TIMESTAMPDIFF(SECOND, start_time, end_time) / duration * 100
                   )) OVER (PARTITION BY tag),2) AS avg_play_progress
    FROM tb_user_video_log tu
             LEFT JOIN tb_video_info tv
                       ON tu.video_id = tv.video_id
)
SELECT DISTINCT tag,
    CONCAT (avg_play_progress, '%') AS avg_play_progress
       FROM t1
WHERE avg_play_progress > 60
ORDER BY avg_play_progress DESC;

# 注意一点就是，秒数相减要采用 TIMESTAMPDIFF（单位，start，end）函数