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

WITH time_point_play_count AS (
    -- 生成所有可能的时间点，这里通过对start_time和end_time去重后合并得到
    SELECT DISTINCT time_point
    FROM (
        SELECT start_time AS time_point
        FROM play_record_tb
        UNION ALL
        SELECT end_time AS time_point
        FROM play_record_tb
    ) AS all_time_points
),
video_play_count_at_time AS (
    -- 计算每个时间点每个视频的播放量
    SELECT 
        tpc.time_point,
        p.cid,
        COUNT(p.id) AS play_count
    FROM 
        time_point_play_count tpc
    -- 左连接原表，判断每个时间点有多少条记录正在播放同一视频
    LEFT JOIN 
        play_record_tb p 
    ON 
        tpc.time_point >= p.start_time AND tpc.time_point < p.end_time
    GROUP BY 
        tpc.time_point, p.cid
),
max_play_count_per_video AS (
    -- 找出每个视频的最大播放量
    SELECT 
        cid,
        MAX(play_count) AS max_peak_uv
    FROM 
        video_play_count_at_time
    GROUP BY 
        cid
)
-- 筛选出最大播放量排名前三的视频信息并排序，保留三位小数
SELECT 
    cid,
    ROUND(CAST(max_peak_uv as FLOAT), 3) AS max_peak_uv
FROM 
    max_play_count_per_video
ORDER BY 
    max_peak_uv DESC
LIMIT 3;