SQL Join: Auswählen der letzten Datensätze in einer eins-zu-viele Beziehung

Question

Angenommen, ich habe eine Tabelle mit Kunden und eine Tabelle mit Einkäufen. Jeder Einkauf gehört zu einem Kunden. Ich möchte eine Liste aller Kunden zusammen mit ihrem letzten Kauf in einer SELECT-Anweisung erhalten. Was ist die beste Vorgehensweise? 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, date

Und in komplizierteren Situationen, wäre es (leistungsbedingt) vorteilhaft, die Datenbank zu denormalisieren, indem man den letzten Kauf in die Kundentabelle aufnimmt?

Wenn die (Kauf-) id garantiert nach Datum sortiert ist, können die Anweisungen durch die Verwendung von etwas wie LIMIT 1 vereinfacht werden?

Answer 1

Dies ist ein Beispiel für das Größte-N-pro-Gruppe-Problem, das regelmäßig auf StackOverflow auftritt.

So empfehle ich normalerweise, es 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;

Erklärung: Für eine Zeile p1 sollte es keine Zeile p2 mit demselben Kunden und einem späteren Datum geben (oder im Falle von Gleichständen eine spätere id). Wenn das zutrifft, ist p1 der aktuellste Kauf für diesen Kunden.

Was die Indizes betrifft, würde ich einen zusammengesetzten Index in purchase über die Spalten (customer_id, date, id) erstellen. Dadurch könnte der äußere Join mithilfe eines deckenden Index durchgeführt werden. Testen Sie auf Ihrer Plattform, denn die Optimierung hängt von der Implementierung ab. Nutzen Sie die Funktionen Ihres RDBMS, um den Optimierungsplan zu analysieren. Z.B. EXPLAIN auf MySQL.

---

Einige Leute verwenden statt der von mir oben gezeigten Lösung Unterabfragen, aber ich finde meine Lösung erleichtert das Auflösen von Gleichständen.

Answer 2

Sie könnten auch versuchen, dies mit einer Unterauswahl zu tun

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 Abfrage sollte alle Kunden und ihr letztes Kaufdatum verknüpfen.

Answer 3

Ein weiterer Ansatz wäre die Verwendung einer NOT EXISTS Bedingung in Ihrer Join-Bedingung, um später Käufe zu testen:

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

Answer 4

Wenn Sie PostgreSQL verwenden, können Sie DISTINCT ON verwenden, um die erste Zeile in einer Gruppe zu finden.

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-Dokumentation - Distinct On

Beachten Sie, dass das DISTINCT ON-Feld bzw. die Felder - hier customer_id - mit den linksten Feldern in der ORDER BY-Klausel übereinstimmen müssen.

Warnung: Dies ist eine nicht standardmäßige Klausel.

Answer 5

Sie haben die Datenbank nicht angegeben. Wenn es eine ist, die analytische Funktionen zulässt, kann es schneller sein, diesen Ansatz zu verwenden als den GROUP BY-Ansatz (auf jeden Fall schneller in Oracle, wahrscheinlich schneller in den neueren SQL Server-Editionen, weiß nicht ü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