计算重叠日期区间内的最大并发数：基于SQL日历表的解决方案-php教程-PHP中文网

计算重叠日期区间内的最大并发数：基于SQL日历表的解决方案

霞舞

发布： 2025-12-05 11:54:07

原创

109人浏览过

计算重叠日期区间内的最大并发数：基于SQL日历表的解决方案

本教程详细阐述了如何使用sql和日历表来精确计算特定时间段内，具有重叠日期区间的事件（如学生入学）的最大并发数量。针对传统count(*)方法无法处理复杂重叠场景的局限性，我们提出了一种通过逐日统计并发数并取最大值的策略，确保结果的准确性，适用于mysql 5.6等版本。

1. 理解并发事件计算的挑战

在数据分析中，我们经常需要处理具有开始日期（StartDate）和结束日期（EndDate）的事件，并计算在某个特定查询时间段内这些事件的最大并发数量。例如，在一个学生管理系统中，学生入学记录包含StartDate和EndDate，每门课程有其最大容量限制。当需要查询特定课程在某个查询日期范围（QueryStartDate到QueryEndDate）内的最大同时在读学生数时，简单的COUNT(*)配合BETWEEN条件往往无法给出正确结果。

传统的SQL查询，如下方示例，通常会统计所有与查询范围有任何重叠的入学记录：

SELECT COUNT(*) FROM enrollments
WHERE IDCourse = ?
AND (
  (StartDate BETWEEN '<QueryStartDate>' AND '<QueryEndDate>')
  OR
  (EndDate BETWEEN '<QueryStartDate>' AND '<QueryEndDate>')
  OR
  (StartDate <= '<QueryStartDate>' AND EndDate >= '<QueryEndDate>')
);

登录后复制

这种方法的问题在于，它只判断一个入学记录是否与查询范围“有交集”，但无法识别在查询范围内的 任何一个具体时刻，有多少学生是同时活跃的。例如，假设有以下入学情况：

学生A：2021-01-01 至 2021-01-02
学生B：2021-01-03 至 2021-01-04
学生C：2020-12-20 至 2021-02-01

如果查询范围是2021-01-01到2021-01-05，上述传统查询可能会返回3，因为它发现所有3个入学记录都与该范围有交集。然而，实际情况是：

在2021-01-01，学生A和学生C活跃，并发数为2。
在2021-01-02，学生C活跃，并发数为1（学生A结束）。
在2021-01-03，学生B和学生C活跃，并发数为2。
在2021-01-04，学生B和学生C活跃，并发数为2。
在2021-01-05，学生C活跃，并发数为1（学生B结束）。

因此，在2021-01-01到2021-01-05这个查询范围内，任何一天最大的并发学生数是2，而不是3。要准确获得这个“2”，我们需要一种更精细的计算方法。

2. 解决方案核心：日历表方法

为了准确计算在给定日期范围内任意时刻的最大并发数，我们需要一种方法来逐日统计活跃事件，然后从这些每日统计中找出最大值。日历表（或称为日期维度表）是解决这类问题的强大工具。

2.1 什么是日历表？

日历表是一个包含一系列连续日期的辅助表，通常从一个很早的日期开始，到未来某个足够远的日期结束。它通常只包含一个日期字段，可以根据需要添加星期几、月份、年份等其他日期属性。日历表在处理时间序列数据、计算日期差异或进行日期范围分析时非常有用。

创建日历表（示例）：

如果您的数据库中没有日历表，可以手动创建并填充它。以下是一个简单的创建和填充示例，用于生成从2020年到2025年的日期：

-- 创建日历表
CREATE TABLE calendar_table (
    dt DATE PRIMARY KEY
);

-- 填充日历表的存储过程
DELIMITER //
CREATE PROCEDURE FillCalendarTable(startDate DATE, endDate DATE)
BEGIN
    WHILE startDate <= endDate DO
        INSERT IGNORE INTO calendar_table (dt) VALUES (startDate); -- 使用 IGNORE 避免重复插入
        SET startDate = DATE_ADD(startDate, INTERVAL 1 DAY);
    END WHILE;
END //
DELIMITER ;

-- 调用存储过程填充日期
CALL FillCalendarTable('2020-01-01', '2025-12-31');

登录后复制

注意： 在实际生产环境中，日历表通常只需要创建一次并预填充足够长的日期范围。

2.2 基于日历表计算最大并发数

有了日历表，我们就可以通过以下两步来计算最大并发数：

Riffo

Riffo是一个免费的文件智能命名和管理工具

216

查看详情

步骤一：计算查询范围内每天的并发数

将enrollments表与calendar_table进行JOIN操作。连接条件是日历表中的每个日期dt必须落在入学记录的StartDate和EndDate之间（包含边界）。这样，我们就能统计出在每个具体日期有多少学生是活跃的。

SELECT
    c.dt,
    COUNT(e.IDStudent) AS stcount -- 统计每天活跃的学生数
FROM
    calendar_table c
JOIN
    enrollments e ON c.dt BETWEEN e.StartDate AND e.EndDate
WHERE
    e.IDCourse = ? -- 筛选特定课程
GROUP BY
    c.dt;

登录后复制

这个查询会返回一个结果集，每行包含一个日期和该日期活跃的学生数量。

步骤二：在查询范围内找出最大并发数

在步骤一的基础上，我们只需要在指定的查询日期范围（QueryStartDate到QueryEndDate）内，从每日并发数中找出最大值。

SELECT MAX(stcount) AS MaxConcurrentStudents
FROM (
    -- 内部子查询：计算指定课程每天的活跃学生数
    SELECT
        c.dt,
        COUNT(e.IDStudent) AS stcount
    FROM
        calendar_table c
    JOIN
        enrollments e ON c.dt BETWEEN e.StartDate AND e.EndDate
    WHERE
        e.IDCourse = ? -- 筛选特定课程
    GROUP BY
        c.dt
) AS daily_counts -- 将内部查询结果命名为 daily_counts
WHERE
    daily_counts.dt BETWEEN '<QueryStartDate>' AND '<QueryEndDate>';

登录后复制

将上述查询中的?替换为实际的IDCourse，并将和替换为目标查询日期。

2.3 完整示例演示

沿用前文的入学数据（enrollments表），假设IDCourse = 1：

IDStudent	StartDate	EndDate	IDCourse
101	2021-01-01	2021-01-02	1
102	2021-01-03	2021-01-04	1
103	2020-12-20	2021-02-01	1

我们想查询课程IDCourse = 1在2021-01-01到2021-01-05期间的最大并发学生数。

应用上述SQL查询：

SELECT MAX(stcount) AS MaxConcurrentStudents
FROM (
    SELECT
        c.dt,
        COUNT(e.IDStudent) AS stcount
    FROM
        calendar_table c
    JOIN
        enrollments e ON c.dt BETWEEN e.StartDate AND e.EndDate
    WHERE
        e.IDCourse = 1 -- 特定课程ID
    GROUP BY
        c.dt
) AS daily_counts
WHERE
    daily_counts.dt BETWEEN '2021-01-01' AND '2021-01-05';

登录后复制

执行过程分析：