SQL如何基于起止时间列生成1小时时间桶统计各时段在岗员工数

你给出的统计规则本质是每个整点时间桶对应该小时起始时刻的在岗状态，只要员工班次的时间范围覆盖该整点时刻，即计入该时段的在岗人数，和示例计算逻辑完全匹配。实现分3步：

• 过滤出目标统计周期内的有效班次数据，保留带日期的完整时间，不要单独截取TIME类型的时分秒部分，避免跨天班次统计错误
• 生成统计周期内连续的1小时间隔整点时间桶
• 通过时间范围关联班次和时间桶，按时间桶分组计数得到结果

适配你现有表结构的SQL如下（基于你当前使用的BigQuery语法编写）：

-- 按需修改统计起始时间、时区参数
DECLARE stats_start TIMESTAMP DEFAULT TIMESTAMP("2022-05-01 00:00:00", "Asia/Shanghai");

WITH valid_shifts AS (
  SELECT
    employee_id,
    DATETIME(shift_start_at, timezone) AS shift_start_local,
    DATETIME(shift_end_at, timezone) AS shift_end_local
  FROM `employee_shifts`
  WHERE DATE(shift_start_at, timezone) >= DATE(stats_start, "Asia/Shanghai")
    AND shift_end_at > shift_start_at -- 过滤起止时间异常的无效班次
),
hour_buckets AS (
  SELECT hour_bucket
  FROM UNNEST(GENERATE_DATETIME_ARRAY(
    DATETIME(stats_start, "Asia/Shanghai"),
    (SELECT MAX(shift_end_local) FROM valid_shifts), -- 截止到最晚班次结束时间，可替换为固定结束时间
    INTERVAL 1 HOUR
  )) AS hour_bucket
)
SELECT
  FORMAT_DATETIME("%Y-%m-%d %I:%M %p", h.hour_bucket) AS stats_time_bucket,
  COUNT(DISTINCT s.employee_id) AS on_duty_count
FROM hour_buckets h
LEFT JOIN valid_shifts s
  ON h.hour_bucket >= s.shift_start_local
  AND h.hour_bucket <= s.shift_end_local -- 若下班整点不算在岗，将<=改为<即可，当前写法匹配示例逻辑
GROUP BY h.hour_bucket
ORDER BY h.hour_bucket;

代入你给出的示例场景：

• 员工A班次12pm-3pm：覆盖12:00、13:00、14:00、15:00四个时间桶
• 员工B班次2pm-4pm：覆盖14:00、15:00、16:00三个时间桶
  输出结果完全符合预期：
• 12pm：在岗1人
• 1pm：在岗1人
• 2pm：在岗2人
• 3pm：在岗2人
• 4pm：在岗1人

• 禁止用TIME()函数单独截取班次的时分秒部分，否则跨零点的夜班（如23:00上班到次日7:00）会出现统计偏差
• 如果只需要统计固定时间范围（如2022年5月全月），直接在GENERATE_DATETIME_ARRAY中写死结束时间即可，不需要取班次表的最大结束时间
• 统计时用COUNT(DISTINCT employee_id)是为了避免同个员工重复班次数据导致的计数虚高，如果确定每个员工同一时间只有一条班次记录，可替换为COUNT(s.employee_id)提升查询效率

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