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

/*
需要输出每个视频的同时播放量，并且排序
可以用UNION自连接，开始时间标记为1，结束时间标记为-1
*/

WITH        -- CTE开启
t1 AS (     -- 清洗得出用户行为表底表
SELECT
    cid,
    start_time AS sj,   -- 行为发生时间
    1 AS bfl            -- 每有一个人进入，播放量+1
FROM play_record_tb

UNION ALL

SELECT
    cid,
    end_time AS sj,
    -1 AS bfl           -- 每有一个人退出，播放量-1
FROM play_record_tb
),

t2 AS (     -- 使用窗口函数计算每个时间节点的同时播放人数
SELECT
    cid,
    sj,
    SUM(bfl) OVER(PARTITION BY cid ORDER BY sj,bfl DESC) AS '同时播放量'
FROM t1)

SELECT      -- 主查询，计算最后的结果指标
    cid,
    ROUND(MAX(同时播放量),3) AS 最大播放量  -- 每个视频的最大同时播放量
FROM t2
GROUP BY cid
ORDER BY 最大播放量 DESC
LIMIT 3;