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的并降序排列