Както подсказва заглавието, бих искал да избера първия ред от всеки набор от редове, групирани с GROUP BY
.
По-конкретно, ако имам таблица purchases
, която изглежда по следния начин:
SELECT * FROM purchases;
Моят изход:
id | customer | total ---+----------+------ 1 | Джо | 5 2 | Сали | 3 3 | Джо | 2 4 | Сали | 1
Бих искал да направя заявка за ид
на най-голямата покупка (общо
), направена от всеки клиент
. Нещо като това:
SELECT FIRST(id), customer, FIRST(total)
FROM purchases
GROUP BY customer
ORDER BY total DESC;
Очакван изход:
FIRST(id) | customer | FIRST(total) ----------+----------+------------- 1 | Джо | 5 2 | Sally | 3
В PostgreSQL това обикновено е просто и по-бързо (повече за оптимизацията на производителността по-долу):
SELECT DISTINCT ON (customer)
id, customer, total
FROM purchases
ORDER BY customer, total DESC, id;
Или по-кратко (ако не е толкова ясно) с поредни номера на изходните колони:
SELECT DISTINCT ON (2)
id, customer, total
FROM purchases
ORDER BY 2, 3 DESC, 1;
Ако total
може да бъде NULL (няма да навреди и в двата случая, но ще искате да съответствате на съществуващите индекси):
...
ORDER BY customer, total DESC NULLS LAST, id;
###Основни точки
- [**`DISTINCT ON`**][1] е разширение на стандарта на PostgreSQL (където е дефиниран само `DISTINCT` за целия списък `SELECT`).
- Избройте произволен брой изрази в клаузата `DISTINCT ON`, като комбинираната стойност на реда дефинира дубликати. [Ръководство:][2]
> Очевидно е, че два реда се считат за различни, ако се различават поне в
> една стойност на колоната. **Нулевите стойности се считат за равни при това сравнение.**
Подчертаването с удебелен шрифт е мое.
- `DISTINCT ON` може да се комбинира с **`ORDER BY`**. Водещите изрази трябва да съвпадат с водещите изрази на `DISTINCT ON` в същия ред. Можете да добавите *допълнителни* изрази към `ORDER BY`, за да изберете конкретен ред от всяка група равностойни изрази. Добавих `id` като последен елемент, за да прекъснем връзките:
*"Изберете реда с най-малкия `id` от всяка група, споделяща най-голям `total`."*
За да подредите резултатите по начин, който не съвпада с реда на сортиране, определящ първия за всяка група, можете да вложите горната заявка във външна заявка с друг `ORDER BY`. Например:
- https://stackoverflow.com/questions/9795660/postgresql-distinct-on-with-different-order-by/9796104#9796104
- Ако `total` може да бъде NULL, най-вероятно* искате реда с най-голямата стойност, която не е нулева. Добавете **`NULLS LAST`**, както е показано. Подробности:
- https://stackoverflow.com/questions/9510509/postgresql-sort-by-datetime-asc-null-first/9511492#9511492
- **Списъкът `SELECT`** не се ограничава по никакъв начин от изразите в `DISTINCT ON` или `ORDER BY`. (Не е необходимо в простия случай по-горе):
- Не е необходимо да включвате нито един от изразите в `DISTINCT ON` или `ORDER BY`.
- Можете да включите всеки друг израз в списъка `SELECT`. Това е полезно за заместване на много по-сложни заявки с подзаявки и функции за агрегиране/прозорци.
- Тествах с версии 8.3 - 12 на Postgres. Но функцията е налична поне от версия 7.1, така че по принцип винаги.
##Index
*Перфектният* индекс за горната заявка би бил [multi-column index][3], обхващащ и трите колони в съвпадаща последователност и със съвпадащ ред на сортиране:
CREATE INDEX purchases_3c_idx ON purchases (customer, total DESC, id);
Може да е твърде специализиран. Но го използвайте, ако производителността на четене за конкретната заявка е от решаващо значение. Ако имате DESC NULLS LAST
в заявката, използвайте същото в индекса, така че редът на сортиране да съвпада и индексът да е приложим.
Преценете разходите и ползите, преди да създадете специално пригодени индекси за всяка заявка. Потенциалът на горния индекс зависи до голяма степен от разпределението на данните. Индексът се използва, защото предоставя предварително сортирани данни. В Postgres 9.2 или по-нова версия заявката може да се възползва и от сканиране само по индекс, ако индексът е по-малък от основната таблица. Индексът обаче трябва да бъде сканиран изцяло.
customer
) това е много ефективно. Още повече, ако така или иначе се нуждаете от сортиран изход. Ползата намалява с нарастването на броя на редовете на клиент.work_mem
, за да обработвате съответната стъпка на сортиране в оперативната памет и да не я прехвърляте на диска. Но като цяло задаването на твърде висока стойност на work_mem
може да има неблагоприятни последици. Помислете за SET LOCAL
при изключително големи заявки. Разберете колко ви е необходимо с помощта на EXPLAIN ANALYZE
. Споменаването на "Disk:" в стъпката за сортиране показва нуждата от повече:customer
), свободно сканиране на индекси (известно още като "skip scan") би било (много) по-ефективно, но това не е реализирано до Postgres 12. (В процес на разработка е имплементация за сканиране само на индекси за Postgres 13. Вижте тук и тук.)Тук имах прост критерий, който вече е остарял. Замених го с подробен бенчмарк в този отделен отговор.
WITH summary AS (
SELECT p.id,
p.customer,
p.total,
ROW_NUMBER() OVER(PARTITION BY p.customer
ORDER BY p.total DESC) AS rk
FROM PURCHASES p)
SELECT s.*
FROM summary s
WHERE s.rk = 1
Но трябва да добавите логика за прекъсване на връзките:
SELECT MIN(x.id), -- change to MAX if you want the highest
x.customer,
x.total
FROM PURCHASES x
JOIN (SELECT p.customer,
MAX(total) AS max_total
FROM PURCHASES p
GROUP BY p.customer) y ON y.customer = x.customer
AND y.max_total = x.total
GROUP BY x.customer, x.total
Решението не е много ефикасно, както посочва Ервин, поради наличието на SubQs
select * from purchases p1 where total in
(select max(total) from purchases where p1.customer=customer) order by total desc;