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

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_video_info 
inner join tb_user_video_log using(video_id)
#on video_id = b.video_id 
group by tag
having avg(if(TIMESTAMPDIFF(second, start_time, end_time) >= duration, 1,timestampdiff(second, start_time,end_time)/ duration)) * 100 > 60
order by avg_play_progress DESC

TIMESTAMPDIFF()函数：

TIMESTAMPDIFF(unit, begin, end)

unit:这个参数是确定（end-begin）的结果的单位，表示为整数。以下是有效单位：

begin和end:是date或datetime格式的表示

if(条件，满足（1），不满足(0))