W PostgreSQL jest to zazwyczaj prostsze i szybsze (więcej optymalizacji wydajności poniżej):

SELECT DISTINCT ON (customer)
       id, customer, total
FROM   purchases
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, ale będziesz chciał dopasować istniejące indeksy):

...
ORDER  BY customer, total DESC NULLS LAST, id;

Główne punkty

• DISTINCT ON jest rozszerzeniem standardu PostgreSQL (gdzie tylko DISTINCT na całej liście SELECT jest zdefiniowany).

• Wypisuje dowolną liczbę wyrażeń w klauzuli DISTINCT ON, wartość łączonego wiersza definiuje duplikaty. Instrukcja:

  Oczywiście dwa rzędy są uważane za odrębne, jeśli różnią się co najmniej wartość jednej kolumny. wartości Null są uważane za równe w tym porównaniu.

  / Align = "left" /

• DISTINCT ON można łączyć z ORDER BY. Wyrażenia wiodące muszą pasować do wiodącego DISTINCT ON wyrażenia 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 do zerwania więzi:

  "wybierz wiersz z najmniejszą id z każdej grupy dzielącej najwyższą total."

  Jeśli total może być NULL, to najprawdopodobniej chcesz wiersz z największą wartością inną niż null. Dodaj {[17] } Jak pokazano. Szczegóły:

  • PostgreSQL Sortuj według datetime asc, najpierw null?

• Lista SELECT nie jest w żaden sposób ograniczona wyrażeniami w DISTINCT ON lub ORDER BY. (Nie potrzebne w prostym przypadku powyżej):

  • Nie musisz zawierać żadnego z wyrażeń wDISTINCT ON lub ORDER BY.

  • Możesz dołączyć dowolne inne wyrażenie do listy SELECT. Jest to instrumentalne do zastąpienia znacznie bardziej złożonych zapytań zapytaniami podrzędnymi i agregatem / window funkcje.

• Testowałem z Postgres w wersjach 8.3 – 10. Ale funkcja jest tam co najmniej od wersji 7.1, więc w zasadzie zawsze.

Indeks

Indeks doskonały dla powyższego zapytania będzie indeks wielokolumnowy obejmujący wszystkie trzy kolumny w pasującej kolejności i pasującej kolejności sortowania:

CREATE INDEX purchases_3c_idx ON purchases (customer, total DESC, id);

Może być zbyt wyspecjalizowany do rzeczywistych zastosowań. Ale użyj go, jeśli wydajność odczytu jest kluczowe. Jeśli masz DESC NULLS LAST W zapytaniu, użyj tego samego w indeksie, aby Postgres znał kolejność sortowania.

Efektywność / Optymalizacja wydajności

Musisz rozważyć koszty i korzyści, zanim stworzysz dostosowany indeks dla każdego zapytania. Potencjał powyższego indeksu w dużej mierze zależy od rozkładu danych .

Indeks jest używany, ponieważ dostarcza wstępnie posortowane dane, a w Postgres 9.2 lub później zapytanie może również korzystać z tylko indeks skanowanie jeśli indeks jest mniejszy niż tabela bazowa. Indeks musi być skanowany w całości.

• Dla kilka wierszy na klienta , jest to bardzo wydajne(tym bardziej, jeśli i tak potrzebujesz sortowanego wyjścia). Korzyści maleją wraz z rosnącą liczbą wierszy na klienta.
  Idealnie, masz wystarczająco dużo work_mem aby przetworzyć zaangażowany krok sortowania w pamięci RAM i nie rozlać na dysk. Ogólnie ustawienie work_mem zbyt wysoki może mieć niekorzystne skutki. Rozważmy SET LOCAL dla wyjątkowo dużych zapytań. Dowiedz się, ile potrzebujesz z EXPLAIN ANALYZE. W kroku sortowania zaznaczenie "Disk: " wskazuje na potrzebę dodania więcej:

  • parametr konfiguracyjny work_mem w PostgreSQL na Linuksie
  • Optymalizacja prostych zapytań za pomocą kolejności według daty i tekstu

• Dla wiele wierszy na klienta, a indeks luźny skanowanie byłoby (o wiele) bardziej wydajne, ale nie jest to obecnie zaimplementowane w Postgres (do v10).
  Istnieją szybsze techniki zapytań , które zastępują to. W szczególności, jeśli masz oddzielny stół z unikalnymi klientami, co jest typowym przypadkiem użycia. Ale także jeśli nie:

  • zoptymalizuj grupę według zapytania, aby pobrać najnowszy rekord na użytkownika
  • Optimize groupwise maximum query
  • zapytanie Ostatnie N powiązanych wierszy w wierszu

Benchmark

Miałem tutaj prosty benchmark, który jest już przestarzały. Wymieniłem na szczegółowy benchmark w tej odrębnej odpowiedzi.

Benchmark

Testowanie najciekawszych kandydatów z Postgres 9.4 oraz 9.5 Z pół realistyczną tabelą 200k wierszy W purchases i 10K odrębnych customer_id (avg. 20 wierszy na klienta ).

Dla Postgres 9.5 przeprowadziłem drugi test z 86446 różnymi klientami. Zobacz poniżej (avg. 2.3 wierszy na klienta ).

Setup

Tabela Główna

CREATE TABLE purchases (
  id          serial
, customer_id int  -- REFERENCES customer
, total       int  -- could be amount of money in Cent
, some_column text -- to make the row bigger, more realistic
);

Używam serial (ograniczenie PK dodano poniżej) i liczbę całkowitą customer_id, ponieważ jest to bardziej typowa konfiguracja. Dodano również some_column, aby uzupełnić typowo większą liczbę kolumn.

Dummy data, PK, index-typowa tabela ma również kilka martwych krotek:

INSERT INTO purchases (customer_id, total, some_column)    -- insert 200k rows
SELECT (random() * 10000)::int             AS customer_id  -- 10k customers
     , (random() * random() * 100000)::int AS total     
     , 'note: ' || repeat('x', (random()^2 * random() * random() * 500)::int)
FROM   generate_series(1,200000) g;

ALTER TABLE purchases ADD CONSTRAINT purchases_id_pkey PRIMARY KEY (id);

DELETE FROM purchases WHERE random() > 0.9; -- some dead rows

INSERT INTO purchases (customer_id, total, some_column)
SELECT (random() * 10000)::int             AS customer_id  -- 10k customers
     , (random() * random() * 100000)::int AS total     
     , 'note: ' || repeat('x', (random()^2 * random() * random() * 500)::int)
FROM   generate_series(1,20000) g;  -- add 20k to make it ~ 200k

CREATE INDEX purchases_3c_idx ON purchases (customer_id, total DESC, id);

VACUUM ANALYZE purchases;

customer table - for superior query

CREATE TABLE customer AS
SELECT customer_id, 'customer_' || customer_id AS customer
FROM   purchases
GROUP  BY 1
ORDER  BY 1;

ALTER TABLE customer ADD CONSTRAINT customer_customer_id_pkey PRIMARY KEY (customer_id);

VACUUM ANALYZE customer;

W moim drugim teście Dla 9.5 użyłem tej samej konfiguracji, ale z random() * 100000 do generowania customer_id, aby uzyskać tylko kilka wierszy na customer_id.

Rozmiary obiektów dla tabeli purchases

Wygenerowane z to zapytanie .

               what                | bytes/ct | bytes_pretty | bytes_per_row
-----------------------------------+----------+--------------+---------------
 core_relation_size                | 20496384 | 20 MB        |           102
 visibility_map                    |        0 | 0 bytes      |             0
 free_space_map                    |    24576 | 24 kB        |             0
 table_size_incl_toast             | 20529152 | 20 MB        |           102
 indexes_size                      | 10977280 | 10 MB        |            54
 total_size_incl_toast_and_indexes | 31506432 | 30 MB        |           157
 live_rows_in_text_representation  | 13729802 | 13 MB        |            68
 ------------------------------    |          |              |
 row_count                         |   200045 |              |
 live_tuples                       |   200045 |              |
 dead_tuples                       |    19955 |              |

Queries

1. row_number()w CTE, ( zobacz inne odpowiedzi )

WITH cte AS (
   SELECT id, customer_id, total
        , row_number() OVER(PARTITION BY customer_id ORDER BY total DESC) AS rn
   FROM   purchases
   )
SELECT id, customer_id, total
FROM   cte
WHERE  rn = 1;

2. row_number() in subquery (moja optymalizacja)

SELECT id, customer_id, total
FROM   (
   SELECT id, customer_id, total
        , row_number() OVER(PARTITION BY customer_id ORDER BY total DESC) AS rn
   FROM   purchases
   ) sub
WHERE  rn = 1;

3. DISTINCT ON (zobacz inne odpowiedzi )

SELECT DISTINCT ON (customer_id)
       id, customer_id, total
FROM   purchases
ORDER  BY customer_id, total DESC, id;

4. rCTE z LATERAL subquery (zobacz tutaj )

WITH RECURSIVE cte AS (
   (  -- parentheses required
   SELECT id, customer_id, total
   FROM   purchases
   ORDER  BY customer_id, total DESC
   LIMIT  1
   )
   UNION ALL
   SELECT u.*
   FROM   cte c
   ,      LATERAL (
      SELECT id, customer_id, total
      FROM   purchases
      WHERE  customer_id > c.customer_id  -- lateral reference
      ORDER  BY customer_id, total DESC
      LIMIT  1
      ) u
   )
SELECT id, customer_id, total
FROM   cte
ORDER  BY customer_id;

5. customer tabela z LATERAL (zobacz tutaj )

SELECT l.*
FROM   customer c
,      LATERAL (
   SELECT id, customer_id, total
   FROM   purchases
   WHERE  customer_id = c.customer_id  -- lateral reference
   ORDER  BY total DESC
   LIMIT  1
   ) l;

6. array_agg() Z ORDER BY (zobacz inne odpowiedzi )

SELECT (array_agg(id ORDER BY total DESC))[1] AS id
     , customer_id
     , max(total) AS total
FROM   purchases
GROUP  BY customer_id;

Wyniki

Czas wykonania dla powyższych zapytań z EXPLAIN ANALYZE (i wszystkie opcje wyłączone), najlepszy z 5 biegów .

wszystkie kwerendy używały indeksu tylko skanowania na purchases2_3c_idx (między innymi). Niektóre z nich tylko dla mniejszej wielkości indeksu, inne skuteczniej.

A. Postgres 9.4 z 200k wierszy i ~ 20 na customer_id

1. 273.274 ms  
2. 194.572 ms  
3. 111.067 ms  
4.  92.922 ms  
5.  37.679 ms  -- winner
6. 189.495 ms

B. to samo z Postgres 9.5

1. 288.006 ms
2. 223.032 ms  
3. 107.074 ms  
4.  78.032 ms  
5.  33.944 ms  -- winner
6. 211.540 ms  

C. Taki sam jak B., ale z ~ 2,3 rzędami na customer_id

1. 381.573 ms
2. 311.976 ms
3. 124.074 ms  -- winner
4. 710.631 ms
5. 311.976 ms
6. 421.679 ms

Oryginał (Nieaktualny) benchmark z 2011 r.

Przeprowadziłem trzy testy z PostgreSQL 9.1 na prawdziwej tabeli 65579 wierszy i jednokolumnowych indeksów btree na każdej z trzech zaangażowanych kolumn i wziął najlepszy Czas wykonania Z 5 uruchomień.
Porównanie @OMGPonies' pierwsze zapytanie (A) do rozwiązania powyżej DISTINCT ON (B):

1. Wybierz całą tabelę, wyniki w 5958 wierszach w tym case.

   A: 567.218 ms
   B: 386.673 ms
   

2. Użyj warunku WHERE customer BETWEEN x AND y wynikającego z 1000 wierszy.

   A: 249.136 ms
   B:  55.111 ms
   

3. Wybierz jednego klienta z WHERE customer = x.

   A:   0.143 ms
   B:   0.072 ms
   

Ten sam test powtórzony z indeksem opisanym w drugiej odpowiedzi

CREATE INDEX purchases_3c_idx ON purchases (customer, total DESC, id);

1A: 277.953 ms  
1B: 193.547 ms

2A: 249.796 ms -- special index not used  
2B:  28.679 ms

3A:   0.120 ms  
3B:   0.048 ms

Jest to powszechny największy-N-na-Grupę problem, który ma już dobrze przetestowane i wysoce zoptymalizowane rozwiązania . Osobiście wolę rozwiązanie left join autorstwa Billa Karwina (oryginalny post z wieloma innymi rozwiązaniami).

Zauważ, że kilka rozwiązań tego wspólnego problemu można zaskakująco znaleźć w jednym z najbardziej oficjalnych źródeł, MySQL manual! Zobacz przykłady typowych zapytań:: wiersze zawierające maksimum grupy a Pewna Kolumna .

W Postgres można użyć array_agg w następujący sposób:

SELECT  customer,
        (array_agg(id ORDER BY total DESC))[1],
        max(total)
FROM purchases
GROUP BY customer

To da ci id największy zakup każdego klienta.

Kilka rzeczy do zapamiętania:

• array_agg jest funkcją zbiorczą, więc działa z GROUP BY.
• array_agg pozwala określić zakres zamówienia tylko do siebie, więc nie ogranicza struktury całego zapytania. Istnieje również składnia sortowania Null, jeśli chcesz zrobić coś innego niż domyślne.
• Once we zbudujemy tablicę, weźmiemy pierwszy element. (Tablice Postgres są indeksowane 1, A Nie 0).
• Możesz użyć array_agg w podobny sposób dla trzeciej kolumny wyjściowej, ale max(total) jest prostsze.
• w przeciwieństwie do DISTINCT ON, użycie array_agg pozwala zachować GROUP BY, Jeśli chcesz tego z innych powodów.

Rozwiązanie nie jest zbyt wydajne, jak wskazał Erwin, ze względu na obecność SubQs

select * from purchases p1 where total in
(select max(total) from purchases where p1.customer=customer) order by total desc;

Używam tego sposobu (tylko postgresql): https://wiki.postgresql.org/wiki/First/last_%28aggregate%29

-- Create a function that always returns the first non-NULL item
CREATE OR REPLACE FUNCTION public.first_agg ( anyelement, anyelement )
RETURNS anyelement LANGUAGE sql IMMUTABLE STRICT AS $$
        SELECT $1;
$$;

-- And then wrap an aggregate around it
CREATE AGGREGATE public.first (
        sfunc    = public.first_agg,
        basetype = anyelement,
        stype    = anyelement
);

-- Create a function that always returns the last non-NULL item
CREATE OR REPLACE FUNCTION public.last_agg ( anyelement, anyelement )
RETURNS anyelement LANGUAGE sql IMMUTABLE STRICT AS $$
        SELECT $2;
$$;

-- And then wrap an aggregate around it
CREATE AGGREGATE public.last (
        sfunc    = public.last_agg,
        basetype = anyelement,
        stype    = anyelement
);

Wtedy twój przykład powinien działać prawie tak jak jest:

SELECT FIRST(id), customer, FIRST(total)
FROM  purchases
GROUP BY customer
ORDER BY FIRST(total) DESC;

Zastrzeżenie: ignoruje wiersze NULL

Edycja 1-zamiast tego użyj rozszerzenia postgres

Teraz używam tego sposobu: http://pgxn.org/dist/first_last_agg/

Aby zainstalować na ubuntu 14.04:

apt-get install postgresql-server-dev-9.3 git build-essential -y
git clone git://github.com/wulczer/first_last_agg.git
cd first_last_app
make && sudo make install
psql -c 'create extension first_last_agg'

Jest to rozszerzenie postgres, które daje pierwszy i ostatni funkcje; widocznie szybszy niż powyższy sposób.

Edycja 2-porządkowanie i filtrowanie

Jeśli korzystasz z funkcji agregujących (takich jak te), możesz zamówić wyniki, bez konieczności posiadania już uporządkowanych danych:

http://www.postgresql.org/docs/current/static/sql-expressions.html#SYNTAX-AGGREGATES

Więc równoważnym przykładem z porządkowaniem byłoby coś w stylu:

SELECT first(id order by id), customer, first(total order by id)
  FROM purchases
 GROUP BY customer
 ORDER BY first(total);

Oczywiście możesz zamawiać i filtrować tak, jak uważasz, że pasuje do agregatu; jest to bardzo potężna składnia.

Bardzo szybkie rozwiązanie

SELECT a.* 
FROM
    purchases a 
    JOIN ( 
        SELECT customer, min( id ) as id 
        FROM purchases 
        GROUP BY customer 
    ) b USING ( id );

I naprawdę bardzo szybko, jeśli tabela jest indeksowana przez id:

create index purchases_id on purchases (id);

Zapytanie:

SELECT purchases.*
FROM purchases
LEFT JOIN purchases as p 
ON 
  p.customer = purchases.customer 
  AND 
  purchases.total < p.total
WHERE p.total IS NULL

JAK TO DZIAŁA! (byłem tam)

Chcemy mieć pewność, że mamy tylko najwyższą sumę dla każdego zakupu.

Niektóre teoretyczne rzeczy (pomiń tę część, jeśli chcesz tylko zrozumieć zapytanie)

Niech suma będzie funkcją t (klient,id), gdzie zwraca wartość podaną nazwą i id Aby udowodnić,że podana suma (t(klient, id)) jest najwyższa musimy udowodnić, że Chcemy udowodnić albo

• ∀x T(customer,id) > t (customer,x) (suma ta jest wyższa niż wszystkie inne razem dla tego klienta)

Lub

• ∃x T(customer, id)

Pierwsze podejście będzie nam potrzebne, aby uzyskać wszystkie rekordy dla tej nazwy, które naprawdę nie lubię.

Drugi będzie potrzebował mądrego sposobu, aby powiedzieć, że nie może być rekordu wyższego niż ten.

Powrót do SQL

Jeśli w lewo dołączymy tabelę o nazwie i razem będzie mniej niż w połączonej tabeli:

      LEFT JOIN purchases as p 
      ON 
      p.customer = purchases.customer 
      AND 
      purchases.total < p.total

Upewniamy się, że wszystkie rekordy, które mają inny rekord o wyższej sumie dla tego samego Użytkownika, mają być połączone:

purchases.id, purchases.customer, purchases.total, p.id, p.customer, p.total
1           , Tom           , 200             , 2   , Tom   , 300
2           , Tom           , 300
3           , Bob           , 400             , 4   , Bob   , 500
4           , Bob           , 500
5           , Alice         , 600             , 6   , Alice   , 700
6           , Alice         , 700

To pomoże nam filtrować najwyższą sumę za każdy zakup bez konieczności grupowania:

WHERE p.total IS NULL

purchases.id, purchases.name, purchases.total, p.id, p.name, p.total
2           , Tom           , 300
4           , Bob           , 500
6           , Alice         , 700

Przyjęte rozwiązanie OMG "obsługiwane przez dowolną bazę danych" ma dobrą szybkość z mojego testu.

Tutaj podaję takie samo podejście, ale bardziej kompletne i czyste rozwiązanie każdej bazy danych. Powiązania są brane pod uwagę (Załóżmy, że chcemy uzyskać tylko jeden wiersz dla każdego klienta, nawet wiele rekordów dla maksymalnej sumy na klienta), a inne pola zakupu (np. purchase_payment_id) zostaną wybrane dla prawdziwych pasujących wierszy w tabeli zakupów.

Wspierane przez dowolne baza danych:

select * from purchase
join (
    select min(id) as id from purchase
    join (
        select customer, max(total) as total from purchase
        group by customer
    ) t1 using (customer, total)
    group by customer
) t2 using (id)
order by customer

To zapytanie jest dość szybkie, zwłaszcza gdy w tabeli zakupów znajduje się indeks złożony (klient, suma).

Uwaga:

1. T1, t2 to alias podrzędny, który można usunąć w zależności od bazy danych.

2. Uwaga : klauzula using (...) nie jest obecnie obsługiwana w MS-SQL i Oracle db od tej edycji w styczniu 2017. Musisz to samemu rozszerzyć na np. on t2.id = purchase.id itp. Składnia użytkowa działa w SQLite, MySQL i PostgreSQL.

• Jeśli chcesz wybrać dowolny (według określonego warunku) wiersz z zestawu zagregowanych wierszy.

• Jeśli chcesz użyć innej (sum/avg) funkcji agregującej oprócz max/min. Dlatego nie można używać clue z DISTINCT ON

Możesz użyć następnego zapytania:

SELECT  
    (  
       SELECT **id** FROM t2   
       WHERE id = ANY ( ARRAY_AGG( tf.id ) ) AND amount = MAX( tf.amount )   
    ) id,  
    name,   
    MAX(amount) ma,  
    SUM( ratio )  
FROM t2  tf  
GROUP BY name

Możesz zastąpić amount = MAX( tf.amount ) dowolnym warunkiem z jednym ograniczeniem: to zapytanie podrzędne nie może zwracać więcej niż jednego wiersza

Ale jeśli chcesz robić takie rzeczy, prawdopodobnie Szukam funkcji okien