DISTINCT ON (вирази) - розширення PostgreSQL: з кожної групи рядків з однаковими значеннями виразів лишається перший рядок за порядком ORDER BY.
Задача: останнє замовлення кожного користувача.
SELECT DISTINCT ON (user_id) user_id, id, total, created_at
FROM orders
ORDER BY user_id, created_at DESC;
- групи визначає
user_id; - усередині групи рядки впорядковані за
created_at DESC; - лишається перший - тобто найновіший.
Правило: вирази з DISTINCT ON мають бути на початку ORDER BY. Інакше:
ERROR: SELECT DISTINCT ON expressions must match initial ORDER BY expressions
Без додаткового сортування всередині групи «перший» рядок випадковий - завжди вказуйте, який саме потрібен.
Порівняння з альтернативами:
| Спосіб | Особливості |
|---|---|
DISTINCT ON |
найкоротший запис, лише PostgreSQL |
віконна функція row_number() |
стандартний SQL, працює й у MySQL 8, можна взяти N рядків на групу |
LATERAL з LIMIT 1 |
швидкий, коли груп мало, а рядків у групі багато, і є індекс |
-- те саме через віконну функцію
SELECT * FROM (
SELECT o.*, row_number() OVER (PARTITION BY user_id ORDER BY created_at DESC) AS rn
FROM orders o
) t WHERE rn = 1;
Продуктивність: індекс (user_id, created_at DESC) дозволяє виконати DISTINCT ON без окремого сортування всієї таблиці. Без індексу PostgreSQL сортує всі рядки - на великій таблиці це повільно.
У Laravel є прямий метод:
Order::query()
->distinct('user_id')
->orderBy('user_id')
->latest()
->get();
distinct('user_id') на PostgreSQL генерує DISTINCT ON ("user_id"). Для зв'язку «останнє замовлення» в Eloquent є latestOfMany() - він будує підзапит, що працює в усіх базах.
Типова помилка - очікувати від DISTINCT ON агрегацію: він не рахує суми й кількості, лише вибирає один рядок з групи. Для підсумків потрібен GROUP BY.