Aggregatfunktionen in SQL 2.0: GROUP BY versus JOIN

In meinem Beitrag Aggregatfunktionen in SQL habe ich die einzelnen Aggregatfunktionen dargestellt:

Aggregatfunktionen in SQL

Wollen wir z.B. wissen wann die Kunden das letzte Mal einen Film beim Verkäufer 1 ausgeliehen haben, können wir die gewünschten Datensätze ohne  Probleme mit folgendem Code ausgeben:

SELECT customer_id, MAX(payment_date) FROM payment WHERE staff_id = 1 GROUP BY customer_id

Sollen allerdings weitere Informationen dazu ausgegeben werden wie z.B. payment_id und amount, müssen diese auch in der GROUP BY-Bedingung angegeben werden, oder es muss mit einem JOIN gearbeitet werden.

Problem: Mehrere Spalten neben einer Aggregatfunktion im Resultset ausgeben lassen.

1.GROUP BY:

Wir nehmen die zusätzlichen Spalten aus dem SELECT-Befehl mit in die GROUP BY-Bedingung auf, wie es syntaxmäßig von SQL gefordert ist (siehe https://www.dateneule.de/2019/05/13/sql-datensaetze-gruppieren-mit-group-by/)

SELECT customer_id, MAX(payment_date), payment_id, amount FROM payment WHERE staff_id = 1 GROUP BY customer_id, payment_id, amount

Hier werden uns nun allerdings alle Datensätze ausgegeben, in denen ein Kunde beim Verkäufer 1 einen Film ausgeliehen hat. Die Aggregatfunktion MAX des Ausleihdatums wirkt auf den kompletten Datensatz, sodass für jede individuelle Kombination aus customer_id, payment_id und amount das letzte Ausleihdatum beim Verkäufer 1 ausgegeben wird.

Wir möchten allerdings nur den letzten Datensatz ausgegeben bekommen,  indem ein Kunde zum letzten Mal beim Verkäufer 1 einen Film ausgeliehen hat. Hier kann uns ein JOIN weiterhelfen.

2.JOIN:

Zunächst starten wir wieder mit unserer Ausgangstabelle:

Diese virtuelle Tabelle liefert uns die gewünschten letzten Ausleihdaten. Somit können wir diese Tabelle für einen JOIN mit der Tabelle der Grunddaten nutzen:

SELECT a.customer_id, a.payment_date, a.payment_id, a.amount
FROM payment a
INNER JOIN
(SELECT customer_id, MAX(payment_date) AS MAX_date FROM payment WHERE staff_id = 1 GROUP BY customer_id) b
ON a.customer_id = b.customer_id AND a.payment_date = b.MAX_Date

Im SELECT-Befehl können wir ganz normal unsere gewünschten Ausgabespalten angeben. Anschließend verknüpfen wir die Ausgangstabelle payment (ich vergebe hier den Alias a) und die virtuelle Tabelle der letzten Ausleihdaten (Alias b). Verknüpft werden die beiden Tabellen anhand der customer_id und dem payment_date. Da wir einen INNER JOIN ausführen wird nur die Schnittmenge der beiden Tabellen ausgegeben, also nur Datensätze mit einen letzten Ausleihdatum beim Verkäufer 1.

Nun können wir alle zusätzlichen Spalteninhalte des Datensatzes ohne Problem als weitere Information ausgeben lassen:

SELECT a.*, b.*
FROM payment a
INNER JOIN
(SELECT customer_id, MAX(payment_date) AS MAX_date FROM payment WHERE staff_id = 1 GROUP BY customer_id) b
ON a.customer_id = b.customer_id AND a.payment_date = b.MAX_Date