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

SELECT
    cid,
    ROUND(MAX(peak_uv),3) as max_peak_uv
FROM
(
    SELECT 
        cid,
        SUM(tag) over(partition by cid ORDER BY time) as peak_uv
    FROM
        (
            SELECT 
                cid,
                start_time as time, 
                1 as tag
            FROM
                play_record_tb
            union all
            SELECT
                cid,
                end_time as time, 
                -1 as tag
            FROM 
                play_record_tb
        ) a
) b
GROUP BY
    cid
ORDER BY
    max_peak_uv DESC
LIMIT 3

1. 窗口函数SUM()，从分区开头一直累加到当前行
2. UNION ALL 是把多条 SELECT 语句结果简单地“拼在一起”——得到的结果集保留所有行（包括重复行），并且各列数和列顺序必须一致。UNION会去重。