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

select
t.tag, concat(round(avg(if(t.duration < t.difftime, 1, t.difftime/t.duration)*100),2),'%') 	avg_play_progress
from(
    select
    user.uid, user.video_id, user.start_time, user.end_time, video.duration, timestampdiff(second, user.start_time, user.end_time) difftime, video.tag
    from
    tb_user_video_log user left join tb_video_info video
    on user.video_id = video.video_id
) t
group by t.tag
having round(avg(if(t.duration < t.difftime, 1, t.difftime/t.duration)*100),2) > 60
order by avg_play_progress desc