SELECT i.tag, CONCAT( #设置百分号显示 concat(number,'%') ROUND( #保留小数位round(number,保留位数) AVG( #计算平均值 IF(TIMESTAMPDIFF(SECOND,l.start_time,l.end_time) > duration,100 ,TIMESTAMPDIFF(SECOND,l.start_time,l.end_time)/duration * 100)),2),'%')
#判断播放时长是不是大于视频时长,如果大于视频时长默认进度为100,否则计算实际播放进度 AS avg_play_progress FROM tb_user_video_log l LEFT JOIN tb_video_info i ON l.video_id=i.video_id GROUP BY i.tag HAVING avg_play_progress >60 ORDER BY avg_play_progress DESC #筛选出播放进度大于60的并降序排列