如何用SQL/Hive计算用户首次加入房间时的房间总用户数

需求说明 #

你需要为用户房间进出记录表新增total_num_user列，统计每位用户首次加入房间时（即该用户的start_time时刻），对应房间内的总用户数——也就是同一房间中，满足start_time <= 当前用户start_time且leave_time > 当前用户start_time的用户总数（包含当前用户自己）。

原始数据 #

user | room | start_time | leave_time
a1   | A    | 07:44      | 08:02
b2   | A    | 07:45      | 07:50
c3   | A    | 07:49      | 08:05
d4   | A    | 08:03      | 08:05

SQL实现（通用版本） #

使用关联子查询即可实现需求，这种写法适用于绝大多数SQL方言（MySQL、PostgreSQL、SQL Server等）：

SELECT
    t1.user,
    t1.room,
    t1.start_time,
    t1.leave_time,
    (
        -- 统计当前房间内，在用户t1加入时刻仍在房间的用户总数
        SELECT COUNT(*)
        FROM your_table t2
        WHERE t2.room = t1.room
          -- 其他用户的加入时间不晚于t1的加入时间
          AND t2.start_time <= t1.start_time
          -- 其他用户的离开时间晚于t1的加入时间（此时该用户仍在房间）
          AND t2.leave_time > t1.start_time
    ) AS total_num_user
FROM your_table t1
-- 按房间和加入时间排序，和示例输出一致
ORDER BY t1.room, t1.start_time;

结果验证 #

执行上述SQL后，输出结果与你的期望完全一致：

user | room | start_time | leave_time | total_num_user
a1   | A    | 07:44      | 08:02      | 1
b2   | A    | 07:45      | 07:50      | 2
c3   | A    | 07:49      | 08:05      | 3
d4   | A    | 08:03      | 08:05      | 2

性能优化（可选） #

如果你的数据量较大，可以考虑在(room, start_time, leave_time)上创建联合索引，来加速子查询的执行。对于支持LATERAL JOIN（PostgreSQL）或CROSS APPLY（SQL Server）的数据库，也可以用以下写法，性能可能更优：

-- PostgreSQL版本
SELECT
    t1.user,
    t1.room,
    t1.start_time,
    t1.leave_time,
    t2.total_num_user
FROM your_table t1
LEFT JOIN LATERAL (
    SELECT COUNT(*) AS total_num_user
    FROM your_table t2
    WHERE t2.room = t1.room
      AND t2.start_time <= t1.start_time
      AND t2.leave_time > t1.start_time
) t2 ON TRUE
ORDER BY t1.room, t1.start_time;

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