mysql - SQL : get current online player count using login/logout timestamp

Question

我有一个 MySQL 表，其中包含 uid、登录/注销状态平面、时间戳，例如:

uid    flag    time
123    login   10:00:00
123    logout  10:10:00
321    login   10:08:00
321    logout  10:18:00

现在我想显示 10:08 到 10:09 之间每分钟的在线玩家数。预期结果:

Minute    current_player_cnt 
10:07         1
10:08         2
10:09         2

我可以将数据导出为 csv 文件并使用 python 执行此操作，但我想直接使用 sql 执行此操作，但找不到可以提供帮助的函数。

不需要给整个sql语句，给个函数名就可以了。任何建议表示赞赏。

Answer 1

一种选择是使用日历表加入，例如

SELECT
    t1.Minute,
    COUNT(t2.uid) AS current_player_cnt
FROM
(
    SELECT '10:07:00' AS Minute UNION ALL
    SELECT '10:08:00' UNION ALL
    SELECT '10:09:00'
) t1
LEFT JOIN
(
    SELECT uid, MIN(time) AS login_time, MAX(time) AS logout_time
    FROM yourTable
    GROUP BY uid
) t2
    ON t1.Minute BETWEEN t2.login_time AND t2.logout_time OR
       (t1.Minute >= t2.login_time AND t2.login_time = t2.logout_time)
GROUP BY
    t1.Minute;

Demo