Дубликаты в SQL: как найти и удалить дубли, не потеряв действия

Коротко

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

• Ключ дедупликации — это event_id или осознанно собранный составной ключ, а не весь набор колонок.
• Повтор доставки чинится в данных, повторное действие пользователя — в определении метрики.
• Канонической строкой обычно делают последнюю доставленную версию события.
• После очистки сверяйте три числа: строки, уникальных пользователей и денежную сумму.
• Если повторы системные, настоящее решение лежит в ingestion, а не в отчёте.

Откуда вообще берутся дубли

Почти все аналитические конвейеры доставляют события по принципу at-least-once: лучше прислать запись дважды, чем потерять её. Мобильный SDK складывает события в локальную очередь и отправляет пачкой; если подтверждение не пришло вовремя, пачка уходит снова — а сервер принял её и в первый раз.

Второй источник — перезапуск загрузки. ETL упал на середине дня, задачу запустили заново, часть партиции записалась повторно. Такие дубли приходят блоками: видно не пары, а ровный всплеск за конкретные часы.

Третий источник вообще не технический. Пользователь нажал «В корзину», интерфейс не отреагировал, он нажал ещё раз. Приехали два add_to_cart, потому что человек дважды кликнул по кнопке. Это факт поведения, и удалять его нельзя, пока не решено, что мы измеряем: нажатия или состояние корзины.

Почему DISTINCT не решает задачу — причём в обе стороны

SELECT DISTINCT * кажется очевидным ходом и почти никогда не работает. Сырая строка события несёт технические колонки: время загрузки, идентификатор батча, иногда received_at от прокси. У повторно доставленной записи эти значения отличаются, строки не идентичны, и DISTINCT спокойно оставляет обе.

Ошибка работает и в обратную сторону. Примените DISTINCT к user_id, названию события и времени с точностью до секунды — и под одну строку схлопнутся два настоящих клика, сделанных подряд. Запрос выглядит аккуратным, а из данных исчезло то поведение, которое вы искали. Результат «по всем колонкам» зависит от того, какие поля случайно попали в SELECT.

В учебной таблице events песочницы дублей нет, поэтому сырой поток собран прямо в запросе: CTE raw_events добавляет к событиям повторную доставку каждого шестого — тот же event_id, другое время загрузки и номер батча. Эта обвязка — учебная вставка, одинаковая в трёх запросах по событиям; на своих данных вместо неё стоит имя сырой таблицы. За 1 июня в потоке 81 строка на 70 событий: 11 лишних, 13,58%. SELECT DISTINCT * по тем же строкам возвращает все 81.

WITH raw_events AS (
  SELECT e.*, event_time + INTERVAL '2 seconds' AS loaded_at, 1 AS ingest_batch_id
  FROM events AS e
  UNION ALL  -- каждое шестое событие доставлено повторно, через четыре минуты
  SELECT e.*, event_time + INTERVAL '4 minutes', 2
  FROM events AS e
  WHERE event_id % 6 = 0
)
SELECT
  count(*)                                     AS raw_rows,
  count(DISTINCT event_id)                     AS distinct_events,
  count(*) - count(DISTINCT event_id)          AS extra_rows,
  count(*) FILTER (WHERE event_id IS NULL)     AS without_event_id,
  round(100.0 * (count(*) - count(DISTINCT event_id)) / count(*), 2) AS dup_share_pct
FROM raw_events
WHERE event_time >= DATE '2026-06-01'
  AND event_time <  DATE '2026-06-02';

Ключ события: что делает две записи одной

Хороший ключ источник генерирует до отправки и не меняет при повторе. В аналитических SDK это event_id или insert_id — UUID, созданный на клиенте в момент события. При повторной отправке он остаётся прежним и прямо отвечает на вопрос «это та же запись?».

Когда своего идентификатора нет, ключ собирают из бизнес-смысла: для покупки это order_id, для платежа — payment_id, для входа — user_id плюс дата. Технически он менее надёжен, зато говорит на языке предметной области: две строки с одним order_id — один заказ, сколько бы раз событие о нём ни приехало.

Опасный вариант — ключ из user_id, названия события и времени. Он ловит повторную доставку, но заодно сливает быстрые повторы, сделанные человеком. Брать его можно только вместе с окном и только там, где повтор через две секунды физически бессмыслен.

Повтор доставки и повторное действие — разные диагнозы

Это главный водораздел темы, и он не технический. Повтор доставки — дефект трубы: бизнес-факт случился один раз, записей о нём несколько. Повторное действие — факт поведения: записей ровно столько, сколько было действий.

Различить помогают два признака. Первый — клиентский идентификатор: у повторной доставки event_id один, у двух разных действий он разный. Второй — распределение интервалов внутри пар: сбой доставки даёт нулевую разницу или ровный лаг в секундах, одинаковый у всех пользователей, а поведение даёт размазанное распределение от секунд до минут.

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

Окно дедупликации: у повтора есть срок

Когда ключ — клиентский event_id, окно формально не нужно: один UUID всегда означает один факт. На практике оно всё равно появляется, потому что запрос работает по партиции, а не по всей истории. Событие, доставленное с опозданием на сутки, попадёт в другое окно и уцелеет как «новое». Поэтому окно очистки берут шире периода отчёта: при редкой задержке до суток чистить нужно трёхдневное окно и перезаписывать эти дни целиком. Иначе дубли оседают на стыках партиций.

Для составного ключа окно несёт другой смысл: это порог, до которого повтор считается техническим. Пять секунд для page_view — разумно. Пять секунд для purchase — нет: два заказа подряд бывают, и удаление второго стоит денег в отчёте. Порог назначается для каждого события отдельно и живёт рядом с его определением, а не в голове автора запроса.

ROW_NUMBER: базовый рецепт

ROW_NUMBER() нумерует строки внутри группы — ровно то, что требуется. Группа задаётся через PARTITION BY по ключу события, порядок внутри группы — через ORDER BY по «свежести», а дальше остаётся оставить первую строку.

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

За 1 июня запрос возвращает 70 строк, по одной на событие. У 11 из них ingest_batch_id = 2: канонической стала повторная доставка.

WITH raw_events AS (
  SELECT e.*, event_time + INTERVAL '2 seconds' AS loaded_at, 1 AS ingest_batch_id
  FROM events AS e
  UNION ALL  -- каждое шестое событие доставлено повторно, через четыре минуты
  SELECT e.*, event_time + INTERVAL '4 minutes', 2
  FROM events AS e
  WHERE event_id % 6 = 0
),
ranked AS (
  SELECT
    e.*,
    ROW_NUMBER() OVER (
      PARTITION BY e.event_id
      ORDER BY e.loaded_at DESC NULLS LAST, e.ingest_batch_id DESC
    ) AS rn
  FROM raw_events AS e
  WHERE e.event_time >= DATE '2026-06-01'
    AND e.event_time <  DATE '2026-06-02'
)
SELECT *
FROM ranked
WHERE rn = 1
ORDER BY event_id;

• Фильтр по периоду стоит внутри CTE: снаружи он заставит базу построить окно по всей таблице.
• У оставленной строки rn всегда 1; число доставок события даёт count(*) OVER (PARTITION BY event_id) в том же CTE.
• rn = 1 оставляет одну строку на ключ; rn > 1 даёт готовый список отброшенного для разбора.

Сортировка внутри окна должна быть однозначной

ORDER BY loaded_at DESC работает, пока время загрузки в паре дублей различается. Когда оно совпадает до микросекунды — а при перезаливе партиции это норма, — база вправе выбрать любую строку. Запрос перестаёт быть воспроизводимым: вчера он вернул одну версию, сегодня другую, и расхождение в отчёте нечем объяснить.

Лечится добавлением tie-break: второго поля с гарантированно разными значениями — идентификатора батча, физического адреса строки, surrogate-ключа. Годится любое, лишь бы порядок стал детерминированным. Без него нельзя сравнить два запуска между собой, а значит нельзя и поймать настоящую ошибку.

Тот же аргумент касается NULL. Пустое значение в loaded_at при сортировке по убыванию в PostgreSQL окажется первым, и именно такая запись станет канонической. Либо отфильтруйте эти строки, либо допишите NULLS LAST, как в запросе выше.

Варианты без ROW_NUMBER

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

Когда event_id вообще нет

В старых потоках клиентского идентификатора часто нет: есть user_id, название события, время и JSON с параметрами. Тогда ключ собирают из пользователя, события и хеша значимых полей payload, а разделителем служит время: повтор — это запись, пришедшая в пределах окна после предыдущей такой же. В учебной таблице payload нет, поэтому ключ в запросе ниже — пользователь и событие.

LAG() отдаёт предыдущее время внутри группы, дальше остаётся сравнить разницу с порогом. Первая запись в группе получает prev_time IS NULL и должна проходить всегда: если забыть это условие, из результата исчезнут все одиночные события, а потеря будет выглядеть как честная очистка.

За 1 июня запрос оставляет те же 70 событий, что и очистка по event_id: копия приходит с тем же временем события, а два одинаковых события одного пользователя в этой базе разделяют минимум 20 часов. Без условия prev_time IS NULL он не возвращает ни одной строки: в пределах дня каждое событие — первое в своей группе.

WITH raw_events AS (
  SELECT e.*, event_time + INTERVAL '2 seconds' AS loaded_at, 1 AS ingest_batch_id
  FROM events AS e
  UNION ALL  -- каждое шестое событие доставлено повторно, через четыре минуты
  SELECT e.*, event_time + INTERVAL '4 minutes', 2
  FROM events AS e
  WHERE event_id % 6 = 0
),
marked AS (
  SELECT
    user_id,
    event_name,
    event_time,
    LAG(event_time) OVER (
      PARTITION BY user_id, event_name
      ORDER BY event_time
    ) AS prev_time
  FROM raw_events
  WHERE event_time >= DATE '2026-06-01'
    AND event_time <  DATE '2026-06-02'
)
SELECT user_id, event_name, event_time
FROM marked
WHERE prev_time IS NULL
   OR event_time - prev_time > INTERVAL '5 seconds'
ORDER BY event_time, user_id, event_name;

• Подход не различает сбой доставки и двойной клик — он применяет договорённость о пороге.
• Для денежных событий такой ключ не годится: там нужен настоящий бизнес-идентификатор.
• Хеш payload считают по значимым полям, без технических, иначе повтор перестанет совпадать сам с собой.

Контрольный набор: восемь строк, которые можно пересчитать руками

Прежде чем запускать очистку на миллионах строк, соберите маленький набор с заранее известным ответом. Восемь строк, двое пользователей, одно утро, все интересные случаи внутри.

Что должно получиться и почему именно так

Правильный результат — шесть строк. Пара ev-1 схлопывается в одну: повторная доставка с опозданием на четыре минуты, время события одинаковое, время загрузки разное. Пара ev-5 схлопывается тоже, но там совпадает и loaded_at, так что без tie-break выбор произволен. На реальных данных такие пары почти всегда различаются хотя бы одной колонкой.

А ev-3 и ev-4 остаются обе. Один пользователь, одно название события, шесть минут разницы — и два разных заказа. Ключ user_id + event_name убил бы вторую покупку и занизил выручку; ключ order_id оставляет обе. Этот случай стоит держать в контрольном наборе постоянно: он ловит самую дорогую ошибку темы.

Как убедиться, что реальные действия не потерялись

Очистка удалась не тогда, когда строк стало меньше, а когда понятно, какие именно строки ушли. Минимальный контроль — три числа до и после: количество строк, количество уникальных пользователей и денежная сумма.

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

Деньги в учебной базе лежат в payments, поэтому сверка показана на ней: каждый шестой платёж доставлен дважды. До очистки — 1 459 строк и сумма $35 621, после — 1 251 строка и $30 639, как в исходной таблице: ушло 208 копий на $4 982. Пользователей 912 и платежей 1 251 в обоих слоях.

Отклонения показывают, где искать ошибку. Уникальных платежей стало меньше, а сумма ушла ниже источника — ключ склеил разные заказы: с user_id вместо payment_id в PARTITION BY от 1 251 платежа остаётся 912. Упало число пользователей — ключ без user_id склеил события разных людей либо в очистке через LAG потеряно условие prev_time IS NULL: на всём учебном потоке без него остаётся 4 433 пользователя из 4 613 — уходят все, у кого ни одно событие не повторилось. Строк почти столько же — дублей по этому ключу нет, и причину надо искать в соединении, которое размножает строки при сборке витрины.

WITH raw_payments AS (
  SELECT p.*, 1 AS ingest_batch_id
  FROM payments AS p
  UNION ALL  -- каждый шестой платёж доставлен повторно
  SELECT p.*, 2
  FROM payments AS p
  WHERE payment_id % 6 = 0
),
clean_payments AS (
  SELECT *
  FROM (
    SELECT r.*,
      ROW_NUMBER() OVER (PARTITION BY payment_id ORDER BY ingest_batch_id DESC) AS rn
    FROM raw_payments AS r
  ) AS ranked
  WHERE rn = 1
)
SELECT 'raw' AS layer,
  count(*)                   AS row_count,
  count(DISTINCT user_id)    AS users,
  count(DISTINCT payment_id) AS payments,
  sum(amount)                AS revenue
FROM raw_payments
UNION ALL
SELECT 'clean', count(*), count(DISTINCT user_id), count(DISTINCT payment_id), sum(amount)
FROM clean_payments
ORDER BY layer DESC;

Чинить в ingestion, а не в отчёте

Дедупликация внутри запроса — компенсация: её приходится повторять в каждой витрине, и рано или поздно кто-то посчитает метрику прямо по сырой таблице и получит другой ответ.

Механизм называется idempotency key: приёмник помнит идентификаторы уже принятых записей и отбрасывает повторы на входе. Для платёжных API это стандарт — клиент передаёт ключ в заголовке, сервер гарантирует, что операция применится один раз, сколько бы ретраев ни пришло. В аналитике ту же роль играет insert_id, который трекер отправляет вместе с событием.

Если чинить источник нельзя — нет доступа к SDK, поток чужой, данные исторические, — дедупликация переносится на витрину: одна таблица clean_events, один документированный ключ, все отчёты читают только её. Чего точно не стоит делать, так это держать свою логику очистки в каждом дашборде: через месяц команды будут защищать разные числа, и спор окажется о том, чей DISTINCT правильнее.

Ошибки, которые выглядят убедительно

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

• Дедуплицировать purchase по user_id и дате: два заказа за день становятся одним, выручка падает, а падение объясняют сезонностью.
• Чистить окно ровно по периоду отчёта: поздние события приезжают в следующую партицию и остаются дублями на стыке.
• Забыть NULLS LAST и сделать канонической строку с пустым временем загрузки.
• Дедуплицировать после JOIN: размноженные строки получат разные значения из правой таблицы и перестанут выглядеть дублями.
• Считать задачу закрытой, потому что DAU вернулся к прежним значениям. Совпадение с ожиданием — это не проверка.

Что записать рядом с определением события

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

Когда через полгода менеджер спросит, почему конверсия в корзину в дашборде ниже, чем в продуктовой аналитике, ответом будет строка контракта, а не расследование с нуля на полдня.

Мини-практика

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

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

Частые вопросы

Что дальше

Очищенный поток событий — основание для всего остального: DAU через count(distinct user_id), воронки, когорты, retention. Пока в таблице живут дубли, каждая из этих метрик завышена на свою величину, и расхождение между ними невозможно объяснить продуктовыми причинами. Поэтому дедупликацию разумно закрыть до появления первого дашборда, а не после первого спора о числах.