按国家、建筑统计近6个月平均HC及病假人数遇重复记录问题求助

核心问题分析 #

• 笛卡尔积引发重复：LEFT JOIN booker.o_reporting_days rd ON 1=1 会让每个员工与所有reporting_days记录强制匹配，生成大量冗余行，后续聚合逻辑失效，最终导致结果重复。
• 日期范围逻辑错误：trunc(rd.calendar_day) BETWEEN '2022-09-01' AND '2022-03-31' 起始日期大于结束日期，该条件不会返回任何有效数据，需调整为小日期在前的合理区间（如'2022-03-31' AND '2022-09-30'）。
• 字段不匹配：主查询引用tp.building_code，但临时表中定义的是emp.building，会触发字段不存在的报错。
• 状态过滤矛盾：WHERE子句已过滤emp.status = 'A'（在职），但CASE又判断status='L'（病假），导致loa字段永远为0，逻辑完全失效。
• 平均计算逻辑错误：当前未按日期统计每日HC再求平均，而是错误聚合笛卡尔积后的结果，无法得到真实的日均Headcount。

修正后的SQL代码 #

WITH daily_headcount AS (
    SELECT
        emp.country,
        emp.building,
        rd.calendar_day,
        COUNT(DISTINCT emp.employee_id) AS daily_hc, -- 按日期统计当日在职总人数
        COUNT(DISTINCT CASE WHEN emp.status = 'L' THEN emp.employee_id END) AS daily_loa -- 当日病假人数
    FROM employees_table emp
    JOIN booker.o_reporting_days rd 
        ON rd.calendar_day BETWEEN emp.hr_begin_dt AND emp.hr_end_dt
    WHERE 
        emp.country IN (SELECT country_long_code FROM static_tbl.aa_included_countries)
        AND emp.job_level_name = '1'
        AND emp.employee_class_name !~~* '%vendor%'
        AND rd.calendar_day BETWEEN '2022-03-31' AND '2022-09-30' -- 修正为近6个月的合理日期区间
    GROUP BY emp.country, emp.building, rd.calendar_day
)
SELECT
    country,
    building,
    AVG(daily_hc) AS avg_hc, -- 计算近6个月的日均总HC
    AVG(daily_loa) AS avg_loa -- 计算近6个月的日均病假HC
FROM daily_headcount
GROUP BY country, building;

修正说明 #

• 消除笛卡尔积：将强制关联ON 1=1改为员工在职日期与报表日期的合理关联，避免冗余行。
• 按日统计HC：先按country、building、calendar_day分组，用COUNT(DISTINCT emp.employee_id)确保同一员工不会因多关联行重复计数，得到每日真实的在职和病假人数。
• 修复状态逻辑：移除错误的emp.status = 'A'过滤，保留对病假状态的判断，确保能统计到病假人员。
• 字段统一：全程使用building字段，避免字段不匹配报错。
• 正确计算平均：基于每日HC数据求平均值，得到真实的日均Headcount结果。

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