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

select v.tag, concat(round(avg(case when TIMESTAMPDIFF(second,u.start_time,u.end_time)>= v.duration then 1 else TIMESTAMPDIFF(second,u.start_time,u.end_time)/v.duration END)*100,2),'%')avg_play_progress
from tb_user_video_log u
join tb_video_info v
on u.video_id=v.video_id
group by v.tag
having avg(case when TIMESTAMPDIFF(second,u.start_time,u.end_time)>= v.duration then 1 else TIMESTAMPDIFF(second,u.start_time,u.end_time)/v.duration END) > 0.6
order by avg_play_progress desc

注意：

①聚类要看准字段名

②最后的形式要搞成xx.xx%，需要先*100，再round，再%