Уникальные строки по одному столбцу с сортировкой по другому в 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;

Полезные материалы

1. PostgreSQL: Документация: 16: SELECT — официальная документация PostgreSQL на тему SELECT и DISTINCT ON.
2. Оконные функции SQL | Продвинутый SQL – Mode — подробно и понятно о оконных функциях.
3. PostgreSQL: Клауза DISTINCT — более глубокий взгляд на DISTINCT ON в PostgreSQL.
4. Как оптимизировать MySQL для работы с InnoDB? – Database Administrators Stack Exchange — глубокое погружение в аналогии (на примере MySQL!) и применимые концепции в PostgreSQL для ORDER BY.