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

=

select tag
      ,concat(cast(round(avg(jd)*100,2) as char),'%') as avg_play_progress
from (
select tvl.*
      ,tvi.duration
      ,tvi.tag
	  ,timediff(end_time,start_time) as df
      ,if(timediff(end_time,start_time)/tvi.duration>1,1,timediff(end_time,start_time)/tvi.duration) as jd
from tb_user_video_log as tvl
left join  tb_video_info as tvi
on tvl.video_id=tvi.video_id
) as a
group by tag
having avg(jd)>0.6
order by avg_play_progress desc