Oracle中Pivot多聚合实现分组错误率计算求助

核心问题定位 #

你的SQL返回全null的直接原因是字符串匹配不一致：子查询里定义的when字段值是'before '和'after '（末尾带空格），但pivot子句里用的是'before'和'after'（无空格），两者无法匹配，导致聚合结果为空。

另外，你需要的是错误率（错误数/总记录数），原SQL只计算了错误数，没有除以总数，所以即使修复空格也得不到比例值。

修正后的SQL方案 #

方案1：直接用条件聚合替代Pivot（更简洁易读） #

这种方式不需要union all，直接在select里通过case计算before/after的错误率：

SELECT
    definition,
    -- 计算before阶段错误率：错误数/总数
    ROUND(
        SUM(CASE WHEN processend < :SOME_DATE AND STATE IN (10,15,19) THEN 1 ELSE 0 END)
        / NULLIF(SUM(CASE WHEN processend < :SOME_DATE THEN 1 ELSE 0 END), 0),
        2
    ) AS before,
    -- 计算after阶段错误率
    ROUND(
        SUM(CASE WHEN processend >= :SOME_DATE AND STATE IN (10,15,19) THEN 1 ELSE 0 END)
        / NULLIF(SUM(CASE WHEN processend >= :SOME_DATE THEN 1 ELSE 0 END), 0),
        2
    ) AS after
FROM REQUEST_HISTORY
GROUP BY definition;

• 用NULLIF避免分母为0导致的报错
• ROUND(...,2)用来保留两位小数，和你期望的结果格式一致

方案2：修复原Pivot写法的问题 #

如果坚持用Pivot语法，需要先修正字符串空格问题，同时计算错误率：

SELECT
    definition,
    ROUND(before_errors / NULLIF(before_total, 0), 2) AS before,
    ROUND(after_errors / NULLIF(after_total, 0), 2) AS after
FROM (
    SELECT
        definition,
        SUM(CASE WHEN when_type = 'before' AND STATE IN (10,15,19) THEN 1 ELSE 0 END) AS before_errors,
        SUM(CASE WHEN when_type = 'before' THEN 1 ELSE 0 END) AS before_total,
        SUM(CASE WHEN when_type = 'after' AND STATE IN (10,15,19) THEN 1 ELSE 0 END) AS after_errors,
        SUM(CASE WHEN when_type = 'after' THEN 1 ELSE 0 END) AS after_total
    FROM (
        SELECT 'before' AS when_type, definition, state
        FROM REQUEST_HISTORY
        WHERE processend < :SOME_DATE
        UNION ALL
        SELECT 'after' AS when_type, definition, state
        FROM REQUEST_HISTORY
        WHERE processend >= :SOME_DATE
    ) t
    GROUP BY definition
) t;

关键说明 #

• 所有方案都处理了分母为0的情况：当某个definition在before/after阶段没有记录时，用NULLIF将0转为null，避免除法报错
• 保留两位小数使用ROUND函数，你可以根据需求调整小数位数
• 原SQL的sum()/count()报错，大概率是因为在Pivot子句里不能直接使用除法，必须先计算出分子和分母再做除法

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