PostgreSQL查询房间内用户重叠人数最多的时间窗口

这类问题属于区间重叠计数场景，核心思路是将用户的"进入"和"离开"事件拆分为增减计数的时间点，通过计算累计在线人数找到峰值，再推导对应的时间窗口。以下是具体实现方案：

基础实现（处理已明确离开的用户） #

假设exit_time不为空（用户均已离开房间），可以用以下SQL查询每个房间内同时在线用户最多的时间窗口：

WITH event_stream AS (
  -- 将进入事件标记为+1，离开事件标记为-1
  SELECT
    room_id,
    entry_time AS event_time,
    1 AS delta
  FROM movement_logs
  WHERE exit_time IS NOT NULL
  UNION ALL
  SELECT
    room_id,
    exit_time AS event_time,
    -1 AS delta
  FROM movement_logs
  WHERE exit_time IS NOT NULL
),
running_counts AS (
  -- 按房间分组，计算每个时间点的累计在线人数
  SELECT
    room_id,
    event_time,
    SUM(delta) OVER (
      PARTITION BY room_id 
      ORDER BY event_time 
      ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
    ) AS concurrent_users
  FROM event_stream
),
max_counts AS (
  -- 找出每个房间的最大在线人数
  SELECT
    room_id,
    MAX(concurrent_users) AS max_concurrent
  FROM running_counts
  GROUP BY room_id
)
-- 匹配达到最大人数的时间窗口
SELECT
  rc.room_id,
  rc.event_time AS window_start,
  LEAD(rc.event_time) OVER (PARTITION BY rc.room_id ORDER BY rc.event_time) AS window_end,
  rc.concurrent_users AS max_users
FROM running_counts rc
JOIN max_counts mc 
  ON rc.room_id = mc.room_id 
  AND rc.concurrent_users = mc.max_concurrent
WHERE LEAD(rc.event_time) OVER (PARTITION BY rc.room_id ORDER BY rc.event_time) IS NOT NULL
ORDER BY rc.room_id, rc.event_time;

处理未离开的用户（exit_time为NULL） #

如果存在用户未离开房间（exit_time为空），可以将其退出时间视为当前时间，调整后的SQL如下：

WITH event_stream AS (
  SELECT
    room_id,
    entry_time AS event_time,
    1 AS delta
  FROM movement_logs
  UNION ALL
  SELECT
    room_id,
    COALESCE(exit_time, CURRENT_TIMESTAMP) AS event_time,
    -1 AS delta
  FROM movement_logs
),
running_counts AS (
  SELECT
    room_id,
    event_time,
    SUM(delta) OVER (
      PARTITION BY room_id 
      ORDER BY event_time 
      ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
    ) AS concurrent_users
  FROM event_stream
),
max_counts AS (
  SELECT
    room_id,
    MAX(concurrent_users) AS max_concurrent
  FROM running_counts
  GROUP BY room_id
),
max_windows AS (
  SELECT
    rc.room_id,
    rc.event_time,
    LEAD(rc.event_time) OVER (PARTITION BY rc.room_id ORDER BY rc.event_time) AS next_event_time,
    rc.concurrent_users,
    CASE WHEN LAG(rc.concurrent_users) OVER (PARTITION BY rc.room_id ORDER BY rc.event_time) = rc.concurrent_users THEN 0 ELSE 1 END AS is_start
  FROM running_counts rc
  JOIN max_counts mc 
    ON rc.room_id = mc.room_id 
    AND rc.concurrent_users = mc.max_concurrent
  WHERE LEAD(rc.event_time) OVER (PARTITION BY rc.room_id ORDER BY rc.event_time) IS NOT NULL
),
window_groups AS (
  SELECT
    room_id,
    event_time,
    next_event_time,
    concurrent_users,
    SUM(is_start) OVER (PARTITION BY room_id ORDER BY event_time) AS group_id
  FROM max_windows
)
-- 合并连续的最大人数窗口
SELECT
  room_id,
  MIN(event_time) AS window_start,
  MAX(next_event_time) AS window_end,
  concurrent_users AS max_users
FROM window_groups
GROUP BY room_id, group_id, concurrent_users
ORDER BY room_id, window_start;

逻辑说明 #

1. 事件流拆分：将每个用户的进入、离开行为转化为增减计数的时间点，方便后续计算累计人数。
2. 累计人数计算：用窗口函数SUM() OVER()按房间分组、时间排序，计算每个时间点后的实时在线人数。
3. 峰值匹配与窗口合并：先找到每个房间的最大在线人数，再匹配所有达到峰值的时间点，最后合并连续的峰值时间区间，得到完整的最大人数窗口。

内容的提问来源于stack exchange，提问作者Binil Thomas