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

select b.tag, concat((round(avg(case when timestampdiff(second,a.start_time,a.end_time)>=b.duration then 1 else (timestampdiff(second,a.start_time,a.end_time))/b.duration end)*100,2)),"%") as avg_play_progress
from tb_user_video_log as a 
left join tb_video_info as b 
on a.video_id=b.video_id
group by b.tag
having replace(avg_play_progress,"%"," ")>60
order by avg_play_progress desc

这道题太折磨人了，首先是concat函数必须在最外面，其次是用timestampdiff精度高，不要直接减，然后就是这个✖️100的位置，如果✖️在外面，先取了2位数再✖️100就会丢失数，所以先✖️100，再取两位数才正确