SELECT DISTINCT ON (expr) ... возвращает одну строку на каждое различное значение expr: первую строку этой группы в порядке ORDER BY. Сначала сортируйте по ключу группы, затем по тому, что определяет «первую». Для последнего заказа каждого клиента это ORDER BY customer, created_at DESC.
Запрос возвращает три строки: заказ 2 для asha, 4 для ben и 5 для chen, у каждого клиента самый свежий. DISTINCT ON это расширение PostgreSQL. В других базах данных нужна форма с оконной функцией, показанная ниже.
Как ORDER BY решает, какая строка останется
PostgreSQL сортирует строки и оставляет первую строку каждый раз, когда меняется значение DISTINCT ON. Поэтому:
ORDER BYдолжен начинаться с выраженийDISTINCT ON, иначе PostgreSQL выдаёт ошибку «SELECT DISTINCT ON expressions must match initial ORDER BY expressions».- Если в
ORDER BYнет ничего после этих выражений, какая строка останется, предсказать нельзя, и от запуска к запуску это может меняться.
Замените created_at DESC на total DESC, и тот же запрос вернёт самый крупный заказ каждого клиента:
Теперь у asha остаётся заказ 1 (40), а у ben заказ 3 (60).
Осознанно разрешайте ничьи
Когда у двух строк одинаковое значение в столбце сортировки, победить может любая. Добавьте последним ключом сортировки что-то уникальное, например первичный ключ, чтобы результат был каждый раз одинаковым:
У строк 2 и 3 по 95 очков. id в качестве последнего ключа делает победителем строку 2.
Группировка по нескольким столбцам
Перечислите несколько выражений, чтобы получить одну строку на каждое сочетание:
Для каждой пары датчика и типа остаётся последнее показание: s1 humidity 40, s1 temp 22, s2 temp 19.
Переносимый вариант с ROW_NUMBER
ROW_NUMBER() нумерует строки внутри каждого раздела. Если оставить номер 1, результат будет таким же, как у DISTINCT ON, и это работает также в MySQL 8, SQL Server и SQLite:
SELECT customer, id, total, created_at
FROM (
SELECT o.*,
ROW_NUMBER() OVER (PARTITION BY customer ORDER BY created_at DESC) AS rn
FROM orders o
) ranked
WHERE rn = 1;
Используйте эту форму, когда нужны первые n строк на группу (rn <= 3) или когда запрос должен работать в другой базе данных. PARTITION BY объясняется на странице об оконных функциях.
Как ускорить
DISTINCT ON сортирует входные данные, поэтому на большой таблице дайте ему индекс, совпадающий с ORDER BY:
CREATE INDEX orders_customer_latest ON orders (customer, created_at DESC);
Тогда PostgreSQL сможет читать строки по порядку, а не сортировать их. Если групп мало, а строк в каждой много, соединение LATERAL, которое берёт LIMIT 1 на клиента из того же индекса, обычно читает меньше строк. LATERAL показан на странице о соединениях. Сравните оба варианта через EXPLAIN ANALYZE на своих данных.
Часто задаваемые вопросы
Чем DISTINCT отличается от DISTINCT ON?
DISTINCT убирает строки, совпадающие во всех выбранных столбцах. DISTINCT ON (a) оставляет одну строку на каждое значение a, а остальные столбцы берутся из той строки, которая в этой группе идёт первой при сортировке.
DISTINCT ON входит в стандарт SQL?
Нет. Это расширение PostgreSQL, поэтому в MySQL и SQL Server оно не работает. Переносимый вариант: ROW_NUMBER() OVER (PARTITION BY ... ORDER BY ...) в подзапросе с фильтром по номеру строки 1.
Почему возникает ошибка «SELECT DISTINCT ON expressions must match initial ORDER BY expressions»?
ORDER BY должен начинаться с тех же выражений, что и DISTINCT ON, в том же порядке. Пишите DISTINCT ON (customer) ... ORDER BY customer, created_at DESC, а не просто ORDER BY created_at DESC.
Может ли DISTINCT ON вернуть первые 3 строки каждой группы?
Нет, он возвращает ровно одну строку на группу. Для первых n пронумеруйте строки через ROW_NUMBER() и оставьте rn <= 3 или используйте подзапрос LATERAL с LIMIT 3.