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

select
    tag,
    concat (avg_play_progress,'%') as avg_play_progress
from
    (
        select
            tag,
            round(
                avg(
                    case
                        when timestampdiff (second, start_time, end_time) > duration then 1
                        else timestampdiff (second, start_time, end_time) / duration
                    end
                ) * 100,
                2
            ) as avg_play_progress
        from
            tb_user_video_log a
            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
    ) as tb_process

1. TIMESTAMPDIFF(unit,datetime_expr1,datetime_expr2)用于返回两个时间之间的差值，unit可以为second/minute/hour/day/week/month/year等
2. 梳理播放进度的概念，若某一用户观看时长>视频总时长则为100%观看此时赋值为1，其余皆为未完整播放赋值为timestampdiff (second, start_time, end_time) / duration
3. concat用于连接字符串