Dieser Artikel behandelt die SQL PARTITION BY-Klausel und, insbesondere der Unterschied zu GROUP BY in einer select-Anweisung. Wir werden auch verschiedene Anwendungsfälle von SQL PARTITION BY untersuchen.
Wir verwenden SQL PARTITION BY, um die Ergebnismenge in Partitionen zu unterteilen und Berechnungen für jede Teilmenge partitionierter Daten durchzuführen.
Beispiel vorbereiten Daten
Lassen Sie uns eine Auftragstabelle in meiner Beispieldatenbank SQLShackDemo erstellen und Datensätze einfügen, um weitere Abfragen zu schreiben.
Ich verwende ApexSQL Generate, um Beispieldaten in diesen Artikel einzufügen. Klicken Sie mit der rechten Maustaste auf die Tabelle „Bestellungen“ und generieren Sie Testdaten.
Es wird ApexSQL Generate gestartet. Ich habe ein Skript generiert, um Daten in die Orders-Tabelle einzufügen. Führen Sie dieses Skript aus, um 100 Datensätze in die Orders-Tabelle einzufügen.
Sobald wir Insert-Anweisungen ausführen, sehen wir die Daten in der Orders-Tabelle im folgenden Bild.
Wir verwenden die SQL GROUP BY-Klausel, um Ergebnisse nach angegebenen Spalten zu gruppieren, und verwenden Aggregatfunktionen wie Avg (), Min (), Max (), um die erforderlichen Werte zu berechnen.
Gruppieren nach Funktionssyntax
|
1
2
3
4
|
SELECT-Ausdruck, Aggregatfunktion ()
FROM-Tabellen
WHERE-Bedingungen
GROUP BY-Ausdruck
|
Angenommen, wir möchten die folgenden Werte in der Tabelle „Bestellungen“ finden.
- Minimaler Bestellwert in einer Stadt
- Maximaler Bestellwert in einer Stadt
- Durchschnittlicher Bestellwert in einer Stadt
Führen Sie die folgende Abfrage mit der GROUP BY-Klausel aus, um diese Werte zu berechnen.
|
1
2
3
4
5
6
|
SELECT Customercity,
AVG (Orderamount) AS AvgOrderAmount,
MIN (OrderAmount) AS MinOrderAmount,
SUM ( Bestellbetrag) TotalOrderAmount
FROM.
GROUP BY Customercity;
|
Im folgenden Screenshot sehen wir Durchschnitt, Minimum und nach CustomerCity gruppierte Maximalwerte.
Nun möchten wir der Spalte auch CustomerName und OrderAmount hinzufügen. Fügen wir diese Spalten in die select-Anweisung ein und führen Sie den folgenden Code aus.
|
1
2
3
4
5
6
|
SELECT Customercity, CustomerName, OrderAmount,
AVG (Orderamount) AS AvgOrderAmount,
MIN (OrderAmount) AS MinOrderAmount,
SUM (Orderamount) TotalOrderAmount
FROM.
GROUP BY Customercity;
|
Sobald wir diese Abfrage ausführen, erhalten wir eine Fehlermeldung . In der SQL GROUP BY-Klausel können wir eine Spalte in der select-Anweisung verwenden, wenn sie auch in der Group by-Klausel verwendet wird. Es ist keine Spalte in der select-Klausel zulässig, die nicht Teil der GROUP BY-Klausel ist.
Wir können SQL verwenden PARTITION BY-Klausel zur Behebung dieses Problems. Lassen Sie uns dies im nächsten Abschnitt näher untersuchen.
SQL PARTITION BY
Wir können die SQL PARTITION BY-Klausel mit der OVER-Klausel verwenden, um die Spalte anzugeben, für die eine Aggregation durchgeführt werden muss . Im vorherigen Beispiel haben wir Group By mit der Spalte CustomerCity verwendet und Durchschnitts-, Minimal- und Maximalwerte berechnet.
Lassen Sie uns dieses Szenario mit der SQL PARTITION BY-Klausel unter Verwendung der folgenden Abfrage erneut ausführen.
|
1
2
3
4
5
|
SELECT Customercity,
AVG (Bestellbetrag) OVER (PARTITION BY Customercity) AS AvgOrderAmount,
MIN (OrderAmount) OVER (PARTITION BY Customercity) AS MinOrderAmount,
SUM (Orderamount) OVER (PARTITION BY Customercity) TotalOrderAmount
FROM .;
|
In der Ausgabe erhalten wir aggregierte Werte ähnlich a GROUP By-Klausel. Möglicherweise stellen Sie einen Unterschied in der Ausgabe der Ausgabe der SQL PARTITION BY- und GROUP BY-Klausel fest.
|
Gruppieren nach |
SQL PARTITION BY |
|
Mit der Group By-Klausel |
Wir erhalten alle Datensätze in einer Tabelle mithilfe der PARTITION BY-Klausel. |
|
Es gibt einen Zeile pro Gruppe in der Ergebnismenge. Beispielsweise erhalten wir ein Ergebnis für jede Gruppe von CustomerCity in der GROUP BY-Klausel. |
Es werden aggregierte Spalten mit jedem Datensatz in der angegebenen Tabelle angegeben. Wir haben 15 Datensätze in der Tabelle „Bestellungen“. In der Abfrageausgabe von SQL PARTITION BY erhalten wir außerdem 15 Zeilen zusammen mit Min-, Max- und Durchschnittswerten. |
Im vorherigen Beispiel wird eine Fehlermeldung angezeigt, wenn wir versuchen, eine Spalte hinzuzufügen, die nicht Teil der GROUP BY-Klausel ist.
Mit der SQL PARTITION BY-Klausel können wir erforderliche Spalten in einer select-Anweisung hinzufügen . Fügen Sie die Spalten CustomerName und OrderAmount hinzu und führen Sie die folgende Abfrage aus.
|
1
2
3
4
5
6
7
|
SELECT Customercity,
CustomerName,
OrderAmount,
AVG (Orderamount) OVER (PARTITION BY Customercity) AS AvgOrderAmount ,
MIN (OrderAmount) OVER (PARTITION BY Customercity) AS MinOrderAmount,
SUM (Orderamount) OVER (PARTITION BY Customercity) TotalOrderAmount
FROM .;
|
Wir erhalten die Spalten CustomerName und OrderAmount zusammen mit der Ausgabe von die aggregierte Funktion. Wir erhalten auch alle Zeilen in der Tabelle „Bestellungen“.
Im folgenden Screenshot können Sie dies für CustomerCity Chicago ausführen Aggregationen (Avg, Min und Max) und gibt Werte in den jeweiligen Spalten an.
In ähnlicher Weise können wir auch andere Aggregatfunktionen verwenden, z als Anzahl, um die Gesamtzahl der Bestellungen in einer bestimmten Stadt mit der SQL-Klausel PARTITION BY herauszufinden.
|
1
2
3
4
5
6
7
8
|
SELECT Customercity,
Kundenname,
OrderAmount,
COUNT (OrderID) OVER (PARTITION BY Customercity) ALS CountOfOrders,
AVG (Orderamount) OVER (PARTITION BY Customercity) AS AvgOrderAmount,
MIN (OrderAmount) OVER (PARTITIO) N NACH Customercity) AS MinOrderAmount,
SUMME (Orderamount) OVER (PARTITION BY Customercity) TotalOrderAmount
FROM .;
|
Wir können die Anzahl der Bestellungen für eine bestimmte Stadt anzeigen. Zum Beispiel haben wir zwei Bestellungen aus Austin City; Es zeigt den Wert 2 in der Spalte CountofOrders.
PARTITION BY-Klausel mit ROW_NUMBER ()
Wir können Verwenden Sie die SQL PARTITION BY-Klausel mit der Funktion ROW_NUMBER (), um eine Zeilennummer für jede Zeile zu erhalten. Wir definieren die folgenden Parameter, um ROW_NUMBER mit der SQL-Klausel PARTITION BY zu verwenden.
- Spalte PARTITION BY – In diesem Beispiel möchten wir Daten in der CustomerCity-Spalte
- Order By partitionieren : In der Spalte ORDER BY definieren wir eine Spalte oder Bedingung, die die Zeilennummer definiert. In diesem Beispiel möchten wir Daten in der OrderAmount-Spalte sortieren.
|
1
2
3
4
5
6
7
8
9
10
|
SELECT Customercity,
CustomerName,
ROW_NUMBER () OVER (PARTITION NACH Customercity
ORDER BY OrderAmount DESC) ALS „Zeilennummer“,
OrderAmount,
COUNT (OrderID) OVER (PARTITION BY Customercity) AS CountOfOrders,
AVG (Orderamount) OVER (PARTITION BY Customercity) AS AvgOrderAmount,
MIN (OrderAmount) OVER (PARTITION BY Customercity) AS MinOrderAmount,
SUM (Orderamount) OVER (PARTITION BY Customercity) TotalOrderAmount
FROM .;
|
Im folgenden Screenshot sehen wir CustomerCity Chicago Wir haben Zeile Nummer 1 für die Bestellung mit dem höchsten Betrag 7577,90. Es liefert die Zeilennummer mit absteigendem OrderAmount.
PARTITION BY-Klausel mit kumulativem Gesamtwert
Angenommen, wir Sie möchten eine kumulative Summe für die Bestellungen in einer Partition erhalten.Die kumulative Summe sollte aus der aktuellen Zeile und der folgenden Zeile in der Partition bestehen.
Zum Beispiel in der Stadt Chicago. Wir haben vier Bestellungen.
|
CustomerCity |
CustomerName |
Rang |
OrderAmount |
Kumulative Gesamtzeilen |
Kumulative Summe |
|
Chicago |
Marvin |
Rang 1 +2 |
|||
|
Chicago |
Lawrence |
Rang 2 + 3 |
|||
|
Chicago |
Alex |
Rang 3 + 4 |
|||
|
Chicago |
Jerome |
Rang 4 |
In der folgenden Abfrage haben wir die angegebene ROWS-Klausel zu se Lesen Sie die aktuelle Zeile (mit CURRENT ROW) und die nächste Zeile (mit 1 FOLLOWING). Es berechnet ferner die Summe in diesen Zeilen unter Verwendung von sum (Orderamount) mit einer Partition in CustomerCity (unter Verwendung von OVER (PARTITION BY Customercity ORDER BY OrderAmount DESC)).
|
1
2
3
4
5
6
7
|
SELECT Customercity,
Kundenname,
OrderAmount,
ROW_NUMBER () OVER (PARTITION NACH Customercity
ORDER BY OrderAmount DESC) ALS „Zeilennummer“,
CONVERT (VARCHAR (20), SUM (orderamount) OVER (TEILUNG NACH Kundenspezifisch
BESTELLUNG NACH OrderAmount DESC-REIHEN ZWISCHEN AKTUELLER REIHE UND 1 FOLGENDEN), 1) AS CumulativeTotal,
|
In ähnlicher Weise können wir den kumulativen Durchschnitt mit dem berechnen folgende Abfrage mit der SQL PARTITION BY-Klausel.
|
1
2
3
4
5
6
7
|
SELECT Customercity,
CustomerName,
OrderAmount,
ROW_NUMBER () OVER (PARTITION BY Customercity
ORDER BY OrderAmount DESC) ALS „Zeilennummer“,
CONVERT (VARCHAR (20), AVG (Bestellbetrag) OVER (TEILUNG NACH Kundenspezifität
BESTELLUNG NACH OrderAmount DESC-REIHEN ZWISCHEN AKTUELLER REIHE UND 1 FOLGENDEM) , 1) AS CumulativeAVG
|
ROWS UNBOUNDED PRECEDING mit der PARTITION BY-Klausel
Wir können ROWS UNBOUNDED PRECEDING mit der SQL PARTITION BY-Klausel verwenden, um eine Zeile in einer Partition auszuwählen vor der aktuellen Zeile und dem höchsten Wert r ow nach der aktuellen Zeile.
In der folgenden Tabelle sehen wir für Zeile 1; Es gibt keine Zeile mit einem hohen Wert in dieser Partition. Daher entspricht der kumulative Durchschnittswert dem von OrderAmount in Zeile 1.
Für Zeile 2 wird nach dem aktuellen Zeilenwert (7199,61) und dem höchsten Wert in Zeile 1 (7577,9) gesucht. Es berechnet den Durchschnitt für diese beiden Beträge.
Für Zeile 3 wird nach dem aktuellen Wert (6847,66) und einem höheren Betragswert als diesem Wert gesucht, der 7199,61 und 7577,90 beträgt. Es berechnet den Durchschnitt dieser und gibt zurück.
|
CustomerCity |
Kundenname |
Rang |
OrderAmount |
Kumulative durchschnittliche Zeilen |
Kumulativer Durchschnitt |
|
Chicago |
Marvin |
Rang 1 |
|||
|
Chicago |
Lawrence |
Rang 1 + 2 |
|||
|
Chicago |
Alex |
Rang 1 + 2 + 3 |
|||
|
Chicago |
Jerome |
Rang 1 + 2 + 3 + 4 |
Führen Sie die folgende Abfrage aus, um dieses Ergebnis mit unseren Beispieldaten zu erhalten.
|
1
2
3
4
5
6
7
8
|
SELECT Customercity,
CustomerName,
OrderAmount,
ROW_NUMBER () OVER (PARTITION BY Customercity
ORDER BY OrderAmount DESC) ALS „Zeilennummer“,
CONVERT (VARCHAR (20), AVG (orderamount) OVER (TEILUNG NACH Kundenspezifität
ORDER BY OrderAmount DESC ROWS UNBOUNDED PRECEDING), 1) AS CumulativeAvg
FROM .;
|
Schlussfolgerung
In diesem Artikel haben wir die SQL PARTIION BY-Klausel und ihren Vergleich mit der GROUP BY-Klausel untersucht. Wir haben seine Verwendung auch anhand einiger Beispiele gelernt. Ich hoffe, Sie finden diesen Artikel nützlich und können Fragen in den Kommentaren unten stellen.
- Autor
- Letzte Beiträge
Mit seiner 50-teiligen Serie zu SQL Server Always On Availability Groups ist er der Schöpfer einer der größten kostenlosen Online-Artikelsammlungen zu einem einzigen Thema. Aufgrund seines Beitrags zur SQL Server-Community wurde er mit verschiedenen Preisen ausgezeichnet, darunter der renommierte „Beste Autor des Jahres“ in den Jahren 2020 und 2021 bei SQLShack.
Raj ist immer an neuen Herausforderungen interessiert, wenn Sie also Beratung benötigen Hilfe zu jedem Thema, das in seinen Schriften behandelt wird, kann unter [email protected] erreicht werden.
Alle Beiträge von Rajendra Gupta anzeigen
- Sitzungszeitlimits in SQL Server Immer verfügbar Gruppen – 8. Februar 2021
- Durchführen kleinerer und größerer Versionsaktualisierungen für AWS RDS SQL Server – 29. Januar 2021
- Bereitstellen von AWS RDS PostgreSQL-Instanzen – 27. Januar 2021