题解 | 输出播放量最高的视频

with peak_uv_atonetime_count as (
    select
        p1.cid as cid,
        sum(if((p1.start_time between p2.start_time and p2.end_time), 1, 0)) as peak_uv_atonetime
    from
        play_record_tb p1
        join play_record_tb p2 on p1.cid = p2.cid
    group by 
            p1.id,
            p1.cid,
            p1.start_time
)
select
    cid,
    round(cast(max(peak_uv_atonetime) as float), 3) as max_peak_uv
from 
    peak_uv_atonetime_count
group by cid
order by max_peak_uv desc
limit 3

解题思路：

1. 理解题意：请统计视频的播放量最大值，输出最大值top3高的视频信息。即先统计每个视频在不同的时间段被观看次数，随后取出每个视频在所有时间段中被观看最多次数的值作为该视频的最大值（最大被观看次数)，最后对所有视频按最大值降序排序，取出前3名即为答案。
2. 首先，求出每个视频在不同的时间段被观看次数。表中的start_time代表视频被观看时间，因为可能有重复观看，所以start_time代表所有视频已经被观看的时间，使用它去做匹配。即把该记录的 start_time 作为一个时间点，去找同一个视频里，有多少其他用户在这个时间点也正在看。使用自连接（自己和自己连），得到同一个视频的不同时间点start_time分别连接这个视频的所有时间点。使用p1.start_time between p2.start_time and p2.end_time，若p1.start_time落在p2.start_time and p2.end_time，则表示p1观看时，p2也在观看。
3. 取出最大值进行排序。首先按cid分组，用聚合函数max取出最大值，最后按最大值排序并取降序排序前3名。完成解答。

例如：假设你的表中有这样几条播放记录（为了简单我们只保留时间）：

现在我们来一条一条分析：

• 第1条记录：在 08:30 的时候，有哪些人正在看视频？08:30 落在 id=1（自己）和 id=2（还没开始）里 → 并发人数是 1
• 第2条记录：在 08:35 的时候，有哪些人？08:35 落在 id=1 和 id=2 里 → 并发人数是 2
• 第3条记录：在 08:50 的时候，有哪些人？08:50 落在 id=1、2、3 里 → 并发人数是 3

👉 所以我们希望得到：

注意：每条 p1 记录 保留一行结果，group by p1.id 是为了保证 “一条记录一行结果”。多条记录 p1 具有相同 start_time（比如两人都在同一时刻开始播放），如果没有group by p1.id ，则它们的并发人数被合并成了一行。先保证记录，再按cid与start_time分组。