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

#完整版：和算完播率一样先按照每一条记录整理出播放时长和视频时长， 再从子表里按照分类group by, 计算播放进度
select tag, concat(avg_1,'%') as avg_play_progress 
from 
(select tag, 
round(avg(if(total_time/duration>1, 1, total_time/duration))*100,2) as avg_1
from 
(select uid, 
TIMESTAMPDIFF(SECOND, start_time, end_time) as total_time,
tag, duration
from tb_user_video_log uv
left join tb_video_info vi
on uv.video_id=vi.video_id ) a
group by tag) aa
where avg_1>60
order by 2 desc


#简单版
select tag, concat(avg_1,'%') as avg_play_progress
from 
(
select tag, round(avg(if((timestampdiff(second, start_time, end_time)/duration)>1,1,(timestampdiff(second, start_time, end_time)/duration)))*100, 2) as avg_1
from tb_user_video_log uv
left join tb_video_info vi
on uv.video_id= vi.video_id
group by tag
) a
where avg_1>60
order by 2 desc