题解 | #平均播放进度大于60%的视频类别#
平均播放进度大于60%的视频类别
http://www.nowcoder.com/practice/c60242566ad94bc29959de0cdc6d95ef
--having 这块不能直接用avg_play_progress 做筛选条件 筛选大于60% 的 因为实际avg_play_progress 是concat后的字符串 具体代码如下：
SELECT t.tag,
CONCAT(
ROUND(
SUM(
IF (TIMESTAMPDIFF(SECOND,u.start_time,u.end_time)>= t.duration,1,
TIMESTAMPDIFF(SECOND,u.start_time,u.end_time)/t.duration)
)/COUNT(u.video_id)*100 ,2),'%')
AS avg_play_progress
FROM tb_user_video_log u
INNER JOIN tb_video_info t
ON u.video_id = t.video_id
GROUP BY u.video_id
HAVING
SUM(
IF (TIMESTAMPDIFF(SECOND,u.start_time,u.end_time)>= t.duration,1,
TIMESTAMPDIFF(SECOND,u.start_time,u.end_time)/t.duration)
)/COUNT(u.video_id)*100 > 60
ORDER BY avg_play_progress DESC;
--
