DISTINCT, DISTINCT ON и операции над множествами

DISTINCT убирает дублирующиеся строки — сортировкой или хешем

SELECT DISTINCT схлопывает идентичные выходные строки в одну. «Идентичные» значит, что все выбранные колонки совпадают. Механизм важен: чтобы найти дубликаты, Postgres должен либо отсортировать результат (тогда соседние равные строки очевидны), либо построить хеш каждой строки. Оба — это O(n) работа по всему набору результатов плюс память; если набор больше work_mem, сортировка или хеш сбрасываются на диск (spill), и отсюда берутся обрывы латентности.

-- одна строка на каждую различную пару (country, plan) в users
SELECT DISTINCT country, plan FROM users;

Так что DISTINCT — не бесплатная «уборка», это сортировка-или-хеш по всему, что ты выбрал. Если ты тянешься к нему, чтобы замаскировать дублирующиеся строки, порождённые пропущенным условием join, чини join вместо этого; DISTINCT прячет баг и добавляет цену.

DISTINCT ON: «последняя строка на группу» в Postgres

Специфичное для Postgres расширение решает вопрос, с которым обычный SQL справляется неуклюже: «дай мне по одной целой строке на группу — конкретно последнюю». DISTINCT ON (expr) оставляет первую строку для каждого различного значения expr, а «первая» решается через ORDER BY. Правило, на котором все спотыкаются: ORDER BY должен начинаться с того же выражения(й), что и DISTINCT ON, а затем добавлять тайбрейкер, выбирающий строку-победителя.

-- последний заказ на пользователя: по одной строке, самая свежая по created_at
SELECT DISTINCT ON (user_id) user_id, id, total, created_at
FROM orders
ORDER BY user_id, created_at DESC;   -- user_id первым (обязательно), затем побеждает свежайшая

Это драматически чище переносимых альтернатив (коррелированный подзапрос или оконная функция ROW_NUMBER(), отфильтрованная по = 1, с которой ты встретишься в разделе оконных функций). Для «свежайшей/лучшей строки на ключ» DISTINCT ON — идиоматичный инструмент Postgres.

UNION против UNION ALL: налог на дедупликацию

Операторы множеств складывают два набора результатов вертикально. Ключевое различие:

• UNION объединяет обе стороны и убирает дублирующиеся строки — а значит, сортирует или хеширует весь объединённый результат, ровно как DISTINCT.
• UNION ALL объединяет обе стороны и сохраняет всё, включая дубликаты. Ни шага дедупа, ни сортировки, ни хеша — просто конкатенирует потоки.

Продакшен-правило: по умолчанию UNION ALL; используй UNION, только когда дубликаты реально возможны и ты реально хочешь их убрать. Когда два входа непересекающиеся по построению (разные статусы, разные диапазоны дат, разные таблицы, не способные делить строку), UNION — чистая бесполезная сортировка. Инцидент из вступления был именно этим — непересекающиеся источники, ненужный дедуп 9 миллионов строк.

INTERSECT, EXCEPT и правила колонок

Ещё два оператора множеств, оба тоже дедуплицируют по умолчанию:

• INTERSECT возвращает строки, присутствующие в обеих сторонах.
• EXCEPT возвращает строки первой стороны, которых нет во второй (разность множеств; другие диалекты зовут это MINUS).

Все операторы множеств делят строгие структурные правила: каждая сторона должна иметь одинаковое число колонок, а соответствующие колонки — совместимые типы. Имена выходных колонок берутся из первого запроса; остальные для именования игнорируются. И один нюанс с NULL, отсылающий к прошлому уроку — для дедупликации операторов множеств два NULL считаются равными (так что UNION схлопывает дублирующиеся NULL-строки), что противоположно тому, как = их трактует. Это намеренный особый случай для контекстов группировки/дедупа.

Когда видишь ошибку несовместимости типов в операторе множеств, проверь число колонок и добавь CAST к несовпадающей колонке в одной из веток. Если поведение NULL в результате UNION удивляет — вспомни: в контекстах дедупа два NULL считаются равными; то же правило работает в GROUP BY и DISTINCT.