题解|百分比转换|#平均播放进度大于60%的视频类别#

# SELECT a.video_id,start_time,end_time,tag,
# ROUND(AVG(TIMESTAMPDIFF(SECOND,start_time,end_time)/duration *100%), 2) AS avg_play_progress
# FROM tb_user_video_log a
# LEFT JOIN tb_video_info b
# ON a.video_id = b.video_id
# GROUP BY tag
# HAVING avg_play_progress >60
# ORDER BY avg_play_progress DESC

# 播放进度：IF(TIMESTAMPDIFF(SECOND,start_time,end_time) > duration,1,TIMESTAMPDIFF(SECOND,start_time,end_time)/duration)
# 平均播放进度:ROUND(AVG(IF(TIMESTAMPDIFF(SECOND,start_time,end_time) > duration,1,TIMESTAMPDIFF(SECOND,start_time,end_time)/duration))*100,2) AS avg_play_progress

SELECT tag,CONCAT(avg_play_progress,'%') AS avg_play_pregress
FROM(
    SELECT tag,ROUND(AVG(IF(TIMESTAMPDIFF(SECOND,start_time,end_time) > duration,1,TIMESTAMPDIFF(SECOND,start_time,end_time)/duration))*100,2) AS avg_play_progress
    FROM tb_user_video_log a
    LEFT JOIN tb_video_info b
    ON a.video_id = b.video_id
    GROUP BY tag
    HAVING avg_play_progress >60
    ORDER BY avg_play_progress DESC
)t1