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

SELECT 
    tag,
    CONCAT(CAST(round(
        100*AVG(
            IF(TIMESTAMPDIFF(second, start_time, end_time)<=duration,TIMESTAMPDIFF(second, start_time, end_time),duration
              )/duration),2)AS CHAR),'%') avg_play_progress
FROM 
    tb_user_video_log 
JOIN 
    tb_video_info 
ON 
    tb_user_video_log.video_id=tb_video_info.video_id
GROUP BY 
    tag
HAVING 
    AVG(IF(TIMESTAMPDIFF(second, start_time, end_time)<=duration,TIMESTAMPDIFF(second, start_time, end_time),duration)/duration)>0.6
ORDER BY 
    avg_play_progress DESC