题解 | 非常粗糙的求平均播放进度大于60%的视频类别

SELECT tvi.tag, CONCAT
(ROUND
    (AVG
        (IF
            (TIMESTAMPDIFF(SECOND,start_time,end_time)>tvi.duration,1,
            TIMESTAMPDIFF(SECOND,start_time,end_time)/tvi.duration)   
        )*100,2
    ),"%"
) AS avg_plg_progress
FROM tb_user_video_log tul
LEFT JOIN tb_video_info tvi
ON tul.video_id=tvi.video_id
GROUP BY tvi.tag
HAVING AVG(IF(TIMESTAMPDIFF(SECOND,start_time,end_time)>tvi.duration,1,TIMESTAMPDIFF(SECOND,start_time,end_time)/tvi.duration)) > 0.6
ORDER BY avg_plg_progress DESC;