In der heutigen Lektion erfahren Sie mehr über die Gruppierung von Ergebnissen, die von Ihren Abfragen mithilfe der SQL GROUP BY-Klausel zurückgegeben wurden.
Die Ziele der heutigen Lektion sind:
- Erfahren Sie, wie Sie Ergebnisse mit GROUP BY
- Gruppieren Verwenden Sie Aggregatfunktionen, um Berechnungen durchzuführen
- Verstehen Sie, wie Sie Gruppen mithilfe der HAVING-Klausel
filtern Wichtig! Bitte folgen Sie und machen Sie die Beispiele in Ihrer Datenbank. Wenn Sie dies nicht bereits getan haben, melden Sie sich für meine Anleitung zum Einstieg in SQL Server., Sie erhalten Anweisungen zur Installation der kostenlosen Tools und der Beispieldatenbank.
SQL GROUP BY Klausel
Die SQL GROUP BY Klausel wird verwendet, um eine Zeile über angegebene Spaltenwerte hinweg auszugeben. Es wird normalerweise in Verbindung mit Aggregatfunktionen wie SUMME oder Zählung verwendet, um Werte zusammenzufassen. In SQL-Gruppen sind eindeutige Kombinationen von Feldern. Anstatt jede Zeile in einer Tabelle zurückzugeben, werden bei der Gruppierung von Werten nur die eindeutigen Kombinationen zurückgegeben.
Die GROUP BY-Klausel wird der SQL-Anweisung nach der WHERE-Klausel hinzugefügt., Hier ist ein Beispiel, in dem wir SalesOrderID auflisten, mit Ausnahme von Mengen größer als 100.
Es gibt ein paar Dinge zu beachten. Zunächst werden die Spalten, die wir zusammenfassen möchten, in der GROUP BY-Klausel durch Kommas getrennt aufgelistet. Zweitens muss dieselbe Spaltenliste in der select-Anweisung aufgeführt sein; Andernfalls schlägt die Anweisung fehl.,
Wenn diese Anweisung ausgeführt wird, wird nicht jede gefilterte Zeile zurückgegeben. Nur eindeutige Kombinationen von SalesOrderID sind im Ergebnis enthalten. Diese Aussage ist sehr ähnlich zu
Aber es gibt einen entscheidenden Unterschied., Der DISTINCT Modifikator stoppt bei der Ausgabe einer eindeutigen Kombination von Zeilen, während wir mit der GROUP BY-Anweisung Werte basierend auf den zugrunde liegenden gefilterten Zeilen für jede eindeutige Kombination berechnen können.
Mit anderen Worten, in unserem Beispiel können wir mit der GROUP BY die Anzahl oder Bestelldetails pro Bestellung wie folgt berechnen:
COUNT ist ein Beispiel für eine Aggregatfunktion.
SQL Aggregatfunktionen
Einige Funktionen wie SUM werden verwendet, um Berechnungen für eine Gruppe von Zeilen durchzuführen, diese werden Aggregatfunktionen genannt. In den meisten Fällen arbeiten diese Funktionen mit einer Gruppe von Werten, die mit der GROUP BY-Klausel definiert werden., Wenn es keine GROUP BY-Klausel gibt, wird allgemein verstanden, dass die Aggregatfunktion für alle gefilterten Ergebnisse gilt.
Einige der häufigsten Aggregatfunktionen umfassen:
| AVG(Ausdruck) | Berechnen Sie den Durchschnitt des Ausdrucks. |
| COUNT (expression) | Count Vorkommen von Nicht-Null-Werte durch den Ausdruck zurückgegeben. |
| COUNT(*) | Zählt alle Zeilen in der angegebenen Tabelle., |
| MIN (Ausdruck) | Findet den minimalen Ausdruckswert. |
| MAX (Ausdruck) | Findet den maximalen Ausdruckswert. |
| SUMME (Ausdruck) | Berechnen Sie die Summe des Ausdrucks. |
Diese Funktionen können verwendet werden auf Ihre eigenen, in Verbindung mit der GROUP BY-Klausel. Für sich genommen arbeiten sie über die gesamte Tabelle hinweg; Wenn sie jedoch mit GROUP BY verwendet werden, werden ihre Berechnungen jedes Mal „zurückgesetzt“, wenn sich die Gruppierung ändert., Auf diese Weise fungieren sie als Zwischensummen.
Einfache Aggregatfunktion
Bei Verwendung der Aggregatfunktion können Sie entweder das Ergebnis für alle Werte oder unterschiedliche Werte berechnen. Um beispielsweise alle SalesOrderDetails-Datensätze zu zählen, können wir den Ausdruck verwenden:
Dies führt dazu, dass eine Anzahl von 121317 zurückgegeben wird.
Um die Anzahl der Bestellungen zu zählen, aus denen die Details bestehen, würden wir Folgendes verwenden:
SELECT COUNT(Distinct SalesOrderID)
FROM Sales.SalesOrderDetail
Die Anzahl beträgt jetzt 31465.,
SQL GROUP BY und Aggregatfunktionen
Zu aggregieren bedeutet, ganz aus einzelnen Teilen zu machen. Aggregatfunktionen sind Funktionen, die an mehr als einer Zeile arbeiten, um ein Ergebnis zurückzugeben.
AVG und SUM
Die Summenfunktion summiert die zurückgegebenen Werte, in ähnlicher Weise berechnet AVG den Durchschnitt.
Mal sehen, ob wir den Gesamtauftragsbetrag aus den Bestelldetails berechnen können., Aus früheren Lektionen wissen wir, wie man den Gesamtbetrag für jedes Detail berechnet, wie folgt:
SELECT SalesOrderID,
ProductID,
OrderQty* UnitPrice As ExtendedPrice
FROM Sales.SalesOrderDetail
Da wir die Aggregatfunktion auf Ausdrücke anwenden können, können wir eine Gruppierung für OrderID einrichten, um den Gesamtpreis pro Bestellung als
SELECT SalesOrderID, SUM(OrderQty * UnitPrice) AS TotalPriceFROM Sales.SalesOrderDetailGROUP BY SalesOrderID
Wir können sogar nach der Summe sortieren, um zuerst die obersten Bestellungen zu erhalten
SELECT SalesOrderID,
SUM(OrderQty * UnitPrice) AS TotalPrice
FROM Sales.SalesOrderDetail
GROUP BY SalesOrderID
ORDER BY TotalPrice DESC
> In ähnlicher Weise können wir die durchschnittliche Auftragsdetails mit AVG berechnen. Warum versuchen Sie es unten! Wenn Sie nicht weiterkommen, schalten Sie die Antwort um.
Für Neugierige, da ein Durchschnitt als Summe der Probe dividiert durch die Stichprobenzahl berechnet wird, ist die Verwendung von AVG in der obigen Anweisung dasselbe wie:
SELECT SalesOrderID,
SUM(OrderQty * UnitPrice) / COUNT(SalesOrderID) AS AvgOrderAmount FROM Sales.SalesOrderDetail
GROUP BY SalesOrderID
Wir haben in diesem Abschnitt viel abgedeckt., Hier sind einige wichtige Punkte zu beachten:
- Eine Aggregatfunktion kann einen Ausdruck wie SUM(A + B)
- Sie sollten Aggregatfunktionen Alias, so dass die Spaltennamen sinnvoll sind
- Wenn Sie mit Aggregatfunktionen und GROUP BY arbeiten, ist es manchmal einfacher, zuerst über die Details nachzudenken, dh eine einfache SELECT-Anweisung zu schreiben, die Ergebnisse zu überprüfen und dann die ausgefallenen Dinge hinzuzufügen. Ich behandle dies, wie man bessere Abfragen schreibt.
SQL COUNT
Die COUNT-Funktion wird verwendet, wenn Sie wissen müssen, wie viele Datensätze in einer Tabelle oder innerhalb einer Gruppe vorhanden sind., COUNT (*) zählt jeden Datensatz in der Gruppierung; während COUNT(expression) jeden Datensatz zählt, bei dem das Ergebnis des Ausdrucks nicht null ist. Sie können Distinct with COUNT auch verwenden, um die Anzahl eindeutiger Werte innerhalb einer Gruppe zu ermitteln.
Um die Anzahl der SalesOrderDetail-Zeilen pro Bestellung zu ermitteln
SELECT SalesOrderID,
COUNT(SalesOrderDetailID)
FROM Sales.SalesOrderDetail
GROUP BY SalesOrderID
Um die Anzahl der eindeutigen Bestellungen pro Produkt zu ermitteln
SELECT ProductID,
COUNT(DISTINCT SalesOrderID)
FROM Sales.SalesOrderDetail
GROUP BY ProductID
MIN und MAX
Verwenden Sie MIN und MAX, um die kleinsten bzw. größten Werte innerhalb einer Tabelle oder Gruppe zu ermitteln.,
Um beispielsweise die kleinsten und größten innerhalb einer Bestellung bestellten Produktmengen zu finden, versuchen Sie es mit
SELECT SalesOrderID,
Min(OrderQty) AS MinQuantity,
Max(OrderQty) AS MaxQuantity
FROM Sales.SalesOrderDetail
GROUP BY SalesOrderID
Sie können auch den MIN-oder MAX-Wert einer Berechnung finden. Hier finden wir die höchste in einem Produkt bestellte Produktmenge:
SELECT SalesOrderID,
MAX(UnitPrice * OrderQty) as MaxAmount
FROM Sales.SalesOrderDetail GROUP BY SalesOrderID
HAVING Klausel
Die SQL HAVING Klausel wird verwendet, um Gruppen nach den Ergebnissen der Aggregatfunktionen zu filtern. Dies ermöglicht es, Probleme wie die Auswahl aller Bestellungen mit mehr als zwei Bestelldetailzeilen zu lösen.,
Dieses Beispiel sieht aus wie
SELECT SalesOrderID,
COUNT(SalesOrderDetailID)
FROM Sales.SalesOrderDetail
GROUP BY SalesOrderID
HAVING Count(SalesOrderDetailID) > 2
Wenn wir alle Bestellungen größer als $1000 finden wollten, würden wir
SELECT SalesOrderID,
SUM(UnitPrice * OrderQty) AS TotalPrice
FROM Sales.SalesOrderDetail
GROUP BY SalesOrderID
HAVING SUM(UnitPrice * OrderQty) > 1000
ORDER BY TotalPrice DESC
Beachten Sie, dass wir den Alias TotalPrice in der ORDER BY-Klausel verwenden können, aber dann muss eine Klausel den Ausdruck verwenden.
Um nach Hause zu hämmern, möchte ich ein letztes Beispiel zeigen. Hier sehen Sie, dass die HAVING Anweisung eine Aggregatfunktion enthält, die nicht in der Auswahlliste enthalten ist.,
SELECT SalesOrderID,
SUM(UnitPrice * OrderQty) AS TotalPrice
FROM Sales.SalesOrderDetail
GROUP BY SalesOrderID
HAVING AVG(UnitPrice * OrderQty) > 500
ORDER BY TotalPrice DESC
In der obigen Abfrage erhalten wir den Gesamtpreis für Bestellungen, bei denen der durchschnittliche SalesOrderDetail-Betrag größer als $500.00 ist.
Endgültige Aussage darüber, DASS Sie
Wenn Sie erfüllen eine ähnliche Funktion, es gibt einen entscheidenden Unterschied zwischen der WHERE-Klausel und. Die WHERE-Klausel zum Filtern einzelner Datensätze; wohingegen die HAVING-Klausel die Gruppen filtert.
Um es gerade in meinem Kopf zu halten, denke ich gerne an die WHERE-Klausel, die ihre Arbeit erledigt, bevor Gruppierungen stattfinden, und dann übernimmt die HAVING-Klausel, nachdem die Gruppen gebildet wurden.,
Übungen
Es ist wichtig, zu üben! Verwenden Sie die Beispieldatenbank, um diese Fragen zu beantworten.
- HR will einen Bericht über die Anzahl der aktiven Mitarbeiter nach Berufsbezeichnung. Welche SQL-würden Sie verwenden?
- Zeigen Sie die bestellte Min -, Max-und Durchschnittsmenge für jedes Produkt in SalesOrderDetails an.
- Liste aller Mitarbeiter Berufsbezeichnungen und Anzahl der Mitarbeiter, bei denen die durchschnittliche Anzahl der Krankenstunden kleiner oder gleich vierzig ist.
- Ist die Anzahl für einen in #3 oben zurückgegebenen Jobtitel für das Ergebnis der entsprechenden Jobtitel-Antwort #1 gleich?
Antworten sind hier!,
Leave a Reply