题解 | #平均播放进度大于60%的视频类别#
平均播放进度大于60%的视频类别
http://www.nowcoder.com/practice/c60242566ad94bc29959de0cdc6d95ef
注意播放时长大于视频时长的情况！！！
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
