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

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?

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

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.

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

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

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.

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.

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]

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.

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;