如何计算含特殊工作日程员工的缺勤日期区间工时？

针对特殊工作日程（周末上班、每日工时不等）的情况，可通过生成缺勤区间内的所有日期，匹配对应工作日的工时并汇总解决，同时优化性能问题。

核心思路 #

• 为每个员工的缺勤区间生成完整日期范围，避免低效递归逻辑
• 将每个日期映射为星期几，关联员工的自定义工时配置
• 过滤掉工时为0的非工作日，汇总实际缺勤工时

优化后的SQL实现 #

WITH date_range AS (
    -- 为目标员工的缺勤区间生成所有日期，避免递归笛卡尔积
    SELECT 
        emp.EMPLOYEE_NUMBER,
        emp.START_DATE_ABSENCE + LEVEL - 1 AS absence_date
    FROM (
        SELECT 
            EMP_A.EMPLOYEE_NUMBER,
            EMP_P.START_DATE AS START_DATE_ABSENCE,
            EMP_P.END_DATE AS END_DATE_ABSENCE
        FROM XXAS.XXAS_FHT_EMPLOYEES_ALL_MV EMP_A
        JOIN XXAS.XXAS_FHT_EMP_PERIODS_R EMP_P
            ON EMP_P.EMPLOYEE_NUMBER = EMP_A.EMPLOYEE_NUMBER
        WHERE EMP_A.EMPLOYEE_NUMBER = '1000599'
          AND EMP_P.START_DATE >= SYSDATE
    ) emp
    CONNECT BY LEVEL <= emp.END_DATE_ABSENCE - emp.START_DATE_ABSENCE + 1
        -- 关键约束：防止重复生成数据
        AND PRIOR emp.EMPLOYEE_NUMBER = emp.EMPLOYEE_NUMBER
        AND PRIOR SYS_GUID() IS NOT NULL
),
employee_hours AS (
    -- 获取目标员工的自定义工作日工时配置
    SELECT 
        EMPLOYEE_NUMBER,
        MONDAY, TUESDAY, WEDNESDAY, THURSDAY, FRIDAY, SATURDAY, SUNDAY
    FROM XXAS.XXAS_FHT_EMPLOYEES_ALL_MV
    WHERE EMPLOYEE_NUMBER = '1000599'
)
-- 汇总总缺勤工时
SELECT 
    dr.EMPLOYEE_NUMBER,
    SUM(
        CASE TO_CHAR(dr.absence_date, 'DY', 'NLS_DATE_LANGUAGE=ENGLISH')
            WHEN 'MON' THEN eh.MONDAY
            WHEN 'TUE' THEN eh.TUESDAY
            WHEN 'WED' THEN eh.WEDNESDAY
            WHEN 'THU' THEN eh.THURSDAY
            WHEN 'FRI' THEN eh.FRIDAY
            WHEN 'SAT' THEN eh.SATURDAY
            WHEN 'SUN' THEN eh.SUNDAY
        END
    ) AS total_absence_hours
FROM date_range dr
JOIN employee_hours eh
    ON dr.EMPLOYEE_NUMBER = eh.EMPLOYEE_NUMBER
-- 过滤员工不上班的日期（工时为0）
WHERE 
    CASE TO_CHAR(dr.absence_date, 'DY', 'NLS_DATE_LANGUAGE=ENGLISH')
        WHEN 'MON' THEN eh.MONDAY
        WHEN 'TUE' THEN eh.TUESDAY
        WHEN 'WED' THEN eh.WEDNESDAY
        WHEN 'THU' THEN eh.THURSDAY
        WHEN 'FRI' THEN eh.FRIDAY
        WHEN 'SAT' THEN eh.SATURDAY
        WHEN 'SUN' THEN eh.SUNDAY
    END > 0
GROUP BY dr.EMPLOYEE_NUMBER;

关键优化点 #

• 避免递归冗余：通过PRIOR约束确保每个员工的日期范围仅生成一次，防止笛卡尔积
• 星期几语言固定：使用NLS_DATE_LANGUAGE=ENGLISH避免数据库语言设置影响星期判断
• 精准过滤非工作日：仅统计员工实际应出勤的日期，排除工时为0的休息日

高性能替代方案：使用日期维度表 #

若数据库存在预定义的日期维度表（包含所有日期及星期属性），可直接关联替代递归生成，性能提升更明显：

SELECT 
    emp.EMPLOYEE_NUMBER,
    SUM(
        CASE dim.DAY_OF_WEEK_ABBR
            WHEN 'MON' THEN emp.MONDAY
            WHEN 'TUE' THEN emp.TUESDAY
            WHEN 'WED' THEN emp.WEDNESDAY
            WHEN 'THU' THEN emp.THURSDAY
            WHEN 'FRI' THEN emp.FRIDAY
            WHEN 'SAT' THEN emp.SATURDAY
            WHEN 'SUN' THEN emp.SUNDAY
        END
    ) AS total_absence_hours
FROM (
    SELECT 
        EMP_A.EMPLOYEE_NUMBER,
        EMP_P.START_DATE AS START_DATE_ABSENCE,
        EMP_P.END_DATE AS END_DATE_ABSENCE,
        EMP_A.MONDAY, EMP_A.TUESDAY, EMP_A.WEDNESDAY,
        EMP_A.THURSDAY, EMP_A.FRIDAY, EMP_A.SATURDAY, EMP_A.SUNDAY
    FROM XXAS.XXAS_FHT_EMPLOYEES_ALL_MV EMP_A
    JOIN XXAS.XXAS_FHT_EMP_PERIODS_R EMP_P
        ON EMP_P.EMPLOYEE_NUMBER = EMP_A.EMPLOYEE_NUMBER
    WHERE EMP_A.EMPLOYEE_NUMBER = '1000599'
      AND EMP_P.START_DATE >= SYSDATE
) emp
JOIN DATE_DIMENSION dim
    ON dim.CALENDAR_DATE BETWEEN emp.START_DATE_ABSENCE AND emp.END_DATE_ABSENCE
WHERE 
    CASE dim.DAY_OF_WEEK_ABBR
        WHEN 'MON' THEN emp.MONDAY
        WHEN 'TUE' THEN emp.TUESDAY
        WHEN 'WED' THEN emp.WEDNESDAY
        WHEN 'THU' THEN emp.THURSDAY
        WHEN 'FRI' THEN emp.FRIDAY
        WHEN 'SAT' THEN emp.SATURDAY
        WHEN 'SUN' THEN emp.SUNDAY
    END > 0
GROUP BY emp.EMPLOYEE_NUMBER;

内容的提问来源于stack exchange，提问作者Gerard van der Schoot