select tag,
concat(round((sum(if(TIMESTAMPDIFF(second,start_time,end_time)>duration,100,TIMESTAMPDIFF(second,start_time,end_time)*100/duration)))/count(u.video_id),2),'%') as avg_play_progress
from tb_user_video_log u join tb_video_info v on u.video_id=v.video_id
group by v.tag
having avg_play_progress>60
order by avg_play_progress desc
解析:CONCAT(str1,str2,…) 函数拼接字符串
IF(expr1,expr2,expr3)
TIMESTAMPDIFF()
round()
sum()
#使用TIMESTAMPDIFF()函数计算时间差,再用if函数,若时间差大于播放时长返回100,否则返回进度比值,这里需要注意的是在差值前*100统一数值,最后除以视频个数得到平均值,having对分组后的数据判断大于某一值的输出。