Łączenie SQL: wybieranie ostatnich rekordów w relacji jeden do wielu


298

Załóżmy, że mam tabelę klientów i tabelę zakupów. Każdy zakup należy do jednego klienta. Chcę uzyskać listę wszystkich klientów wraz z ich ostatnim zakupem w jednym wyciągu SELECT. Jaka jest najlepsza praktyka? Wszelkie porady dotyczące budowania indeksów?

Proszę użyć tych nazw tabel / kolumn w swojej odpowiedzi:

  • klient: identyfikator, imię i nazwisko
  • zakup: identyfikator, identyfikator klienta, identyfikator przedmiotu, data

A czy w bardziej skomplikowanych sytuacjach (pod względem wydajności) korzystne byłoby denormalizowanie bazy danych poprzez umieszczenie ostatniego zakupu w tabeli klientów?

Jeśli gwarantuje się, że identyfikator (zakupu) jest posortowany według daty, czy można uprościć wyciągi, używając czegoś takiego LIMIT 1?


Tak, może być warte denormalizacji (jeśli znacznie poprawi wydajność, o czym można się przekonać, testując obie wersje). Ale wady denormalizacji są zwykle warte uniknięcia.
Vince Bowdren

Odpowiedzi:


449

To jest przykład greatest-n-per-groupproblemu, który pojawiał się regularnie na StackOverflow.

Oto jak zwykle zalecam rozwiązanie tego problemu:

SELECT c.*, p1.*
FROM customer c
JOIN purchase p1 ON (c.id = p1.customer_id)
LEFT OUTER JOIN purchase p2 ON (c.id = p2.customer_id AND 
    (p1.date < p2.date OR (p1.date = p2.date AND p1.id < p2.id)))
WHERE p2.id IS NULL;

Objaśnienie: w przypadku wiersza p1nie powinno być wiersza p2z tym samym klientem i datą późniejszą (lub w przypadku powiązań późniejszą id). Kiedy uznamy, że to prawda, p1jest to ostatni zakup dla tego klienta.

W odniesieniu do wskaźników, by utworzyć wskaźnik związek w purchaseciągu kolumn ( customer_id, date, id). To może pozwolić na wykonanie połączenia zewnętrznego za pomocą indeksu pokrywającego. Testuj na swojej platformie, ponieważ optymalizacja zależy od implementacji. Skorzystaj z funkcji RDBMS, aby przeanalizować plan optymalizacji. Np. EXPLAINNa MySQL.


Niektóre osoby używają podkwerend zamiast rozwiązania pokazanego powyżej, ale uważam, że moje rozwiązanie ułatwia rozwiązywanie powiązań.


3
Ogólnie rzecz biorąc, korzystnie. Zależy to jednak od marki używanej bazy danych oraz ilości i dystrybucji danych w bazie danych. Jedynym sposobem na uzyskanie dokładnej odpowiedzi jest przetestowanie obu rozwiązań pod kątem danych.
Bill Karwin

27
Jeśli chcesz uwzględnić klientów, którzy nigdy nie dokonali zakupu, zmień JOIN zakup p1 ON (c.id = p1.customer_id) na LEFT JOIN zakup p1 ON (c.id = p1.customer_id)
GordonM

5
@russds, potrzebujesz unikalnej kolumny, której możesz użyć do rozwiązania remisu. Nie ma sensu mieć dwóch identycznych wierszy w relacyjnej bazie danych.
Bill Karwin,

6
Jaki jest cel „WHERE p2.id IS NULL”?
clu

3
to rozwiązanie działa tylko wtedy, gdy istnieje więcej niż 1 dokumentacja zakupu. istnieje link 1: 1, NIE działa. tam musi być „GDZIE (p2.id IS NULL lub p1.id = p2.id)
Bruno Jennrich

126

Możesz także spróbować to zrobić za pomocą wyboru podrzędnego

SELECT  c.*, p.*
FROM    customer c INNER JOIN
        (
            SELECT  customer_id,
                    MAX(date) MaxDate
            FROM    purchase
            GROUP BY customer_id
        ) MaxDates ON c.id = MaxDates.customer_id INNER JOIN
        purchase p ON   MaxDates.customer_id = p.customer_id
                    AND MaxDates.MaxDate = p.date

Wybrani powinni dołączyć do wszystkich klientów i ich daty ostatniego zakupu.


4
Dzięki tym właśnie mnie uratował - to rozwiązanie wydaje się bardziej reasable i utrzymaniu potem inni jej nie wymieniono + specyficznych dla danego produktu
Daveo

Jak zmodyfikowałbym to, gdybym chciał zdobyć klienta, nawet jeśli nie było żadnych zakupów?
clu

3
@clu: Zmiana INNER JOINDo LEFT OUTER JOIN.
Sasha Chedygov

3
Wygląda na to, że zakłada się, że tego dnia jest tylko jeden zakup. Gdyby były dwa, dostałbyś dwa wiersze wyjściowe dla jednego klienta, tak myślę?
artfulrobot

1
@IstiaqueAhmed - ostatnie WEJŚCIE WEWNĘTRZNE pobiera tę wartość Max (data) i przywiązuje ją z powrotem do tabeli źródłowej. Bez tego połączenia jedynymi informacjami, które można uzyskać z purchasetabeli, są data i identyfikator_użytkownika, ale zapytanie wymaga podania wszystkich pól z tabeli.
Laughing Vergil,

26

Nie określono bazy danych. Jeśli jest taka, która pozwala na funkcje analityczne, może być szybsze zastosowanie tego podejścia niż GROUP BY (zdecydowanie szybsze w Oracle, najprawdopodobniej szybsze w późnych edycjach SQL Server, nie wiem o innych).

Składnia w SQL Server to:

SELECT c.*, p.*
FROM customer c INNER JOIN 
     (SELECT RANK() OVER (PARTITION BY customer_id ORDER BY date DESC) r, *
             FROM purchase) p
ON (c.id = p.customer_id)
WHERE p.r = 1

10
To zła odpowiedź na pytanie, ponieważ używasz „RANK ()” zamiast „ROW_NUMBER ()”. RANK nadal da ci ten sam problem, gdy dwa zakupy mają dokładnie tę samą datę. Tak właśnie działa funkcja Ranking; jeśli najlepsze 2 pasują, obaj otrzymują przypisaną wartość 1, a trzeci rekord otrzymuje wartość 3. W Row_Number nie ma remisu, jest unikalny dla całej partycji.
MikeTeeVee,

4
Próbując tutaj podejść Billa Karwina do podejścia Madaliny, z włączonymi planami wykonania na serwerze SQL 2008, zauważyłem, że podejście Billa Karwina kosztowało 43% w porównaniu z podejściem Madaliny, które wykorzystało 57% - więc pomimo bardziej eleganckiej składni tej odpowiedzi, nadal faworyzuje wersję Billa!
Shawson,

26

Innym podejściem byłoby użycie NOT EXISTSwarunku w warunku łączenia w celu przetestowania pod kątem późniejszych zakupów:

SELECT *
FROM customer c
LEFT JOIN purchase p ON (
       c.id = p.customer_id
   AND NOT EXISTS (
     SELECT 1 FROM purchase p1
     WHERE p1.customer_id = c.id
     AND p1.id > p.id
   )
)

Czy możesz wyjaśnić tę AND NOT EXISTSczęść prostymi słowami?
Istiaque Ahmed

Wybór podrzędny sprawdza tylko, czy jest wiersz o wyższym identyfikatorze. Otrzymasz wiersz w zestawie wyników, jeśli nie zostanie znaleziony żaden z wyższym identyfikatorem. To powinna być wyjątkowa najwyższa.
Stefan Haberl

2
To jest dla mnie najbardziej czytelne rozwiązanie. Jeśli to jest ważne.
fguillen

:) Dzięki. Zawsze dążę do jak najbardziej czytelnego rozwiązania, ponieważ jest to ważne.
Stefan Haberl

19

Znalazłem ten wątek jako rozwiązanie mojego problemu.

Ale kiedy spróbowałem, wydajność była niska. Poniżej moja sugestia dotycząca lepszej wydajności.

With MaxDates as (
SELECT  customer_id,
                MAX(date) MaxDate
        FROM    purchase
        GROUP BY customer_id
)

SELECT  c.*, M.*
FROM    customer c INNER JOIN
        MaxDates as M ON c.id = M.customer_id 

Mam nadzieję, że to będzie pomocne.


żeby dostać tylko 1, użyłem top 1i ordered it byMaxDatedesc
Roshna Omer

1
jest to łatwe i bezpośrednie rozwiązanie, w MOIM przypadku (wielu klientów, kilka zakupów) 10% szybciej niż rozwiązanie @Stefan Haberl i ponad 10 razy lepsze niż zaakceptowana odpowiedź
Juraj Bezručka

Świetna sugestia wykorzystująca wspólne wyrażenia tabelowe (CTE) do rozwiązania tego problemu. To znacznie poprawiło wydajność zapytań w wielu sytuacjach.
AdamsTips

Najlepsza odpowiedź imo, łatwa do odczytania, klauzula MAX () zapewnia doskonałą wydajność w porównaniu z ORDER BY + LIMIT 1
mrj

10

Jeśli używasz PostgreSQL, możesz użyć DISTINCT ONdo znalezienia pierwszego wiersza w grupie.

SELECT customer.*, purchase.*
FROM customer
JOIN (
   SELECT DISTINCT ON (customer_id) *
   FROM purchase
   ORDER BY customer_id, date DESC
) purchase ON purchase.customer_id = customer.id

Dokumenty PostgreSQL - Wyraźnie włączone

Zauważ, że DISTINCT ONpola (pola) - tutaj customer_id- muszą pasować do pól znajdujących się najbardziej po lewej stronie w ORDER BYklauzuli.

Zastrzeżenie: jest to niestandardowa klauzula.


8

Spróbuj tego, to pomoże.

Użyłem tego w moim projekcie.

SELECT 
*
FROM
customer c
OUTER APPLY(SELECT top 1 * FROM purchase pi 
WHERE pi.customer_id = c.Id order by pi.Id desc) AS [LastPurchasePrice]

Skąd pochodzi alias „p”?
TiagoA

to nie działa dobrze .... trwało wiecznie, gdzie inne przykłady zajęły 2 sekundy na zestawie danych, który mam ....
Joel_J

3

Testowane na SQLite:

SELECT c.*, p.*, max(p.date)
FROM customer c
LEFT OUTER JOIN purchase p
ON c.id = p.customer_id
GROUP BY c.id

Funkcja max()agregująca upewni się, że z każdej grupy wybrano ostatni zakup (ale zakłada, że ​​kolumna daty ma format, w którym max () podaje ostatnie - co zwykle ma miejsce). Jeśli chcesz obsługiwać zakupy z tą samą datą, możesz użyćmax(p.date, p.id) .

Jeśli chodzi o indeksy, użyłbym indeksu przy zakupie z (identyfikator klienta, data, [dowolne inne kolumny zakupów, które chcesz zwrócić w wybranym]).

LEFT OUTER JOIN( W przeciwieństwie do INNER JOIN) zapewni uwzględnienie również klientów, którzy nigdy nie dokonali zakupu.


nie będzie działał w t-sql, ponieważ select c. * ma kolumny nie w grupie według klauzuli
Joel_J

1

Spróbuj tego,

SELECT 
c.Id,
c.name,
(SELECT pi.price FROM purchase pi WHERE pi.Id = MAX(p.Id)) AS [LastPurchasePrice]
FROM customer c INNER JOIN purchase p 
ON c.Id = p.customerId 
GROUP BY c.Id,c.name;
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.