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

问题：计算各类视频的平均播放进度，将进度大于60%的类别输出
 先计算各类视频平均播放进度,由于给了一个前提：当播放时长大于视频时长时，播放进度均记为100%
 所以用if()函数进行判断 if(timestampdiff(second,a.start_time,a.end_time)> b.duration,1,
              timestampdiff(second,a.start_time,a.end_time)/b.duration)
然后再嵌套round()与avg()函数计算出每个类型的平均播放进度在与tb_video_info连接得出，每个类型对应的平均进度

•  select a.video_id,round(avg(if(timestampdiff(second,a.start_time,a.end_time)>  b.duration,1,
•             timestampdiff(second,a.start_time,a.end_time)/b.duration))*100,2)   as avg_play_progress
•  from tb_user_video_log as a
•  join tb_video_info as b
•  on a.video_id=b.video_id
•  group by a.video_id

 最后在连接tb_video_info找出对应的tag,然后concat()改成百分比

• select g.tag, concat(f.avg_play_progress,'%')avg_play_progress
• from 
• (
• select a.video_id,round(avg(if(timestampdiff(second,a.start_time,a.end_time)>  b.duration,1,
•              timestampdiff(second,a.start_time,a.end_time)/b.duration))*100,2)   as avg_play_progress
• from tb_user_video_log as a
• join tb_video_info as b
• on a.video_id=b.video_id
• group by a.video_id
• ) as f
• join  tb_video_info as g
• on f.video_id=g.video_id
• where avg_play_progress>60
• order by  avg_play_progress desc ;