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

select
    tbb.tag,
    tbb.avg_play_progress
from
    (
        select
            tb.tag,
            concat (
                format (
                    (
                        case
                            when sum(rate) / count(tb.video_id) > 0.6 then sum(rate) / count(tb.video_id) * 100
                            else null
                        end
                    ),
                    2
                ),
                '%'
            ) as avg_play_progress
        from
            (
                select
                    a.video_id,
                    b.tag,
                    (
                        case
                            when TIMESTAMPDIFF (SECOND, start_time, end_time) >= duration then 1
                            when TIMESTAMPDIFF (SECOND, start_time, end_time) / b.duration < 1 then TIMESTAMPDIFF (SECOND, start_time, end_time) / b.duration
                            else null
                        end
                    ) as rate
                from
                    tb_user_video_log a
                    inner join tb_video_info b on a.video_id = b.video_id
            ) as tb
        group by
            tb.tag
    ) as tbb
where
    tbb.avg_play_progress is not null
order by
    tbb.avg_play_progress desc

这题需要注意

1.两个时间直接相减运算是不行的，要定单位为秒，用这个函数`TIMESTAMPDIFF`（一开始我就是用时间相减，结果算出来旅游那里不行）

2.我用的外层的case语句，最后会选出旅行为空的行，所以再加了一层select语句筛选不为空的行。（暂时没想到去空的函数，所以用了select语句方便）