使用SQL和PHP查找课程并发学生数：日历表方法

本文详细介绍了如何在mysql 5.6和php 7.2环境下，通过构建和利用日历表，准确计算给定课程在特定日期范围内最大并发学生数的方法。传统基于日期范围重叠的计数方式可能无法反映真实的并发情况，而日历表方法能有效解决这一问题，实现按日统计并从中找出峰值，确保结果的精确性。

在学生管理系统中，准确统计特定课程在某一时间段内的并发学生数是一项常见而关键的需求。例如，为了确保课程容量不被突破，我们需要知道在指定开始日期和结束日期之间，某一课程在任何一个时刻最多有多少学生同时在读。简单的日期范围重叠查询往往不足以满足这一需求，因为它可能统计所有有任何重叠的记录，而非某个特定时间点的并发峰值。

理解并发学生数问题

假设我们有一个enrollments表，包含IDStudent、StartDate、EndDate和IDCourse字段。我们希望查询在给定查询范围（例如query_start_date到query_end_date）内，某一特定课程（target_IDCourse）的最大并发学生数。

考虑以下示例： 我们想查询在 2021年1月1日 到 2021年1月5日 期间，某个课程的并发学生数。 该课程有以下三条选课记录：

1. 记录1: 2021年1月1日 - 2021年1月2日
2. 记录2: 2021年1月3日 - 2021年1月4日
3. 记录3: 2020年12月20日 - 2021年2月1日

如果使用简单的重叠查询，例如：

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

此查询会返回3，因为所有三条记录都与查询范围有重叠。然而，真正的并发情况是：

立即学习“PHP免费学习笔记（深入）”；

• 2021年1月1日：记录1和记录3活跃 (2人)
• 2021年1月2日：记录1和记录3活跃 (2人)
• 2021年1月3日：记录2和记录3活跃 (2人)
• 2021年1月4日：记录2和记录3活跃 (2人)
• 2021年1月5日：记录3活跃 (1人)

在此期间，最大并发学生数应为 2。为了获得这个精确值，我们需要一种更精细的统计方法。

解决方案：利用日历表

解决此类问题的有效方法是使用“日历表”（Calendar Table）。日历表是一个包含一系列连续日期的辅助表，通过它我们可以将复杂的日期范围重叠问题转化为每日的计数问题，再从每日计数中找出最大值。

1. 创建和填充日历表

首先，我们需要一个日历表。这个表可以包含你应用程序所需的最早日期到最晚日期之间的所有日期。

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

-- 填充日历表 (示例：填充2020年到2025年的日期)
DELIMITER //
CREATE PROCEDURE FillCalendarTable(startDate DATE, endDate DATE)
BEGIN
    DECLARE current_date DATE;
    SET current_date = startDate;
    WHILE current_date <= endDate DO
        INSERT INTO calendar_table (dt) VALUES (current_date);
        SET current_date = DATE_ADD(current_date, INTERVAL 1 DAY);
    END WHILE;
END //
DELIMITER ;

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

-- 或者，如果MySQL版本支持，可以使用递归CTE (MySQL 8+) 或更简洁的日期生成方式
-- 对于MySQL 5.6，上述存储过程是常见且可靠的方法。

注意事项： 日历表只需要创建一次并定期更新（如果需要扩展日期范围）。它是一个静态的辅助表，对查询性能有显著提升，因为它避免了在每次查询时动态生成日期序列。

百度智能云·曦灵

百度旗下的AI数字人平台

下载

2. 计算每日并发学生数

有了日历表后，我们可以将其与enrollments表进行连接，统计在特定课程中，每一天有多少学生是活跃的。

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 = ? -- 替换为目标课程ID
    AND c.dt BETWEEN ? AND ? -- 替换为查询的开始日期和结束日期
GROUP BY
    c.dt
ORDER BY
    c.dt;

此查询将返回在指定课程和查询日期范围内，每天的活跃学生总数。

3. 找出最大并发学生数

最后一步是从每日的并发学生数中找出最大值，这就是我们所需的课程在指定时间段内的最大并发学生数。

SELECT
    MAX(stcount) AS max_concurrent_students
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 = ? -- 替换为目标课程ID
            AND c.dt BETWEEN ? AND ? -- 替换为查询的开始日期和结束日期
        GROUP BY
            c.dt
    ) AS daily_counts;

将?替换为实际的IDCourse、query_start_date和query_end_date即可。

示例参数代入： 如果IDCourse = 101，query_start_date = '2021-01-01'，query_end_date = '2021-01-05'，则查询如下：

SELECT
    MAX(stcount) AS max_concurrent_students
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 = 101
            AND c.dt BETWEEN '2021-01-01' AND '2021-01-05'
        GROUP BY
            c.dt
    ) AS daily_counts;

此查询将返回 2，这与我们通过手动分析得出的结果一致。

PHP集成

在PHP应用程序中，你可以通过PDO或其他数据库扩展执行上述SQL查询。

 PDO::ERRMODE_EXCEPTION,
    PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_ASSOC,
    PDO::ATTR_EMULATE_PREPARES   => false,
];

try {
    $pdo = new PDO($dsn, $user, $pass, $options);
} catch (\PDOException $e) {
    throw new \PDOException($e->getMessage(), (int)$e->getCode());
}

// 查询参数
$targetCourseId = 101;
$queryStartDate = '2021-01-01';
$queryEndDate   = '2021-01-05';

// SQL 查询语句
$sql = "
    SELECT
        MAX(stcount) AS max_concurrent_students
    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 = :course_id
                AND c.dt BETWEEN :start_date AND :end_date
            GROUP BY
                c.dt
        ) AS daily_counts;
";

try {
    $stmt = $pdo->prepare($sql);
    $stmt->bindParam(':course_id', $targetCourseId, PDO::PARAM_INT);
    $stmt->bindParam(':start_date', $queryStartDate, PDO::PARAM_STR);
    $stmt->bindParam(':end_date', $queryEndDate, PDO::PARAM_STR);
    $stmt->execute();

    $result = $stmt->fetch();

    if ($result) {
        echo "课程ID " . $targetCourseId . " 在 " . $queryStartDate . " 到 " . $queryEndDate . " 期间的最大并发学生数是: " . $result['max_concurrent_students'];
    } else {
        echo "未找到数据。";
    }

} catch (\PDOException $e) {
    echo "查询失败: " . $e->getMessage();
}

?>

总结与注意事项

• 日历表的优势： 日历表是处理日期和时间序列相关复杂查询的强大工具，尤其适用于需要按天、周、月等粒度进行统计的场景。它将复杂的时间重叠逻辑简化为简单的日期匹配和聚合。
• 性能考量： 一旦日历表创建完成，其查询性能通常很高，因为数据库可以高效地执行JOIN操作。对于大型enrollments表，确保StartDate和EndDate字段上有索引，可以进一步优化查询速度。
• 灵活性： 这种方法不仅适用于查找最大并发数，还可以轻松扩展以查找平均并发数、特定日期的并发数，或者在不同时间粒度（例如小时）上进行分析，只需调整日历表的粒度即可。
• MySQL版本兼容性： 本文提供的方法在MySQL 5.6及更高版本中均适用。对于MySQL 8+，虽然可以使用递归CTE等新特性来动态生成日期，但对于这种固定范围的日历需求，预先构建的日历表仍然是高效且易于管理的方案。
• 数据准确性： 确保enrollments表中的StartDate和EndDate数据准确无误，是所有日期统计的基础。

通过采纳日历表方法，我们可以准确地计算出课程在特定时间段内的最大并发学生数，为课程规划、资源分配和容量管理提供可靠的数据支持。