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

#先查出每条互动记录的播放进度
#在按tag分组 查平均播放进度
select t4.tag , concat(round(avg(t3.bofangjindu),2),"%") avg_play_progress
from (
    select
    t1.video_id ,
    (if(((hour(timediff(t1.end_time,t1.start_time))*60*60+minute(timediff(t1.end_time,t1.start_time))*60 + second(timediff(t1.end_time,t1.start_time)))/t2.duration)*100 
    >=100,
    100,
    ((hour(timediff(t1.end_time,t1.start_time))*60*60+minute(timediff(t1.end_time,t1.start_time))*60 + second(timediff(t1.end_time,t1.start_time)))/t2.duration)*100))
    bofangjindu
    from tb_user_video_log t1 
    left outer join 
    tb_video_info t2
    on t1.video_id = t2.video_id 
) t3 
left outer join 
tb_video_info t4
on t3.video_id = t4.video_id
group by tag
having avg_play_progress > 60
order by avg_play_progress desc ;