SQL-Verknüpfung: Auswählen der letzten Datensätze in einer Eins-zu-viele-Beziehung

Angenommen ich habe eine Tabelle Kunden und eine Tabelle der Einkäufe. Jedem Kauf gehört einem Kunden. Ich möchte eine Liste aller Kunden, die zusammen mit Ihren letzten Einkauf in einer SELECT-Anweisung. Was ist die beste Praxis? Jede Beratung über die Erstellung von Indizes?

Bitte verwenden Sie diese Tabelle/Spaltennamen in Ihrer Antwort:

Kunde: id, name
kaufen: id, kunden_id, item_id, Datum

Und in komplizierteren Situationen, wäre es (performance-wise) positiv auf denormalize der Datenbank, indem Sie den letzten Kauf in die customer-Tabelle?

Wenn die (Kauf -) id ist garantiert nach Datum sortiert, können die Aussagen vereinfacht werden, indem Sie mit so etwas wie LIMIT 1?

InformationsquelleAutor der Frage netvope | 2010-01-21

321

Dies ist ein Beispiel für die greatest-n-per-group problem, das erschien regelmäßig auf StackOverflow.

Hier ist, wie ich in der Regel empfehlen die Lösung:
```
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: gegeben eine Reihe p1 sollte es keine Zeile p2 mit den gleichen Kunden und zu einem späteren Zeitpunkt (oder in dem Fall von Bindungen, die später id). Wenn wir finden, dass, um wahr zu sein, dann p1 ist der jüngste Kauf für die Kunden.

Bezüglich der Indizes, ich würde erstellen Sie einen zusammengesetzten index in purchase über die Spalten (customer_iddateid). Das kann erlauben, wird das outer-join zu tun mit einem abdeckenden index. Werden Sie sicher, dass der test auf Ihrer Plattform, denn Optimierung ist von der Implementierung abhängig. Verwenden Sie die Funktionen Ihres RDBMS zu analysieren, um die Optimierung zu planen. E. g. EXPLAIN auf MySQL.

Einige Leute verwenden Unterabfragen anstelle der Lösung, die ich oben zeige, aber ich finde meine Lösung macht es einfacher zu lösen Bindungen.

InformationsquelleAutor der Antwort Bill Karwin

Könnte man auch versuchen, dies zu tun mit einem sub-select

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

Den wählen, sollte sich auf alle Kunden und Ihre Letzten Kaufdatum.

InformationsquelleAutor der Antwort Adriaan Stander

21

Du noch nicht angegeben, der Datenbank. Wenn es eine, die analytischen Funktionen kann es schneller sein, um diesen Ansatz verwenden, als die GRUPPE DURCH eine(auf jeden Fall schneller in Oracle, wahrscheinlich schneller in die späten Editionen von SQL Server, weiß nicht, über andere).

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
```
InformationsquelleAutor der Antwort Madalina Dragomir

Ein weiterer Ansatz wäre die Verwendung einer NOT EXISTS Bedingung in den join-Bedingung zu testen, für 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
   )
)

InformationsquelleAutor der Antwort Stefan Haberl

Fand ich diesen thread als Lösung für mein problem.

Aber wenn ich habe versucht, Ihnen die Leistung zu gering war. Balg 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, dass dies hilfreich sein wird.

InformationsquelleAutor der Antwort Mathee

2

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
```
Den max() Aggregat-Funktion stellen Sie sicher, dass die aktuellen Kauf ausgewählt ist aus jeder Gruppe (geht aber davon aus, dass die Datum-Spalte in einem format, wobei max() gibt das aktuelle - was der Normalfall ist). Wenn Sie damit umgehen wollen Einkäufe mit dem gleichen Datum, dann können Sie max(p.date, p.id).

In Bezug auf die Indizes, würde ich einen index auf kaufen mit (customer_id, Datum, [jedem anderen Kauf Spalten, die Sie wollen zurück in Ihre select]).

Den LEFT OUTER JOIN (im Gegensatz zu INNER JOIN) stellen Sie sicher, dass die Kunden, die noch nie einen Kauf getätigt haben, sind ebenfalls enthalten.

InformationsquelleAutor der Antwort Mark

Bitte versuchen Sie dieses,

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;

InformationsquelleAutor der Antwort Milad Shahbazi

Versuchen Sie dies, Es wird helfen.

Ich habe diese in mein Projekt.

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

InformationsquelleAutor der Antwort Rahul Murari

1

Wenn Sie mit PostgreSQL können Sie DISTINCT ON zu finden, die erste Zeile in einer Gruppe.
```
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 - Verschiedene Auf

Beachten Sie, dass die DISTINCT ON field(s) -- hier customer_id -- muss mit der am weitesten Links Feld(s) in der ORDER BY - Klausel.

Achtung: Dies ist ein nicht dem Standard entsprechende Klausel.

InformationsquelleAutor der Antwort Tate Thurston

Schreibe einen Kommentar

Du musst angemeldet sein, um einen Kommentar abzugeben.