Избиране на първия ред във всяка група GROUP BY?

Както подсказва заглавието, бих искал да избера първия ред от всеки набор от редове, групирани с 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
DineshDB
Редактиран въпрос Направете март 2018 в 8:18
Програмиране
sql postgresql sqlite group-by greatest-n-per-group
Направете септември 2010 в 1:23
19 изгледи

В 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 или по-нова версия заявката може да се възползва и от сканиране само по индекс, ако индексът е по-малък от основната таблица. Индексът обаче трябва да бъде сканиран изцяло.

Erwin Brandstetter
Редактиран отговор Направете август 2019 в 7:29
Коментари (7)
Решение

В Oracle 9.2+ (а не 8i+, както беше посочено първоначално), SQL Server 2005+, PostgreSQL 8.4+, DB2, Firebird 3.0+, Teradata, Sybase, Vertica:

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
mach128x
Редактиран отговор Направете декември 2017 в 6:42
Коментари (7)

Решението не е много ефикасно, както посочва Ервин, поради наличието на SubQs

select * from purchases p1 where total in
(select max(total) from purchases where p1.customer=customer) order by total desc;
user2407394
Редактиран отговор Направете юни 2013 в 8:39
Коментари (4)