Как посчитать log loss в SQL

Зачем log loss

Log loss (cross-entropy) — стандартная loss-функция для бинарной классификации. В отличие от accuracy, штрафует «уверенно неверные» предсказания: предсказание 0.99 для actual=0 даёт огромный штраф, а 0.51 для actual=0 — маленький. Используется как objective при обучении логистической регрессии и нейросетей, и как метрика для сравнения probability-моделей.

Формула

log_loss = − (1/N) × Σ [y_i × ln(p_i) + (1 − y_i) × ln(1 − p_i)]

• y_i ∈ {0, 1} — actual
• p_i ∈ (0, 1) — predicted probability

Нижняя граница (perfect) — 0. Random binary — ln(2) ≈ 0.693.

Log loss в SQL

WITH safe_predictions AS (
    SELECT
        actual_label AS y,
        GREATEST(1e-15, LEAST(1 - 1e-15, predicted_proba)) AS p
    FROM model_predictions
    WHERE prediction_date >= CURRENT_DATE - INTERVAL '30 days'
)
SELECT
    -AVG(y * LN(p) + (1 - y) * LN(1 - p)) AS log_loss,
    COUNT(*) AS n
FROM safe_predictions;

GREATEST(1e-15, LEAST(1 - 1e-15, p)) — clipping. Без него LN(0) = -inf сломает запрос.

Сравнение моделей

Считаем log loss для нескольких моделей на одном test set:

WITH safe AS (
    SELECT
        model_name,
        actual_label AS y,
        GREATEST(1e-15, LEAST(1 - 1e-15, predicted_proba)) AS p
    FROM model_predictions
    WHERE prediction_date >= CURRENT_DATE - INTERVAL '30 days'
)
SELECT
    model_name,
    -AVG(y * LN(p) + (1 - y) * LN(1 - p)) AS log_loss,
    COUNT(*) AS n
FROM safe
GROUP BY model_name
ORDER BY log_loss;

Меньше log loss — лучше калиброванная модель.

Class imbalance

Если 95% точек — negative, baseline log loss «всегда p=0.05»:

log_loss_baseline = -[0.05 × ln(0.05) + 0.95 × ln(0.95)] ≈ 0.198

WITH base_rate AS (
    SELECT AVG(actual_label) AS p_pos FROM model_predictions
)
SELECT
    -(p_pos * LN(p_pos) + (1 - p_pos) * LN(1 - p_pos)) AS baseline_log_loss,
    (SELECT -AVG(actual_label * LN(GREATEST(1e-15, LEAST(1 - 1e-15, predicted_proba)))
                + (1 - actual_label) * LN(GREATEST(1e-15, LEAST(1 - 1e-15, 1 - predicted_proba))))
     FROM model_predictions) AS model_log_loss
FROM base_rate;

Сравнение с baseline показывает, насколько модель полезнее «всегда возвращать p=base_rate».

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

Ошибка 1. Не клипить probabilities. p=0 или p=1 → LN(0) = -inf. Clipping 1e-15 безопасен и стандартен в scikit-learn.

Ошибка 2. Считать log loss на predicted class, а не probability. Бинарные labels превращают log loss в binary cross-entropy на 0/1 — теряется информация о confidence.

Ошибка 3. Сравнивать log loss моделей с разной base rate. Если test set имеет разную base_rate, log loss моделей несравним. Сравнивайте на одной test set.

Ошибка 4. Log loss vs AUC. Log loss чувствителен к калибровке. AUC — к ранжированию. Модель может иметь высокий AUC и плохой log loss (нужна recalibration).

Ошибка 5. Считать на train data. Train log loss всегда ниже. Метрика — только на test/validation.

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

• Как посчитать confusion matrix в SQL
• Как посчитать AUC-ROC в SQL
• Как посчитать Brier score в SQL
• Как посчитать precision-recall в SQL

FAQ

Range log loss?

[0, +∞). 0 — perfect. ln(2) ≈ 0.693 — random binary.

Log loss vs cross-entropy?

Эквивалентны для бинарной классификации. Cross-entropy — multi-class generalization.

Clipping какой?

1e-15 (sklearn default). 1e-7 для float32 моделей.

Почему модель с хорошим accuracy может иметь плохой log loss?

Модель плохо калибрована — предсказывает 0.95 когда фактически 0.6.

Log loss на multi-class?

-Σ y_i × ln(p_i) для one-hot encoded labels. В SQL — через JOIN на class.