0Pricing
SQL Academy · Урок

PostgreSQL DISTINCT ON

Выбирайте одну строку для каждой группы

«PostgreSQL DISTINCT ON» — бесплатный урок SQL Academy на CoddyKit. Это урок 3 из 4. Ты можешь прочитать весь урок бесплатно ниже — а потом практиковать его прямо в браузере с встроенным редактором кода и ИИ-репетитором 24/7. Это часть пути обучения SQL Academy, и твой прогресс синхронизируется между веб-версией и приложением CoddyKit. Курс SQL Academy содержит 4 уроков всего.

Что такое DISTINCT ON

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

Представьте это так: «Для каждого уникального значения в этом столбце верните мне одну строку». Это особенно полезно, когда нужно получить последний заказ каждого клиента, самый высокий результат каждого студента или первое событие каждой категории.

Базовый синтаксис DISTINCT ON

Синтаксис помещает DISTINCT ON (column) сразу после SELECT. Столбец в скобках задаёт группировку — PostgreSQL вернёт по одной строке для каждого уникального значения этого столбца.

Приведённый ниже пример возвращает по одной строке для каждого customer_id из таблицы заказов. PostgreSQL выбирает возвращаемую строку на основе следующего за ним предложения ORDER BY.

SELECT DISTINCT ON (customer_id)
  customer_id,
  order_id,
  order_date,
  total_amount
FROM orders
ORDER BY customer_id, order_date DESC;

Создание таблиц для примера

Создадим простую таблицу orders и добавим несколько примеров строк, чтобы опробовать DISTINCT ON на практике. У нас есть три клиента, у каждого из которых несколько заказов, оформленных в разные даты.

CREATE TABLE orders (
  order_id      SERIAL PRIMARY KEY,
  customer_id   INT,
  order_date    DATE,
  total_amount  NUMERIC(10, 2)
);

INSERT INTO orders (customer_id, order_date, total_amount) VALUES
  (1, '2024-01-05', 120.00),
  (1, '2024-03-12', 85.50),
  (1, '2024-06-20', 200.00),
  (2, '2024-02-14', 45.00),
  (2, '2024-05-30', 310.00),
  (3, '2024-04-01', 75.00);

Последний заказ каждого клиента

Очень распространённый практический случай: найти самый недавний заказ каждого клиента. Если упорядочить order_date DESC внутри каждой группы customer_id, DISTINCT ON выберет строку с самой поздней датой.

Обратите внимание: предложение ORDER BY должно начинаться с того же столбца или тех же столбцов, которые указаны в DISTINCT ON. Это требование PostgreSQL.

SELECT DISTINCT ON (customer_id)
  customer_id,
  order_id,
  order_date,
  total_amount
FROM orders
ORDER BY customer_id, order_date DESC;

Самый ранний заказ каждого клиента

Чтобы вместо этого получить первый, то есть самый старый, заказ каждого клиента, просто измените направление сортировки на ASC. Меняется только порядок строк внутри каждой группы — DISTINCT ON всегда выбирает первую строку после сортировки.

SELECT DISTINCT ON (customer_id)
  customer_id,
  order_id,
  order_date,
  total_amount
FROM orders
ORDER BY customer_id, order_date ASC;

Правило ORDER BY

Важное правило: при использовании DISTINCT ON (col) предложение ORDER BY должно начинаться с того же столбца или тех же столбцов, которые указаны внутри DISTINCT ON. В противном случае PostgreSQL выдаст ошибку.

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

-- Correct: ORDER BY starts with the DISTINCT ON column
SELECT DISTINCT ON (customer_id)
  customer_id, order_date, total_amount
FROM orders
ORDER BY customer_id, total_amount DESC;

-- This would cause an error:
-- ORDER BY order_date DESC  (missing customer_id at the start)

Наивысший результат каждого студента

Вот ещё один практический пример с использованием таблицы test_scores. Мы хотим узнать наивысший результат, когда-либо полученный каждым студентом. Если отсортировать score DESC внутри группы каждого студента, DISTINCT ON вернёт только строку с наивысшим результатом для каждого студента.

CREATE TABLE test_scores (
  id         SERIAL PRIMARY KEY,
  student_id INT,
  subject    VARCHAR(50),
  score      INT,
  taken_on   DATE
);

INSERT INTO test_scores (student_id, subject, score, taken_on) VALUES
  (101, 'Math',    92, '2024-02-10'),
  (101, 'Math',    78, '2024-04-15'),
  (102, 'Math',    85, '2024-02-10'),
  (102, 'Math',    91, '2024-04-15'),
  (103, 'Math',    67, '2024-02-10');

SELECT DISTINCT ON (student_id)
  student_id, subject, score, taken_on
FROM test_scores
ORDER BY student_id, score DESC;

DISTINCT ON с несколькими столбцами

Можно группировать более чем по одному столбцу, перечислив несколько столбцов внутри DISTINCT ON. В результате будет возвращена одна строка для каждой уникальной комбинации этих столбцов.

Приведённый ниже пример выбирает наивысший результат каждого студента по каждому предмету, рассматривая каждую пару (студент, предмет) как отдельную группу.

INSERT INTO test_scores (student_id, subject, score, taken_on) VALUES
  (101, 'Science', 88, '2024-03-01'),
  (101, 'Science', 95, '2024-05-20'),
  (102, 'Science', 72, '2024-03-01');

SELECT DISTINCT ON (student_id, subject)
  student_id, subject, score, taken_on
FROM test_scores
ORDER BY student_id, subject, score DESC;

Фильтрация с помощью WHERE

DISTINCT ON естественным образом работает вместе с предложениями WHERE. Сначала применяется фильтр, затем DISTINCT ON выбирает по одной строке для каждой группы из отфильтрованных результатов.

Здесь мы находим самый недавний заказ каждого клиента, но только среди заказов на сумму больше 100.

SELECT DISTINCT ON (customer_id)
  customer_id,
  order_id,
  order_date,
  total_amount
FROM orders
WHERE total_amount > 100
ORDER BY customer_id, order_date DESC;

DISTINCT ON и GROUP BY

И DISTINCT ON, и GROUP BY могут возвращать по одной строке для каждой группы, но предназначены они для разных задач:

  • GROUP BY объединяет строки и требует агрегатных функций (SUM, MAX и т. д.) для столбцов, не входящих в группировку.
  • DISTINCT ON сохраняет реально существующую строку — все её столбцы доступны без агрегации.

Используйте GROUP BY, когда нужны агрегированные значения. Используйте DISTINCT ON, когда нужны все данные из определённой строки внутри каждой группы.

-- GROUP BY: only aggregated columns allowed
SELECT customer_id, MAX(order_date) AS latest_date
FROM orders
GROUP BY customer_id;

-- DISTINCT ON: returns the whole row for that latest date
SELECT DISTINCT ON (customer_id)
  customer_id, order_id, order_date, total_amount
FROM orders
ORDER BY customer_id, order_date DESC;

Использование DISTINCT ON в подзапросе

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

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

SELECT *
FROM (
  SELECT DISTINCT ON (customer_id)
    customer_id,
    order_id,
    order_date,
    total_amount
  FROM orders
  ORDER BY customer_id, order_date DESC
) AS latest_orders
ORDER BY total_amount DESC;

Быстрая проверка

Проверьте, насколько хорошо Вы поняли принцип работы DISTINCT ON. Внимательно прочитайте запрос ниже и выберите ответ, который лучше всего описывает его результат.

SELECT DISTINCT ON (department_id) department_id, employee_name, salary FROM employees ORDER BY department_id, salary DESC;

Итоги урока

Отличная работа! Вот краткое содержание того, что Вы узнали о PostgreSQL DISTINCT ON:

  • DISTINCT ON (col) возвращает ровно одну строку для каждого уникального значения указанного столбца или столбцов.
  • Предложение ORDER BY должно начинаться с того же столбца или тех же столбцов, которые указаны в DISTINCT ON, — это определяет, какая строка будет выбрана из каждой группы.
  • Можно использовать несколько столбцов: DISTINCT ON (col1, col2) группирует строки по комбинации обоих столбцов.
  • В отличие от GROUP BY, DISTINCT ON возвращает реальную строку со всеми её исходными столбцами — агрегация не требуется.
  • Оборачивайте запрос в подзапрос, когда нужно отсортировать итоговый результат по другому столбцу.

DISTINCT ON — это специфичная для PostgreSQL возможность и один из самых элегантных способов решать задачи, в которых нужно «выбрать одну строку для каждой группы».

Часто задаваемые вопросы

Урок «PostgreSQL DISTINCT ON» бесплатный?

Да — полный текст урока «PostgreSQL DISTINCT ON» бесплатно доступен здесь в веб-версии. Чтобы практиковать его интерактивно (встроенный редактор кода и ИИ-репетитор 24/7) и разблокировать остальной курс SQL Academy, подпишись на CoddyKit PRO. Курс SQL Academy содержит 4 уроков всего.

Чему я научусь в уроке «PostgreSQL DISTINCT ON»?

Выбирайте одну строку для каждой группы Ты практикуешь SQL Academy с помощью реального кода, который запускаешь прямо в браузере, и ИИ-репетитор 24/7 отвечает на твои вопросы во время урока.

Нужен ли мне опыт, чтобы начать SQL Academy?

Предыдущий опыт не требуется. SQL Academy на CoddyKit структурирован для всех уровней — от новичков до продвинутых, поэтому ты можешь начать отсюда или с самого начала и учиться в своем темпе. Это урок 3 из 4.

Сколько времени занимает урок «PostgreSQL DISTINCT ON»?

Большинство уроков CoddyKit занимают около 5–10 минут. Каждый из них компактный и интерактивный, поэтому ты постоянно делаешь прогресс и продолжаешь с того же места в веб-версии и приложении.

Можно ли писать и запускать код в этом уроке SQL Academy?

Да. Каждый урок SQL Academy включает встроенный редактор кода, поэтому ты пишешь и запускаешь реальный код прямо в браузере и получаешь моментальную обратную связь от AI — локальная установка не требуется.

Все уроки этого курса

  1. Основы SELECT DISTINCT
  2. DISTINCT для нескольких столбцов
  3. PostgreSQL DISTINCT ON
  4. Подсчёт уникальных значений
← Назад к SQL Academy