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

select
    tag,
    concat(
        round(
            avg(
                if(
                    timestampdiff(second, u.start_time, u.end_time) / v.duration * 100 > 100,
                    100,
                    timestampdiff(second, u.start_time, u.end_time) / v.duration * 100
                )
            ),
            2
        ),
        '%'
    ) as avg_play_progress
from
    tb_user_video_log u
    left join tb_video_info v on u.video_id = v.video_id
group by
    tag
having
    cast(substr(avg_play_progress,1,5) as float) > 60
order by
    avg_play_progress desc

先用if对超过100的播放进度进行固定

再利用concat函数粘上 '%'

最后利用cast函数强制转化数据类型，在having处筛选处播放进度大于60%的数据