方法1:
select
a.tag,
avg_play_progress
from (
select
tag,
concat(round(avg(if(timestampdiff(second,start_time,end_time)>=duration,
1,
timestampdiff(second,start_time,end_time)/duration)*100),2),
"%") as avg_play_progress
from tb_user_video_log log
join tb_video_info info
on log.video_id=info.video_id
group by tag
) a
where replace(avg_play_progress,"%","")>60
order by avg_play_progress desc
方法2:
#第一步 取出需要的数据,主要是取出每条视频的播放时间
with t as (select tag,TIMESTAMPDIFF(second,start_time,end_time) times,duration d
from tb_user_video_log tl left join tb_video_info ti on tl.video_id = ti.video_id)
#第二步,利用if函数计算每条视频的播放率,在利用avg()函数计算各类视频的平均完播率
select tag, concat(round(avg(if(times <= d,times/d*100,100)),2),'%') avg_play_progress
from t
group by tag
having avg(if(times <= d,times/d*100,100)) > 60
order by avg_play_progress desc