SQL-Aggregatfunktionen für Einsteiger

Aggregat-Funktionen sind leistungsstarke SQL Tools, die numerische Berechnungen mit Daten durchführen, so dass die Abfrage zusammengefasste Informationen über eine bestimmte Spalte oder Ergebnismenge zurückgeben kann. Diese Funktionen können in Verbindung mit der GROUP BY-Anweisung verwendet werden. Schauen wir uns ihre Funktionsweise anhand einiger einfacher Beispiele.

SQL-Aggregat-Funktionen

Angenommen, wir haben Benutzer, die in einer Stadt wohnen, und wir speichern ihre Informationen in zwei Tabellen. Diese Tabellen und ihre Beziehung zueinander sind unten dargestellt:

Fügen wir einige Daten in dieses Modell ein:

INSERT INTO `cities` VALUES (1,'Miami'),(2,'Orlando'),
(3,'Las Vegas'),(4,'Coyote Springs');
INSERT INTO `users` VALUES (1,1,'Jhon','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);

Jetzt haben wir genug Material, um die grundlegende Verwendung von SQL-Aggregatfunktionen zu erklären. Fangen wir mit einer einfachen Funktion an.

MIN

Diese Funktion gibt den kleinsten Wert in einer bestimmten Spalte zurück. Ein Beispiel: Wir wollen das Mindestalter unserer Benutzergruppe ermitteln:

SELECT MIN(age) FROM users;

Diese Funktion gibt eine "9" zurück.

Sie können diese Funktion auch verwenden, um alphabetische Informationen zu finden. Probieren wir es mit der Spalte "last_name":

SELECT MIN(last_name) FROM users;

Das Ergebnis ist "Bennet", der in alphabetischer Reihenfolge an erster Stelle steht.

Hinweis: Die Sortierung, die Sie zum Sortieren Ihrer Daten verwenden, hat Auswirkungen auf die Ergebnisse dieser Abfrage. In der dänischen Sortierung wird zum Beispiel"A" als "A" behandelt - der letzte Buchstabe im Alphabet. Bei der lateinischen Sortierung wird "A" natürlich als erster Buchstabe des Alphabets behandelt.

MAX

Ähnlich wie MIN gibt MAX den größten Wert in einer Spalte zurück. Holen wir uns das Höchstalter aus unserer Benutzerliste:

SELECT MAX(age) FROM users;

Dies ergibt eine "81".

Versuchen wir das Gleiche mit der Nachnamensspalte:

SELECT MAX(last_name) FROM users;

Es wird "Thomson" zurückgegeben, also der letzte in alphabetischer Reihenfolge. Denken Sie daran, dass sich dies je nach der von Ihnen verwendeten Sortierung ändern kann.

SUMME

Diese Funktion berechnet die Summe aller numerischen Werte in einer Spalte. Benutzen wir sie, um die Summe aller Altersangaben in der Tabelle zu ermitteln:

SELECT SUM(age) FROM users;

Sie wird "265" zurückgeben.

AVG

Mit dieser Funktion wird der Durchschnittswert für eine Spalte berechnet. Sehen wir uns das Durchschnittsalter unserer Benutzer an:

SELECT AVG(age) FROM users;

Es wird "27,75" zurückgegeben.

COUNT (Spalte)

Diese Funktion gibt die Anzahl der Nicht-NULL-Werte in einer bestimmten Spalte zurück. Wenn wir wissen wollten, wie viele Benutzer uns ihr Alter mitgeteilt haben, würden wir schreiben:

SELECT COUNT(age) FROM users;

Es wird eine "7" zurückgegeben. Alle Datensätze in der Tabelle "users" Tabelle haben einen Alterswert. Wenn ein Datensatz keinen Alterswert hätte, wäre er NULL (und nicht im COUNT-Ergebnis enthalten). Wenn Sie die tatsächliche Anzahl der Tabellenzeilen unabhängig vom Spaltenwert zählen wollen, benötigen Sie die Funktion COUNT(*). Anstatt einen Spaltennamen als Funktionsargument anzugeben, verwenden wir ein Sternchen:

SELECT COUNT(*) FROM users;

In unserem Fall wird immer noch eine "7" zurückgegeben, da die Tabelle sieben Datensätze enthält.

NULL-Werte können verwirrend sein, aber machen Sie sich keine Sorgen. Wir werden später in diesem Artikel zeigen, wie Sie mit NULL-Werten in SQL-Aggregatfunktionen umgehen können.

Nachdem Sie nun wissen, was diese Aggregatfunktionen bewirken, wollen wir sehen, wie man sie noch nützlicher machen kann.

Möchten Sie mehr über SQL-JOINs erfahren? Sehen Sie sich eine Folge unserer We Learn SQL-Serie auf Youtube an. Prüfen Sie, ob Sie bereits alles über die verschiedenen Arten von JOINs wissen.

Verwendung von GROUP BY mit Aggregatfunktionen

Mit der Anweisung GROUP BY können wir Aggregationen über eine Gruppe von Werten auf der Grundlage bestimmter Spalten durchführen. Sie werden GROUP BY häufig mit Aggregatfunktionen verwenden, daher werden diese Beispiele etwas komplizierter und realistischer sein als die einfachen, die wir zuvor verwendet haben.

Konzeptionell bedeutet "GROUP BY (column_x)" bedeutet "alle Datensätze, die denselben Wert in "column_x" haben, in eine Gruppe zusammenfassen". Schauen wir uns an, wie das mit den einzelnen Funktionen funktioniert, die wir bereits besprochen haben.

MIN + GROUP BY

Nehmen wir an, dass wir das Alter unseres jüngsten Benutzers in jeder Stadt wissen wollen. Wir können sehen, dass die Tabelle "users" eine Spalte mit der Bezeichnung "city_id" enthält, die die Stadt angibt, in der der jeweilige Nutzer lebt. Wir können diese Spalte mit einer GROUP BY -Anweisung verwenden, um das jüngste Alter in jeder Stadt zu ermitteln:

SELECT 
    city_id, MIN(age)
FROM
    users
GROUP BY city_id;

Um besser zu verstehen, was hier vor sich geht, sehen Sie sich die Rohdaten in der Tabelle "users" Tabelle:

Wenn Sie MIN() mit GROUP BY verwenden, werden die Datensätze nach den Werten in der Spalte "city_id" gruppiert , bevor das Aggregat für jede Gruppe berechnet wird. Wenn Sie die gruppierten Werte sehen könnten, würde es in etwa so aussehen:

Dann wird die Abfrage das niedrigste Alter innerhalb jeder Gruppe erfassen. Wenn wir diesen Schritt in Aktion sehen könnten, sähe es so aus:

Die hervorgehobenen Werte stellen die MIN() berechneten Werte für jede Gruppe dar.

Schließlich zeigt die Abfrage die folgenden Ergebnisse:

MAX + GROUP BY

Wie Sie vielleicht schon erraten haben, funktioniert die Verwendung von MAX in Kombination mit GROUP BY genauso wie MIN. Sie gibt einfach den größten Wert für jede Gruppe zurück. Wir könnten das Höchstalter für jede Stadt auf ähnliche Weise berechnen:

SELECT 
    city_id, MAX(age)
FROM
    users
GROUP BY city_id;

Diese Abfrage gruppiert die Benutzer anhand ihres Feldes "city_id" und ermittelt dann den maximalen Alterswert für jede Gruppe. Sie liefert die folgenden Ergebnisse:

SUM + GROUP BY

Wir könnten auch die Summe des Alters der Benutzer in jeder Stadt berechnen. Dazu können wir die folgende Abfrage ausführen...

SELECT 
    city_id, SUM(age)
FROM
    users
GROUP BY city_id;

... die folgende Ergebnisse liefert:

COUNT + GROUP BY

Vielleicht möchten wir auch die Anzahl der Nutzer in jeder Stadt berechnen. Dies ist mit der Funktion COUNT leicht zu bewerkstelligen:

SELECT 
    city_id, COUNT(age)
FROM
    users
GROUP BY city_id;

Ausgehend von den letzten beiden Beispielen können wir das Durchschnittsalter in jeder Stadt berechnen, indem wir die Summe aller Altersgruppen in einer Stadtgruppe durch die Anzahl der Nutzer in dieser Stadt teilen. So machen wir es:

SELECT 
    city_id,
    SUM(age),
    COUNT(age),
    SUM(age) / COUNT(age) as average
FROM
    users
GROUP BY city_id;

Hinweis: Wir hätten hier auch COUNT(*) verwenden können, da es keine Datensätze mit NULL-Werten in der Spalte "age" gibt. In diesem Fall funktioniert COUNT(age) genauso wie COUNT(*). Andernfalls würde sich der Wert unterscheiden, wie wir später im Abschnitt "Umgang mit NULLs" erklären werden.

AVG + GROUP BY

Im vorherigen Beispiel haben wir das Durchschnittsalter für jede Stadt "manuell" berechnet. Wir könnten die Funktion AVG() verwenden, um diesen Vorgang für uns durchzuführen, wie unten gezeigt:

SELECT 
    city_id,
    AVG(age)
FROM
    users
GROUP BY city_id;

Der von AVG(age) zurückgegebene Wert ist identisch mit dem Ergebnis der zuvor durchgeführten mathematischen Operation.

Filtern gruppierter Ergebnisse

Es kann vorkommen, dass Sie die Ergebnisse auf der Grundlage von Bedingungen weiter filtern müssen, die durch gruppierte Ergebnisse erzeugt wurden. Das Hinzufügen von Bedingungen in WHERE würde fehlschlagen. Sie glauben mir nicht? Sehen Sie sich an, was passiert, wenn wir versuchen, die Anzahl der Nutzer nur in Städten abzurufen , in denen das Durchschnittsalter der Nutzer größer als 20 ist:

SELECT 
    city_id, COUNT(age), AVG(age)
FROM
    users
WHERE AVG(age) >= 20
GROUP BY city_id;

Dann beschwert sich die Engine (in meinem Fall MySQL). Sie sagt dann etwas wie dies:

Fehlercode: 1111. Ungültige Verwendung der Gruppenfunktion

Um die Ergebnisse auf diese Weise zu filtern, müssen wir die Klausel HAVING verwenden. HAVING filtert die zusammengefassten Ergebnisse von GROUP BY; die Klausel WHERE gilt nur für einzelne Datensätze. Wenn eine Gruppe die Kriterien in der HAVING Klausel nicht erfüllt, wird sie nicht zurückgegeben.

Wenn wir also die COUNT für jede Stadt mit einem Durchschnittsalter von mindestens 20 Jahren abrufen wollten, würde die korrekte Vorgehensweise bei der Filterung folgendermaßen aussehen:

SELECT 
    city_id, COUNT(age), AVG(age)
FROM
    users
GROUP BY city_id
HAVING AVG(age) >= 20

Hinweis: Die Gruppe mit city_id = 1 wird verworfen, da AVG(age) 18,5 Jahre alt ist.

Schließlich können WHERE und HAVING ohne Probleme gleichzeitig verwendet werden (natürlich nur, wenn es in Ihrer Abfrage Sinn macht). Beachten Sie, wie dies im nächsten Beispiel funktioniert. Wir führen die Berechnung erneut durch, aber diesmal schließen wir alle Benutzer aus, deren Nachname "Simpson" lautet:

SELECT 
    city_id, COUNT(age), AVG(age)
FROM
    users
WHERE last_name <> 'Simpson'
GROUP BY city_id
HAVING AVG(age) >= 20

Beachten Sie, dass sich die Berechnung von COUNT und AVG für city_id = 3 unterscheidet. Es gibt einen Benutzer mit dem Nachnamen "Simpson" für city_id = 3, und dieser Datensatz wurde aufgrund der Bedingung WHERE last_name <> 'Simpson’ verworfen.

Umgang mit NULLs

Bis jetzt wurden alle unsere Spalten mit Daten gefüllt, aber das ist nicht immer der Fall. Fügen wir einige Datensätze mit NULL-Alter ein, um einige Randfälle zu erklären. Die folgenden beiden INSERTfügen zwei neue Benutzer mit einer ID von 8 bzw. 9 hinzu, die in der Spalte Alter einen NULL-Wert haben:

INSERT INTO `users` (`id`, `city_id`, `first_name`, `last_name`, `age`)
VALUES ('8', '2', 'Frederic', 'Scott',NULL);

INSERT INTO `users` (`id`, `city_id`, `first_name`, `last_name`, `age`) 
VALUES ('9', '4', 'Stacy', 'Roberts',NULL);

Datensätze mit Nullwerten werden in MIN, MAX, SUM, AVG und COUNT(column) nicht berücksichtigt. Diese Datensätze werden bei der Berechnung einfach ignoriert. Eine allgemeine Faustregel lautet: Wenn Sie keine Nullwerte erwarten oder wenn Sie erwarten, dass NULL-Werte als "0" behandelt werden, dann sollten Sie die Spalten als NOT NULL festlegen und die NULL-Spalten auf "0" oder einen anderen Wert setzen, der für Ihren Anwendungsfall sinnvoll ist.

COUNT(Spalte) vs. COUNT(*)

Die Funktion COUNT(column) zählt keine Datensätze mit NULL-Alterswerten. Schauen wir uns dies in Aktion an:

SELECT 
    COUNT(age)
FROM
    users;

Diese Abfrage gibt wieder eine "7" zurück; die beiden von uns hinzugefügten Datensätze haben NULL-Werte in der Spalte "age" und werden daher ignoriert. Wenn wir jeden Datensatz unabhängig von seinem Wert zählen wollten, verwenden wir die COUNT(*) function:


SELECT 
    COUNT(*)
FROM
    users;

Dies liefert das erwartete Ergebnis "9".

Schauen wir uns an, wie sich NULLs auf die Funktion AVG() auswirken.

AVG + NULL

Da AVG Datensätze mit NULL-Werten in der angegebenen Spalte ignoriert, ist der resultierende Wert möglicherweise nicht sinnvoll. Dies ist der Grund.

Die Funktion AVG(age) addiert nur die Benutzer mit einem Nicht-Null-Wert in der Spalte "age" und teilt diese Zahl durch die COUNT der Benutzer, die ebenfalls einen Nicht-Null-Wert "age" haben. In unserem Beispiel sind dies 7. Wenn Sie bedenken, dass die beiden soeben hinzugefügten Benutzer mit NULL-Alter für die Berechnung des Durchschnittsalters berücksichtigt werden sollten, dann wäre der Wert, den Sie mit AVG(age) erhalten, falsch. Die folgende Abfrage zeigt den Unterschied in den Berechnungen:

SELECT 
    SUM(age),
    COUNT(age),
    AVG(age),
    SUM(age) / COUNT(age),
    COUNT(*),
    SUM(age) / COUNT(*)
FROM
    users;

Beachten Sie, dass der Wert von AVG(age) mit dem Wert übereinstimmt, der mit SUM(age)/COUNT(age) berechnet wurde; beide Funktionen berücksichtigen keine Datensätze mit NULL-Werten. Schauen Sie sich jedoch an, wie sich der Durchschnittswert ändert, wenn Sie COUNT(*) verwenden, das alle Datensätze berücksichtigt.

Wir haben gesehen, dass NULL-Werte in den Funktionen MIN, MAX, AVG, SUM und COUNT(column_name) nicht berechnet werden. Wenn Sie diese Funktionen verwenden und einige NULL-Felder erwarten, stellen Sie sicher, dass Sie NULLs als einen bestimmten Wert festlegen.

Stellen Sie außerdem die Spaltentypen konsequent auf den Wert ein, den Sie speichern. So werden beispielsweise Zahlen, die in einer VARCHAR-Spalte gespeichert sind, nicht als numerisch behandelt und können zu unerwünschten Ergebnissen bei den Berechnungen Ihrer Aggregationsfunktionen führen.

Schließlich möchte ich noch betonen , wie wichtig es ist, die Filterbedingungen an den richtigen Stellen in Ihren Abfragen zu platzieren. Verwenden Sie HAVING, wenn Sie Bedingungen benötigen, die auf aggregierten Werten basieren.

Was halten Sie von SQL-Aggregatfunktionen? Haben Sie Erfahrungen oder Beispiele für deren Verwendung? Bitte teilen Sie Ihre Gedanken mit unserer Community. Und verpassen Sie nicht den zweiten Teil dieses Artikels, in dem ich erklären werde wie man SQL-Aggregatfunktionen mit JOINs integriert. Wir werden ihn bald auf dem Vertabelo-Blog veröffentlichen!