Oracle SQL统计月度无员工在岗总小时数

规则对齐 #

以下实现完全匹配排班业务规则：

• 24小时制班次，单班次固定8小时
• 支持不同员工差异化周休配置
• 自动过滤班次生效范围外的时段、员工请假时段
• 统计粒度精确到单班次，最终输出全月无任何员工在岗的总小时数

表结构约定 #

按示例表逻辑统一字段命名，实际使用时替换为自身业务表的表名、字段名即可：

• 排班表名：SCHEDULE
• 字段清单：
  • EMP_ID：员工唯一标识
  • SHIFT_START_HH24：数字类型，班次开始小时（24小时制，如0、8、16分别对应三个8小时班次）
  • WORK_DAYS_PATTERN：长度为7的字符串，按周顺序标记上班/休息，1代表当日当班，0代表周休，例如1111100代表周一至周五上班、周六周日休息
  • SHIFT_EFFECT_START：日期类型，当前排班规则生效起始日期
  • SHIFT_EFFECT_END：日期类型，当前排班规则生效截止日期
  • LEAVE_START：日期时间类型，员工全休请假的开始时间（精确到小时）
  • LEAVE_END：日期时间类型，员工全休请假的结束时间（精确到小时）

如果请假数据单独存储在独立请假表中，只需把SQL中关联请假的逻辑调整为左连接请假表、判断无匹配请假记录即可，不需要修改核心统计逻辑。

核心实现SQL #

WITH
-- 统计参数配置：替换为你要统计的月份任意日期即可
stat_param AS (
    SELECT
        TRUNC(TO_DATE('2024-06-01', 'yyyy-mm-dd'), 'MONTH') AS stat_month_start,
        LAST_DAY(TRUNC(TO_DATE('2024-06-01', 'yyyy-mm-dd'), 'MONTH')) + 1 - 1/86400 AS stat_month_end
    FROM dual
),
-- 生成统计月份内的所有自然日
all_dates AS (
    SELECT stat_month_start + LEVEL - 1 AS cur_date
    FROM stat_param
    CONNECT BY LEVEL <= stat_month_end - stat_month_start + 1
),
-- 生成统计周期内所有8小时粒度的班次时间片
all_shift_slots AS (
    SELECT
        ad.cur_date + sh.hh_val/24 AS slot_start,
        ad.cur_date + (sh.hh_val + 8)/24 AS slot_end
    FROM all_dates ad
    CROSS JOIN (SELECT 0 hh_val FROM dual UNION ALL SELECT 8 FROM dual UNION ALL SELECT 16 FROM dual) sh
    JOIN stat_param sp ON ad.cur_date + (sh.hh_val + 8)/24 <= sp.stat_month_end + 1
),
-- 统计每个时间片的实际在岗人数
slot_duty_count AS (
    SELECT
        ass.slot_start,
        ass.slot_end,
        COUNT(DISTINCT sc.emp_id) AS on_duty_emp_count
    FROM all_shift_slots ass
    LEFT JOIN SCHEDULE sc
        -- 匹配对应小时开始的班次
        ON sc.SHIFT_START_HH24 = EXTRACT(HOUR FROM CAST(ass.slot_start AS TIMESTAMP))
        -- 时间片在排班生效周期内
        AND ass.slot_start >= sc.SHIFT_EFFECT_START
        AND ass.slot_end <= sc.SHIFT_EFFECT_END + 1
        -- 匹配周休规则：非周休日才计入当班，固定NLS参数避免周计算偏移
        AND SUBSTR(sc.WORK_DAYS_PATTERN, TO_CHAR(ass.slot_start, 'D', 'NLS_DATE_LANGUAGE=AMERICAN') - 1, 1) = '1'
        -- 排除请假覆盖的时段：时间片与请假时段无重叠才计入
        AND NOT (ass.slot_start < sc.LEAVE_END AND ass.slot_end > sc.LEAVE_START)
    GROUP BY ass.slot_start, ass.slot_end
)
-- 汇总空岗总时长：每个空岗时间片固定8小时
SELECT SUM(8) AS TOTAL_NO_STAFF_HOURS
FROM slot_duty_count
WHERE on_duty_emp_count = 0;

适配说明 #

• 上述SQL固定了NLS参数确保周几计算不受数据库默认配置影响，不需要额外调整周起始偏移
• 如果存在非8小时班次、跨天班次，只需修改all_shift_slots部分的时间片生成规则即可
• 如果需要按日期拆分查看每天的空岗时长，在最终查询的SELECT和GROUP BY中加上TRUNC(slot_start)即可

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