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

select t.tag,
       concat(cast(round(t.tmp_avg_play_progress*100,2) as char),'%') as avg_play_progress
from (
select c.tag,
       sum(if((c.time/c.duration)>=1,1,c.time/c.duration))/count(c.tag) as tmp_avg_play_progress
from
(select (unix_timestamp(a.end_time)-unix_timestamp(a.start_time)) as time,
        b.tag,
        b.duration
from tb_user_video_log a inner join tb_video_info b on a.video_id=b.video_id) c
group by c.tag) t
where t.tmp_avg_play_progress > 0.6
order by avg_play_progress desc