SQL Studio / Разборы SQL / События и доставки
В журнале пришло 8 строк, но событий оказалось 5. Одна покупка доставлена трижды, ещё одно событие повторилось два раза. Опасный способ — оставить по одной записи на пару (user_id, event_name): он выбросит вторую настоящую покупку того же человека. Для удаления повторных доставок в выборке задайте бизнес-ключ события, отсортируйте доставки внутри него по received_at, row_id и оставьте ROW_NUMBER() = 1. Сами сырые строки лучше сохранять для аудита.
Два времени и два разных вида идентификатора
В событиях аналитического продукта есть event_at — когда действие произошло у пользователя, и received_at — когда запись пришла в систему. row_id уникален для каждой попытки доставки; event_key должен одинаково обозначать одно логическое событие у данного пользователя. У evt-A три разных received_at, но одно время исходного события. У evt-B тот же пользователь и тот же тип покупки, однако это другая покупка. Два NULL-ключа нельзя автоматически признать одной доставкой.
| row_id | event_key | user_id | получено |
|---|---|---|---|
| 1 | evt-A | 7 | 10:00:01 |
| 2 | evt-A | 7 | 10:00:05 |
| 3 | evt-A | 7 | 10:00:09 |
| 4 | evt-B | 7 | 10:03:01 |
| 5 | NULL | 8 | 10:04:01 |
| 6 | NULL | 8 | 10:04:03 |
| 7 | evt-C | 8 | 10:05:01 |
| 8 | evt-C | 8 | 10:05:01 |
Ошибка: дедупликация по пользователю и типу события
Выглядит похоже на правильный код: используется оконная функция и детерминированный порядок. Но ключ группировки слишком грубый: две настоящие покупки одного пользователя сольются в одну, а два самостоятельных просмотра — тоже.
WITH raw_events(row_id, event_key, user_id, event_name, event_at, received_at) AS (
VALUES (1,'evt-A',7,'purchase','2026-11-01 10:00:00','2026-11-01 10:00:01'),
(2,'evt-A',7,'purchase','2026-11-01 10:00:00','2026-11-01 10:00:05'),
(3,'evt-A',7,'purchase','2026-11-01 10:00:00','2026-11-01 10:00:09'),
(4,'evt-B',7,'purchase','2026-11-01 10:03:00','2026-11-01 10:03:01'),
(5,NULL,8,'page_view','2026-11-01 10:04:00','2026-11-01 10:04:01'),
(6,NULL,8,'page_view','2026-11-01 10:04:02','2026-11-01 10:04:03'),
(7,'evt-C',8,'page_view','2026-11-01 10:05:00','2026-11-01 10:05:01'),
(8,'evt-C',8,'page_view','2026-11-01 10:05:00','2026-11-01 10:05:01')
),
ranked AS (
SELECT row_id, user_id, event_name,
ROW_NUMBER() OVER (
PARTITION BY user_id, event_name
ORDER BY received_at, row_id
) AS rn
FROM raw_events
)
SELECT row_id, user_id, event_name
FROM ranked WHERE rn=1 ORDER BY row_id;
| row_id | user_id | event_name |
|---|---|---|
| 1 | 7 | purchase |
| 5 | 8 | page_view |
Осталось 2 строки вместо 5. Здесь ошибка не техническая, а смысловая: равенство типа события не означает равенство экземпляра события. Даже если оба заказа совершены за одну минуту, разные стабильные event_key позволяют сохранить оба.
Сначала пронумеровать доставки по настоящему ключу
В примере область уникальности ключа — (user_id, event_key). Для event_key IS NULL каждую запись рассматриваем как отдельный, пока не доказанный дубль: добавляем row_id в отдельное выражение раздела. Без него все строки с NULL-ключом одного пользователя попали бы в одну группу. При равном received_at выбирает меньший row_id; глобальный ORDER BY управляет показом, а не выбором победителя.
WITH raw_events(row_id, event_key, user_id, event_name, event_at, received_at) AS (
VALUES (1,'evt-A',7,'purchase','2026-11-01 10:00:00','2026-11-01 10:00:01'),
(2,'evt-A',7,'purchase','2026-11-01 10:00:00','2026-11-01 10:00:05'),
(3,'evt-A',7,'purchase','2026-11-01 10:00:00','2026-11-01 10:00:09'),
(4,'evt-B',7,'purchase','2026-11-01 10:03:00','2026-11-01 10:03:01'),
(5,NULL,8,'page_view','2026-11-01 10:04:00','2026-11-01 10:04:01'),
(6,NULL,8,'page_view','2026-11-01 10:04:02','2026-11-01 10:04:03'),
(7,'evt-C',8,'page_view','2026-11-01 10:05:00','2026-11-01 10:05:01'),
(8,'evt-C',8,'page_view','2026-11-01 10:05:00','2026-11-01 10:05:01')
)
SELECT row_id, event_key, user_id, received_at,
ROW_NUMBER() OVER (
PARTITION BY user_id, event_key,
CASE WHEN event_key IS NULL THEN row_id END
ORDER BY received_at, row_id
) AS rn
FROM raw_events ORDER BY row_id;
| row_id | event_key | rn |
|---|---|---|
| 1 | evt-A | 1 |
| 2 | evt-A | 2 |
| 3 | evt-A | 3 |
| 4 | evt-B | 1 |
| 5 | NULL | 1 |
| 6 | NULL | 1 |
| 7 | evt-C | 1 |
| 8 | evt-C | 2 |
Ключ evt-C специально доставлен дважды в одну секунду, поэтому только вторичный порядок по row_id делает решение устойчивым. Если идентификатор событий уникален глобально, user_id можно исключить из PARTITION BY; но это должно быть известно из контракта источника, а не угадано аналитиком.
Выбрать первую доставку, не потеряв другие события
Оставляем только ранги 1. Важное отличие от SELECT DISTINCT: у дублей отличаются технический ID и время доставки, поэтому полные строки не равны. ROW_NUMBER выбирает целую запись по явному правилу.
WITH raw_events(row_id, event_key, user_id, event_name, event_at, received_at) AS (
VALUES (1,'evt-A',7,'purchase','2026-11-01 10:00:00','2026-11-01 10:00:01'),
(2,'evt-A',7,'purchase','2026-11-01 10:00:00','2026-11-01 10:00:05'),
(3,'evt-A',7,'purchase','2026-11-01 10:00:00','2026-11-01 10:00:09'),
(4,'evt-B',7,'purchase','2026-11-01 10:03:00','2026-11-01 10:03:01'),
(5,NULL,8,'page_view','2026-11-01 10:04:00','2026-11-01 10:04:01'),
(6,NULL,8,'page_view','2026-11-01 10:04:02','2026-11-01 10:04:03'),
(7,'evt-C',8,'page_view','2026-11-01 10:05:00','2026-11-01 10:05:01'),
(8,'evt-C',8,'page_view','2026-11-01 10:05:00','2026-11-01 10:05:01')
),
ranked AS (
SELECT row_id, event_key, user_id, event_name, event_at,
ROW_NUMBER() OVER (
PARTITION BY user_id, event_key,
CASE WHEN event_key IS NULL THEN row_id END
ORDER BY received_at, row_id
) AS rn
FROM raw_events
)
SELECT row_id, event_key, event_name
FROM ranked WHERE rn=1 ORDER BY row_id;
| row_id | event_key | event_name |
|---|---|---|
| 1 | evt-A | purchase |
| 4 | evt-B | purchase |
| 5 | NULL | page_view |
| 6 | NULL | page_view |
| 7 | evt-C | page_view |
Событие evt-B сохранилось, хотя у него тот же пользователь и название purchase, что у evt-A. У двух NULL-ключей сохранились обе записи: без бизнес-идентификатора мы не знаем, повторная ли это доставка или два отдельных действия.
Контроль полноты: сколько пришло и сколько отсеяли
Перед сводным отчётом полезно явно посчитать физические строки и отброшенные доставки. Сумма двух последних счётчиков должна равняться количеству входных строк:
WITH raw_events(row_id, event_key, user_id, event_name, event_at, received_at) AS (
VALUES (1,'evt-A',7,'purchase','2026-11-01 10:00:00','2026-11-01 10:00:01'),
(2,'evt-A',7,'purchase','2026-11-01 10:00:00','2026-11-01 10:00:05'),
(3,'evt-A',7,'purchase','2026-11-01 10:00:00','2026-11-01 10:00:09'),
(4,'evt-B',7,'purchase','2026-11-01 10:03:00','2026-11-01 10:03:01'),
(5,NULL,8,'page_view','2026-11-01 10:04:00','2026-11-01 10:04:01'),
(6,NULL,8,'page_view','2026-11-01 10:04:02','2026-11-01 10:04:03'),
(7,'evt-C',8,'page_view','2026-11-01 10:05:00','2026-11-01 10:05:01'),
(8,'evt-C',8,'page_view','2026-11-01 10:05:00','2026-11-01 10:05:01')
),
ranked AS (
SELECT event_key, row_id,
ROW_NUMBER() OVER (
PARTITION BY user_id, event_key,
CASE WHEN event_key IS NULL THEN row_id END
ORDER BY received_at, row_id
) AS rn
FROM raw_events
)
SELECT COUNT(*) AS arrived_rows,
SUM(CASE WHEN rn=1 THEN 1 ELSE 0 END) AS logical_events,
SUM(CASE WHEN rn>1 THEN 1 ELSE 0 END) AS duplicate_deliveries
FROM ranked;
| arrived_rows | logical_events | duplicate_deliveries |
|---|---|---|
| 8 | 5 | 3 |
Проверка 5 + 3 = 8 не доказывает правильность бизнес-ключа, но страхует от ошибки в самой механике фильтра. Дубли по evt-A дали два отсева, по evt-C — один. При агрегации по часам сначала дедуплицируйте во всём нужном горизонте, затем группируйте по времени исходного события: иначе повторные доставки в соседние часы снова могут попасть в разные группы.
Упражнение: изменить одно правило
Добавьте строку (9,'evt-D',7,'purchase',...) с новым ключом, даже если время точно совпадает с evt-B. Сколько логических событий останется? Затем измените ключ на evt-B и проверьте, как изменился счётчик повторных доставок. Почему row_id нельзя использовать как единственный признак бизнес-события?
Описание оконной функции: SQLite — ROW_NUMBER(). Примеры предназначены для SQLite WASM в SQL Studio; отдельный Stepik-курс использует MySQL 8, где оконный синтаксис похож, но весь запрос стоит проверить в своей СУБД.
Для закрепления оконных функций в MySQL 8 можно пройти отдельный курс SQL на Stepik. Самостоятельный разбор выше целиком доступен бесплатно.