23rd Jun 2022 13 Leseminuten Was sind Aggregatfunktionen in SQL, und wie verwende ich sie? Martyna Sławińska SQL SQL lernen Aggregatefunktionen Inhaltsverzeichnis Wie Aggregatfunktionen funktionieren Das * Argument bei Aggregatfunktionen Das Schlüsselwort DISTINCT Die CASE-Anweisung Was passiert mit NULLs? Die Rolle von HAVING und GROUP BY bei Aggregatfunktionen Lassen Sie uns üben! Beispiel-Datenbank Beispiele mit COUNT() Beispiele mit SUM() Beispiele mit AVG() Beispiele mit MAX() und MIN() 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; IdAuthorTitlePrice 234Anthony MolinaroSQL Cookbook20.00 235Alan BeaulieuLearning SQL25.00 236Donald KnuthThings a Computer Scientist Rarely Talks About25.00 237Donald KnuthThe Art of Computer Programming27.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; AuthorNumberOfBooks Anthony Molinaro1 Alan Beaulieu1 Donald Knuth2 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; IdAuthorTitlePrice 234Anthony MolinaroSQL Cookbook20.00 235Alan BeaulieuLearning SQL25.00 236Donald KnuthThings a Computer Scientist Rarely Talks About25.00 237Donald KnuthThe Art of Computer ProgrammingNULL 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; AuthorAvgBookPrice Alan Beaulieu25 Donald Knuth26 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; AuthorAvgBookPrice Alan Beaulieu25 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: CustomerIdFirstNameLastNameStreetHouseNoCityCountryEmailPhoneNo 1RachelHartleyBicetown Road602New YorkUSArh@email.com0123456789 2CaitlynRayFriedrichstrasse44BerlinGermanycr@email.com0987654321 3AndrewDuncanLairg Road38LondonNULLad@email.com0567432678 4TaylorJenkinsPark Row106EdinburghUKNULL0876345123 5BenHollandWilliams Avenue252Los AngelesUSAbh@email.com0987456789 Die Tabelle Orders Tabelle (die Datumsspalte hat das Format TT-MM-JJJJ): OrderIdCustomerIdOrderDate 45110-10-2021 46211-12-2020 47305-05-2021 48409-08-2021 495NULL 50102-06-2021 51207-07-2021 Die OrderDetails Tabelle: OrderIdProductIdQuantity 451002 451013 461001 471024 481013 481035 491042 501003 511011 Die Products Tabelle: ProductIdNameUnitPriceAvailableInStock 100Keyboard30.00300 101USB Drive20.00450 102Mouse20.00500 103Screen100.00450 104Laptop600.00200 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; CountryNumberOfCustomers NULL1 Germany1 UK1 USA2 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; CountryNumberOfCustomers NULL1 Germany1 UK0 USA2 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; CountryNumberOfCustomersWithEmail USA2 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 ); OrderIdQualifiesForDiscount 451 460 470 481 491 500 510 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; EarliestOrderLatestOrder 11-12-202010-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; CheapestProductPriceMostExpensiveProductPrice 20600 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 ); MinNumberOfOrdersMaxNumberOfOrders 12 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: SQL SQL lernen Aggregatefunktionen