wake-up-neo.com

SQL-Join: Auswahl der letzten Datensätze in einer Eins-zu-Viele-Beziehung

Angenommen, ich habe eine Tabelle mit Kunden und eine Tabelle mit Einkäufen. Jeder Einkauf gehört einem Kunden. Ich möchte eine Liste aller Kunden zusammen mit ihrem letzten Einkauf in einer SELECT-Anweisung erhalten. Was ist die beste Praxis? Irgendwelche Ratschläge zum Erstellen von Indizes?

Bitte verwenden Sie diese Tabellen-/Spaltennamen in Ihrer Antwort:

  • kunde: ID, Name
  • kauf: id, customer_id, item_id, Datum

Und in komplizierteren Situationen wäre es (in Bezug auf die Leistung) vorteilhaft, die Datenbank zu denormalisieren, indem der letzte Kauf in die Kundentabelle gestellt wird.

Wenn die (Kauf-) ID garantiert nach Datum sortiert ist, können die Anweisungen mit etwas wie LIMIT 1 Vereinfacht werden?

252
netvope

Dies ist ein Beispiel für das Problem greatest-n-per-group, Das regelmäßig in StackOverflow auftritt.

So empfehle ich normalerweise, das Problem zu lösen:

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;

Erläuterung: Bei einer gegebenen Zeile p1 Sollte es keine Zeile p2 Mit demselben Kunden und einem späteren Datum geben (oder im Falle von Bindungen eine spätere id). Wenn dies zutrifft, ist p1 Der letzte Kauf für diesen Kunden.

In Bezug auf Indizes würde ich einen zusammengesetzten Index in purchase über den Spalten erstellen (customer_id, date, id). Dies kann dazu führen, dass der äußere Join mit einem abdeckenden Index erstellt wird. Testen Sie auf jeden Fall auf Ihrer Plattform, da die Optimierung von der Implementierung abhängt. Verwenden Sie die Funktionen Ihres RDBMS, um den Optimierungsplan zu analysieren. Z.B. EXPLAIN unter MySQL.


Einige Leute verwenden Unterabfragen anstelle der oben gezeigten Lösung, aber ich finde, meine Lösung erleichtert das Auflösen von Bindungen.

397
Bill Karwin

Sie können dies auch mit einer Unterauswahl versuchen

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

Die Auswahl sollte an allen Kunden und ihrem letzten Kaufdatum teilnehmen.

108
Adriaan Stander

Sie haben die Datenbank nicht angegeben. Wenn es sich um eine Methode handelt, die analytische Funktionen ermöglicht, ist diese Methode möglicherweise schneller als die Methode GROUP BY (definitiv schneller in Oracle, höchstwahrscheinlich schneller in den späten SQL Server-Editionen, keine Kenntnis über andere).

Die Syntax in SQL Server wäre:

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
24

Ein anderer Ansatz wäre die Verwendung eines NOT EXISTS Bedingung in Ihrer Beitrittsbedingung zum Testen auf spätere Käufe:

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
   )
)
22
Stefan Haberl

Ich habe diesen Thread als Lösung für mein Problem gefunden.

Aber als ich sie ausprobierte, war die Leistung gering. Bellow ist mein Vorschlag für eine bessere Leistung.

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 

Hoffe das wird hilfreich sein.

12
Mathee

Versuchen Sie dies, es wird helfen.

Ich habe dies in meinem Projekt verwendet.

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]
6
Rahul Murari

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

Die Aggregatfunktion max() stellt sicher, dass der letzte Kauf aus jeder Gruppe ausgewählt wird (setzt jedoch voraus, dass die Datumsspalte ein Format hat, bei dem max () den letzten Kauf angibt - was normalerweise der Fall ist). Wenn Sie Einkäufe mit demselben Datum abwickeln möchten, können Sie max(p.date, p.id) verwenden.

In Bezug auf Indizes würde ich beim Kauf einen Index mit (customer_id, date, [alle anderen Kaufspalten, die Sie in Ihrer Auswahl zurückgeben möchten]) verwenden.

Der LEFT OUTER JOIN (Im Gegensatz zu INNER JOIN) Stellt sicher, dass Kunden, die noch nie einen Kauf getätigt haben, ebenfalls eingeschlossen sind.

3
Mark

Wenn Sie PostgreSQL verwenden, können Sie mit DISTINCT ON Die erste Zeile in einer Gruppe suchen.

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

PostgreSQL Docs - Distinct On

Beachten Sie, dass die Felder DISTINCT ON - hier customer_id - mit den Feldern ganz links in der Klausel ORDER BY Übereinstimmen müssen.

Vorsichtsmaßnahme: Dies ist eine nicht standardmäßige Klausel.

2
Tate Thurston

Bitte versuchen Sie dies,

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;
1
Milad Shahbazi