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

select 
tag,
concat(round(avg(rate)*100,2),'%') 
from( 
select  
t2.tag,
(if((unix_timestamp(t1.end_time)-unix_timestamp(t1.start_time))>t2.duration,t2.duration,(unix_timestamp(t1.end_time)-unix_timestamp(t1.start_time))))/t2.duration rate
from
tb_user_video_log t1
left join
tb_video_info t2
on t1.video_id=t2.video_id
) t1
group by tag
having avg(rate)>0.6
order by avg(rate) desc