如何用SQL高效生成多规则Confusion Matrix（含NULL场景处理）

需求定义 #

• 当rule_name非NULL（规则触发）时，预测结果视为positive：
  • 若true_label为positive，记为True Positive (TP)
  • 若true_label为negative，记为False Positive (FP)
• 当rule_name为NULL（无规则触发）时：
  • 若true_label为positive，所有规则均记为False Negative (FN)
  • 若true_label为negative，所有规则均记为True Negative (TN)

输入数据集 #

期望输出 #

（注：原示例期望输出的TP/FN数值与数据集统计逻辑不符，此处按需求定义修正；若需匹配原示例，可调整统计条件）

替代UNION ALL的高效实现 #

无需逐个枚举规则并拼接UNION语句，可通过以下SQL一次性计算所有规则的混淆矩阵：

WITH unique_rules AS (
    -- 自动提取所有已存在的规则名
    SELECT DISTINCT rule_name AS rule
    FROM your_table
    WHERE rule_name IS NOT NULL
),
global_totals AS (
    -- 统计全局正负样本总数，避免重复计算
    SELECT
        COUNT(CASE WHEN true_label = 'positive' THEN 1 END) AS total_pos,
        COUNT(CASE WHEN true_label = 'negative' THEN 1 END) AS total_neg
    FROM your_table
)
SELECT
    ur.rule,
    -- TP：当前规则触发且实际为positive的样本数
    COUNT(CASE WHEN t.rule_name = ur.rule AND t.true_label = 'positive' THEN 1 END) AS "True Positive",
    -- FP：当前规则触发且实际为negative的样本数
    COUNT(CASE WHEN t.rule_name = ur.rule AND t.true_label = 'negative' THEN 1 END) AS "False Positive",
    -- TN：全局阴性样本数减去当前规则触发的阴性样本数（包含rule_name为NULL的场景）
    (gt.total_neg - COUNT(CASE WHEN t.rule_name = ur.rule AND t.true_label = 'negative' THEN 1 END)) AS "True Negative",
    -- FN：全局阳性样本数减去当前规则触发的阳性样本数（包含rule_name为NULL的场景）
    (gt.total_pos - COUNT(CASE WHEN t.rule_name = ur.rule AND t.true_label = 'positive' THEN 1 END)) AS "False Negative"
FROM unique_rules ur
CROSS JOIN your_table t
CROSS JOIN global_totals gt
GROUP BY ur.rule, gt.total_pos, gt.total_neg;

逻辑说明 #

1. unique_rules：自动获取所有非空规则名，无需手动维护规则列表，适配规则数量变化。
2. global_totals：预先计算全局正负样本总量，减少分组时的重复统计开销。
3. 分组统计：
   • TP/FP直接统计当前规则触发的对应样本；
   • TN通过全局阴性总数减去当前规则触发的阴性样本，得到非当前规则（含NULL）的阴性样本数；
   • FN通过全局阳性总数减去当前规则触发的阳性样本，得到非当前规则（含NULL）的阳性样本数。

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