SQL统计每日各时段在岗员工数：跨天班次统计缺失问题

问题概述 #

现有员工考勤班次数据，已合并连续班次，但SQL查询仅能统计班次起始日期的小时在岗数，无法覆盖跨天班次的次日时段（如2022-10-23 18:00至2022-10-24 10:30的班次，无法统计2022-10-24的1-10点数据）。

原查询的核心问题 #

1. 日期分组局限：使用CAST(Start_Time AS DATE)作为分组日期，跨天班次仅会关联到起始日期，忽略结束日期的时段。
2. 小时匹配逻辑失效：HOUR BETWEEN DATEPART(HOUR, START_TIME) AND DATEPART(HOUR, END_TIME)仅适用于当天班次，跨天时结束时间的小时数值小于起始时间，条件不成立。

解决方案 #

步骤1：生成完整的日期-小时维度表 #

先获取考勤数据中的最小和最大日期，生成该范围内所有日期的24小时序列，确保覆盖所有需要统计的时段。

步骤2：调整班次匹配逻辑 #

判断每个日期-小时是否落在员工的班次时间段内，需考虑跨天场景：

• 若班次在同一天：日期匹配且小时在起始和结束小时之间
• 若班次跨天：要么是起始日期且小时≥起始小时，要么是结束日期且小时≤结束小时，或者是中间的完整日期（若有）

完整SQL代码 #

CREATE TABLE tab(
    employee        INT,
    start_time      DATETIME,    
    end_time        DATETIME
);
INSERT INTO tab VALUES
(123,       '2022-10-23 10:40:00.000', '2022-10-23 14:00:00.000'),
(123,       '2022-10-23 14:00:00.000', '2022-10-23 14:30:00.000'),
(123,       '2022-10-23 14:35:00.000', '2022-10-23 17:07:00.000'),
(541,       '2022-10-23 06:50:00.000', '2022-10-23 12:00:00.000'),
(541,       '2022-10-23 13:00:00.000', '2022-10-23 15:30:00.000'),
(799,       '2022-10-23 18:00:00.000', '2022-10-23 22:30:00.000'),
(799,       '2022-10-23 22:35:00.000', '2022-10-24 10:30:00.000');

WITH cte AS (
    -- 标记班次分区：前后班次间隔≥60分钟则新建分区
    SELECT *,
           CASE WHEN DATEDIFF(mi, LAG(end_time) OVER(PARTITION BY employee ORDER BY start_time), start_time) >= 60 
                THEN 1 ELSE 0 
           END AS change_partition
    FROM tab
), cte2 AS (
    -- 生成员工的班次分区ID
    SELECT *, SUM(change_partition) OVER(PARTITION BY employee ORDER BY start_time) AS partitions
    FROM cte
), merged_shifts AS (
    -- 合并连续班次
    SELECT 
        Employee, MIN(start_time) AS start_time, MAX(end_time) AS end_time 
    FROM 
        cte2
    GROUP BY 
        Employee, partitions
), date_range AS (
    -- 获取考勤数据的日期范围
    SELECT MIN(CAST(start_time AS DATE)) AS min_date, MAX(CAST(end_time AS DATE)) AS max_date
    FROM merged_shifts
), dates AS (
    -- 生成日期序列
    SELECT min_date AS stat_date
    FROM date_range
    UNION ALL
    SELECT DATEADD(day, 1, stat_date)
    FROM dates
    WHERE stat_date < (SELECT max_date FROM date_range)
), hours AS (
    -- 生成小时序列
    SELECT 0 AS hour_num
    UNION ALL
    SELECT hour_num + 1 FROM hours WHERE hour_num < 23
), date_hours AS (
    -- 组合日期和小时，生成完整维度表
    SELECT 
        stat_date,
        hour_num,
        -- 生成当前小时的起始和结束时间，用于匹配班次
        DATETIMEFROMPARTS(YEAR(stat_date), MONTH(stat_date), DAY(stat_date), hour_num, 0, 0, 0) AS hour_start,
        DATETIMEFROMPARTS(YEAR(stat_date), MONTH(stat_date), DAY(stat_date), hour_num, 59, 59, 999) AS hour_end
    FROM dates, hours
)
SELECT 
    dh.stat_date AS [Date],
    dh.hour_num AS [Hour],
    COUNT(DISTINCT ms.employee) AS [Count]
FROM date_hours dh
LEFT JOIN merged_shifts ms 
    ON (
        -- 情况1：班次在同一天，当前小时在班次时间范围内
        (CAST(ms.start_time AS DATE) = CAST(ms.end_time AS DATE) 
         AND dh.stat_date = CAST(ms.start_time AS DATE)
         AND dh.hour_num BETWEEN DATEPART(HOUR, ms.start_time) AND DATEPART(HOUR, ms.end_time))
        OR
        -- 情况2：班次跨天，当前是起始日期且小时≥起始小时
        (CAST(ms.start_time AS DATE) < CAST(ms.end_time AS DATE)
         AND dh.stat_date = CAST(ms.start_time AS DATE)
         AND dh.hour_num >= DATEPART(HOUR, ms.start_time))
        OR
        -- 情况3：班次跨天，当前是结束日期且小时≤结束小时
        (CAST(ms.start_time AS DATE) < CAST(ms.end_time AS DATE)
         AND dh.stat_date = CAST(ms.end_time AS DATE)
         AND dh.hour_num <= DATEPART(HOUR, ms.end_time))
        OR
        -- 情况4：班次跨多天，当前是中间的完整日期（所有小时都算在岗）
        (CAST(ms.start_time AS DATE) < dh.stat_date 
         AND dh.stat_date < CAST(ms.end_time AS DATE))
    )
GROUP BY dh.stat_date, dh.hour_num
ORDER BY dh.stat_date, dh.hour_num;

说明 #

• 新增date_range和dates CTE生成需要统计的所有日期，覆盖跨天的结束日期
• date_hours生成每个日期的24小时完整序列，同时计算每个小时的起止时间
• 匹配逻辑分四种情况，全面覆盖当天、跨天、跨多天的班次场景
• 使用COUNT(DISTINCT ms.employee)确保同一员工同一小时不重复计数

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