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

with tag_avg as (select
    v.tag,
    round(avg(
        case when timestampdiff(second,start_time,end_time)<=v.duration then timestampdiff(second,start_time,end_time)/v.duration else 1 end
    )*100,2) as avg_play
from tb_user_video_log u 
left join tb_video_info v on u.video_id=v.video_id
group by v.tag
having avg_play>60)
select 
    tag,
    concat(avg_play,'%') as avg_play_progress
from tag_avg
order by avg_play desc

1. 字符串是不能排序或者比大小的
2. 要注意边界情况，算百分比时要考虑百分百以外还是百分百