Как посчитать Cohen's kappa в SQL

Зачем Cohen's kappa

Cohen's kappa — согласованность двух разметчиков (или модели и человека) с поправкой на случайное совпадение. Если оба ставят 90% «yes», простой agreement 81% — но это могло случиться и случайно. Kappa этот эффект убирает. Применяется в content moderation, label quality, validation classification моделей.

Формула

κ = (p_o − p_e) / (1 − p_e)

• p_o — наблюдаемая доля совпадений
• p_e — ожидаемая случайная доля совпадений (по marginals)

Range: [-1, 1]. 0 — случайное согласие. 1 — perfect. < 0 — «хуже случайного» (редко).

Kappa в SQL

WITH agreements AS (
    SELECT
        item_id,
        rater_a_label,
        rater_b_label,
        CASE WHEN rater_a_label = rater_b_label THEN 1 ELSE 0 END AS agreed
    FROM dual_ratings
    WHERE created_at >= CURRENT_DATE - INTERVAL '30 days'
),
totals AS (
    SELECT COUNT(*) AS n FROM agreements
),
po AS (
    SELECT SUM(agreed)::NUMERIC / NULLIF((SELECT n FROM totals), 0) AS p_o FROM agreements
),
marginals AS (
    SELECT
        rater_a_label AS lbl,
        COUNT(*)::NUMERIC / NULLIF((SELECT n FROM totals), 0) AS p_a
    FROM agreements
    GROUP BY rater_a_label
),
marginals_b AS (
    SELECT
        rater_b_label AS lbl,
        COUNT(*)::NUMERIC / NULLIF((SELECT n FROM totals), 0) AS p_b
    FROM agreements
    GROUP BY rater_b_label
),
pe AS (
    SELECT SUM(ma.p_a * mb.p_b) AS p_e
    FROM marginals ma
    JOIN marginals_b mb USING (lbl)
)
SELECT
    po.p_o,
    pe.p_e,
    (po.p_o - pe.p_e) / NULLIF(1 - pe.p_e, 0) AS kappa
FROM po, pe;

Multi-class kappa

Та же формула, но p_e суммируется по всем классам. Для категориальных меток (3+ классов) — стандартный Cohen's kappa.

-- Тот же CTE-пайплайн, marginals считаются по `rater_a_label` и `rater_b_label`
-- независимо от числа классов. Формула не меняется.

Альтернатива для ordinal — weighted kappa (штрафует крупные расхождения сильнее, чем соседние).

Интерпретация

Стандарт Landis & Koch (1977). Для production-критериев labelling обычно требуют κ > 0.7.

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

Ошибка 1. Сообщать только raw agreement (p_o). 90% согласие на бинарной задаче с 80/20 split — почти случайно (κ может быть 0.1).

Ошибка 2. Kappa на разных категориях у двух разметчиков. Если у одного класс «X», у другого нет — marginals JOIN исключит. Добавляйте COALESCE с 0.

Ошибка 3. p_e = 1. Если оба ставят 100% одного класса, denominator = 0. Kappa undefined.

Ошибка 4. Использовать на ordinal без weighted. «5 vs 4» и «5 vs 1» учитываются одинаково. Weighted kappa решает.

Ошибка 5. Сравнивать kappa между датасетами с разной marginal distribution. Kappa зависит от prevalence. Для строгого сравнения — alpha-coefficient (Krippendorff).

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

• Как посчитать confusion matrix в SQL
• Как посчитать F1-score в SQL
• Как посчитать precision-recall в SQL
• Как посчитать data uniqueness в SQL

FAQ

Kappa vs accuracy?

Accuracy — raw agreement. Kappa — после поправки на случайность.

Какой kappa нормальный?

Для production-labelling — > 0.7. Для exploratory — > 0.5.

Kappa отрицательный — что значит?

Разметчики систематически расходятся. Часто баг в guidelines или label-mapping.

Cohen vs Fleiss kappa?

Cohen — 2 rater. Fleiss — 3+. Расчёт другой.

Weighted kappa нужна?

Для ordinal (например, рейтинг 1-5) — да. Для категориальных — нет.