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

select
    tag,
    avg_play_progress
from
    (
        select
            tag,
            video_id,
            concat (round(avg(rate) * 100, 2), '%') as avg_play_progress
        from
            (
                select
                    tag,
                    video_id,
                    if(times / duration>=1,1,times / duration) as rate
                from
                    (
                        select
                            i.tag,
                            l.id,
                            l.uid,
                            l.video_id,
                            timestampdiff (second, l.start_time, l.end_time) as times,
                            i.duration
                        from
                            tb_user_video_log l
                            join tb_video_info i on l.video_id = i.video_id
                    ) t
            ) p
        group by
            video_id
        order by
            avg_play_progress desc
    ) q
where
    q.avg_play_progress > 60;

这个也用了接近15分钟...心碎了 刷会儿抖音作为回报............