Jak sugeruje tytuł, chciałbym wybrać pierwszy wiersz z każdego zestawu wierszy zgrupowanych za pomocą GROUP BY
.
Konkretnie, jeśli I've got a purchases
table that looks like this:
SELECT * FROM purchases;
Moje wyjście:
id | klient | suma ---+----------+------ 1 | Joe | 5 2 | Sally | 3 3 | Joe | 2 4 | Sally | 1
Chciałbym zapytać o id
największego zakupu (total
) dokonanego przez każdego klienta
. Coś w tym stylu:
SELECT FIRST(id), customer, FIRST(total)
FROM purchases
GROUP BY customer
ORDER BY total DESC;
Oczekiwane wyjście:
PIERWSZY(id) | klient | PIERWSZY(suma) ----------+----------+------------- 1 | Joe | 5 2 | Sally | 3
W PostgreSQL jest to zazwyczaj prostsze i szybsze (więcej optymalizacji wydajności poniżej):
SELECT DISTINCT ON (customer)
id, klient, suma
FROM zakupy
ORDER BY customer, total DESC, id;
Lub krótszy (jeśli nie tak jasny) z liczbami porządkowymi kolumn wyjściowych:
SELECT DISTINCT ON (2)
id, customer, total
FROM purchases
ORDER BY 2, 3 DESC, 1;
Jeśli total
może być NULL (nie zaszkodzi w żaden sposób, ale będziesz chciał dopasować istniejące indeksy):
...
ORDER BY customer, total DESC NULLS LAST, id;
###Ważne punkty
- **`DISTINCT ON`**][1] jest rozszerzeniem standardu PostgreSQL (gdzie definiuje się tylko `DISTINCT` na całej liście `SELECT`).
- Wymień dowolną ilość wyrażeń w klauzuli `DISTINCT ON`, połączona wartość wiersza określa duplikaty. [Instrukcja:][2]
> Oczywiście, dwa wiersze są uważane za odrębne, jeśli różnią się co najmniej
> jedną wartością kolumny. **Wartości zerowe są uważane za równe w tym porównaniu.**
Pogrubienie podkreślenia moje.
- Opcja `DISTINCT ON` może być połączona z **`ORDER BY`**. Wiodące wyrażenia muszą być zgodne z wiodącymi wyrażeniami `DISTINCT ON` w tej samej kolejności. Możesz dodać *dodatkowe* wyrażenia do `ORDER BY` aby wybrać konkretny wiersz z każdej grupy rówieśników. Dodałem `id` jako ostatni element, aby przełamać więzy:
*"Wybierz wiersz z najmniejszym `id` z każdej grupy, która ma największą `sumę`."*.
Aby uporządkować wyniki w sposób, który nie zgadza się z porządkiem sortowania określającym pierwsze na grupę, możesz zagnieździć powyższe zapytanie w zewnętrznym zapytaniu z innym `ORDER BY`. Na przykład:
- https://stackoverflow.com/questions/9795660/postgresql-distinct-on-with-different-order-by/9796104#9796104
- Jeśli `total` może być NULL, najprawdopodobniej* chcesz wiersz z największą wartością non-null. Dodaj **`NULLS LAST`** jak zademonstrowano. Szczegóły:
- https://stackoverflow.com/questions/9510509/postgresql-sort-by-datetime-asc-null-first/9511492#9511492
- Lista `SELECT`** nie jest ograniczona przez wyrażenia `DISTINCT ON` lub `ORDER BY` w żaden sposób. (Nie jest to potrzebne w prostym przypadku powyżej):
- You *don't have to* include any of the expressions in `DISTINCT ON` or `ORDER BY`.
- Możesz* dołączyć dowolne inne wyrażenie do listy `SELECT`. Jest to instrumentalne dla zastąpienia znacznie bardziej złożonych zapytań z podzapytaniami i funkcjami agregującymi / okienkowymi.
- Testowałem z Postgres w wersjach 8.3 - 12. Ale ta funkcja jest tam co najmniej od wersji 7.1, więc w zasadzie zawsze.
##Index
Idealnym* indeksem dla powyższego zapytania byłby [indeks wielokolumnowy][3] obejmujący wszystkie trzy kolumny w pasującej kolejności i z pasującą kolejnością sortowania:
CREATE INDEX purchases_3c_idx ON purchases (customer, total DESC, id);
Może być zbyt wyspecjalizowany. Ale użyj go, jeśli wydajność odczytu dla danego zapytania jest kluczowa. Jeśli masz DESC NULLS LAST
w zapytaniu, użyj tego samego w indeksie, aby kolejność sortowania pasowała, a indeks miał zastosowanie.
Należy rozważyć koszty i korzyści przed utworzeniem indeksów dostosowanych do każdego zapytania. Potencjał powyższego indeksu w dużej mierze zależy od dystrybucji danych. Indeks ten jest używany, ponieważ dostarcza wstępnie posortowane dane. W Postgres 9.2 lub nowszym zapytanie może również skorzystać z skanowania tylko indeksem, jeśli indeks jest mniejszy niż tabela bazowa. Indeks musi zostać przeskanowany w całości.
klient
), jest to bardzo wydajne. Tym bardziej, jeśli i tak potrzebujesz posortowanych danych wyjściowych. Korzyść kurczy się wraz z rosnącą liczbą wierszy na klienta.work_mem
aby przetworzyć krok sortowania w pamięci RAM i nie wysypywać na dysk. Ale generalnie ustawienie work_mem
zbyt wysoko może mieć negatywne skutki. Rozważ SET LOCAL
dla wyjątkowo dużych zapytań. Sprawdź ile potrzebujesz za pomocą EXPLAIN ANALYZE
. Wzmianka o "Disk:" w kroku sortowania wskazuje na potrzebę większej ilości:customer
), loose index scan (a.k.a. "skip scan") byłby (znacznie) bardziej wydajny, ale to'nie jest zaimplementowane do Postgres 12. (Implementacja dla skanowania tylko do indeksu jest w trakcie opracowywania dla Postgres 13. Zobacz tutaj i tutaj).Miałem tutaj prosty benchmark, który jest już przestarzały. Zastąpiłem go szczegółowy benchmark w tej osobnej odpowiedzi.
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
Ale musisz dodać logikę, aby przełamać krawaty:
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
Rozwiązanie to nie jest zbyt efektywne, jak zauważył Erwin, z powodu obecności SubQs
select * from purchases p1 where total in
(select max(total) from purchases where p1.customer=customer) order by total desc;