MySQL能否按时长交集分组统计月内最大并发通话数？

核心思路 #

通过递归CTE生成指定月份的连续30秒时间块，再关联通话数据表，通过重叠判断逻辑统计每个时间块内的并发通话数。

完整查询语句 #

WITH RECURSIVE time_blocks AS (
    -- 初始化：生成当月第一个30秒时间块
    SELECT 
        DATE_FORMAT(DATE_SUB(CURDATE(), INTERVAL DAY(CURDATE())-1 DAY), '%Y-%m-01 00:00:00') AS block_start,
        DATE_ADD(DATE_FORMAT(DATE_SUB(CURDATE(), INTERVAL DAY(CURDATE())-1 DAY), '%Y-%m-01 00:00:00'), INTERVAL 30 SECOND) AS block_end
    UNION ALL
    -- 递归生成后续所有30秒时间块，直到当月结束
    SELECT 
        DATE_ADD(block_start, INTERVAL 30 SECOND),
        DATE_ADD(block_end, INTERVAL 30 SECOND)
    FROM time_blocks
    WHERE block_start < LAST_DAY(CURDATE())
)
SELECT 
    CONCAT(block_start, ' ', block_end) AS group_by,
    COUNT(c.call_id) AS count
FROM time_blocks tb
LEFT JOIN calls c 
    -- 关键重叠判断：通话时间段与当前时间块存在交集
    ON c.start_time < tb.block_end 
    AND c.end_time > tb.block_start
GROUP BY tb.block_start, tb.block_end
ORDER BY tb.block_start;

关键说明 #

1. 时间块生成：递归CTEtime_blocks会自动生成当月从第一天0点开始，每30秒一个的连续时间区间，覆盖整个月份。
2. 重叠判断逻辑：c.start_time < tb.block_end AND c.end_time > tb.block_start是判断通话与时间块是否重叠的核心条件——只要通话开始于时间块结束前，且结束于时间块开始后，就认定该通话在这个时间块内处于并发状态。
3. 结果输出：通过CONCAT拼接时间块的起止时间作为分组标识，COUNT统计每个时间块内的并发通话数，LEFT JOIN确保即使无通话的时间块也会被输出（count为0），若不需要这类空数据，可改为INNER JOIN。

优化建议 #

• 给通话表的start_time和end_time建立联合索引，大幅提升关联查询的效率，尤其是数据量较大时。
• 若需统计特定日期范围，可修改CTE的起始和结束条件，比如将CURDATE()替换为指定日期，缩小时间块生成范围。
• 针对MySQL 8.0以下不支持递归CTE的版本，可预先创建数字辅助表（如包含0到2880的数字，对应一天的48*60个30秒块），通过计算偏移量生成时间块：

  -- 示例：用数字表生成一天的30秒时间块
  SELECT 
      DATE_ADD('2023-09-01 00:00:00', INTERVAL n*30 SECOND) AS block_start,
      DATE_ADD('2023-09-01 00:00:00', INTERVAL (n+1)*30 SECOND) AS block_end
  FROM numbers
  WHERE n < 2880;
  

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