SQL查询需求：统计指定日期各小时内的在籍人员数量

DevOps 小满

2026-5-26

嘿，这个需求我之前也碰到过，核心就是先把要统计的每个工作小时都明确列出来，再和业务表关联计算，这样就不会漏掉那些没人的小时啦。给你整理了几种主流数据库的实现方案，你可以按需调整：

核心思路

先构建一个包含目标日期所有工作小时的维度表（用CTE或者临时表都可以），然后通过左连接业务表，统计每个小时内满足「人员开始时间早于当前小时结束、结束时间晚于等于当前小时开始」的人员数量，这样能保证每个工作小时都有统计结果（哪怕是0）。

具体实现（分数据库）

PostgreSQL 版本

-- 先生成目标日期的工作小时序列，这里假设工作时间是9:00到18:00
WITH hourly_dim AS (
    SELECT generate_series(
        TIMESTAMP '2024-05-20 09:00:00', -- 目标日期的开始小时
        TIMESTAMP '2024-05-20 18:00:00', -- 目标日期的结束小时
        INTERVAL '1 hour'
    ) AS hour_start
)
SELECT
    hd.hour_start,
    COUNT(DISTINCT t.person_id) AS in_service_count
FROM hourly_dim hd
LEFT JOIN your_table t
    -- 人员的时间段覆盖当前小时：开始时间早于下一小时，结束时间晚于等于当前小时
    ON t.start_time < hd.hour_start + INTERVAL '1 hour'
    AND t.end_time >= hd.hour_start
    -- 可选：过滤有效状态（比如正常在籍）
    AND t.status = 'active'
    -- 如果需求是按「星期几」统计（而非具体日期），保留这行；如果是具体日期，替换成 DATE(t.end_time) = '2024-05-20'
    AND EXTRACT(DOW FROM t.end_time) = EXTRACT(DOW FROM hd.hour_start)
GROUP BY hd.hour_start
ORDER BY hd.hour_start;

MySQL 版本

MySQL没有generate_series，用递归CTE生成小时序列：

WITH RECURSIVE hourly_dim AS (
    SELECT STR_TO_DATE('2024-05-20 09:00:00', '%Y-%m-%d %H:%i:%s') AS hour_start
    UNION ALL
    SELECT hour_start + INTERVAL 1 HOUR
    FROM hourly_dim
    WHERE hour_start < STR_TO_DATE('2024-05-20 18:00:00', '%Y-%m-%d %H:%i:%s')
)
SELECT
    hd.hour_start,
    COUNT(DISTINCT t.person_id) AS in_service_count
FROM hourly_dim hd
LEFT JOIN your_table t
    ON t.start_time < hd.hour_start + INTERVAL 1 HOUR
    AND t.end_time >= hd.hour_start
    AND t.status = 'active'
    -- MySQL用DAYOFWEEK，注意它的返回值是1=周日，2=周一...7=周六，和PostgreSQL的DOW（0=周日，1=周一...6=周六）不同，要对应调整
    AND DAYOFWEEK(t.end_time) = DAYOFWEEK(hd.hour_start)
GROUP BY hd.hour_start
ORDER BY hd.hour_start;

SQL Server 版本

用递归CTE生成小时序列：

WITH hourly_dim AS (
    SELECT CAST('2024-05-20 09:00:00' AS DATETIME) AS hour_start
    UNION ALL
    SELECT DATEADD(HOUR, 1, hour_start)
    FROM hourly_dim
    WHERE hour_start < CAST('2024-05-20 18:00:00' AS DATETIME)
)
SELECT
    hd.hour_start,
    COUNT(DISTINCT t.person_id) AS in_service_count
FROM hourly_dim hd
LEFT JOIN your_table t
    ON t.start_time < DATEADD(HOUR, 1, hd.hour_start)
    AND t.end_time >= hd.hour_start
    AND t.status = 'active'
    AND DATEPART(WEEKDAY, t.end_time) = DATEPART(WEEKDAY, hd.hour_start)
GROUP BY hd.hour_start
ORDER BY hd.hour_start
OPTION (MAXRECURSION 0); -- 递归次数限制，默认是100，这里放开

关键注意点

避免重复计数：用COUNT(DISTINCT person_id)，因为同一个人员的时间段可能覆盖多个小时，确保每个小时只统计一次。
空值处理：左连接保证即使某个小时没有符合条件的人员，结果也会显示0，不会漏掉该小时。
日期/星期匹配：如果需求是统计具体某一天的各小时数据，把星期判断换成DATE(t.end_time) = '2024-05-20'（对应数据库的日期函数）；如果是统计每周固定星期几的平均/汇总数据，保留星期判断即可。
工作时间调整：修改CTE里的起止时间就能适配不同的工作时段，比如改成'2024-05-20 08:00:00'到'2024-05-20 19:00:00'。

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