Как посчитать Precision и Recall в SQL

Зачем Precision и Recall

В fraud-детекции precision 95% звучит круто. Recall — 40%. Значит модель ловит только 40% всех fraud случаев, остальные 60% проходят. Деньги уходят. Precision и Recall вместе дают full picture модели.

Формулы

Precision = TP / (TP + FP)  -- из flagged как positive, сколько реально positive
Recall    = TP / (TP + FN)  -- из всех реально positive, сколько поймали
F1        = 2 × P × R / (P + R)

Базовый расчёт

Данные: predictions(user_id, predicted, actual) где predicted/actual ∈ {0, 1}.

SELECT
    COUNT(*) FILTER (WHERE predicted = 1 AND actual = 1) AS tp,
    COUNT(*) FILTER (WHERE predicted = 1 AND actual = 0) AS fp,
    COUNT(*) FILTER (WHERE predicted = 0 AND actual = 1) AS fn,
    COUNT(*) FILTER (WHERE predicted = 0 AND actual = 0) AS tn,
    COUNT(*) FILTER (WHERE predicted = 1 AND actual = 1)::NUMERIC
        / NULLIF(COUNT(*) FILTER (WHERE predicted = 1), 0) AS precision,
    COUNT(*) FILTER (WHERE predicted = 1 AND actual = 1)::NUMERIC
        / NULLIF(COUNT(*) FILTER (WHERE actual = 1), 0) AS recall
FROM predictions;

Threshold tuning

Модель выдаёт score 0-1. Threshold определяет, что считать positive. Разные thresholds дают разные precision/recall.

WITH thresholds AS (
    SELECT generate_series(0.1, 0.9, 0.1) AS thr
)
SELECT
    t.thr,
    COUNT(*) FILTER (WHERE p.score >= t.thr AND p.actual = 1)::NUMERIC
        / NULLIF(COUNT(*) FILTER (WHERE p.score >= t.thr), 0) AS precision_at_threshold,
    COUNT(*) FILTER (WHERE p.score >= t.thr AND p.actual = 1)::NUMERIC
        / NULLIF(COUNT(*) FILTER (WHERE p.actual = 1), 0) AS recall_at_threshold
FROM thresholds t
CROSS JOIN predictions p
GROUP BY t.thr
ORDER BY t.thr;

High threshold → high precision, low recall. Low threshold — наоборот.

По сегментам

SELECT
    u.segment,
    COUNT(*) FILTER (WHERE p.predicted = 1 AND p.actual = 1)::NUMERIC
        / NULLIF(COUNT(*) FILTER (WHERE p.predicted = 1), 0) AS precision,
    COUNT(*) FILTER (WHERE p.predicted = 1 AND p.actual = 1)::NUMERIC
        / NULLIF(COUNT(*) FILTER (WHERE p.actual = 1), 0) AS recall
FROM predictions p
JOIN users u ON u.user_id = p.user_id
GROUP BY u.segment;

Если precision разный по сегментам — модель biased.

Частые ошибки

Ошибка 1. Precision/Recall на несбалансированных классах. Class 1 — 0,1%. Precision/Recall на normal threshold 0.5 — обманчиво. Используйте PR-curve.

Ошибка 2. F1 vs F0.5/F2. F1 — равный вес. F0.5 — precision важнее. F2 — recall важнее. Зависит от задачи.

Ошибка 3. Confusion на definition. TP = «I said positive AND it WAS positive». FN = «I said negative BUT it WAS positive». Точно прописать.

Ошибка 4. Threshold без context. Threshold = 0.5 — не дефолт во всех задачах. Подбирайте по бизнес-критерию.

Ошибка 5. Игнорировать calibration. Score 0.7 ≠ «70% вероятность positive» если модель не calibrated.

Связанные темы

• Accuracy vs F1
• AUC ROC простыми словами
• F1 score
• Метрики ML на собесе DS

FAQ

Precision или Recall — что важнее?

Зависит. В fraud — recall (нельзя пропустить). В spam-фильтре — precision (нельзя блокировать ham).

Precision-Recall trade-off?

Да. Threshold up → precision up, recall down (и наоборот).

F1 vs AUC?

F1 — на single threshold. AUC — across all thresholds.

Precision 100% — что значит?

Модель сверх-консервативна. Возможно, recall очень low.

PR-curve vs ROC?

На несбалансированных PR-curve более показательна (ROC может быть optimistic).