select a.tag, concat(round(a.avg_play_progress*100,2),'%') avg_play_progress
from
(select tvi.tag,
avg(if(timestampdiff(second,tuvl.start_time,tuvl.end_time)>tvi.duration,1,
timestampdiff(second,tuvl.start_time,tuvl.end_time)/tvi.duration)) avg_play_progress
from tb_user_video_log tuvl
right join
tb_video_info tvi
on tuvl.video_id=tvi.video_id
group by tvi.tag 
having avg_play_progress>0.6
order by avg_play_progress desc) a
  • 使用timestampdiff 要指定输出的时间单位 second, minute, hour, day 等
  • 使用if 控制播放时间不超过视频时间
  • 最好在最外面一层输出使用round