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

select
    tag,
    CONCAT (avg_play_progress, "%")
from
    (
        select
            i.tag as tag,
            round(
                avg(
                    if (
                        timestampdiff (second, l.start_time, l.end_time) > i.duration,
                        100,
                        (
                            timestampdiff (second, l.start_time, l.end_time) / i.duration
                        ) * 100
                    )
                ),
                2
            ) as avg_play_progress
        from
            tb_user_video_log as l
            inner join tb_video_info as i using (video_id)
        group by
            i.tag
        HAVING
            avg_play_progress > 60
        ORDER BY
            avg_play_progress DESC
    ) as t

• 将两个表连接起来
• 查询tag，并且计算avg_play_progress
• 使用having，将不符合要求的分组过滤
• 将avg_play_progress转化为百分比形式