DML. DISTINCT и GROUP BY
Дорогу осилит идущийВ рамках сегодняшнего урока мы вернемся к инструментарию для получения данных в SQL и поговорим о возможности получения уникальных записей из таблицы.
По сути, для реализации подобного нам достаточно уже известного нам оператора GROUP BY.
Например, напишем запрос для получения списка уникальных имен среди всех пассажиров:
select first_name from passenger group by first_name;
По сути, нам вовсе не обязательно использовать агрегатную функцию, чтобы “группировать” что-то. В данном случае GROUP BY лишь собирает строки по уникальному значению и возвращает вместо каждой такой группы одну строку.
Так же можно поступить и в случае необходимости выбора уникальных записей по нескольким выражениям (колонкам):
select first_name, last_name from passenger group by first_name, last_name;
В результате этого запроса в результирующей выборке могут быть тезки (по имени), но каждая связка “имя+фамилия” будет уникальной.
GROUP BY хорош, в том числе, и своей гибкостью - блок с возвращаемыми выражениями (SELECT) обособлен от блока с описанием группировки (GROUP BY).
Но необходимость дублировать каждую новую колонку в SELECT и в GROUP BY увеличивает шанс ошибки - всегда можно забыть о необходимости дублирования при редактировании запроса.
Кроме того, многие типовые задачи требуют просто получить список уникальных значений, в таком случае гибкость GROUP BY будет избыточной, а сам запрос может выглядеть раздутым.
Для описанных задач SQL предоставляет модификатор для SELECT - DISTINCT.
Например, так можно описать запрос для получения уникальных имен:
select distinct first_name from passenger;
А так - запрос для получения уникальных связок ”имя+фамилия”:
select distinct first_name, last_name from passenger;
Как видите, синтаксис более лаконичный, чем у GROUP BY и нет необходимости дублировать данные в двух блоках.
Однако не стоит относиться к DISTINCT как к панацее. Он хорош для более-менее простых запросов, как на примерах выше. Однако под использованием этого модификатора зачастую скрывается желание решить проблему просто. А точнее, нежелание разбираться, почему в выборку затесались дубликаты.
Этот момент может стать чуть более актуальным, когда мы познакомимся со связями между таблицами и научимся использовать несколько таблиц в одном запросе - до этого момента наши запросы остаются относительно простыми. Но стоит запомнить уже сейчас:
Возвращаясь к возможностям DISTINCT, стоит разобрать и его близкого родственника - модификатор DISTINCT ON.
Обычный DISTINCT берет уникальные наборы значений для всех указанных в SELECT выражений. Таким образом, он лишает возможности получить таким запросом дополнительные колонки, к которым нет требований по уникальности.
В противовес ему, DISTINCT ON позволяет указать выражение для определения уникальности записи и вернуть все, что угодно, относящееся к этой самой записи. Другие записи с тем же значением выражения будут проигнорированы.
Разберем на примере:
select distinct on(first_name) first_name from passenger;
Данный запрос просто возвращает все уникальные имена из таблицы.
А вот этот:
select distinct on(first_name) first_name, last_name from passenger;
Вернет по одной записи на каждое уникальное имя, а также фамилию пассажира с таким именем. Если записей с таким именем было несколько - будет взята первая попавшаяся, остальные будут проигнорированы.
Эта функциональность может показаться сомнительной. Но стоит добавить сортировку и у нее появится своя сфера применения.
Допустим, нам нужно вернуть всю информацию по самому старому носителю каждого из имен. В общем-то, мы можем написать что-то подобное:
explain analyse select * from passenger p where (birth_date, first_name) = ( select min(p1.birth_date), p1.first_name from passenger p1 where p1.first_name = p.first_name group by p1.first_name );
Тут используется не рассмотренный нами синтаксис для совместного сравнения нескольких выражений. Честно говоря, я сомневаюсь в необходимости разбирать его в рамках курса, хоть он и достаточно понятен интуитивно. Для гугла: postgres tuple comparison.
И это даже будет корректно работать (если нет тезок, родившихся в один день). Но с использованием синтаксиса DISTINCT ON подобный запрос станет намного лаконичнее:
select distinct on(first_name) * from passenger order by first_name, birth_date;
К тому же, он решает (хоть и не эффективно - посредством выбора случайной записи) проблему тезок с одинаковой датой рождения.
В целом, проблема выбора первых N записей из подмножества в выборке запроса периодически появляется в различных ипостасях. И ее решение для N = 1 (как в примере выше) через DISTINCT - достаточно простое и лаконичное. Строго говоря, к N = 1 эта задача решается несложно вне зависимости от выбранного инструмента.
Более глобально (для N >= 1) эта проблема может быть решена различно в разных СУБД. В большинстве случаев, в т.ч. и в PostgreSQL, оно сводится к применению оконных функций. Мы, скорее всего, не затронем их в рамках курса, но вы можете поинтересоваться этой темой самостоятельно. На мой взгляд, данная концепция и ее применение сложноваты для начинающих специалистов, но как минимум стоит попытаться понять, что такое оконные функции и какие у них возможности.
Тем временем, вернемся к DISTINCT ON. Единственное ограничения для связки DISTINCT ON+ORDER BY - выражения в DISTINCT ON должны соответствовать первым выражениям в ORDER BY.
К слову, DISTINCT ON позволяет проверять уникальность по нескольким колонкам (как и обычный DISTINCT):
select distinct on (first_name, last_name) first_name, last_name from passenger;
Если же говорить про выбор между подзапросом и использованием DISTINCT ON (для задач, где оба инструмента позволяют решить задачу) с точки зрения производительности - рекомендую сравнивать в каждом конкретном случае. В примере выше - оба подхода дают примерно идентичный результат. Но в зависимости от дополнительной нагрузки на запрос (фильтров, агрегаций и т.д.) один из подходов может стать или невозможным технически, или нерелевантным в силу слабой производительности.
С теорией на сегодня все!
Переходим к практике:
Задача 1
Верните данные обо всех уникальных датах рождения среди пассажиров мужского пола.
Задача 2
Верните информацию по самому молодому носителю каждой фамилии.
Задача 3
Верните список уникальных имен. Если имя носит и мужчина, и женщина - оно должно быть указано дважды.
Если что-то непонятно или не получается – welcome в комменты к посту или в лс:)
Канал: https://t.me/ViamSupervadetVadens
Мой тг: https://t.me/ironicMotherfucker
Дорогу осилит идущий!