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

select ti.tag,
concat(round(avg(
    if(timestampdiff(second, tl.start_time, tl.end_time) > ti.duration, 100,
       timestampdiff(second, tl.start_time, tl.end_time) / ti.duration*100)
), 2),'%') as avg_play_progress
from tb_user_video_log as tl left join tb_video_info as ti
on tl.video_id=ti.video_id
group by ti.tag
having(avg(timestampdiff(second,tl.start_time,tl.end_time)/ti.duration)>0.6)
order by avg_play_progress desc