Дедупликация, последняя версия записи и топ-N

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

Дедупликация: одна строка на ключ

В events 27 304 строки, а уникальных event_id 26 732: 572 события доставлены дважды с разным received_at. Это нормальное поведение очередей сообщений, которые гарантируют доставку «хотя бы раз». Убирать дубли обязана загрузка.

DISTINCT здесь не поможет: строки различаются в received_at, поэтому для базы они разные. Нужно выбрать одну строку на event_id по правилу, например самую раннюю доставку:

SELECT event_id, user_id, device_id, event_type, page, event_ts, received_at
FROM (
  SELECT *,
         row_number() OVER (PARTITION BY event_id ORDER BY received_at) AS rn
  FROM events
) e
WHERE rn = 1;   -- 26 732 строки

Правило выбора важно. ORDER BY received_at оставляет первую доставку, ORDER BY received_at DESC последнюю. Если порядок неоднозначен, у двух дублей одинаковый received_at, результат будет случайным. Добавляйте в ORDER BY колонки до полной однозначности или принимайте, что при полном совпадении строк любая подойдёт.

PostgreSQL даёт короткую запись того же самого:

SELECT DISTINCT ON (event_id) *
FROM events
ORDER BY event_id, received_at;

DISTINCT ON оставляет первую строку каждой группы по ORDER BY, который обязан начинаться с тех же колонок. Это диалект PostgreSQL и Greenplum; в других базах используйте row_number. ClickHouse предлагает LIMIT 1 BY event_id и движки таблиц, которые дедуплицируют при слиянии.

Последняя версия записи

В stg_orders лежат все версии каждого заказа, как их привозила ежедневная загрузка, плюс 254 полных дубля. Витрине нужна одна строка на заказ: последняя по updated_at.

Первое, что приходит в голову:

SELECT order_id, max(updated_at) AS updated_at, max(status) AS status
FROM stg_orders
GROUP BY order_id;

Это неверно, и ошибка тихая. max(status) выбирает максимальную строку по алфавиту, а не статус последней версии: у 5350 заказов из 8000 статус в таком результате не совпадает с настоящим. Агрегаты считаются по колонкам независимо друг от друга и не «помнят», из какой строки взято значение.

Правильно то же, что при дедупликации: выбрать всю строку целиком:

SELECT DISTINCT ON (order_id) order_id, customer_id, status, total_amount, updated_at
FROM stg_orders
ORDER BY order_id, updated_at DESC, loaded_at DESC;

Все 8000 строк совпадают с таблицей orders. Обратите внимание на второй ключ сортировки: у полных дублей updated_at одинаковый, и loaded_at DESC делает выбор однозначным.

Тот же паттерн в других задачах: последний заказ каждого клиента, актуальная цена товара, текущее состояние сущности из журнала изменений. Если для каждой строки одной таблицы нужна одна связанная строка из другой, вариант через LATERAL из урока про диапазоны часто быстрее: подзапрос с ORDER BY ... LIMIT 1 использует индекс, а row_number требует прохода по всей таблице.

Топ-N в группе

Три самых продаваемых товара в каждой категории по количеству:

WITH sales AS (
  SELECT p.category, p.title, sum(i.quantity) AS qty
  FROM order_items i
  JOIN products p USING (product_id)
  GROUP BY 1, 2
),
ranked AS (
  SELECT *, rank() OVER (PARTITION BY category ORDER BY qty DESC) AS place
  FROM sales
)
SELECT category, title, qty, place
FROM ranked
WHERE place <= 3
ORDER BY category, place;

Здесь выбран rank: если два товара продали поровну, оба попадут в тройку, и это честнее, чем случайно выбросить один. Если нужно ровно три строки на категорию для фиксированного макета, берите row_number с однозначной сортировкой, например ORDER BY qty DESC, title.

Общая структура паттерна: агрегат, затем ранжирование в CTE, затем фильтр по рангу снаружи. Фильтр снаружи обязателен, потому что окно нельзя использовать в WHERE того же запроса.

Полные дубли строк

Отдельный случай: строки совпадают целиком, как 254 повторных загрузки в stg_orders. Их находит GROUP BY по всем колонкам с HAVING count(*) > 1, а убирает SELECT DISTINCT. Но лучше не допускать их: в блоке про идемпотентность разберём, как повторный запуск загрузки не должен создавать дубли вовсе.

Практика