多表聚合查询：用Join/Union关联两表计算员工生产错误率

问题背景 #

需要结合两张表统计每位员工的生产错误率：

• ERROR_TABLE：存储员工操作ID、错误类型及错误次数，需统计近30天指定类型（Mistake 1/2/3）的错误总数
• PRODUCTION_TABLE：记录生产任务的员工操作ID，需统计近30天员工的生产任务总数

期望输出格式：

OPR, Errors, Total, Error_Rate
123, 2, 3, 66.67%
456, 1, 2, 50.00%

（注：原需求中期望结果的123错误数为3，与给定示例数据不符，以下按实际数据逻辑输出）

当前使用LEFT JOIN时遇到两个问题：

1. 过滤指定错误类型时，丢失了无错误员工的NULL值
2. 聚合统计错误数和生产任务数时出现异常计数

问题原因 #

1. 过滤条件位置错误：如果将错误类型过滤放在主查询的WHERE子句中，会把LEFT JOIN后Errors为NULL的行（无错误的员工）直接过滤掉，因为WHERE会筛选所有行，包括左连接的结果。
2. 未预聚合导致重复计数：直接关联两张原始表进行聚合，会因为一对多关系导致生产任务数或错误数被重复统计。

解决方案 #

通过预聚合子查询分别处理两张表的数据，再进行LEFT JOIN，确保保留所有有生产任务的员工，同时正确统计错误数和任务数：

完整SQL代码 #

-- 预聚合生产任务表，统计每个员工的任务总数
WITH production_stats AS (
    SELECT 
        PROCESS_2_OPR AS OPR,
        COUNT(*) AS Total
    FROM PRODUCTION_TABLE 
    WHERE START_DATE >= DATEADD(day, -30, GETDATE())
    GROUP BY PROCESS_2_OPR
),
-- 预聚合错误表，统计每个员工的指定类型错误数
error_stats AS (
    SELECT 
        OPR,
        COUNT(REDO_COUNT) AS Errors
    FROM ERROR_TABLE
    WHERE 
        REDO_DESC IN ('Mistake 1', 'Mistake 2', 'Mistake 3')
        AND REDO_DATE >= DATEADD(day, -30, GETDATE())
    GROUP BY OPR
)
-- 关联两个统计结果，计算错误率
SELECT 
    p.OPR,
    COALESCE(e.Errors, 0) AS Errors, -- 无错误时显示0而非NULL
    p.Total,
    -- 计算错误率，保留两位小数并转为百分比格式
    CONCAT(CAST(ROUND(CAST(COALESCE(e.Errors, 0) AS FLOAT) / p.Total * 100, 2) AS VARCHAR), '%') AS Error_Rate
FROM production_stats p
LEFT JOIN error_stats e ON p.OPR = e.OPR
ORDER BY p.OPR;

代码说明 #

1. 预聚合子查询：
   • production_stats：先统计每个员工的生产任务总数，确保每个员工只返回一行结果
   • error_stats：先过滤指定错误类型并统计错误数，同样每个员工只返回一行结果
2. LEFT JOIN关联：以生产任务统计结果为基础，确保所有有生产任务的员工都被保留，即使他们没有错误
3. COALESCE处理NULL：将无错误员工的Errors从NULL转为0，避免计算错误率时出现异常
4. 错误率计算：通过ROUND保留两位小数，用CONCAT拼接百分号，得到友好的百分比格式

输出结果 #

根据给定示例数据，执行上述SQL会得到：

OPR, Errors, Total, Error_Rate
123, 2, 3, 66.67%
456, 1, 2, 50.00%

内容的提问来源于stack exchange，提问作者Sean S.