如何基于登录登出记录统计并发用户数 是否需要参考时间表

核心问题先明确：是否需要独立时间参考表？ #

• 不是必须依赖物理独立的时间维度表。
• 两种实现路径都可行：
  • 临时统计场景：直接在SQL中通过数据库内置的递归能力，动态生成指定范围、固定粒度的连续时间序列，不需要提前建表。
  • 长期固定报表场景：提前搭建独立的时间维度表（预生成所有常用粒度的时间节点），长期复用统计逻辑，性能更优。

注意：如果纯靠现有登录业务表做聚合，会漏掉没有任何登录/登出事件发生的空白时间节点（比如示例中09:00的节点，当时没有新用户登录、也没有用户登出，纯靠业务表聚合根本不会生成这个统计点），所以必须要有一套独立的连续时间序列作为统计的基准骨架。

核心统计口径 #

对于每个时间粒度的节点T，判定为在线并发用户的规则为：
login_time <= T 且 logout_time > T
（即用户在T点前已经登录，且在T点之后才完成登出，才算T点的在线用户，刚好在T点登出的用户不纳入统计，避免口径偏差）

具体SQL实现（无实体时间表版本） #

以MySQL 8.0+环境为例，通过递归CTE动态生成时间序列，不需要提前创建时间参考表：

-- 自定义统计参数
SET @start_time = '2016-06-08 08:00:00'; -- 统计开始时间
SET @end_time = '2016-06-08 11:00:00';    -- 统计结束时间
SET @time_gap = 15;                        -- 固定统计粒度，单位：分钟

WITH RECURSIVE time_point_seq AS (
    -- 生成第一个对齐粒度的时间节点
    SELECT 
        TIMESTAMPADD(
            SECOND, 
            FLOOR(TIMESTAMPDIFF(SECOND, '1970-01-01', @start_time) / (@time_gap * 60)) * @time_gap * 60,
            '1970-01-01'
        ) AS stat_time
    UNION ALL
    -- 按粒度循环生成后续所有时间节点，直到超出统计结束时间
    SELECT 
        TIMESTAMPADD(MINUTE, @time_gap, stat_time)
    FROM time_point_seq
    WHERE stat_time < @end_time
)
-- 关联业务登录表统计每个时间点的并发数
SELECT 
    t.stat_time AS `Date`,
    COUNT(l.loginid) AS `No of Users`
FROM time_point_seq t
LEFT JOIN login_log l 
    ON t.stat_time >= l.login_time 
    AND t.stat_time < l.logout_time
GROUP BY t.stat_time
ORDER BY t.stat_time;

实现细节说明 #

• 时间节点生成时会自动对齐到粒度刻度，比如15分钟粒度会自动对齐到整刻（0分、15分、30分、45分），不会出现错位的统计节点。
• 用左连接关联业务表的方式，哪怕某个时间点没有任何在线用户，也会输出0值，不会漏掉该节点。
• 如果用独立时间维度表的方案，只需要把上面的递归CTE部分替换成从维度表查询时间节点的逻辑即可，关联统计的部分完全一致。

常见踩坑提醒 #

• 不要直接用现有业务表的login_time、logout_time去聚合生成时间范围，会丢失无事件的空白统计节点。
• 时间判定的边界要统一：不要把登出时间的判定写成logout_time >= t，否则会把刚好在节点时间点登出的用户算入并发，和业务实际口径不符。
• 当业务表数据量超过10万行时，建议给login_time、logout_time两个字段建联合索引，避免关联统计时全表扫描导致性能过差。

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