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

注意播放时长大于视频时长的情况！！！

SELECT tag, concat(FORMAT(AVG(watch_ratio) * 100,2),"%") AS avg_play_progress FROM(
	SELECT log.id AS id, log.video_id AS video_id,IF(TIMESTAMPDIFF(SECOND,log.start_time,log.end_time) > info.duration,1.0,TIMESTAMPDIFF(SECOND,log.start_time,log.end_time)/info.duration)	AS watch_ratio, info.tag AS tag 	FROM tb_user_video_log log LEFT JOIN tb_video_info info 
	ON log.video_id = info.video_id
)temp
GROUP BY tag
HAVING avg_play_progress > 60
ORDER BY avg_play_progress DESC