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

## 下面一种是本人自己的写法，sum()/count() 得到了平均值，相比于avg() 其实逻辑是相同的。第一钟是参考了题解的写法写的。功能上大体类似的哈。但是我的方法略显繁琐有点画蛇添足了。

# 平均播放进度大于60% 的视频类别  
    /*
    select tag,
           avg_play_progress 
      from (
    select tvi.tag ,
           round(avg(if(timestampdiff(second,tvl.start_time,tvl.end_time)>tvi.duration,1,
                        timestampdiff(second,tvl.start_time,tvl.end_time)/tvi.duration))*100,2) avg_play ,
           concat(round(avg(if(timestampdiff(second,tvl.start_time,tvl.end_time)>tvi.duration,1,
                        timestampdiff(second,tvl.start_time,tvl.end_time)/tvi.duration))*100,2),'%') avg_play_progress
      from tb_user_video_log tvl join tb_video_info tvi using(video_id) 
     group by tvi.tag 
     having avg_play > 60 
     order by avg_play desc 
     ) A ;  */

     select tag,
           avg_play_progress 
      from (
    select tvi.tag ,
           round(sum(if(timestampdiff(second,tvl.start_time,tvl.end_time)>tvi.duration,1,
                        timestampdiff(second,tvl.start_time,tvl.end_time)/tvi.duration))/count(tvl.start_time)*100,2) avg_play ,
           concat(round(sum(if(timestampdiff(second,tvl.start_time,tvl.end_time)>tvi.duration,1,
                        timestampdiff(second,tvl.start_time,tvl.end_time)/tvi.duration))/count(tvl.start_time)*100,2),'%') avg_play_progress
      from tb_user_video_log tvl join tb_video_info tvi using(video_id) 
     group by tvi.tag 
     having avg_play > 60 
     order by avg_play desc 
     ) A ;