题解 | 播放量峰值top3高的视频

with
    video_cnt as (
        select
            a.cid,
            round(
                sum(
                    if (
                        a.start_time between b.start_time and b.end_time,
                        1,
                        0
                    )
                ),
                3
            ) as peak_uv
        from
            play_record_tb as a
            join play_record_tb as b on a.cid = b.cid
        group by
            a.id,
            a.cid
    )
select cid,max(peak_uv) as max_peak_uv
from video_cnt
group by cid
order by max_peak_uv desc
limit 3

本题目难点在于如何理解题意并用SQL语句表示！找到播放量峰值TOP3高的视频，乍一看题目有些奇怪，只有一个表，有些难以理解如何去实现，核心部分就在于如何计算某一视频某一时刻的播放量峰值。可以这样子：把play_record_tb用join连接play_record_tb，前者为a表，后者为b表，连接条件为a.cid = b.cid，这样子就显示出每一个视频对应的其他用户的观看时间了。然后在此基础上，group by id，cid，必须要引入id，要不然后面会统计错误！再采用“如果一个用户播放视频的起始时间在别的n个用户（采用的是join连接，所以包括自己）的播放视频起始时间和终止时间之间的话，就说明这个视频最高有n个用户同时观看，即peak_uv = n”这样的思想，因为每个用户观看的时间不尽相同，所以join连接后的peak_uv结果也不会一致，这就需要外层的sql语句通过max（）来筛选出每个视频的最高播放量峰值了。再回过头来解释这个id为什么要引入，不引入会有什么后果？引入 id 字段的作用是确保统计逻辑的精确性。若不引入 id：当两条记录的 cid 相同时，会被合并为同一组，导致统计次数错误；而引入了id字段就能快速定位唯一的记录，从而不会混淆累加起来cid相同的视频的播放次数。