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

SELECT b.tag, 
CONCAT(
    ROUND(
        AVG(
            (CASE WHEN TIMESTAMPDIFF(SECOND,a.start_time,a.end_time) >= b.duration  THEN 1 
            ELSE (TIMESTAMPDIFF(SECOND,a.start_time,a.end_time)/b.duration) END)*100)
            /* IF (TIMESTAMPDIFF(SECOND,a.start_time,a.end_time) >= b.duration,1,      TIMESTAMPDIFF(SECOND,a.start_time,a.end_time)/b.duration)*100)*/, 
    2),
'%') 
AS avg_play_progress
FROM tb_user_video_log AS a
LEFT JOIN tb_video_info AS b
ON a.video_id = b.video_id
GROUP BY b.tag
HAVING AVG(CASE WHEN TIMESTAMPDIFF(SECOND,a.start_time,a.end_time) >= b.duration THEN 1 ELSE TIMESTAMPDIFF(SECOND,a.start_time,a.end_time)/b.duration END) > 0.6

/* HAVING AVG(IF(TIMESTAMPDIFF(SECOND,a.start_time,a.end_time) >= b.duration,1,      TIMESTAMPDIFF(SECOND,a.start_time,a.end_time)/b.duration)) > 0.6 */

ORDER BY avg_play_progress DESC