Дедупликация в аналитике: типичные ошибки и решения

Пользователь нажал «Оплатить» один раз, клиент повторил запрос после тайм-аута — и в отчёте две покупки. Если просто удалить одинаковые строки, можно стереть и две настоящие покупки на одну сумму. Ниже — практические способы дедупликации событий в PostgreSQL 16: как выбрать правило, не склеить лишнее и проверить результат.

Сначала решите, что считать дублем

Дедупликация — не поиск строк, которые выглядят одинаково. Это правило, по которому система решает, что несколько записей описывают одно событие. Для аналитики обычно важны три вещи: какой объект учитываем, какой идентификатор отличает событие и какая запись считается основной.

У события могут быть отдельный идентификатор event_id, пользователь, тип события, время возникновения и время приёма системой. Время приёма особенно полезно при разборе повторов: клиент мог отправить событие позже или повторить запрос несколько раз. Но само по себе совпадение пользователя, типа и времени ещё не доказывает, что событие одно и то же.

Идемпотентность — свойство операции, при котором повтор одного и того же запроса не меняет результат второй раз. В аналитическом конвейере её обычно обеспечивают стабильным идентификатором события и ограничением уникальности, а не эвристикой по похожим полям.

Правильное правило дедупликации должно убирать повторную доставку одного события, но сохранять два самостоятельных события — даже если они произошли у одного пользователя почти одновременно.

Три подхода — и цена каждого

1. Стабильный ID — для повторной доставки

Если источник назначает событию один и тот же event_id при каждом повторе отправки, PostgreSQL может не принимать второй экземпляр. Уникальное ограничение — не просто проверка: база не позволит двум транзакциям одновременно записать один и тот же ключ.

Ниже предполагается, что event_id уникален во всей таблице. Если ID уникален лишь внутри клиента или проекта, ограничение должно включать и эту область, например пару «проект + event_id».

ALTER TABLE events
ADD CONSTRAINT events_event_id_key UNIQUE (event_id);

INSERT INTO events (event_id, user_id, event_name, occurred_at, received_at, payload)
VALUES ('00000000-0000-0000-0000-000000000001', 42, 'purchase',
        '2025-01-01T12:00:00Z', '2025-01-01T12:00:02Z', '{}'::jsonb)
ON CONFLICT (event_id) DO NOTHING;

Ограничение не добавится, если в таблице уже есть дубли по этому ключу. Сначала найдите и разберите их, затем создавайте уникальность. DO NOTHING подходит, когда повторное событие не должно менять первую запись; если источник присылает исправления, нужно определить отдельное правило обновления, а не молча отбрасывать их.

2. Оконная функция — для разбора накопленных дублей

В PostgreSQL 16 функция row_number() нумерует строки внутри каждой группы. Сортировка задаёт, какая запись получит номер 1. В примере выбирается запись с самым ранним временем приёма, а ingest_id разрешает ничью устойчивым образом.

SELECT event_id, user_id, event_name, occurred_at, payload
FROM (
    SELECT e.*,
           row_number() OVER (
               PARTITION BY event_id
               ORDER BY received_at ASC, ingest_id ASC
           ) AS duplicate_rank
    FROM events AS e
) AS ranked
WHERE duplicate_rank = 1;

Это запрос для получения очищенного набора, а не команда на удаление исходных записей. Перед изменением таблицы сравните число строк до и после, проверьте несколько групп вручную и убедитесь, что выбранная запись сохраняет нужные поля. Правило «самая ранняя принятая» — пример, не универсальная истина: для исправленных событий основной может быть последняя версия.

3. Составное совпадение — только с проверкой риска

Если идентификатора нет, можно искать совпадения по полям, например по пользователю, типу события и бизнес-объекту. Для заказов естественным кандидатом может быть ID заказа, для просмотра страницы — такого надёжного ключа часто нет. Временное окно иногда помогает сузить поиск, но его границы нужно обосновать поведением источника, а не выбрать на глаз.

Опасная ошибка — считать одинаковые user_id, event_name и секунду времени достаточным основанием для удаления одной строки. Пользователь может дважды нажать кнопку или выполнить два одинаковых действия подряд. Проверяйте долю совпадений на выборке с исходными логами, а неоднозначные события оставляйте раздельно. Если цена ложного удаления высока, лучше сохранить возможный дубль и пометить его для анализа.

Типичные ошибки, которые портят отчёт

• Дедуплицировать по всем полям. Повторная доставка может получить другое время приёма или слегка изменённый payload, и строки перестанут быть одинаковыми. И наоборот, две реальные операции могут совпасть по большинству атрибутов.
• Считать время события уникальным ключом. Часы устройств расходятся, метки округляются, а два действия могут случиться в одну секунду. Время — признак для анализа, но редко надёжный идентификатор.
• Выбирать запись без порядка. Если в row_number() сортировка не задаёт устойчивое правило, выбор победителя не отражает бизнес-смысл. Добавьте дополнительный критерий и объясните, почему именно эта запись главная.
• Удалять дубли до выяснения источника. Повторы могут появляться при ретраях, повторной загрузке файла или ошибке генерации ID. Устранение строк не исправит причину — следующая загрузка создаст проблему снова.
• Проверять только количество строк. Итоговое число может выглядеть правдоподобно, хотя исчезли покупки или сохранились два события с разными ID. Сверяйте также суммы и число событий по ключевым типам, датам и источникам.

Как выбрать правило на проекте

1. Зафиксируйте сущность. Решите, что именно нельзя посчитать дважды: событие, заказ, платёж или попытку оплаты. Проверка: аналитик и владелец источника одинаково объясняют, что означает одна строка.
2. Найдите ключ у источника. Уточните, создаётся ли ID до первой отправки и остаётся ли прежним при ретраях. Проверка: повторно отправьте тестовое событие и сравните его идентификатор.
3. Посчитайте кандидатов в дубли отдельно. Сравните группы по ключу и проверьте их содержимое до удаления или фильтрации. Проверка: известные повторные доставки попали в одну группу, а самостоятельные события — в разные.
4. Закрепите правило в базе или запросе. Для надёжного ключа добавьте уникальность; для очистки истории применяйте явную сортировку в оконной функции. Проверка: повторная загрузка не увеличивает число учитываемых событий, а контрольные суммы отчётов остаются ожидаемыми.
5. Наблюдайте за источником. Считайте частоту конфликтов по ключу и отслеживайте новые группы совпадений. Проверка: рост дублей виден как отдельный сигнал, а не только как внезапное изменение отчёта.

Когда подход ломается

Уникальный ключ работает только при дисциплине источника. Если генератор присваивает новый ID каждой попытке, база честно сохранит оба повтора. Если один ID ошибочно используют для двух разных действий, ограничение, наоборот, отбросит настоящее событие. Поэтому полезно мониторить не только конфликты, но и случаи, когда одинаковый ID приходит с отличающимися пользователем, типом или бизнес-объектом.

Оконная дедупликация может скрыть проблему повторно запускаемой загрузки, если её результат не защищён уникальностью на целевом уровне. А составные ключи особенно хрупки при изменении схемы: добавление нового типа события или изменение точности времени способно изменить группы совпадений. Инвалидация уже построенных витрин — отдельная задача; здесь речь только о выборе и проверке правила дедупликации.

Что делать дальше

Начните не с удаления данных, а с отчёта по кандидатам в дубли: ключ, число строк в группе, время приёма и различия в важных полях. Затем согласуйте с владельцем источника смысл события и только после этого закрепляйте правило. Для PostgreSQL 16 синтаксис уникальных ограничений и оконных функций описан в документации по ограничениям и разделе об оконных функциях.