SQL同时在线问题

题目描述：计算每个直播间 同时在线用户的最大数量（即峰值）。

输入示例表（lives 表）

预期输出结果

思路：

1.将start_time和end_time拆分并使用UNION ALL自连接

2.使用窗口函数SUM() OVER (...)按直播间分组并按时间顺序累加事件值

3.对live_id进行分组，对实时在线人数取最大值

WITH a AS (
  SELECT id, live_id, visitor_id, start_time AS event_time, 1 AS cnt
  FROM lives
  UNION ALL
  SELECT id, live_id, visitor_id, end_time AS event_time, -1 AS cnt
  FROM lives
),
b AS (
  SELECT 
    *,
    SUM(cnt) OVER (
      PARTITION BY live_id 
      ORDER BY event_time
    ) AS cur_cnt
  FROM a
)
SELECT live_id, MAX(cur_cnt) AS max_cnt
FROM b
GROUP BY live_id;