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

WITH                -- 使用CTE
wb AS (             -- 先关联两张表，然后计算出每个视频的播放进度
    SELECT
        u.uid,u.video_id,v.tag,v.duration
        ,CASE
            WHEN TIMESTAMPDIFF(SECOND,u.start_time,u.end_time) >= v.duration THEN 1     -- 把播放时间转换为秒，并且播放时间超过视频长度的播放率记作1
            WHEN TIMESTAMPDIFF(SECOND,u.start_time,u.end_time) < v.duration THEN TIMESTAMPDIFF(SECOND,u.start_time,u.end_time) *1.0 / v.duration
            /*播放时间小于视频长度的，以播放时长/视频长度计算播放进度，这里加*1.0是为了保证计算精度，不加可能导致计算结果错误*/
            ELSE 0 END AS jindu     -- 防止出现灵异现象
    FROM tb_user_video_log AS u
    INNER JOIN tb_video_info AS v
        ON u.video_id = v.video_id
)

SELECT
    tag,
    CONCAT(     -- 在计算结果后加上%符号
        ROUND(AVG(jindu) * 100,2)       -- 保留小数点后两位数，题干没有要求，但是输出示例是这样
    ,'%') AS avg_play_progress
FROM wb
GROUP BY tag
HAVING  AVG(jindu) > 0.6                -- 只输出平均播放进度在60%以上的视频
ORDER BY avg_play_progress DESC;        -- 按照视频的平均播放进度降序，题干没有要求，但是输出示例是这样