如何用SQL计算classification_report的precision、recall等指标

问题背景 #

现有一张存储二分类预测结果的表，表结构示例如下：

id, predict_value, real_value
1,  true, true
2,  true, false
...

需要通过SQL生成类似以下格式的分类报告：

class   precision    recall  f1-score   support

   False       0.97      1.00      0.98     57241
    True       0.68      0.22      0.33      2323
accuracy       NULL      NULL      0.97     59564

SQL查询语句 #

以下是适配该需求的SQL语句（以MySQL为例，其他数据库可根据语法微调）：

-- 计算各类别及整体的混淆矩阵指标
WITH metrics AS (
    SELECT
        real_value AS class,
        COUNT(*) AS support,
        SUM(CASE WHEN predict_value = real_value THEN 1 ELSE 0 END) AS correct,
        SUM(CASE WHEN predict_value = true AND real_value = true THEN 1 ELSE 0 END) AS tp,
        SUM(CASE WHEN predict_value = true AND real_value = false THEN 1 ELSE 0 END) AS fp,
        SUM(CASE WHEN predict_value = false AND real_value = true THEN 1 ELSE 0 END) AS fn
    FROM your_table_name
    GROUP BY real_value
    UNION ALL
    -- 计算整体准确率相关指标
    SELECT
        'accuracy' AS class,
        COUNT(*) AS support,
        SUM(CASE WHEN predict_value = real_value THEN 1 ELSE 0 END) AS correct,
        NULL AS tp,
        NULL AS fp,
        NULL AS fn
    FROM your_table_name
)
SELECT
    class,
    -- 精确率：仅类别行计算，保留两位小数
    CASE WHEN class != 'accuracy' THEN ROUND(tp / (tp + fp), 2) ELSE NULL END AS precision,
    -- 召回率：仅类别行计算，保留两位小数
    CASE WHEN class != 'accuracy' THEN ROUND(tp / (tp + fn), 2) ELSE NULL END AS recall,
    -- F1分数：类别行用调和平均数，准确率行用整体准确率
    CASE
        WHEN class != 'accuracy' THEN ROUND(2 * (tp/(tp+fp)) * (tp/(tp+fn)) / ((tp/(tp+fp)) + (tp/(tp+fn))), 2)
        ELSE ROUND(correct / support, 2)
    END AS f1_score,
    support
FROM metrics
ORDER BY 
    CASE WHEN class = 'accuracy' THEN 1 ELSE 0 END,
    class;

语句说明 #

• 先用CTE metrics 统计每个真实类别的支持数（support）、正确预测数、真正例（TP）、假正例（FP）、假负例（FN），同时单独计算整体样本的准确率相关数据。
• 外层查询基于中间结果计算各指标：
  • 精确率：真正例占所有预测为该类别的比例
  • 召回率：真正例占所有真实为该类别的比例
  • F1分数：精确率与召回率的调和平均数
  • 准确率：所有正确预测占总样本的比例
• 通过排序让类别行在前、准确率行在后，匹配目标输出格式。

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