Menu

PostgreSQL DISTINCT ON: jeden wiersz na grupę

SELECT DISTINCT ON (key) ... ORDER BY key, sort zostawia pierwszy wiersz każdej grupy, na przykład najnowsze zamówienie każdego klienta. Działa tylko w PostgreSQL.

Na tej stronie są działające edytory: edytuj, uruchamiaj i od razu zobacz wynik.

SELECT DISTINCT ON (expr) ... zwraca jeden wiersz dla każdej różnej wartości expr: pierwszy wiersz tej grupy w kolejności z ORDER BY. Sortuj najpierw po kluczu grupy, a potem po tym, co decyduje o „pierwszym”. Dla najnowszego zamówienia każdego klienta to ORDER BY customer, created_at DESC.

Zapytanie zwraca trzy wiersze: zamówienie 2 dla asha, 4 dla ben i 5 dla chen, czyli najnowsze zamówienie każdego klienta. DISTINCT ON to rozszerzenie PostgreSQL. Inne bazy danych potrzebują wersji z funkcjami okna pokazanej niżej.

Jak ORDER BY decyduje, który wiersz zostaje

PostgreSQL sortuje wiersze, a potem zostawia pierwszy wiersz za każdym razem, gdy zmienia się wartość DISTINCT ON. Dlatego:

  • ORDER BY musi zaczynać się od wyrażeń z DISTINCT ON, inaczej PostgreSQL zgłasza "SELECT DISTINCT ON expressions must match initial ORDER BY expressions".
  • Bez ORDER BY wykraczającego poza te wyrażenia nie wiadomo, który wiersz zostanie, i może się to zmieniać między uruchomieniami.

Zamień created_at DESC na total DESC, a to samo zapytanie zwróci największe zamówienie każdego klienta:

Teraz wiersz asha to zamówienie 1 (40), a wiersz ben to zamówienie 3 (60).

Celowe rozstrzyganie remisów

Gdy dwa wiersze mają tę samą wartość w kolumnie sortowania, wygrać może każdy z nich. Dodaj jako ostatni klucz sortowania coś unikalnego, na przykład klucz główny, żeby wynik był za każdym razem taki sam:

Wiersze 2 i 3 mają po 95 punktów. id jako ostatni klucz sprawia, że wygrywa wiersz 2.

Grupowanie po więcej niż jednej kolumnie

Wypisz kilka wyrażeń, żeby dostać jeden wiersz na każdą kombinację:

Każda para czujnika i rodzaju pomiaru zostawia najnowszy odczyt: s1 humidity 40, s1 temp 22, s2 temp 19.

Wersja przenośna z ROW_NUMBER

ROW_NUMBER() numeruje wiersze w każdej partycji. Zostawienie numeru 1 daje ten sam wynik co DISTINCT ON i działa też w MySQL 8, SQL Server i 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;

Użyj tej formy, gdy potrzebujesz pierwszych n w grupie (rn <= 3) albo gdy zapytanie ma działać na innej bazie danych. Strona o funkcjach okna wyjaśnia PARTITION BY.

Przyspieszenie

DISTINCT ON sortuje dane wejściowe, więc na dużej tabeli daj mu indeks zgodny z ORDER BY:

CREATE INDEX orders_customer_latest ON orders (customer, created_at DESC);

PostgreSQL może wtedy czytać wiersze po kolei zamiast je sortować. Przy niewielu grupach i wielu wierszach w każdej złączenie LATERAL, które bierze LIMIT 1 na klienta z tego samego indeksu, zwykle czyta mniej wierszy. Strona o złączeniach pokazuje LATERAL. Porównaj oba sposoby przez EXPLAIN ANALYZE na swoich danych.

Najczęściej zadawane pytania

Czym różni się DISTINCT od DISTINCT ON?

DISTINCT usuwa wiersze identyczne we wszystkich wybranych kolumnach. DISTINCT ON (a) zostawia jeden wiersz dla każdej wartości a, a pozostałe kolumny pochodzą z wiersza, który w tej grupie jest pierwszy po sortowaniu.

Czy DISTINCT ON to standardowy SQL?

Nie. To rozszerzenie PostgreSQL, więc nie działa w MySQL ani w SQL Server. Przenośna wersja to ROW_NUMBER() OVER (PARTITION BY ... ORDER BY ...) w podzapytaniu, przefiltrowane do wiersza numer 1.

Skąd błąd "SELECT DISTINCT ON expressions must match initial ORDER BY expressions"?

ORDER BY musi zaczynać się od tych samych wyrażeń co DISTINCT ON, w tej samej kolejności. Napisz DISTINCT ON (customer) ... ORDER BY customer, created_at DESC, a nie samo ORDER BY created_at DESC.

Czy DISTINCT ON zwróci 3 pierwsze wiersze w każdej grupie?

Nie, zwraca dokładnie jeden wiersz na grupę. Dla pierwszych n ponumeruj wiersze przez ROW_NUMBER() i zostaw rn <= 3 albo użyj podzapytania LATERAL z LIMIT 3.

Ilustracja języków programowania w Coddy

Ucz się programowania z Coddy

Zacznij