Как посчитать PR-AUC в SQL

Зачем PR-AUC

Когда положительный класс — редкое событие (fraud, churn 2%), ROC-AUC завышает оценку модели: false positive rate почти не растёт от накопления negative-точек. PR-AUC (Average Precision) — стандарт для imbalanced classification: чувствительнее к качеству на positive класс.

Precision-Recall curve

Перебираем threshold от 0 до 1, для каждого считаем precision и recall:

WITH thresholds AS (
    SELECT generate_series(0.0, 1.0, 0.01)::NUMERIC AS th
),
predictions AS (
    SELECT actual_label, predicted_proba
    FROM model_predictions
    WHERE prediction_date >= CURRENT_DATE - INTERVAL '30 days'
),
pr_points AS (
    SELECT
        t.th,
        SUM(CASE WHEN p.predicted_proba >= t.th AND p.actual_label = 1 THEN 1 ELSE 0 END) AS tp,
        SUM(CASE WHEN p.predicted_proba >= t.th AND p.actual_label = 0 THEN 1 ELSE 0 END) AS fp,
        SUM(CASE WHEN p.actual_label = 1 THEN 1 ELSE 0 END) AS pos
    FROM thresholds t
    CROSS JOIN predictions p
    GROUP BY t.th
)
SELECT
    th,
    tp::NUMERIC / NULLIF(tp + fp, 0) AS precision,
    tp::NUMERIC / NULLIF(pos, 0) AS recall
FROM pr_points
ORDER BY recall;

generate_series(0.0, 1.0, 0.01) даёт 101 точку. На больших датасетах можно увеличить шаг.

Trapezoidal area

Площадь под PR кривой через лестницу трапеций:

WITH pr_curve AS (
    -- из предыдущего CTE: th, precision, recall
    SELECT * FROM pr_points
),
with_prev AS (
    SELECT
        th,
        recall,
        precision,
        LAG(recall) OVER (ORDER BY th DESC) AS prev_recall,
        LAG(precision) OVER (ORDER BY th DESC) AS prev_precision
    FROM pr_curve
),
trapezoids AS (
    SELECT
        (recall - prev_recall) * (precision + prev_precision) / 2.0 AS area
    FROM with_prev
    WHERE prev_recall IS NOT NULL
)
SELECT SUM(area) AS pr_auc
FROM trapezoids;

LAG ... ORDER BY th DESC — потому что когда threshold падает, recall растёт.

PR-AUC vs ROC-AUC

SELECT
    pr_auc,
    roc_auc,
    pr_auc - roc_auc AS gap
FROM model_metrics;

Если разница больше 0.1 — данные imbalanced и PR-AUC точнее. На balanced — оба значения близки.

Baseline для PR-AUC

Random модель даёт PR-AUC = base rate:

WITH base AS (
    SELECT AVG(actual_label) AS p_base FROM model_predictions
)
SELECT
    p_base AS random_baseline_pr_auc,
    (SELECT SUM(area) FROM trapezoids) AS model_pr_auc,
    ((SELECT SUM(area) FROM trapezoids) - p_base) / NULLIF(1 - p_base, 0) AS normalized_lift
FROM base;

PR-AUC = 0.4 при base rate 0.05 — серьёзное улучшение. PR-AUC = 0.4 при base rate 0.5 — почти случайно.

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

Ошибка 1. Сравнивать PR-AUC между датасетами. PR-AUC зависит от base rate. На imbalanced 0.3 — отлично, на balanced 0.7 — норма.

Ошибка 2. Linear-interpolation между PR-точками. Стандарт — Average Precision (step interpolation). Trapezoidal даёт чуть завышенный score.

Ошибка 3. Считать на ограниченном числе threshold. 10 точек дают грубую оценку. 100+ для точности.

Ошибка 4. PR-AUC vs ROC-AUC одинаково. На balanced data они близки. На imbalanced разделяются.

Ошибка 5. Сообщать PR-AUC без base rate. 0.4 — много или мало? Зависит от base rate. Всегда сообщайте оба.

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

• Как посчитать AUC-ROC в SQL
• Как посчитать precision-recall в SQL
• Как посчитать F1-score в SQL
• ROC-AUC vs PR-AUC на собесе DS

FAQ

Когда PR-AUC, когда ROC-AUC?

Imbalanced (positive < 20%) — PR-AUC. Balanced — оба.

Range PR-AUC?

[0, 1]. Random = base rate. Perfect = 1.

Average Precision и PR-AUC — одно?

AP — step-функция, PR-AUC через трапеции — близко, но не идентично. На большинстве данных разница < 0.01.

PR-AUC для multi-class?

One-vs-rest на каждый класс, потом macro-average.

Можно ли в Postgres без extension?

Да, через generate_series и LAG. Точность зависит от шага threshold.