Как посчитать data uniqueness в SQL

Зачем uniqueness

Поле, которое должно быть unique (user_id, order_id, transaction_id), вдруг становится не unique — это серьёзный data bug. ETL дублирует строки, JOIN раздувает, сторонний API повторяет webhook. Каждый дубль смещает counts и метрики.

Простой uniqueness ratio

SELECT
    COUNT(*) AS total_rows,
    COUNT(DISTINCT order_id) AS unique_ids,
    COUNT(DISTINCT order_id)::NUMERIC / NULLIF(COUNT(*), 0) AS uniqueness_ratio
FROM orders
WHERE created_at >= CURRENT_DATE - INTERVAL '7 days';

Если ratio = 1.0 — поле полностью уникально. 0.95 — 5% дублей.

Найти дубли

Какие именно order_id повторяются:

SELECT
    order_id,
    COUNT(*) AS occurrences
FROM orders
WHERE created_at >= CURRENT_DATE - INTERVAL '7 days'
GROUP BY order_id
HAVING COUNT(*) > 1
ORDER BY occurrences DESC
LIMIT 50;

Часто дубли группируются — один день/час/source. Это подсказка, где искать причину.

Мониторинг во времени

Динамика uniqueness:

SELECT
    DATE_TRUNC('day', created_at)::DATE AS day,
    COUNT(*) AS total,
    COUNT(DISTINCT order_id) AS unique_ids,
    COUNT(*) - COUNT(DISTINCT order_id) AS duplicate_rows,
    1 - COUNT(DISTINCT order_id)::NUMERIC / COUNT(*) AS duplicate_rate
FROM orders
WHERE created_at >= CURRENT_DATE - INTERVAL '30 days'
GROUP BY DATE_TRUNC('day', created_at)
ORDER BY day;

Резкий скачок duplicate_rate — день, когда что-то поломалось.

Composite key uniqueness

Иногда uniqueness держится на наборе колонок: (user_id, transaction_date, amount):

SELECT
    COUNT(*) AS total_rows,
    COUNT(DISTINCT (user_id, transaction_date, amount)) AS unique_keys,
    COUNT(*) - COUNT(DISTINCT (user_id, transaction_date, amount)) AS duplicate_rows
FROM transactions
WHERE transaction_date >= CURRENT_DATE - INTERVAL '7 days';

Композит из 3+ полей — обычно индикатор «логического PK» в pipeline. Если он нарушен — есть проблема дедупа на ETL-этапе.

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

Ошибка 1. Считать uniqueness на all-time. Старые данные могут содержать known историч-дубли (исправлены, но не удалены). Считайте на свежем окне.

Ошибка 2. COUNT(DISTINCT field) с NULL. NULL не входит в COUNT(DISTINCT). Если NULL ≠ дубль для вас, делайте COALESCE или фильтр.

Ошибка 3. Не различать строковые дубли и логические. John Smith и john smith — формально разные. Если важна логическая уникальность, нормализуйте.

Ошибка 4. Игнорировать composite keys. Single-column unique не значит «нет дублей». Проверяйте бизнес-ключ.

Ошибка 5. Считать uniqueness как «pkey». PK constraint защищает на write. Но если данные пришли из external source без constraint — дубли возможны.

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

• Как посчитать duplicate rate в SQL
• Как посчитать data completeness в SQL
• Как посчитать data quality score в SQL
• Как посчитать data staleness в SQL

FAQ

Какая uniqueness допустима?

Для PK — 100%. Для логических ключей — > 99.5%, иначе дубли искажают метрики.

Дубль = строки идентичные?

Нет. Дубль обычно по ключу. Остальные поля могут отличаться (timestamp, amount после fix).

Что делать с дублями?

Сначала найти источник, потом дедуплицировать с ROW_NUMBER() OVER (PARTITION BY key ORDER BY updated_at DESC).

COUNT(DISTINCT) медленный?

На больших таблицах — да. Альтернатива: HyperLogLog через extension hll.

Composite key — это всегда composite?

Зависит от source. Часто (source, source_id) или (user_id, event_time, event_name).