Wybierz pierwszy wiersz w każdej grupie GROUP BY?


1321

Jak sugeruje tytuł, chciałbym wybrać pierwszy wiersz każdego zestawu wierszy zgrupowanych za pomocą GROUP BY.

W szczególności, jeśli mam purchasesstół, który wygląda następująco:

SELECT * FROM purchases;

Mój wynik:

id | klient | całkowity
--- + ---------- + ------
 1 | Joe | 5
 2 | Sally | 3)
 3 | Joe | 2)
 4 | Sally | 1

Chciałbym zapytać o idnajwiększy zakup ( total) dokonany przez każdego customer. Coś takiego:

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

Oczekiwany wynik:

FIRST (id) | klient | PIERWSZY (ogółem)
---------- + ---------- + -------------
        1 | Joe | 5
        2 | Sally | 3)

skoro szukasz tylko każdego największego, dlaczego nie zapytać o MAX(total)?
phil294,

4
@ phil294, kwerenda o maksimum (suma) nie skojarzy tej sumy z wartością „id” wiersza, w którym wystąpiła.
gwideman

Odpowiedzi:


1115

W Oracle 9.2+ (nie 8i + jak pierwotnie podano), 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

Obsługiwane przez dowolną bazę danych:

Ale musisz dodać logikę, aby zerwać więzi:

  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

2
Informix 12.x obsługuje również funkcje okna (CTE musi zostać przekonwertowany na tabelę pochodną). A Firebird 3.0 będzie także obsługiwał funkcje Window
a_horse_w__na_nazwa

37
ROW_NUMBER() OVER(PARTITION BY [...])wraz z kilkoma innymi optymalizacjami pomógł mi skrócić zapytanie z 30 sekund do kilku milisekund. Dzięki! (PostgreSQL 9.2)
Sam

8
Jeśli istnieje wiele zakupów z równie najwyższymi totaldla jednego klienta, pierwsze zapytanie zwraca dowolnego zwycięzcę (w zależności od szczegółów implementacji; idmoże ulec zmianie przy każdym wykonaniu!). Zazwyczaj (nie zawsze) chciałbyś mieć jeden wiersz na klienta, zdefiniowany przez dodatkowe kryteria, takie jak „ten z najmniejszym id”. Aby to naprawić, dołącz iddo ORDER BYlisty row_number(). Otrzymujesz taki sam wynik jak w drugim zapytaniu, co jest bardzo nieefektywne w tym przypadku. Będziesz także potrzebować innego podzapytania dla każdej dodatkowej kolumny.
Erwin Brandstetter,

2
Google BigQuery obsługuje także polecenie ROW_NUMBER () pierwszego zapytania. Dla nas
zadziałało

2
Zauważ, że pierwsza wersja z funkcją okna działa od wersji SQLite 3.25.0: sqlite.org/windowfunctions.html#history
brianz

1147

W PostgreSQL jest to zwykle 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 wyraźny) z liczbami porządkowymi kolumn wyjściowych:

SELECT DISTINCT ON (2)
       id, customer, total
FROM   purchases
ORDER  BY 2, 3 DESC, 1;

Jeśli totalmoże mieć wartość NULL (nie zaszkodzi w żaden sposób, ale będziesz chciał dopasować istniejące indeksy ):

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

Najważniejsze punkty

  • DISTINCT ONjest rozszerzeniem standardu PostgreSQL (gdzie zdefiniowana jest tylko DISTINCTcała SELECTlista).

  • Wymień dowolną liczbę wyrażeń w DISTINCT ONklauzuli, połączona wartość wiersza definiuje duplikaty. Instrukcja:

    Oczywiście dwa wiersze są uważane za odrębne, jeśli różnią się co najmniej jedną wartością kolumny. W tym porównaniu wartości zerowe są uważane za równe.

    Odważny nacisk moje.

  • DISTINCT ONmożna połączyć z ORDER BY. Wyrażenia wiodące w ORDER BYmuszą znajdować się w zestawie wyrażeń w DISTINCT ON, ale możesz dowolnie zmieniać porządek między nimi. Przykład. Możesz dodać dodatkowe wyrażenia, aby ORDER BYwybrać konkretny wiersz z każdej grupy rówieśników. Lub, jak to mówi instrukcja :

    DISTINCT ONWyrażenie (y) musi być zgodna z najbardziej z lewej strony ORDER BY (y) ekspresyjne. ORDER BYPunkt zazwyczaj zawierają dodatkowe wyrażeniu (i), które określają pożądane pierwszeństwo rzędach wewnątrz każdej DISTINCT ONgrupy.

    Dodałem idjako ostatni element do zerwania więzi:
    „Wybierz wiersz z najmniejszymi idz każdej grupy dzielącymi najwyższe total”.

    Aby uporządkować wyniki w sposób, który nie zgadza się z kolejnością sortowania określającą pierwszą na grupę, można zagnieździć powyższe zapytanie w zapytaniu zewnętrznym z innym zapytaniem ORDER BY. Przykład.

  • Jeśli totalmoże mieć wartość NULL, najprawdopodobniej chcesz wiersz o największej wartości innej niż null. Dodaj NULLS LASTjak pokazano. Widzieć:

  • SELECTLista nie jest ograniczony wyrażeń DISTINCT ONlub ORDER BYw jakikolwiek sposób. (Niepotrzebne w prostym przypadku powyżej):

    • Nie musisz dołączać żadnych wyrażeń do DISTINCT ONlub ORDER BY.

    • Państwo może zawierać dowolny inny wyraz w SELECTliście. Jest to pomocne w zastępowaniu znacznie bardziej złożonych zapytań podkwerendami i funkcjami agregacji / okna.

  • Testowałem z wersjami Postgres 8.3 - 12. Ale ta funkcja istnieje przynajmniej od wersji 7.1, więc w zasadzie zawsze.

Indeks

Doskonały wskaźnik dla powyższego zapytania byłoby Indeks Multi-column obejmujące wszystkie trzy kolumny i dopasowanie sekwencji z pasującymi 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 kluczowa jest wydajność odczytu dla konkretnego zapytania. Jeśli masz DESC NULLS LASTw zapytaniu, użyj tego samego w indeksie, aby kolejność sortowania była zgodna i indeks miał zastosowanie.

Optymalizacja efektywności / wydajności

Zważ koszty i korzyści przed utworzeniem dostosowanych indeksów dla każdego zapytania. Potencjał powyższego wskaźnika w dużej mierze zależy od dystrybucji danych .

Indeks jest używany, ponieważ dostarcza wstępnie posortowane dane. W Postgresie 9.2 lub nowszym zapytanie może korzystać ze skanowania indeksu tylko wtedy, gdy indeks jest mniejszy niż tabela bazowa. Indeks należy jednak skanować w całości.

Reper

Miałem tutaj prosty test porównawczy, który jest już nieaktualny. W tej osobnej odpowiedzi zastąpiłem ją szczegółowym testem porównawczym .


28
To świetna odpowiedź dla większości rozmiarów baz danych, ale chcę zauważyć, że w miarę zbliżania się ~ milion wierszy DISTINCT ONstaje się bardzo wolny. Implementacja zawsze sortuje całą tabelę i skanuje ją w poszukiwaniu duplikatów, ignorując wszystkie indeksy (nawet jeśli utworzono wymagany indeks wielokolumnowy). Zobacz objaśnieniextended.com/2009/05/ 03/ postgresql- optimizing- distinct, aby znaleźć możliwe rozwiązanie.
Meekohi

14
Użycie rzędnych do „skrócenia kodu” to okropny pomysł. Co powiesz na pozostawienie nazw kolumn, aby były czytelne?
KOTJMF

13
@KOTJMF: Sugeruję więc, aby wybrać osobiste preferencje. Pokazuję obie opcje edukacji. Skrócona składnia może być użyteczna w przypadku długich wyrażeń na SELECTliście.
Erwin Brandstetter,

1
@jangorecki: Oryginalny test porównawczy pochodzi z 2011 roku, nie mam już konfiguracji. Ale nadszedł czas, aby przeprowadzić testy z pg 9.4 i pg 9.5. Zobacz szczegóły w dodanej odpowiedzi. . Możesz dodać komentarz z wynikiem swojej instalacji poniżej?
Erwin Brandstetter,

2
@PirateApp: Nie z góry mojej głowy. DISTINCT ONnadaje się tylko do uzyskania jednego wiersza na grupę rówieśników.
Erwin Brandstetter,

134

Reper

Testowanie najbardziej interesujących kandydatów z PostgreSQL 9.4 i 9.5 z połowy realistycznym stole 200k wierszy w purchasesi 10k odrębnegocustomer_id ( AVG. 20 wierszy na klienta ).

W przypadku Postgres 9.5 przeprowadziłem drugi test z efektywnie 86446 różnymi klientami. Zobacz poniżej ( średnio 2,3 wiersza na klienta ).

Ustawiać

Stół główny

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 dodane poniżej) i liczby całkowitej, customer_idponieważ jest to bardziej typowa konfiguracja. Dodano również, some_columnaby uzupełnić zwykle więcej kolumn.

Dummy data, PK, index - typowa tabela zawiera 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 tabela - dla zapytania nadrzędnego

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 drugim teście dla 9.5 użyłem tej samej konfiguracji, ale z random() * 100000generowaniem, customer_idaby uzyskać tylko kilka wierszy na customer_id.

Rozmiary obiektów dla tabeli purchases

Wygenerowano za pomocą tego zapytania .

               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 |              |

Zapytania

1. row_number()w CTE ( patrz inna odpowiedź )

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()w podzapytaniu (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 inną odpowiedź )

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

4. rCTE z LATERALpodzapytaniem ( patrz 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. customerstół z LATERAL( patrz 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( patrz inna odpowiedź )

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 powyższych zapytań z EXPLAIN ANALYZE(i wyłączonymi wszystkimi opcjami ), najlepszy z 5 uruchomień .

Wszystkie zapytań używany jest Index Skanuj tylko na purchases2_3c_idx(wśród innych etapów). Niektóre z nich tylko dla mniejszego rozmiaru indeksu, inne bardziej efektywnie.

A. Postgres 9,4 z 200 tys. Rzędów 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. To samo co B., ale z ~ 2,3 wierszami 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

Powiązane testy porównawcze

Oto nowy test „ogr” z 10 milionami wierszy i 60 tysiącami unikalnych „klientów” na Postgresie 11.5 (aktualny od września 2019). Wyniki są nadal zgodne z tym, co widzieliśmy do tej pory:

Oryginalny (nieaktualny) test porównawczy z 2011 roku

Przeprowadziłem trzy testy z PostgreSQL 9.1 na rzeczywistej tabeli zawierającej 65579 wierszy i indeksach btree w jednej kolumnie dla każdej z trzech zaangażowanych kolumn i najlepszy czas wykonania wynosił 5 uruchomień.
Porównanie pierwszego zapytania @OMGPonies ( A) z powyższym DISTINCT ONrozwiązaniem ( B):

  1. Wybierz całą tabelę, w tym przypadku powstanie 5958 wierszy.

    A: 567.218 ms
    B: 386.673 ms
  2. Użyj warunku, w WHERE customer BETWEEN x AND ywyniku którego powstanie 1000 wierszy.

    A: 249.136 ms
    B:  55.111 ms
  3. Wybierz pojedynczego klienta za pomocą WHERE customer = x.

    A:   0.143 ms
    B:   0.072 ms

Ten sam test powtórzono 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

5
Dzięki za świetny test. Zastanawiałem się, czy zapytanie o dane zdarzeń, w których masz znacznik czasu zamiast sumy , skorzystałoby z nowego indeksu BRIN. Może to potencjalnie przyspieszyć zapytania tymczasowe.
jangorecki

3
@jangorecki: Każda ogromna tabela z fizycznie posortowanymi danymi może korzystać z indeksu BRIN.
Erwin Brandstetter,

@ErwinBrandstetter W przykładach 2. row_number()i 5. customer table with LATERAL, co zapewnia, że ​​identyfikator będzie najmniejszy?
Artem Novikov

@ArtemNovikov: Nic. Celem jest odzyskanie według customer_id wiersza o najwyższej wartości total. Jest to mylący przypadek w danych testowych pytania, że idw wybranych wierszach zdarza się również, że jest najmniejszy na customer_id.
Erwin Brandstetter,

1
@ArtemNovikov: Aby zezwolić na skanowanie tylko za pomocą indeksu.
Erwin Brandstetter,

55

To jest powszechne problem, który ma już dobrze przetestowane i wysoce zoptymalizowane rozwiązania . Osobiście wolę lewe rozwiązanie łączenia autorstwa Billa Karwina ( oryginalny post z wieloma innymi rozwiązaniami ).

Zauważ, że wiele rozwiązań tego powszechnego problemu można zaskakująco znaleźć w jednym z najbardziej oficjalnych źródeł, podręczniku MySQL ! Zobacz przykłady typowych zapytań: Wiersze trzymające grupowo maksimum określonej kolumny .


22
W jaki sposób podręcznik MySQL jest w jakikolwiek sposób „oficjalny” w przypadku pytań Postgres / SQLite (nie wspominając o SQL)? Ponadto, dla jasności, DISTINCT ONwersja jest znacznie krótsza, prostsza i ogólnie działa lepiej w Postgresie niż alternatywy z samodzielnym LEFT JOINlub pół-anty-złączeniem z NOT EXISTS. Jest również „dobrze przetestowany”.
Erwin Brandstetter,

3
Oprócz tego, co napisał Erwin, powiedziałbym, że używanie funkcji okna (która jest obecnie powszechną funkcją SQL) jest prawie zawsze szybsze niż łączenie z tabelą pochodną
a_horse_w_na_nazwie

6
Świetne referencje. Nie wiedziałem, że to się nazywa problem największej liczby grup. Dziękuję Ci.
David Mann

Pytanie nie dotyczy największej liczby n na grupę, ale pierwszej liczby n.
reinierpost

1
W przypadku dwóch pól zamówień, które próbowałem, „rozwiązanie pozostawione przez Billa Karwina” daje słabą wydajność. Zobacz mój komentarz poniżej stackoverflow.com/a/8749095/684229
Johnny Wong

30

W Postgres możesz używać array_aggtego 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 idnajwiększy zakup każdego klienta.

Kilka rzeczy do zapamiętania:

  • array_aggjest funkcją agregującą, więc działa z GROUP BY.
  • array_aggpozwala określić zakres zamówienia tylko do siebie, więc nie ogranicza struktury całego zapytania. Istnieje również składnia sposobu sortowania wartości NULL, jeśli chcesz zrobić coś innego niż domyślny.
  • Po zbudowaniu tablicy bierzemy pierwszy element. (Tablice Postgres są indeksowane 1, a nie 0).
  • Możesz użyć array_aggw podobny sposób dla trzeciej kolumny wyników, ale max(total)jest to prostsze.
  • W przeciwieństwie do DISTINCT ONużywania, array_aggmożesz zachować swoje GROUP BY, na wypadek, gdybyś chciał z innych powodów.

14

Rozwiązanie to nie jest bardzo wydajne, jak wskazał Erwin, ze względu na obecność SubQ

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

Dzięki, tak, zgadzam się z tobą, połączenie między subq a zewnętrznym zapytaniem faktycznie trwa dłużej. „In” nie będzie tutaj problemem, ponieważ subq spowoduje tylko jeden wiersz. BTW, na jaki błąd składniowy wskazujesz?
user2407394,

ohh .. kiedyś edytowany „Teradata” .. edytowany teraz .. jednak zerwanie więzi nie jest tutaj wymagane, ponieważ musi znaleźć najwyższą sumę dla każdego klienta ..
user2407394

Czy wiesz, że w przypadku remisu dostajesz wiele wierszy dla jednego klienta? To, czy jest to pożądane, zależy od dokładnych wymagań. Zwykle tak nie jest. W przypadku tego pytania tytuł jest dość jasny.
Erwin Brandstetter,

Nie jest to jasne z pytania, jeśli ten sam klient ma zakup = Max dla 2 różnych identyfikatorów, myślę, że powinniśmy wyświetlić oba.
user2407394

10

Używam w ten sposób (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
);

Wówczas 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;

CAVEAT: Ignoruje NULL wiersze


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 pierwszą i ostatnią funkcję; najwyraźniej szybszy niż powyższy sposób.


Edycja 2 - Porządkowanie i filtrowanie

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

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

Tak więc równoważny przykład z zamówieniem wyglądałby mniej więcej tak:

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 porządkować i filtrować według własnego uznania; to bardzo potężna składnia.


Korzystając również z tego niestandardowego podejścia do funkcji. Wystarczająco uniwersalny i prosty. Po co komplikować, czy to znacznie mniej wydajne rozwiązanie niż inne?
Sergey Shcherbakov

9

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łam tam)

Chcemy mieć pewność, że mamy tylko najwyższą sumę na każdy zakup.


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

Niech Total będzie funkcją T (klient, identyfikator), gdzie zwraca wartość podaną nazwą i identyfikatorem Aby udowodnić, że podana suma (T (klient, identyfikator)) jest najwyższa, musimy udowodnić, że chcemy udowodnić

  • ∀x T (klient, identyfikator)> T (klient, x) (ta suma jest wyższa niż wszystkie inne sumy dla tego klienta)

LUB

  • ¬∃x T (klient, identyfikator) <T (klient, x) (dla tego klienta nie istnieje wyższa suma)

Pierwsze podejście będzie wymagało od nas zebrania wszystkich rekordów dotyczących tego imienia, które tak naprawdę nie lubię.

Drugi będzie wymagał sprytnego sposobu na stwierdzenie, że rekord nie może być wyższy niż ten.


Powrót do SQL

Jeśli opuścimy, dołącza do tabeli w nazwie i suma jest mniejsza niż połączona tabela:

      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, zostaną dołą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

Pomoże nam to przefiltrować najwyższą sumę dla każdego zakupu 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

I takiej odpowiedzi potrzebujemy.


8

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);

Klauzula USING jest bardzo standardowa. Tyle, że niektóre mniejsze systemy baz danych tego nie mają.
Holger Jakobs

2
Nie znajduje to zakupów klientów z największą sumą
Johnny Wong

7

W SQL Server możesz to zrobić:

SELECT *
FROM (
SELECT ROW_NUMBER()
OVER(PARTITION BY customer
ORDER BY total DESC) AS StRank, *
FROM Purchases) n
WHERE StRank = 1

Wyjaśnienie: w tym przypadku Grupowanie według odbywa się na podstawie klienta, a następnie zamówienie jest sumowane, następnie każda taka grupa otrzymuje numer seryjny jako StRank i pozyskujemy pierwszego 1 klienta, którego StRank wynosi 1


Dziękuję Ci! Działało to doskonale i było bardzo łatwe do zrozumienia i wdrożenia.
ruohola


4

W PostgreSQL inną możliwością jest użycie first_valuefunkcji okna w połączeniu z SELECT DISTINCT:

select distinct customer_id,
                first_value(row(id, total)) over(partition by customer_id order by total desc, id)
from            purchases;

Utworzyłem kompozyt (id, total), więc obie wartości są zwracane przez ten sam agregat. Oczywiście możesz zawsze złożyć wniosek first_value()dwukrotnie.


3

Akceptowane rozwiązanie „Obsługiwane przez dowolną bazę danych” firmy OMG Kucyki ma dobrą prędkość z mojego testu.

Tutaj zapewniam to samo podejście, ale bardziej kompletne i czyste rozwiązanie dla dowolnej bazy danych. Wiązania są brane pod uwagę (zakładamy chęć uzyskania tylko jednego wiersza dla każdego klienta, nawet wielu rekordów dla maksymalnej sumy przypadającej na jednego klienta), a inne pola zakupu (np. Id_płatności_kupu) zostaną wybrane dla rzeczywistych pasujących wierszy w tabeli zakupów.

Obsługiwane przez dowolną bazę 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, taki jak (klient, ogółem).

Uwaga:

  1. t1, t2 to alias podzapytania, który można usunąć w zależności od bazy danych.

  2. Uwaga : using (...)klauzula nie jest obecnie obsługiwana w MS-SQL i Oracle db od tej edycji w styczniu 2017 r. Musisz ją rozwinąć do np. on t2.id = purchase.idItp. Składnia USING działa w SQLite, MySQL i PostgreSQL.


2

Snowflake / Teradata obsługuje QUALIFYklauzulę, która działa jak HAVINGdla funkcji okienkowych:

SELECT id, customer, total
FROM PURCHASES
QUALIFY ROW_NUMBER() OVER(PARTITION BY p.customer ORDER BY p.total DESC) = 1

1
  • Jeśli chcesz wybrać dowolny (według określonych warunków) wiersz ze zbioru zagregowanych wierszy.

  • Jeśli chcesz użyć innej ( sum/avg) funkcji agregującej oprócz max/min. W związku z tym nie można używać pojęcia zDISTINCT ON

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

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żna zastąpić amount = MAX( tf.amount )dowolnym warunkiem z jednym ograniczeniem: to podzapytanie nie może zwracać więcej niż jednego wiersza

Ale jeśli chcesz robić takie rzeczy, prawdopodobnie szukasz funkcji okna


1

W przypadku SQl Server najskuteczniejszym sposobem jest:

with
ids as ( --condition for split table into groups
    select i from (values (9),(12),(17),(18),(19),(20),(22),(21),(23),(10)) as v(i) 
) 
,src as ( 
    select * from yourTable where  <condition> --use this as filter for other conditions
)
,joined as (
    select tops.* from ids 
    cross apply --it`s like for each rows
    (
        select top(1) * 
        from src
        where CommodityId = ids.i 
    ) as tops
)
select * from joined

i nie zapomnij utworzyć indeksu klastrowego dla używanych kolumn

Korzystając z naszej strony potwierdzasz, że przeczytałeś(-aś) i rozumiesz nasze zasady używania plików cookie i zasady ochrony prywatności.
Licensed under cc by-sa 3.0 with attribution required.