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

 SELECT tag, CONCAT(avg_bfjd, "%") as avg_bfjd
FROM (
    SELECT tag,
        ROUND(AVG(
            IF(TIMESTAMPDIFF(SECOND, start_time, end_time) > duration, 1,
               TIMESTAMPDIFF(SECOND, start_time, end_time) / duration)
        ) * 100, 2) as avg_bfjd
    FROM tb_user_video_log
   left JOIN tb_video_info USING(video_id)
    GROUP BY tag
    HAVING avg_bfjd > 60
    ORDER BY avg_bfjd DESC
) as t_progress;

问题分解：

关联用户-视频互动记录和短视频信息表：tb_user_video_log a left JOIN tb_video_info b on a.video_id=b.video_id

计算每个类别的平均播放进度：

播放进度=播放时长÷视频时长*100%

播放时长=TIMESTAMPDIFF(SECOND, start_time, end_time)；特殊情况：播放时长大于视频时长时，播放进度为100%（加个IF判断）

平均进度=AVG(每个进度)

如果播放时长大于视频时长，播放进度记为100% ：IF(TIMESTAMPDIFF(SECOND, start_time, end_time) > duration, 1,

TIMESTAMPDIFF(SECOND, start_time, end_time) / duration)

结果保留2位小数：ROUND(x, 2)

百分比格式化：CONCAT(x, '%')

按视频类别分组：GROUP BY tag

筛选播放进度>60%的视频类别：HAVING avg_bfjd > 60

按播放进度倒序排序：ORDER BY avg_bfjd DESC