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

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

1、timestampdiff函数，用于计算时间戳函数之间的时间差，可以进行单位的限制。例如：timestampdiff(second,start_time,end_time)

2、concat函数

3、需要注意的点是不能直接avg（播放时长/duration），播放时长大于 duration的时候完播率均为1