Уникальные строки по одному столбцу с сортировкой по другому в PostgreSQL
Быстрый ответ
В PostgreSQL можно достичь фильтрации уникальных строк, используя подзапросы с применением DISTINCT ON
. Для этого потребуется определить особую сортировку в основном запросе.
Вот пример SQL-запроса:
SELECT * FROM (
SELECT DISTINCT ON (column1) column1, column2
FROM your_table
ORDER BY column1, column2 DESC
) sub
ORDER BY column2;
Ключевые моменты:
DISTINCT ON (column1)
исключает дубликаты на основнииcolumn1
.- Этап внутренней сортировки
ORDER BY column1, column2 DESC
определяет, какая запись будет репрезентировать группу поcolumn1
. - Этап внешней сортировки
ORDER BY column2
устанавливает итоговый порядок представления записей.
А теперь давайте погрузимся глубже. 🍪
Оптимизация извлечения значений с DISTINCT ON
DISTINCT ON
– эффективный инструмент для получения максимальных или минимальных значений в рамках каждой уникальной группы. Просто сортируйте строки по нужным вам полям в запросе с DISTINCT ON
, и вы уже на финише!
Получите последние заказы. Интересно? 👀
SELECT * FROM (
SELECT DISTINCT ON (customer_id) customer_id, order_id, order_date
FROM orders
ORDER BY customer_id, order_date DESC
) sub
ORDER BY order_date DESC;
Применение подзапросов для сложных сортировок
Если столбцы, участвующие в сортировке, не входят в DISTINCT ON
, применение подзапросов позволяет достичь желаемого порядка.
Пример:
SELECT * FROM (
SELECT DISTINCT ON (address_id) address_id, product_id, purchase_date
FROM purchases
ORDER BY address_id, purchase_date DESC
) sub
ORDER BY product_id, purchase_date DESC;
Оконные функции как альтернатива DISTINCT ON
DISTINCT ON
отлично работает, но иногда необходимы более сложные решения. Здесь могут помочь оконные функции PostgreSQL, такие как ROW_NUMBER()
, RANK()
, LAST_VALUE()
. Они прекрасно подходят для работы с группами связанных записей.
Использование оконных функций для извлечения уникальных значений:
SELECT column1, column2
FROM (
SELECT column1, column2,
ROW_NUMBER() OVER (PARTITION BY column1 ORDER BY column2 DESC) as rn
FROM your_table
) sub
WHERE rn = 1
ORDER BY column2;
Разбираем записи на группы по column1
и выбираем первую запись в каждой группе. Ведь все мы стремимся быть первыми, не так ли?
Влияние подзапросов на производительность
Подзапросы являются универсальным средством, но не забывайте о производительности. На порядок дня ставится оптимизация запросов. Используйте индексированные столбцы!
Пример:
SELECT * FROM (
SELECT DISTINCT ON (indexed_column) indexed_column, other_column
FROM your_table
ORDER BY indexed_column, other_column DESC
) sub
ORDER BY other_column;
Благодаря индексу на indexed_column
, вышеуказанный запрос выполнится в разы быстрее!
Использование псевдонимов для эффективной сортировки
Если параметры сортировки отличаются от DISTINCT ON
, в таком случае вы можете задать столбцам псевдонимы. Это придаст необходимую гибкость, и данные останутся уникальными и будут отсортированы по методу, который вам нужен.
Пример:
SELECT alias.column1, alias.column2 FROM (
SELECT DISTINCT ON (column1) column1 as col1, column2 as col2
FROM your_table
ORDER BY column1, column2 DESC
) alias
ORDER BY alias.col2;
Здесь видно, как вы можете использовать разные псевдонимы в подзапросе и изменить порядок сортировки в основном запросе, сохранив простоту и ясность запроса.
Визуализация
Представьте гонку, где у каждого спортсмена уникальная майка (🟢 🟡 🔴 🔵):
Участники гонки: 🟢 🟡 🔴 🔵 🟡 🔴 🟢 🔵
DISTINCT ON
похож на правило гонки: на финише остается только самый быстрый в каждой майке:
SELECT DISTINCT ON (jersey_color) *
FROM sprinters
ORDER BY jersey_color, finish_time;
Результаты гонки:
Победители: 🟢 🟡 🔴 🔵
А если отсортировать участников по номерам на шортах, сохраняя уникальность цвета маек?
WITH ordered_sprinters AS (
SELECT *
FROM sprinters
ORDER BY jersey_color, finish_time
)
SELECT *
FROM ordered_sprinters
ORDER BY shorts_number;
Смотрим на спортсменов под другим углом:
Вид через зрительский монитор: 🔴 🟢 🔵 🟡
Управление NULL-значениями при сортировке
SQL может повести себя неожиданно при обработке NULL-значений в процессе сортировки, но к счастью, PostgreSQL предлагает инструменты для контроля этого поведения с помощью NULLS FIRST
или NULLS LAST
.
Способ победить NULL-неожиданности:
SELECT DISTINCT ON (column1) column1, column2
FROM your_table
ORDER BY column1, column2 DESC NULLS LAST;
Полезные материалы
- PostgreSQL: Документация: 16: SELECT — официальная документация PostgreSQL на тему
SELECT
иDISTINCT ON
. - Оконные функции SQL | Продвинутый SQL – Mode — подробно и понятно о оконных функциях.
- PostgreSQL: Клауза DISTINCT — более глубокий взгляд на DISTINCT ON в PostgreSQL.
- Как оптимизировать MySQL для работы с InnoDB? – Database Administrators Stack Exchange — глубокое погружение в аналогии (на примере MySQL!) и применимые концепции в PostgreSQL для
ORDER BY
.