DISTINCT в SQL: убрать дубликаты и COUNT DISTINCT

DISTINCT убирает из результата повторяющиеся строки. Главное про него помещается в одну фразу: он смотрит на строку целиком, а не на колонку, рядом с которой написан. Отсюда растёт почти вся путаница, включая запросы, которые «почему-то всё равно возвращают дубли». Всё дальше снято на PostgreSQL 16.11.

Данные те же, что в остальных статьях справочника. users:

orders:

payments понадобится в конце. Заказ 11 оплачен двумя частями, у заказа 15 платежа нет вовсе:

SQL / 01koddo

Повторяется выбранная строка — остаётся одна

Что делает DISTINCT

Без него запрос возвращает столько строк, сколько их в таблице:

SELECT city FROM users ORDER BY city;

Четыре клиента, два города, четыре строки в ответе. DISTINCT схлопывает одинаковые:

SELECT DISTINCT city FROM users ORDER BY city;

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

Почему DISTINCT не убрал дубликаты

Потому что он сравнивает строки целиком. Добавь к городу имя, и повторов не останется вовсе:

SELECT DISTINCT city, name FROM users ORDER BY city, name;

Четыре строки, а не две. Пара «Казань, Борис» не равна паре «Казань, Глеб», значит обе уникальны и обе остаются. Именно так выглядит жалоба «поставил DISTINCT, а дубли всё равно есть»: дубли были в одной колонке, а уникальность считалась по всем.

Скобки, на которые часто надеются, не меняют ничего:

SELECT DISTINCT(city), name FROM users ORDER BY city, name;

Ответ тот же, четыре строки. DISTINCT(city) — это не функция от колонки, а то же ключевое слово и лишние скобки вокруг первого элемента списка.

Если нужен один город и вместе с ним какой-нибудь клиент оттуда, задача уже другая. Её решает DISTINCT ON или группировка.

Как взять по одной строке на каждое значение: DISTINCT ON

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

SELECT DISTINCT ON (city) city, id, name
FROM users
ORDER BY city, id;

Два города, и рядом с каждым клиент с наименьшим id. Слово «первая» здесь буквальное: строку выбирает ORDER BY, поэтому без него результат не определён, а с ORDER BY city, id DESC в ответе окажутся Глеб и Вера.

Порядок сортировки обязан начинаться с тех же выражений, иначе запрос не выполнится:

ERROR:  SELECT DISTINCT ON expressions must match initial ORDER BY expressions

Это самая частая ошибка при переходе с GROUP BY на DISTINCT ON: сортировку пишут по нужной колонке, забыв поставить перед ней ключ уникальности. Переносимый аналог того же приёма делают через оконную функцию row_number() с фильтром по первому номеру, и он разобран в статье про оконные функции.

COUNT(DISTINCT) и другие агрегаты

Внутри агрегата DISTINCT работает уже по одной колонке, а не по строке, и это единственное место, где интуиция «отдельно по колонке» верна:

SELECT count(*)                 AS rows_total,
       count(DISTINCT user_id)  AS buyers,
       count(DISTINCT amount)   AS distinct_amounts,
       count(DISTINCT status)   AS statuses
FROM orders;

Шесть заказов сделали три клиента. Различных сумм пять: 1200 встречается дважды. Статуса два, paid и cancelled.

count(DISTINCT ...) — рабочая лошадка отчётов, а вот sum(DISTINCT ...) почти всегда ошибка:

SELECT sum(amount) AS total, sum(DISTINCT amount) AS total_distinct FROM orders;

Выручка потеряла 1200 рублей. Два разных заказа на одинаковую сумму — это два заказа, а sum(DISTINCT) считает такую сумму один раз. Ошибка тихая: число выглядит правдоподобно и просто занижено.

Что DISTINCT делает с пустыми значениями

Все NULL он считает одинаковыми и оставляет ровно один:

SELECT DISTINCT channel FROM order_channel ORDER BY channel NULLS LAST;

Три строки, хотя заказов без канала было два. Поведение противоречит правилу «NULL не равен ничему», и это осознанное исключение: при сравнении пропуски не равны, при устранении дубликатов равны. Полностью правила разобраны в статье про NULL и трёхзначную логику.

Отсюда практическое следствие для отчётов: count(DISTINCT channel) на этих данных вернёт 2, а не 3. Агрегат пропускает NULL, а SELECT DISTINCT его показывает.

DISTINCT или GROUP BY: что выбрать

Пока агрегатов нет, они дают один результат и один план. На маленькой таблице планы совпадают дословно:

EXPLAIN (COSTS OFF) SELECT DISTINCT city FROM users;
EXPLAIN (COSTS OFF) SELECT city FROM users GROUP BY city;

 HashAggregate
   Group Key: city
   ->  Seq Scan on users

Оба запроса собирают хеш-таблицу по city и отдают её ключи. Планировщик не различает эти формы записи, поэтому спор «что быстрее» на пустом месте: быстрее ничего.

Проверка на миллионе строк подтверждает то же самое. Таблица events с 40 различными городами, тёплый кеш, три прогона каждого запроса:

DISTINCT и GROUP BY неразличимы между собой и втрое дороже простого прохода по таблице. Это и есть настоящая цена: устранение дубликатов заставляет базу либо отсортировать данные, либо построить хеш-таблицу, и бесплатным оно не бывает никогда.

Разница между формами появляется, когда нужен агрегат. Посчитать что-то по группе умеет только GROUP BY, к нему же прикручивается HAVING. DISTINCT умеет одно, убирать повторы. Правило выбора простое. Нужен список различных значений, пиши DISTINCT. Нужны счётчики и суммы по каждому значению, пиши GROUP BY и читай про него отдельную статью.

Две ошибки, которые выдаёт DISTINCT

Сортировка по колонке, которой нет в выборке. Запрос «различные города, отсортированные по имени клиента» невыполним по смыслу:

SELECT DISTINCT city FROM users ORDER BY name;

ERROR:  for SELECT DISTINCT, ORDER BY expressions must appear in select list
LINE 1: SELECT DISTINCT city FROM users ORDER BY name;
                                                 ^

База не спорит с синтаксисом, она спорит со смыслом. В строке «Москва» схлопнулись Аня и Вера, имя у этой строки больше не одно, и сортировать по нему нечего. Либо добавь name в выборку и получи четыре строки вместо двух, либо реши, какое из имён группы считать главным, и возьми min(name) с группировкой.

WHERE DISTINCT. Такой конструкции не существует, хотя ищут её часто:

SELECT name FROM users WHERE DISTINCT city = 'Москва';

ERROR:  syntax error at or near "DISTINCT"
LINE 1: SELECT name FROM users WHERE DISTINCT city = 'Москва';
                                     ^

DISTINCT стоит только сразу после SELECT. WHERE отбирает строки по условию и о дубликатах ничего не знает. Как читать такие сообщения и почему ошибку надо искать перед указанным местом, разобрано в статье про синтаксическую ошибку.

DISTINCT после JOIN — обычно симптом, а не лечение

Самый частый способ применить DISTINCT неправильно. Запрос «кто из клиентов что-то оплатил» через два соединения:

SELECT u.name
FROM users u
JOIN orders o ON o.user_id = u.id
JOIN payments p ON p.order_id = o.id
ORDER BY u.name;

Шесть строк на трёх клиентов. Дубли появились не в данных, а в соединении: у Ани два оплаченных заказа, причём один из них оплачен двумя платежами, и каждая пара «заказ, платёж» дала свою строку. Соблазн понятен:

SELECT DISTINCT u.name
FROM users u
JOIN orders o ON o.user_id = u.id
JOIN payments p ON p.order_id = o.id
ORDER BY u.name;

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

SELECT u.name,
       sum(o.amount)          AS naive,
       sum(DISTINCT o.amount) AS with_distinct
FROM users u
JOIN orders o ON o.user_id = u.id
JOIN payments p ON p.order_id = o.id
GROUP BY u.name
ORDER BY u.name;

Правильные суммы: у Ани 3300, у Бориса 2400, у Глеба 500. Обе колонки ошибаются, и каждая на своей строке. naive завысил Аню, потому что её заказ на 2400 попал в выборку дважды. with_distinct занизил Бориса, потому что два его заказа на 1200 схлопнулись в один. Поставить DISTINCT внутрь агрегата и успокоиться нельзя: он лечит следствие и создаёт новую ошибку там, где суммы совпадают по-честному.

Лечится причина. Оплаченные заказы проверяются существованием платежа, а не соединением с ним:

SELECT u.name, sum(o.amount) AS total
FROM users u
JOIN orders o ON o.user_id = u.id
WHERE EXISTS (SELECT 1 FROM payments p WHERE p.order_id = o.id)
GROUP BY u.name
ORDER BY u.name;

Строки не размножаются, DISTINCT не нужен, суммы верные. У Ани в итог не вошёл заказ 15 на 800 рублей: он не оплачен, и платежа у него нет.

Для подсчёта заказов после такого соединения подходит счётчик по уникальному ключу. count(*) даст Ане три, count(DISTINCT o.id) даст честные два, потому что id уникален по определению. Именно поэтому count(DISTINCT id) работает, а sum(DISTINCT amount) нет. Это не единственный устойчивый к дублированию агрегат: повторение тех же значений не меняет min и max, но сумму заказов они не заменяют.

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

На чём ловят на собеседовании

«DISTINCT убирает дубли по первой колонке». По всей строке. SELECT DISTINCT city, name на четырёх клиентах вернёт четыре строки, а не два города.

«DISTINCT и GROUP BY различаются по скорости». На одинаковой задаче без агрегатов планировщик строит один план, а замер на миллионе строк даёт ~32 мс обоим.

«DISTINCT бесплатный». Втрое дороже простого прохода по той же таблице: базе нужна сортировка или хеш-таблица.

«sum(DISTINCT amount) уберёт задвоение после JOIN». Уберёт задвоение и заодно потеряет честные повторы. Два разных заказа на 1200 превратятся в один.

«DISTINCT нужен после UNION». Не нужен, UNION убирает повторы сам. Лишний DISTINCT поверх него добавляет в план вторую агрегацию, которая ничего не находит. Разница между UNION и UNION ALL разобрана в отдельной статье.

«NULL не попадёт в SELECT DISTINCT». Попадёт, ровно одной строкой. А вот count(DISTINCT колонка) его пропустит, и два счётчика разойдутся на единицу.

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

Как посчитать количество уникальных значений

count(DISTINCT колонка). Если уникальность считается по нескольким колонкам, в PostgreSQL пишут count(DISTINCT (a, b)) с кортежем в скобках либо считают строки подзапроса с SELECT DISTINCT a, b.

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

В PostgreSQL это DISTINCT ON (колонка) с обязательным ORDER BY. Переносимый вариант — оконная функция row_number() OVER (PARTITION BY колонка ORDER BY ...) и фильтр WHERE rn = 1. Простой DISTINCT эту задачу не решает, он всегда сравнивает строку целиком.

Влияет ли DISTINCT на порядок строк

Формально нет. Устранение дубликатов через сортировку часто выдаёт упорядоченный результат, и на это иногда полагаются, но гарантии не существует: смена плана на хеш-агрегацию порядок ломает. Нужен порядок, пиши ORDER BY.

Что делать, если DISTINCT работает медленно

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

Где потренироваться

В пути «SQL для аналитиков» на Koddo уникальность разбирается вместе с агрегатами: сначала считаешь покупателей через count(DISTINCT user_id), потом ловишь задвоение после соединения с платежами. Хорошая проверка себя — задача на поиск повторных откликов: там дубли настоящие, из данных, и DISTINCT в ней как раз не поможет, потому что нужно не убрать повторы, а найти их.