基于MySQL按时间段统计最大重叠登录会话数的实现问询

可以通过MySQL单查询实现需求，以下是针对MySQL 8.0+版本的解决方案（支持CTE和窗口函数），同时提供低版本兼容的替代方案。

假设前提 #

• 数据表名为user_sessions，字段包括user_id（用户ID）、login_time（登录时间，DATETIME类型）、logout_time（登出时间，DATETIME类型）
• 传入参数@target_date为目标日期（DATE类型，例如'2024-05-20'）
• 按每小时时段分组，可根据需求调整时段粒度

MySQL 8.0+ 单查询实现 #

WITH hourly_windows AS (
    -- 生成目标日期的24个小时时段
    SELECT 
        CONCAT(@target_date, ' ', LPAD(hour, 2, '0'), ':00:00') AS window_start,
        CONCAT(@target_date, ' ', LPAD(hour + 1, 2, '0'), ':00:00') AS window_end
    FROM 
        (SELECT 0 AS hour UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3 
         UNION ALL SELECT 4 UNION ALL SELECT 5 UNION ALL SELECT 6 UNION ALL SELECT 7 
         UNION ALL SELECT 8 UNION ALL SELECT 9 UNION ALL SELECT 10 UNION ALL SELECT 11 
         UNION ALL SELECT 12 UNION ALL SELECT 13 UNION ALL SELECT 14 UNION ALL SELECT 15 
         UNION ALL SELECT 16 UNION ALL SELECT 17 UNION ALL SELECT 18 UNION ALL SELECT 19 
         UNION ALL SELECT 20 UNION ALL SELECT 21 UNION ALL SELECT 22 UNION ALL SELECT 23) AS hours
),
session_events AS (
    -- 转换登录/登出为+1/-1事件，登出时间超当天则截断到当天结束
    SELECT 
        login_time AS event_time,
        1 AS event_delta
    FROM user_sessions
    WHERE DATE(login_time) = @target_date
    UNION ALL
    SELECT 
        LEAST(logout_time, CONCAT(@target_date, ' 23:59:59')) AS event_time,
        -1 AS event_delta
    FROM user_sessions
    WHERE DATE(login_time) = @target_date OR DATE(logout_time) = @target_date
),
cumulative_concurrency AS (
    -- 计算每个事件点的实时并发数
    SELECT 
        event_time,
        SUM(event_delta) OVER (ORDER BY event_time) AS current_concurrency
    FROM session_events
    ORDER BY event_time
),
window_concurrency AS (
    -- 关联时段与对应并发事件，同时包含时段开始前的最后一个并发值
    SELECT 
        hw.window_start,
        hw.window_end,
        cc.current_concurrency
    FROM hourly_windows hw
    LEFT JOIN cumulative_concurrency cc
        ON cc.event_time >= hw.window_start 
        AND cc.event_time < hw.window_end
    UNION ALL
    SELECT 
        hw.window_start,
        hw.window_end,
        (SELECT current_concurrency 
         FROM cumulative_concurrency 
         WHERE event_time < hw.window_start 
         ORDER BY event_time DESC 
         LIMIT 1) AS current_concurrency
    FROM hourly_windows hw
    WHERE EXISTS (
        SELECT 1 FROM cumulative_concurrency WHERE event_time < hw.window_start
    )
)
-- 统计每个时段的最大并发数
SELECT 
    window_start AS period_start,
    window_end AS period_end,
    COALESCE(MAX(current_concurrency), 0) AS max_concurrent_sessions
FROM window_concurrency
GROUP BY window_start, window_end
ORDER BY window_start;

查询逻辑说明

1. hourly_windows：生成目标日期的所有时段区间，可修改此部分调整时段粒度（如30分钟、15分钟）。
2. session_events：将登录行为转为+1事件，登出行为转为-1事件，确保登出时间不超过目标日期范围。
3. cumulative_concurrency：通过窗口函数按时间排序计算累计并发数，得到每个时间点的实时在线用户数。
4. window_concurrency：将时段与并发事件关联，同时覆盖时段开始前延续的并发状态，避免遗漏。
5. 最后分组统计每个时段内的最大并发数，无并发时显示0。

MySQL 8.0以下版本替代方案 #

低版本不支持CTE和窗口函数，需用临时表和变量实现：

1. 生成时段临时表

CREATE TEMPORARY TABLE hourly_windows (
    window_start DATETIME,
    window_end DATETIME
);

INSERT INTO hourly_windows
SELECT 
    CONCAT(@target_date, ' ', LPAD(hour, 2, '0'), ':00:00'),
    CONCAT(@target_date, ' ', LPAD(hour + 1, 2, '0'), ':00:00')
FROM 
    (SELECT 0 AS hour UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3 
     UNION ALL SELECT 4 UNION ALL SELECT 5 UNION ALL SELECT 6 UNION ALL SELECT 7 
     UNION ALL SELECT 8 UNION ALL SELECT 9 UNION ALL SELECT 10 UNION ALL SELECT 11 
     UNION ALL SELECT 12 UNION ALL SELECT 13 UNION ALL SELECT 14 UNION ALL SELECT 15 
     UNION ALL SELECT 16 UNION ALL SELECT 17 UNION ALL SELECT 18 UNION ALL SELECT 19 
     UNION ALL SELECT 20 UNION ALL SELECT 21 UNION ALL SELECT 22 UNION ALL SELECT 23) AS hours;

2. 计算时段最大并发数

SELECT 
    hw.window_start,
    hw.window_end,
    COALESCE(MAX(cc.current_concurrency), 0) AS max_concurrent_sessions
FROM hourly_windows hw
LEFT JOIN (
    SELECT 
        event_time,
        @curr_concurrency := @curr_concurrency + event_delta AS current_concurrency
    FROM (
        SELECT login_time AS event_time, 1 AS event_delta
        FROM user_sessions
        WHERE DATE(login_time) = @target_date
        UNION ALL
        SELECT LEAST(logout_time, CONCAT(@target_date, ' 23:59:59')) AS event_time, -1 AS event_delta
        FROM user_sessions
        WHERE DATE(login_time) = @target_date OR DATE(logout_time) = @target_date
        ORDER BY event_time
    ) AS events
    CROSS JOIN (SELECT @curr_concurrency := 0) AS init
) AS cc
    ON cc.event_time >= hw.window_start 
    AND cc.event_time < hw.window_end
UNION ALL
SELECT 
    hw.window_start,
    hw.window_end,
    (SELECT current_concurrency 
     FROM (
         SELECT 
             event_time,
             @curr_concurrency := @curr_concurrency + event_delta AS current_concurrency
         FROM (
             SELECT login_time AS event_time, 1 AS event_delta
             FROM user_sessions
             WHERE DATE(login_time) = @target_date
             UNION ALL
             SELECT LEAST(logout_time, CONCAT(@target_date, ' 23:59:59')) AS event_time, -1 AS event_delta
             FROM user_sessions
             WHERE DATE(login_time) = @target_date OR DATE(logout_time) = @target_date
             ORDER BY event_time
         ) AS events
         CROSS JOIN (SELECT @curr_concurrency := 0) AS init
     ) AS cc
     WHERE event_time < hw.window_start 
     ORDER BY event_time DESC 
     LIMIT 1) AS current_concurrency
FROM hourly_windows hw
WHERE EXISTS (
    SELECT 1 FROM (
        SELECT login_time AS event_time
        FROM user_sessions
        WHERE DATE(login_time) = @target_date
        UNION ALL
        SELECT LEAST(logout_time, CONCAT(@target_date, ' 23:59:59')) AS event_time
        FROM user_sessions
        WHERE DATE(login_time) = @target_date OR DATE(logout_time) = @target_date
    ) AS events
    WHERE event_time < hw.window_start
)
GROUP BY window_start, window_end
ORDER BY window_start;

额外优化建议 #

• 若存在用户未主动登出（logout_time为NULL），可在session_events中用COALESCE(logout_time, CONCAT(@target_date, ' 23:59:59'))处理。
• 给login_time和logout_time添加索引，减少查询时间。
• 如需自定义时段粒度（如30分钟），只需修改时段生成逻辑，生成对应数量的时段区间即可。

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