DISTINCT — возвращение к set-семантике

В первом модуле мы говорили: реляция — это множество, но PostgreSQL по умолчанию работает с мультимножествами (bags). DISTINCT — это явный способ сказать «убери дубли, я хочу обратно множество».

Формально, SELECT DISTINCT a, b, c FROM t — это композиция π (проекция) и применения set-семантики к результату. На уровне SQL это означает: «возьми все уникальные комбинации значений (a, b, c)».

Главный момент: DISTINCT применяется ко всему набору колонок в SELECT сразу, а не к отдельным колонкам. Запись SELECT DISTINCT a, DISTINCT b — синтаксическая ошибка.

В set-семантике NULL ведёт себя как равный самому себе (исключение из общей трёхзначной логики). Это значит, что две строки с (RU, NULL) будут считаться дублями для DISTINCT.

Сколько стоит DISTINCT

Чтобы устранить дубли, СУБД должна либо:

1. Отсортировать все строки и удалить соседние повторы — O(n log n) времени, O(n) памяти.
2. Построить hash-таблицу по всем колонкам — O(n) времени, O(n_unique) памяти, но требует, чтобы все unique-комбинации поместились в work_mem.

PostgreSQL обычно выбирает hash для маленьких результатов и sort+unique для больших. На таблице в миллион строк DISTINCT стоит секунды; на сотню миллионов — может быть проблемой.

Часто DISTINCT появляется как «защита» от непонятно откуда взявшихся дублей в результате JOIN. Это анти-паттерн: правильнее разобраться, почему дубли появились, чем заклеивать симптом дорогим DISTINCT.

DISTINCT ON — изобретение PostgreSQL

Стандарт SQL предоставляет DISTINCT только в форме «уникальные строки целиком». А что, если нужно «один представитель из каждой группы»? Например: для каждого клиента — его самый последний заказ.

В стандарте SQL это решается через window functions с ROW_NUMBER() OVER (PARTITION BY ...). Но PostgreSQL предлагает более компактный синтаксис — DISTINCT ON (cols):

SELECT DISTINCT ON (customer_id) customer_id, placed_at, status
FROM orders
ORDER BY customer_id, placed_at DESC;

Читается так: «для каждой уникальной комбинации значений в DISTINCT ON (...) верни первую строку в порядке ORDER BY».

Без DISTINCT ON тот же результат получался бы через подзапрос с ROW_NUMBER():

SELECT customer_id, placed_at, status FROM (
  SELECT *, ROW_NUMBER() OVER (PARTITION BY customer_id ORDER BY placed_at DESC) AS rn
  FROM orders
) sub
WHERE rn = 1;

Версия с DISTINCT ON короче и часто быстрее, потому что PostgreSQL оптимизирует её через прямой проход с пропуском дублей.

Подводные камни DISTINCT ON

ORDER BY определяет «победителя»

Правило: первые колонки в ORDER BY должны совпадать с колонками в DISTINCT ON. Иначе результат непредсказуем.

Здесь видно: первая колонка ORDER BY (customer_id) совпадает с DISTINCT ON. Вторая (placed_at ASC или DESC) выбирает, какой именно заказ из группы будет первым.

Если ORDER BY не совпадает

Если первая колонка ORDER BY не совпадает с DISTINCT ON, PostgreSQL вернёт результат, но он будет детерминистски, но непредсказуемо зависеть от того, в каком порядке оптимизатор будет обходить строки. Это плохо.

PostgreSQL не требует, чтобы префикс ORDER BY совпадал с DISTINCT ON синтаксически, — но логически без этого результат становится произвольным. Запомни: всегда явно прописывай ORDER BY для DISTINCT ON так, чтобы первые колонки совпадали.

NULL в DISTINCT ON

NULL в колонке DISTINCT ON ведёт себя как обычное значение (как и в DISTINCT). Все строки с NULL в группирующей колонке схлопнутся в одну группу.

Когда DISTINCT ON, а когда window function

Правило: для top-1 — DISTINCT ON. Для top-N или ранкинга — window function.

Углубление: DISTINCT ON в JOIN

DISTINCT ON особенно полезен в подзапросах перед JOIN. Например, нужно для каждого клиента приджойнить его последний заказ:

Альтернатива через correlated subquery или LATERAL JOIN была бы либо медленнее, либо длиннее.

Чек-лист

• DISTINCT a, b, c — уникальные комбинации всего набора колонок.
• DISTINCT стоит O(n log n) или O(n) памяти — на больших данных недёшево.
• DISTINCT ON (cols) — Postgres-расширение: «по одной строке для каждой уникальной комбинации cols».
• Первые колонки ORDER BY обязаны совпадать с DISTINCT ON.
• Следующие колонки ORDER BY выбирают, какая строка станет «представителем» группы.
• В set-операциях (включая DISTINCT) NULL равен самому себе — исключение из трёхзначной логики.
• Для top-N (N > 1) или ранкинга — используй window functions, не пытайся натянуть DISTINCT ON.