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

select
    t.tag,
    concat (round(avg(t.play_time) * 100, 2), '%') avg_play_progress
from
    (
        select
            tag,
            if (
			# 不能直接用结束时间减去开始时间，时间差超过一分钟相减的到的是m0s不是m*60+s
                timestampdiff (second, start_time, end_time) / duration > 1,
                1,
                timestampdiff (second, start_time, end_time) / duration
            ) play_time
        from
            tb_user_video_log l
            inner join tb_video_info i on l.video_id = i.video_id
    ) t
group by
    t.tag
having
    substring_index(avg_play_progress,'%',1) > 60
order by
    avg_play_progress desc;