23rd Jun 2022 13 Leseminuten

Was sind Aggregatfunktionen in SQL, und wie verwende ich sie?

Aggregatfunktionen

Inhaltsverzeichnis

Wie Aggregatfunktionen funktionieren
Die Rolle von HAVING und GROUP BY bei Aggregatfunktionen
Lassen Sie uns üben!
SQL-Aggregatfunktionen als wesentliches Werkzeug in der Datenwissenschaft

Daten sind Ihre Quelle des Wissens. Und dank der SQL-Aggregatfunktionen können Sie aus Ihren Daten genau das Wissen extrahieren, das Sie benötigen. Lesen Sie weiter und erfahren Sie mehr.

Die wichtigsten SQL-Aggregatfunktionen sind die folgenden:

COUNT(column_name | *) gibt die Anzahl der Zeilen in einer Tabelle zurück.
SUM(column_name) gibt die Summe der Werte einer numerischen Spalte zurück.
AVG(column_name) Gibt den Durchschnittswert einer numerischen Spalte zurück.
MIN(column_name) gibt den Mindestwert einer ausgewählten Spalte zurück.
MAX(column_name) gibt den Maximalwert einer ausgewählten Spalte zurück.

In diesem Artikel werden wir jeden dieser Werte anhand von Beispielen erläutern. Sie werden erfahren, was mit NULLs und Duplikaten passiert, wenn sie Aggregatfunktionen unterworfen werden. Außerdem werden wir *, das Schlüsselwort DISTINCT und die Anweisung CASE erklären.

Fangen wir an!

Wie Aggregatfunktionen funktionieren

SQL-Aggregatfunktionen akkumulieren Daten aus mehreren Zeilen in einer einzigen zusammenfassenden Zeile. Der kumulierte Wert basiert auf den Werten der als Argument übergebenen Spalte. Wir können die Zeilen mit einer GROUP BY Klausel gruppieren und mit einer HAVING Klausel weiter filtern.

Ein Standardbeispiel ist die Ermittlung der Anzahl der Zeilen in einer Tabelle. Hier werden alle Zeilen der Tabelle Bücher in einer Zeile zusammengefasst.

SELECT * FROM Books;

Id	Author	Title	Price
234	Anthony Molinaro	SQL Cookbook	20.00
235	Alan Beaulieu	Learning SQL	25.00
236	Donald Knuth	Things a Computer Scientist Rarely Talks About	25.00
237	Donald Knuth	The Art of Computer Programming	27.00

SELECT COUNT(*) AS NumberOfBooks
FROM Books;

NumberOfBooks
4

Einfach, nicht wahr? Mit COUNT(*) können Sie die Anzahl aller Zeilen zählen.

Sie können auch einen Blick auf unsere anderen Artikel über SQL-Aggregatfunktionen hier und hier werfen.

Das * Argument bei Aggregatfunktionen

Wenn Sie mich fragen, wofür * in SQL steht, lautet meine Antwort, dass es für all steht. Es wird häufig in Verbindung mit einer SELECT -Anweisung verwendet, wenn alle Spalten einer bestimmten Tabelle abgefragt werden. Nehmen wir zum Beispiel SELECT * FROM Books, wie oben.

Das Argument * kann auch mit der Aggregatfunktion COUNT() verwendet werden. Sie zählt dann alle Zeilen einer Tabelle. Nehmen Sie zum Beispiel SELECT COUNT(*) as NumberOfBooks FROM Books, wie oben beschrieben.

Sie können Daten nach einer Spalte oder sogar nach mehreren Spalten gruppieren. Schauen Sie sich das folgende Beispiel an:

SELECT Author, COUNT(*) AS NumberOfBooks
FROM Books
GROUP BY Author;

Author	NumberOfBooks
Anthony Molinaro	1
Alan Beaulieu	1
Donald Knuth	2

Hier wird die Anzahl der Bücher nach Autor gezählt.

Das Argument * gilt nur für die Aggregatfunktion COUNT(). Für andere Aggregatfunktionen ist eine bestimmte Spalte oder eine Kombination von Spalten als Argument erforderlich.

Das Schlüsselwort DISTINCT

Das Schlüsselwort DISTINCT teilt der Datenbank mit, dass wir keine doppelten Werte berücksichtigen wollen.

Mit COUNT(Author) erhalten wir beispielsweise die Anzahl aller in einer Tabelle vorhandenen Autoren. Wenn aber ein und derselbe Autor mehrmals in einer Spalte auftaucht, wird der Autor mehrfach gezählt. Schauen Sie sich das an:

SELECT COUNT(Author) AS NumberOfAuthors
FROM books;

NumberOfAuthors
4

Sehen Sie? Es werden vier Autoren gezählt, weil Donald Knuth zweimal gezählt wird.

Was passiert, wenn wir das Schlüsselwort DISTINCT hinzufügen?

SELECT COUNT(DISTINCT Author) AS NumberOfAuthors
FROM Books;

NumberOfAuthors
3

Dieses Mal verwenden wir ein DISTINCT Schlüsselwort. Jetzt wird Donald Knuth nur noch einmal gezählt. Wenn Sie das Schlüsselwort DISTINCT verwenden, muss COUNT() eine bestimmte Spalte als Argument annehmen. Es gibt die Anzahl der eindeutigen Werte zurück, die in dieser Spalte gespeichert sind.

In ähnlicher Weise können wir das Schlüsselwort DISTINCT mit den Argumenten der Aggregatfunktionen SUM() und AVG() verwenden. Nachfolgend werden die Ergebnisse der Ausführung der Funktion SUM() mit und ohne das Schlüsselwort DISTINCT verglichen.

SELECT SUM(DISTINCT Price)
AS TotalDistinctPrice
FROM Books;

TotalDistinctPrice
72

	SELECT SUM(Price)
	AS TotalPrice
	FROM Books;

TotalPrice
97

Wie Sie sehen können, werden bei Verwendung des Schlüsselworts DISTINCT die Bücher mit demselben Preis nur einmal in SUM() berücksichtigt. In diesem Fall ist es sinnvoller, die Funktion SUM() ohne das Schlüsselwort DISTINCT zu verwenden.

Auch bei der Berechnung eines Durchschnittspreises ist es besser, das Schlüsselwort DISTINCT nicht zu verwenden; wir sollten jeden Preis so oft berücksichtigen, wie er in der Spalte erscheint. Sehen Sie, was mit AVG() passiert:

SELECT AVG(DISTINCT Price)
AS TotalDistinctAvg
FROM Books;

TotalDistinctAvg
24

	SELECT AVG(Price)
	AS TotalAvg
	FROM Books;

TotalAvg
24.25

Bei den Aggregatfunktionen MIN() und MAX() macht das Schlüsselwort DISTINCT keinen Unterschied. Aber es verursacht auch keine Fehler. Warum eigentlich? Betrachten wir eine Menge von Zahlen {1, 2, 2, 3, 4, 5, 5, 6}. Ihr maximaler und minimaler Wert ist 6 bzw. 1. Mit dem Schlüsselwort DISTINCT wird diese Menge zu {1, 2, 3, 4, 5, 6}, so dass die Höchst- und Mindestwerte immer noch dieselben sind.

Die CASE-Anweisung

Die Anweisung CASE kategorisiert und filtert Daten. Sie ist wie ein Pförtner für die Argumente einer Aggregatfunktion, der entscheidet, welche Werte zugelassen werden sollen. Schauen wir uns einige Beispiele an, um dieses Konzept zu veranschaulichen.

In der folgenden Abfrage verwenden wir eine CASE -Anweisung als Argument für die Funktion COUNT(). Es werden nur die Bücher gezählt, deren Preis höher als $20.00 ist.

SELECT COUNT(CASE WHEN Price > 20 THEN Price END)
AS NumberOfExpensiveBooks
FROM Books;

NumberOfExpensiveBooks
3

Eine CASE -Anweisung kann auch als Argument für andere Aggregatfunktionen verwendet werden. In der folgenden Abfrage werden die Preise der Bücher addiert, die genau 25,00 $ kosten. Die Anweisung CASE innerhalb der Funktion SUM() ermöglicht es, dass nur Bücher mit einem Preis von $25,00 in die Summe aufgenommen werden.

SELECT SUM(CASE WHEN Price = 25 THEN Price END)
AS BooksSum
FROM Books;

BooksSum
50

In der nächsten Abfrage werden nun die Preise der Bücher, die weniger als 26,00 $ kosten, gemittelt. Die Anweisung CASE innerhalb der Funktion AVG() ermöglicht es, dass nur Bücher mit einem Preis unter 26,00 $ in den Durchschnitt einbezogen werden. Die Funktion AVG() ist ein Argument für die Funktion ROUND(), daher wird die Ausgabe der Funktion AVG() auf zwei Dezimalstellen gerundet.

SELECT ROUND(AVG(CASE WHEN Price < 26 THEN Price END), 2)
AS BooksAvg
FROM Books;

BooksAvg
23.33

In der nächsten Abfrage wird der Mindestpreis der Bücher über SQL ermittelt, die weniger als 26,00 $ kosten. Mit der Anweisung CASE innerhalb der Funktion MIN() können nur Bücher mit einem Preis unter 26,00 $ in die Menge aufgenommen werden.

SELECT MIN(CASE WHEN Price < 26 AND Title LIKE '%SQL%' THEN Price END)
AS BooksMin
FROM Books;

BooksMin
20

Als nächstes wird der Höchstpreis der Bücher ermittelt, die weniger als 25,00 $ kosten. Mit der Anweisung CASE innerhalb der Funktion MAX() können nur Bücher mit einem Preis unter 25,00 $ in die Menge aufgenommen werden.

SELECT MAX(CASE WHEN Price < 25 THEN Price END)
AS BooksMax
FROM Books;

BooksMax
20

Ich bin mir sicher, dass Sie sich die Ausgabe dieser Abfragen schon denken können!

Was passiert mit NULLs?

Die Antwort ist einfach. SQL-Aggregatfunktionen ignorieren NULL Werte.

Betrachten wir eine aktualisierte Books Tabelle. Diesmal haben wir einen NULL Preis.

SELECT * FROM Books;

Id	Author	Title	Price
234	Anthony Molinaro	SQL Cookbook	20.00
235	Alan Beaulieu	Learning SQL	25.00
236	Donald Knuth	Things a Computer Scientist Rarely Talks About	25.00
237	Donald Knuth	The Art of Computer Programming	NULL

COUNT(Price) now gibt 3 zurück, nicht 4, und SUM(Price) gibt 70,00 zurück. NULLs wird in beiden Fällen ignoriert.

Sie können Aggregatfunktionen auch mit JOINs verwenden! Werfen Sie einen Blick auf unseren Artikel über die Verwendung von SQL-Aggregatfunktionen mit JOINs, um mehr zu erfahren.

Die Rolle von HAVING und GROUP BY bei Aggregatfunktionen

Es ist einfach zu verstehen, was eine HAVING Klausel bewirkt, wenn Sie mit der WHERE Klausel vertraut sind. Eine HAVING Klausel filtert die Ausgabewerte von Aggregatfunktionen. Mit einer GROUP BY -Klausel können Sie Ihre Daten in Gruppen unterteilen und einen Aggregatwert für jede Gruppe finden.

Schauen wir uns ein Beispiel an.

SELECT Author, AVG(Price) AS AvgBookPrice
FROM Books
GROUP BY Author
HAVING AVG(Price) > 20;

Author	AvgBookPrice
Alan Beaulieu	25
Donald Knuth	26

Wir gruppieren die Daten nach der Spalte "Autor" mit einer GROUP BY -Klausel. Dann schränken wir die Werte von AVG(Price) mit Hilfe einer HAVING -Klausel auf Werte größer als 20 ein.

Wir können versuchen, die Klauseln WHERE und HAVING zusammen zu verwenden, um den Unterschied zwischen ihnen zu erkennen.

SELECT Author, AVG(Price) AS AvgBookPrice
FROM Books
WHERE Author LIKE 'A%'
GROUP BY Author
HAVING AVG(Price) > 20;

Author	AvgBookPrice
Alan Beaulieu	25

Die HAVING Klausel wird oft mit der WHERE Klausel verwechselt. Denken Sie daran, dass Sie keine Aggregatfunktionen in einer WHERE -Klausel verwenden können. Vergewissern Sie sich, dass Sie genügend Übung haben, und sehen Sie sich unser SQL HAVING-Tutorial an. Lesen Sie auch unseren Artikel über die Verwendung von GROUP BY in SQL, um noch mehr Einblick in die GROUP BY-Klausel zu erhalten.

SQL-Aggregatfunktionen oder SQL-Fensterfunktionen? Oder vielleicht beides? Lesen Sie unseren Artikel über die Gemeinsamkeiten und Unterschiede zwischen den beiden!

Lassen Sie uns üben!

Es reicht nicht aus, nur zu lesen. SQL erfordert eine Menge Übung. Wir zeigen Ihnen hier einige Beispiele, damit Sie selbständig weitermachen können!

Bevor wir uns mit den Beispielen befassen, sollten Sie sich über alle SQL-Grundlagen und Standard-SQL-Funktionen!

Beispiel-Datenbank

Unten sehen Sie den Entwurf der Datenbank. Wir werden diese Datenbank in unseren Beispielen weiter unten verwenden.

Analysieren wir den Datenbankentwurf, beginnend auf der linken Seite.

Die Tabelle Customers Tabelle speichert Daten über die Kunden. Ihr Primärschlüssel ist die Spalte CustomerId. Die Spalten Customers und Orders sind über die Spalte CustomerId miteinander verknüpft. Die Tabelle Orders speichert das Bestelldatum und die ID des Kunden, der die Bestellung aufgegeben hat. Ihr Primärschlüssel ist die Spalte OrderId. Die Verknüpfung zwischen den Tabellen Customers und Orders Tabellen definiert die Beziehung zwischen ihnen. Ein Kunde kann null oder mehr Bestellungen haben, aber eine Bestellung kann nur einem Kunden zugeordnet werden.

Die Tabellen Orders und OrderDetails sind über die Spalte OrderId miteinander verbunden. Die Tabellen Products und OrderDetails sind über die Spalte ProductId verknüpft. Der Primärschlüssel der Tabelle OrderDetails Tabelle besteht aus den Spalten OrderId und ProductId.

Eine Bestellung kann aus einem oder mehreren Produkten bestehen. Daher kann eine Zeile der Tabelle Orders Tabelle mit einer oder mehreren Zeilen aus der Tabelle OrderDetails Tabelle beziehen. Außerdem kann ein Produkt in null oder mehr Bestellungen enthalten sein. Daher kann eine Zeile aus der Products Tabelle mit null oder mehr Zeilen aus der Tabelle OrderDetails Tabelle beziehen.

Fügen wir nun einige Daten in unsere Tabellen ein.

Die Tabelle Customers Tabelle:

CustomerId	FirstName	LastName	Street	HouseNo	City	Country	Email	PhoneNo
1	Rachel	Hartley	Bicetown Road	602	New York	USA	rh@email.com	0123456789
2	Caitlyn	Ray	Friedrichstrasse	44	Berlin	Germany	cr@email.com	0987654321
3	Andrew	Duncan	Lairg Road	38	London	NULL	ad@email.com	0567432678
4	Taylor	Jenkins	Park Row	106	Edinburgh	UK	NULL	0876345123
5	Ben	Holland	Williams Avenue	252	Los Angeles	USA	bh@email.com	0987456789

Die Tabelle Orders Tabelle (die Datumsspalte hat das Format TT-MM-JJJJ):

OrderId	CustomerId	OrderDate
45	1	10-10-2021
46	2	11-12-2020
47	3	05-05-2021
48	4	09-08-2021
49	5	NULL
50	1	02-06-2021
51	2	07-07-2021

Die OrderDetails Tabelle:

OrderId	ProductId	Quantity
45	100	2
45	101	3
46	100	1
47	102	4
48	101	3
48	103	5
49	104	2
50	100	3
51	101	1

Die Products Tabelle:

ProductId	Name	UnitPrice	AvailableInStock
100	Keyboard	30.00	300
101	USB Drive	20.00	450
102	Mouse	20.00	500
103	Screen	100.00	450
104	Laptop	600.00	200

Wir sind nun bereit, mit den Beispielen zu beginnen.

Beispiele mit COUNT()

Wir beginnen mit der Customers Tabelle. Wir wollen herausfinden, wie viele Kunden es pro Land gibt.

SELECT Country, COUNT(CustomerId) AS NumberOfCustomers
FROM Customers
GROUP BY Country;

Country	NumberOfCustomers
NULL	1
Germany	1
UK	1
USA	2

Wir haben alle eindeutigen Werte aus der Spalte Country ausgewählt, einschließlich des Wertes NULL. Die Spalte NumberOfCustomers speichert die Anzahl der Kunden für jeden Wert der Spalte Country.

Was passiert, wenn wir die Spalte Email als Argument für die Funktion COUNT() verwenden?

SELECT Country, COUNT(Email) AS NumberOfCustomers
FROM Customers
GROUP BY Country;

Country	NumberOfCustomers
NULL	1
Germany	1
UK	0
USA	2

Der Wert der Spalte NumberOfCustomers für das Land "UK" wird Null. Das liegt daran, dass die Spalte Email in der Tabelle Customers Tabelle NULL für diesen Kunden ist.

Schauen wir uns nun ein Beispiel an, das die Klauseln GROUP BY und HAVING verwendet.

SELECT Country, COUNT(Email) AS NumberOfCustomersWithEmail
FROM Customers
WHERE Country IS NOT NULL
GROUP BY Country
HAVING COUNT(Email) > 1;

Country	NumberOfCustomersWithEmail
USA	2

Wie zuvor wählen wir die Werte aus der Spalte Land aus und erhalten die Anzahl der Kunden mit E-Mails nach Land. In der Klausel WHERE geben wir an, dass wir die Werte von NULL für die Spalte Country nicht berücksichtigen. Als nächstes gruppieren wir unsere Daten nach Country. Schließlich schränken wir die Werte der Spalte NumberOfCustomersWithEmail mit einer HAVING -Klausel auf Werte größer als 1 ein.

Beispiele mit SUM()

Lassen Sie uns prüfen, wie viel alle verfügbaren Produkte wert sind.

SELECT SUM(UnitPrice * AvailableInStock) AS AllProductsValue
FROM Products;

AllProductsValue
193000

Hier erzeugt die Aggregatfunktion SUM() für jede Zeile einen Wert von UnitPrice * AvailableInStock und addiert dann alle diese Werte.

Nehmen wir an, dass jede Bestellung mit einem Wert von mehr als 100,00 $ für einen Rabatt qualifiziert ist. Wir wollen herausfinden, welche Bestellungen für den Rabatt qualifizieren.

SELECT OrderId,
       CASE WHEN OrderValue > 100 THEN 1 ELSE 0 END
       AS QualifiesForDiscount
FROM
     ( SELECT aod.OrderId AS OrderId,
              SUM(aod.Quantity * ap.UnitPrice) AS OrderValue
       FROM Products ap
       JOIN OrderDetails aod
       ON ap.ProductId = aod.ProductId
       GROUP BY aod.OrderId );

OrderId	QualifiesForDiscount
45	1
46	0
47	0
48	1
49	1
50	0
51	0

Die innere Abfrage wählt alle OrderId Spaltenwerte aus und berechnet den Wert jeder Bestellung mit der Funktion SUM(). Die äußere Abfrage verwendet eine CASE Anweisung, um zu entscheiden, ob die Bestellung für einen Rabatt qualifiziert ist (1) oder nicht (0).

Angenommen, wir definieren alle Produkte mit einem Stückpreis von mehr als 90,00 $ als teuer. Lassen Sie uns den Gesamtwert aller teuren Produkte auf Lager herausfinden.

SELECT
    SUM(CASE WHEN UnitPrice > 90 THEN UnitPrice * AvailableInStock END)
    AS ExpensiveProductsValue
FROM Products;

ExpensiveProductsValue
165000

Wir haben eine Anweisung CASE als Argument an die Funktion SUM() übergeben. Mit diesem Argument wird sichergestellt, dass nur die Zeilen mit dem Wert UnitPrice größer als 90,00 $ berücksichtigt werden. Abgesehen davon ist dieses Beispiel dem ersten in diesem Abschnitt sehr ähnlich.

Beispiele mit AVG()

Lassen Sie uns prüfen, wie hoch der Durchschnittspreis einer Bestellung ist.

SELECT AVG(OrderValue) AS AvgOrderValue
FROM
     ( SELECT aod.OrderId AS OrderId,
              SUM(aod.Quantity * ap.UnitPrice) AS OrderValue
       FROM Products ap
       JOIN OrderDetails aod
       ON ap.ProductId = aod.ProductId
       GROUP BY aod.OrderId );

AvgOrderValue
300

Die innere Abfrage gibt den Gesamtwert der Bestellung für jede Bestellung aus. Die äußere Abfrage errechnet den Durchschnittswert einer Bestellung.

Wir können auch die durchschnittliche Bestellmenge pro Produkt herausfinden.

SELECT ROUND(AVG(Quantity), 2) AS AvgOrderQuantity
FROM OrderDetails;

AvgOrderQuantity
2.67

Im Durchschnitt kaufen unsere Kunden zwischen 2 und 3 Artikel eines bestimmten Produkts in einer Bestellung.

Schauen wir uns an, was sich ändert, wenn wir nur die eindeutigen Werte der Spalte Quantity berücksichtigen.

SELECT ROUND(AVG(DISTINCT Quantity), 2) AS AvgOrderQuantity
FROM OrderDetails;

AvgOrderQuantity
3

Der Ausgabewert ändert sich, da wir keine doppelten Werte mehr berücksichtigen, die in der Spalte Quantity der Tabelle OrderDetails Tabelle erscheinen.

Beispiele mit MAX() und MIN()

Zu guter Letzt! Die Funktionen MAX() und MIN() sind ziemlich einfach. Wir wollen die älteste und die jüngste Bestellung ermitteln.

SELECT MIN(OrderDate) AS EarliestOrder,
       MAX(OrderDate) AS LatestOrder
FROM Orders;

EarliestOrder	LatestOrder
11-12-2020	10-10-2021

Die Funktion MIN() liefert das älteste Datum und die Funktion MAX() das jüngste Datum.

Wir können auch die billigsten und die teuersten Produkte identifizieren. Sie können die Products Tabelle durchsuchen.

SELECT MIN(UnitPrice) AS CheapestProductPrice,
       MAX(UnitPrice) AS MostExpensiveProductPrice
FROM Products;

CheapestProductPrice	MostExpensiveProductPrice
20	600

Wir wollen herausfinden, wie viele Bestellungen es pro Kunde gibt, und dann die minimale und maximale Anzahl von Bestellungen pro Kunde ermitteln.

SELECT MIN(NumberOfOrders) AS MinNumberOfOrders,
       MAX(NumberOfOrders) AS MaxNumberOfOrders
FROM
     ( SELECT CustomerId,
              COUNT(OrderID) AS NumberOfOrders
       FROM Orders
       GROUP BY CustomerId );

MinNumberOfOrders	MaxNumberOfOrders
1	2

Die innere Abfrage wählt die Spalte CustomerId und die Gesamtzahl der von einem bestimmten Kunden aufgegebenen Bestellungen aus. Die Funktion COUNT(OrderId) zählt die Anzahl der Bestellungen pro Kunde. Als nächstes gruppieren wir unsere Daten nach der Spalte CustomerId mit Hilfe einer GROUP BY Klausel. In diesem Schritt zählt die Funktion COUNT(OrderId) die Bestellungen pro Kunde und nicht für alle Kunden zusammen. Die äußere Abfrage wählt die Mindest- und Höchstwerte der Spalte NumberOfOrders aus der inneren Abfrage aus.

SQL-Aggregatfunktionen als wesentliches Werkzeug in der Datenwissenschaft

Durch die Verwendung von Aggregatfunktionen können wir leicht Antworten auf bestimmte Fragen finden, z. B. wie viele Kunden es gibt oder wie hoch der Durchschnittspreis einer Bestellung ist. Mit SQL-Aggregatfunktionen können wir Daten effizient analysieren.

Diese zentralen SQL-Aggregatfunktionen sind in der Datenwissenschaft sehr hilfreich. Mit ihnen können Sie Daten so organisieren, wie Sie es wünschen, und die Informationen extrahieren, die Sie benötigen.

Wir haben viele Beispiele mit den Aggregatfunktionen COUNT(), SUM(), AVG(), MIN() und MAX() besprochen. Wir haben auch Beispiele für *, das Schlüsselwort DISTINCT und die Anweisung CASE als Argumente für Aggregatfunktionen behandelt. Jetzt können Sie Ihre Datenbank erstellen und weiter üben!

Tags:

Aggregatfunktionen