Eine Einführung in die Verwendung von SQL-Aggregatfunktionen mit JOINs

Zuvor haben wir über die Verwendung von SQL-Aggregatfunktionen mit der GROUP BY-Anweisung. Regelmäßige Leser unseres Blogs werden sich auch an unseren letzten Tutorium über JOINs. Wenn Sie bei beiden Themen etwas eingerostet sind, sollten Sie diese nachlesen, bevor Sie mit diesem Artikel fortfahren. Wir werden nämlich weiter in das Thema eintauchen. Aggregat-Funktionen indem sie mit JOINs kombiniert werden. Durch dieses Duo werden die Möglichkeiten der SQL-Aggregatfunktionen voll ausgeschöpft und wir können Berechnungen für mehrere Tabellen in einer einzigen Abfrage durchführen.

Was bewirken SQL-Aggregatfunktionen?

Hier finden Sie einen kurzen Überblick über die gängigsten SQL-Aggregatfunktionen:

Es ist auch wichtig, sich daran zu erinnern, dass die Anweisung GROUP BY, wenn sie mit Aggregaten verwendet wird, Werte berechnet, die nach Spalten gruppiert wurden. (Für weitere Informationen siehe Einsteigerhandbuch für SQL-Aggregatfunktionen.) Wir können GROUP BY mit jeder der oben genannten Funktionen verwenden. Im folgenden Beispiel verwenden wir beispielsweise die Funktion MIN():

SELECT MIN(column_name)
FROM table_name 
GROUP BY group_column

Dies würde den Mindestwert abrufen, der in column_name für jeden Satz von Werten in einer Gruppe auf der Grundlage der Spalte group_column gefunden wurde. Die gleiche Idee gilt für die Funktionen MAX, SUM, AVG und COUNT.

Parent-Child JOINs

Kommen wir nun zu einigen häufigen Situationen, in denen Sie JOINs nach Gruppen mit Aggregatfunktionen verwenden. Wenn Sie A Beginner's Guide to SQL Aggregate Functions gelesen haben, wird Ihnen das folgende Diagramm bereits bekannt sein:

Wenn Sie dieses Modell schon einmal verwendet haben (z. B. bei den Beispielen aus dem vorigen Artikel), stellen Sie bitte sicher, dass Sie alle vorhandenen Datensätze aus Ihrer Tabelle löschen. Dies können Sie tun, indem Sie die folgenden Befehle ausführen:

TRUNCATE cities;
TRUNCATE users;

Geben wir nun einige neue Daten in die Tabellen ein:

INSERT INTO `cities` VALUES
    (1,'Miami'),
    (2,'Orlando'),
    (3,'Las Vegas'),
    (4,'Coyote Springs');
INSERT INTO `users` VALUES
    (1,1,'John','Doe',22),
    (2,1,'Albert','Thomson',15),
    (3,2,'Robert','Ford',65),
    (4,3,'Samantha','Simpson',9),
    (5,2,'Carlos','Bennet',42),
    (6,2,'Mirtha','Lebrand',81),
    (7,3,'Alex','Gomez',31);

Wir haben also eine Tabelle namens users und eine weitere Tabelle namens cities. Diese beiden Tabellen haben etwas gemeinsam: einen numerischen Wert für die Stadtkennung. Dieser Wert ist in der Spalte id in der Tabelle cities und in der Spalte city_id in der Tabelle users Tabelle gespeichert. Die Spalte city_id enthält einen Verweis (auch bekannt als Fremdschlüssel), der einen Benutzerdatensatz mit einer Stadt verbindet. Diese übereinstimmenden Datensätze ermöglichen es uns, die beiden Tabellen JOIN miteinander zu verknüpfen.

Mit anderen Worten, wir kennen die Stadt eines Benutzers, wenn wir den Datensatz aus der Tabelle cities der einen id Wert hat, der dem Wert in users.city_id entspricht. In der folgenden Abfrage können wir dies in Aktion sehen:

SELECT  cities.*, users.*
FROM cities
JOIN users
  ON cities.id = users.city_id;

Da die users Tabelle über den Fremdschlüssel city_id mit einer Stadt verbunden ist, können wir sagen, dass ein Benutzer zu einer Stadt gehört und die Stadt somit viele Benutzer hat. Dies ist eine Eltern-Kind-Beziehung (Städte-Benutzer); die users Tabelle teilt eine Verbindung zur cities Tabelle.

Mit dieser Beziehung im Hinterkopf wollen wir nun sehen, wie wir einige interessante zusammengefasste Daten berechnen können, die beide Tabellen miteinander verbinden.

Aggregat + GROUP BY + JOIN

Lassen Sie uns nun einige praktische Situationen betrachten, in denen wir GROUPWerte aus JOINTabellen.

MIN + GRUPPE NACH + JOIN

Das Berechnen von Werten auf der Grundlage von untergeordneten Datensätzen, die nach einer übergeordneten Spalte gruppiert sind, ist ziemlich üblich. Erstellen wir eine Abfrage, die den niedrigsten users.age (untergeordneten Datensatz) für jeden cityname (übergeordneten Datensatz) abruft:

SELECT cities.cityname, MIN(users.age)
FROM cities
JOIN users
  ON cities.id = users.city_id
GROUP BY cities.cityname

Dies wird zurückgegeben:

Es gibt etwas sehr Wichtiges über die Art und Weise, wie JOIN funktioniert, zu erwähnen. Es wird deutlicher, wenn wir uns alle Städte ansehen:

SELECT cities.cityname
FROM cities

Wie Sie sehen können, war "Coyote Springs" vorher nicht aufgeführt, weil es keine Benutzer hat. Wenn Sie diese Stadt in den zusammengefassten Ergebnissen anzeigen lassen möchten, sollten Sie stattdessen eine LEFT JOIN stattdessen verwenden:

SELECT cities.cityname, MIN(users.age)
FROM cities
LEFT JOIN users
  ON cities.id = users.city_id
GROUP BY cities.cityname

Dies wird zurückgegeben:

Ob dies sinnvoll ist oder nicht, hängt von Ihrem Anwendungsfall ab, aber es ist wichtig, dass Sie diese Situation im Hinterkopf behalten, wenn Sie Tabellen verbinden.

MAX + GRUPPIEREN NACH + VERKNÜPFUNGEN

Wir können das höchste Alter für jede Stadt mit der Funktion MAX() finden:

SELECT cities.cityname, MAX(users.age)
FROM cities
LEFT JOIN users
  ON cities.id = users.city_id
GROUP BY cities.cityname

Die obige Abfrage liefert die Ergebnisse:

Beachten Sie, dass ich LEFT JOIN verwendet habe. Ich möchte eine Liste aller Städte, nicht nur derjenigen mit zugehörigen Benutzerdatensätzen.

SUMME + GRUPPIERUNG NACH + VERKNÜPFUNG

Schauen wir uns nun an, wie wir die Altersangaben für jede Stadt summieren können. Dazu können wir die Funktion SUM() verwenden:

SELECT cities.cityname, SUM(users.age)
FROM cities
LEFT JOIN users
  ON cities.id = users.city_id
GROUP BY cities.cityname

Diese gibt zurück:

COUNT + GROUP BY + JOIN

Angenommen, wir wollen die Anzahl der Nutzer in jeder Stadt sehen. Wir würden die Funktion COUNT() wie folgt verwenden

SELECT cities.cityname, COUNT(users.id)
FROM cities
LEFT JOIN users
  ON cities.id = users.city_id
GROUP BY cities.cityname

Die zurückgibt:

DURCHSCHNITT + GRUPPIERUNG NACH + VERKNÜPFUNG

Unter Verwendung der Anzahl der Nutzer in jeder Stadt (COUNT) und der SUM des kombinierten Alters der Nutzer in jeder Stadt können wir das Durchschnittsalter für jede Stadt errechnen. Wir dividieren einfach das summierte Alter durch die Anzahl der Nutzer in jeder Stadt:

SELECT 
    cities.cityname,
    SUM(users.age) AS sum,
    COUNT(users.id) AS count,
    SUM(users.age) / COUNT(users.id) AS average
FROM cities
LEFT JOIN users
  ON cities.id = users.city_id
GROUP BY cities.cityname

Rückgabe:

Beachten Sie, dass die Summe und der berechnete Durchschnitt einen NULL-Wert für Coyote Springs ergeben. Das liegt daran, dass Coyote Springs keine Benutzer hat und die zusammengefasste Spalte daher keinen numerischen Wert berechnen kann.

AVG + GRUPPE NACH + JOINS

Im vorherigen Beispiel wurde eine Berechnung verwendet, die wir eingegeben haben, um ein Durchschnittsalter für jede Stadt zu ermitteln. Wir hätten stattdessen auch die Funktion AVG() verwenden können, wie unten gezeigt:

SELECT cities.cityname, AVG(users.age)
FROM cities
LEFT JOIN users
  ON cities.id = users.city_id
GROUP BY cities.cityname

Dies führt zu den gleichen Werten wie im vorherigen Beispiel:

Ergebnisse filtern

Manchmal müssen Sie Zeilen auf der Grundlage bestimmter Bedingungen filtern. Bei dieser Art von Abfrage gibt es drei Stufen, in denen Sie dies tun können: WHERE, HAVING, und JOIN.

Je nach Situation kann jede dieser Optionen zu einem anderen Ergebnis führen. Es ist wichtig, dass Sie wissen, welche Sie verwenden müssen, wenn Sie ein bestimmtes Ergebnis wünschen. Schauen wir uns einige Beispiele an, um dies zu verdeutlichen.

Verwendung des JOIN-Prädikats

Ermitteln wir die Anzahl der Nutzer unter 30 Jahren in jeder Stadt. Wir verwenden LEFT JOIN um Städte ohne Benutzerdatensätze abzurufen:

SELECT cityname, COUNT(users.id)
FROM cities
LEFT JOIN users
  ON cities.id = users.city_id
  AND users.age < 30
GROUP BY cities.cityname
ORDER BY cities.cityname;

Die Bedingung, nur Benutzer mit einem Alter unter 30 Jahren einzuschließen, wird im Prädikat JOIN festgelegt. Dies ergibt die folgende Ausgabe:

Alle Städte werden aufgelistet, und nur die Benutzer mit einem Alter innerhalb des Bereichs geben eine Zahl ungleich Null zurück. Städte, in denen es keine Benutzer gibt, die unseren Kriterien entsprechen, geben eine Null zurück.

Was wäre passiert, wenn wir dieselbe Filterbedingung in die WHERE Klausel gesetzt hätten?

Verwendung von WHERE-Bedingungen

Würden wir die gleichen Bedingungen in die WHERE Klausel einfügen, sähe es wie folgt aus:

SELECT cityname, COUNT(users.id)
FROM cities
LEFT JOIN users
  ON cities.id = users.city_id 
WHERE users.age < 30
GROUP BY cities.cityname
ORDER BY cities.cityname;

Das Ergebnis wäre dann:

Das ist nicht das, was ich erwartet habe; ich wollte ALLE Städte und die Anzahl ihrer jeweiligen Nutzer unter 30 Jahren erhalten. Selbst wenn eine Stadt keine Nutzer hat, sollte sie mit einer Nullzahl aufgelistet werden, wie es das Beispiel des JOIN Prädikats zeigt.

Der Grund dafür, dass diese Datensätze nicht zurückgegeben werden, ist, dass WHERE Bedingungen nach der JOIN. Da die Bedingung users.age < 30 alle "Coyote Springs"- und "Orlando"-Datensätze entfernt, kann die zusammenfassende Berechnung diese Werte nicht enthalten. Nur "Las Vegas" und "Miami" erfüllen die Bedingungen von WHERE, daher werden nur "Las Vegas" und "Miami" zurückgegeben.

Im Gegensatz dazu werden bei Anwendung der Bedingung im JOIN -Prädikat Benutzerdatensätze ohne übereinstimmendes Alter entfernt , bevor die beiden Tabellen verbunden werden. Dann werden alle Städte nach Benutzerspalten abgeglichen, wie Sie es erwarten würden, wenn Sie eine LEFT JOIN. Das bedeutet, dass alle Städte in den Ergebnissen enthalten sind; nur Benutzerdatensätze, die die Bedingung users.age < 30 nicht erfüllen, werden herausgefiltert. In diesem Fall liefert das JOIN-Prädikat das gewünschte Ergebnis.

HAVING-Bedingungen verwenden

Wir haben dies bereits im ersten Artikel erwähnt, aber wir wiederholen es hier noch einmal: Die Verwendung der WHERE Klausel zum Filtern von zusammengefassten Spalten funktioniert nicht. Sehen Sie sich das folgende Beispiel an.

SELECT cityname, COUNT(users.id)
FROM cities
LEFT JOIN users
  ON cities.id = users.city_id
WHERE COUNT(users.id) > 2
GROUP BY cities.cityname
ORDER BY cities.cityname;

Dies führt dazu, dass die Datenbank eine Beschwerde wie diese von MySQL ausgibt:

Error Code: 1111. Invalid use of group function

Verwenden Sie stattdessen die HAVING Klausel:

SELECT cityname, COUNT(users.id)
FROM cities
LEFT JOIN users
  ON cities.id = users.city_id
GROUP BY cities.cityname
HAVING COUNT(users.id) > 2
ORDER BY cities.cityname;

Diese gibt die gewünschten Datensätze zurück (nur Städte mit mehr als zwei Benutzern):

Der Umgang mit NULLs

Neben den bereits vorgestellten Randfällen ist es wichtig, etwas zu berücksichtigen, das nicht so offensichtlich ist. Kehren wir zum Beispiel von COUNT() zurück:

SELECT cities.cityname, COUNT(users.id)
FROM cities
LEFT JOIN users
  ON cities.id = users.city_id
GROUP BY cities.cityname

Dieses gibt zurück:

Hätte ich COUNT(*) anstelle von COUNT(users.id) verwendet, wäre die Gesamtzahl der Zeilen generiert worden. Dadurch hätten wir einen unbeabsichtigten Wert erhalten - in diesem Fall eine falsche "1" für "Coyote Springs". Dieses Ergebnis ist auf die Art von LEFT JOIN zurückzuführen. Hier ein Beispiel:

SELECT cities.cityname, COUNT(*)
FROM cities
LEFT JOIN users
  ON cities.id = users.city_id
GROUP BY cities.cityname

Dies würde zurückgegeben:

COUNT(*) zählt also eine "1" für Coyote Springs, weil LEFT JOIN eine Zeile mit NULL-Werten zurückgibt. Denken Sie daran, dass in COUNT(*) eine Zeile mit NULL-Werten immer noch zählt.

Aus demselben Grund gibt COUNT(users.id) die erwartete Anzahl von "0" zurück; der Spaltenwert von users.id ist für Coyote Springs null.

Mit anderen Worten: Verwenden Sie bei dieser Art von Abfrage immer Count(column).

Ein letzter Tipp zur Arbeit mit SQL-Aggregatfunktionen

Abschließend möchte ich noch hinzufügen, dass die Arbeit mit SQL-Aggregatfunktionen - insbesondere bei der Verwendung von JOIN- voraussetzt, dass Sie SQL und die Daten, mit denen Sie arbeiten, verstehen. Probieren Sie die Abfragen zunächst an einer kleineren Teilmenge Ihrer Daten aus, um sicherzustellen, dass alle Berechnungen wie erwartet funktionieren. Wenn möglich, sollten Sie einige Ausgaben mit einem Referenzwert vergleichen, um die Ergebnisse Ihrer Abfragen zu überprüfen.

Denken Sie daran, dass die Verwendung von Bedingungen im Prädikat JOIN (nach ON) nicht dasselbe ist wie die Filterung in WHERE (oder die Verwendung von HAVING). Dies kann zu subtilen (oder auch nicht so subtilen) Unterschieden in Ihren zusammengefassten Daten führen, die schwer zu entdeckende Fehler zur Folge haben können. Achten Sie besonders auf die Wahl Ihrer Filterung.

Wie immer danke ich Ihnen für die Lektüre und freue mich, wenn Sie Ihre eigenen Erfahrungen im Kommentarbereich mitteilen.