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

select a.tag,CONCAT(a.rate, "%") aa from
(
select i.tag, 
round(
avg(if(timestampdiff(second,l.start_time,l.end_time) >=i.duration,1,timestampdiff(second,l.start_time,l.end_time)/i.duration))*100
,2)
rate 
from tb_user_video_log l left join tb_video_info i on l.video_id=i.video_id
group by i.tag having rate>60 ) a order by aa desc