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

#多个视频的播放进度
select sub.tag, concat(round(avg(if(rat >= 1, 1, rat)) * 100, 2), '%') avg_play from

#  单个视频的id, tag, 播放率
(
    select u.video_id, TIMESTAMPDIFF(SECOND, start_time, end_time) /v.duration as rat, tag from tb_user_video_log u left join tb_video_info v on u.video_id = v.video_id
) sub

group by 1
having avg(if(rat >= 1, 1, rat)) > 0.6
order by avg_play desc

记得用timestampdiff，我直接两个时间相减会有奇奇怪怪的错误。

思路是子查询的，先算单个视频的播放进度，再算这一类的