Dit artikel behandelt de SQL PARTITION BY clausule en, in het bijzonder het verschil met GROUP BY in een select statement. We zullen ook verschillende gebruiksscenarios van SQL PARTITION BY onderzoeken.
We gebruiken SQL PARTITION BY om de resultatenset in partities te verdelen en berekeningen uit te voeren op elke subset van gepartitioneerde gegevens.
Voorbeeld voorbereiden Data
Laten we een Orders-tabel maken in mijn voorbeelddatabase SQLShackDemo en records invoegen om verdere queries te schrijven.
Ik gebruik ApexSQL Generate om voorbeeldgegevens in dit artikel in te voegen. Klik met de rechtermuisknop op de tabel Orders en genereer testgegevens.
Het start de ApexSQL Generate. Ik heb een script gegenereerd om gegevens in de tabel Orders in te voegen. Voer dit script uit om 100 records in de tabel Orders in te voegen.
Zodra we invoeginstructies hebben uitgevoerd, kunnen we de gegevens in de tabel Orders in de volgende afbeelding zien.
We gebruiken de SQL GROUP BY-clausule om resultaten te groeperen op gespecificeerde kolom en gebruiken geaggregeerde functies zoals Avg (), Min (), Max () om vereiste waarden te berekenen.
Groeperen op functiesyntaxis
|
1
2
3
4
|
SELECT expressie, geaggregeerde functie ()
FROM tabellen
WAAR voorwaarden
GROUP BY expressie
|
Stel dat we de volgende waarden willen vinden in de tabel Bestellingen
- Minimale bestelwaarde in een stad
- Maximale bestelwaarde in een stad
- Gemiddelde bestelwaarde in een stad
Voer de volgende zoekopdracht uit met de GROUP BY-clausule om deze waarden te berekenen.
|
1
2
3
4
5
6
|
SELECT Customercity,
AVG (Orderamount) AS AvgOrderAmount,
MIN (OrderAmount) AS MinOrderAmount,
SUM ( Orderamount) TotalOrderAmount
FROM.
GROEP BY Customercity;
|
In de volgende schermafbeelding zien we gemiddelde, minimum en maximale waarden gegroepeerd op CustomerCity.
Nu willen we de kolom CustomerName en OrderAmount ook aan de uitvoer toevoegen. Laten we deze kolommen toevoegen aan de select-instructie en de volgende code uitvoeren.
|
1
2
3
4
5
6
|
SELECTEER Customercity, CustomerName, OrderAmount,
AVG (Orderamount) AS AvgOrderAmount,
MIN (OrderAmount) AS MinOrderAmount,
SUM (Orderamount) TotalOrderAmount
VAN.
GROEP BY Customercity;
|
Zodra we deze zoekopdracht uitvoeren, krijgen we een foutmelding . In de SQL GROUP BY-clausule kunnen we een kolom in de select-instructie gebruiken als deze ook in de Group by-clausule wordt gebruikt. Het staat geen enkele kolom toe in de select-clausule die geen deel uitmaakt van de GROUP BY-clausule.
We kunnen de SQL gebruiken PARTITION BY-clausule om dit probleem op te lossen. Laten we het verder onderzoeken in de volgende sectie.
SQL PARTITION BY
We kunnen de SQL PARTITION BY-clausule gebruiken met de OVER-clausule om de kolom te specificeren waarop we aggregatie moeten uitvoeren . In het vorige voorbeeld gebruikten we Group By met CustomerCity-kolom en berekende gemiddelde, minimum- en maximumwaarden.
Laten we dit scenario opnieuw uitvoeren met de SQL PARTITION BY-component met behulp van de volgende query.
|
1
2
3
4
5
|
SELECT Customercity,
AVG (Orderamount) OVER (PARTITION BY Customercity) AS AvgOrderAmount,
MIN (OrderAmount) OVER (PARTITION BY Customercity) AS MinOrderAmount,
SUM (Orderamount) OVER (PARTITION BY Customercity) TotalOrderAmount
FROM.;
|
In de uitvoer krijgen we geaggregeerde waarden die lijken op een GROUP By-clausule. Mogelijk merkt u een verschil in de uitvoer van de uitvoer van de SQL PARTITION BY en GROUP BY-component.
|
Groeperen op |
SQL PARTITIE DOOR |
|
We krijgen een beperkt aantal records met behulp van de Group By-clausule |
We krijgen alle records in een tabel met behulp van de PARTITION BY-clausule. |
|
Het geeft een rij per groep in resultaatset. We krijgen bijvoorbeeld een resultaat voor elke groep CustomerCity in de GROUP BY-clausule. |
Het geeft geaggregeerde kolommen met elk record in de opgegeven tabel. We hebben 15 records in de tabel Orders. In de query-uitvoer van SQL PARTITION BY krijgen we ook 15 rijen samen met Min, Max en gemiddelde waarden. |
In het vorige voorbeeld krijgen we een foutmelding als we proberen een kolom toe te voegen die geen deel uitmaakt van de GROUP BY-clausule.
We kunnen vereiste kolommen toevoegen in een select-instructie met de SQL PARTITION BY-clausule . Laten we de kolommen CustomerName en OrderAmount toevoegen en de volgende zoekopdracht uitvoeren.
|
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.;
|
We krijgen de kolom CustomerName en OrderAmount samen met de uitvoer van de geaggregeerde functie. We krijgen ook alle rijen beschikbaar in de tabel Orders.
In de volgende schermafbeelding kunt u voor CustomerCity Chicago presteren aggregaties (Avg, Min en Max) en geeft waarden in respectieve kolommen.
Evenzo kunnen we andere aggregatiefuncties gebruiken, zoals als count om het totale aantal bestellingen in een bepaalde stad te achterhalen met de SQL PARTITION BY-clausule.
|
1
2
3
4
5
6
7
8
|
SELECTEER Customercity,
CustomerName,
OrderAmount,
COUNT (OrderID) OVER (PARTITION BY Customercity) AS CountOfOrders,
AVG (Orderamount) OVER (PARTITION BY Customercity) AS AvgOrderAmount,
MIN (OrderAmount) OVER (PARTITIO N BY Customercity) AS MinOrderAmount,
SUM (Orderamount) OVER (PARTITION BY Customercity) TotalOrderAmount
FROM.;
|
We kunnen het aantal bestellingen voor een bepaalde stad zien. We hebben daarom bijvoorbeeld twee bestellingen uit de stad Austin; het toont waarde 2 in de CountofOrders-kolom.
PARTITION BY-clausule met ROW_NUMBER ()
We kunnen gebruik de SQL PARTITION BY-clausule met de functie ROW_NUMBER () om een rijnummer van elke rij te hebben. We definiëren de volgende parameters om ROW_NUMBER te gebruiken met de SQL PARTITION BY-clausule.
- PARTITION BY-kolom – In dit voorbeeld willen we gegevens partitioneren in de CustomerCity-kolom
- Order By : In de ORDER BY-kolom definiëren we een kolom of voorwaarde die het rijnummer definieert. In dit voorbeeld willen we gegevens sorteren in de OrderAmount-kolom
|
1
2
3
4
5
6
7
8
9
10
|
SELECTEER Customercity,
CustomerName,
ROW_NUMBER () OVER (PARTITION BY Customercity
ORDER BY OrderAmount DESC) AS “Row Number”,
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.;
|
In de volgende schermafbeelding zien we voor CustomerCity Chicago , we hebben rij nummer 1 voor bestelling met het hoogste bedrag 7577.90. het geeft een rijnummer met aflopende OrderAmount.
PARTITION BY-clausule met cumulatieve totale waarde
Stel dat we wil een cumulatief totaal krijgen voor de bestellingen in een partitie.Het cumulatieve totaal moet van de huidige rij en de volgende rij in de partitie zijn.
Bijvoorbeeld, in de stad Chicago, we hebben vier bestellingen.
|
CustomerCity |
Klantnaam |
Rang |
OrderAmount |
Cumulatief totaal aantal rijen |
Cumulatief totaal |
|
Chicago |
Marvin |
Rang 1 +2 |
|||
|
Chicago |
Lawrence |
Rang 2 + 3 |
|||
|
Chicago |
Alex |
Rang 3 + 4 |
|||
|
Chicago |
Jerome |
Rang 4 |
In de volgende query hebben we de opgegeven ROWS-clausule moeten se lees de huidige rij (met CURRENT ROW) en de volgende rij (met 1 FOLLOWING). Het berekent verder de som van die rijen met behulp van sum (Orderamount) met een partitie op CustomerCity (met OVER (PARTITION BY Customercity ORDER BY OrderAmount DESC).
|
1
2
3
4
5
6
7
|
SELECTEER Customercity,
CustomerName,
OrderAmount,
ROW_NUMBER () OVER (PARTITION BY Customercity
ORDER BY OrderAmount DESC) AS “Row Number”,
CONVERT (VARCHAR (20), SUM (orderamount) OVER (PARTITION BY Customercity
ORDER BY OrderAmount DESC ROWS TUSSEN HUIDIGE RIJ EN 1 VOLGENDE), 1) ALS cumulatief totaal,
|
Evenzo kunnen we het cumulatieve gemiddelde berekenen met de volgende vraag met de SQL PARTITION BY-clausule.
|
1
2
3
4
5
6
7
|
SELECTEER Customercity,
CustomerName,
OrderAmount,
ROW_NUMBER () OVER (PARTITION BY Customercity
ORDER BY OrderAmount DESC) ALS “Rijnummer”,
CONVERT (VARCHAR (20), AVG (orderbedrag) OVER (PARTITION BY Customercity
ORDER BY OrderAmount DESC RIJEN TUSSEN DE HUIDIGE RIJ EN 1 VOLGENDE) , 1) AS cumulatiefAVG
|
ROWS UNBOUNDED PRECEDING met de PARTITION BY-clausule
We kunnen ROWS UNBOUNDED PRECEDING gebruiken met de SQL PARTITION BY-clausule om een rij in een partitie te selecteren vóór de huidige rij en de hoogste waarde r ow na huidige rij.
In de volgende tabel kunnen we zien voor rij 1; het heeft geen enkele rij met een hoge waarde in deze partitie. Daarom is de cumulatieve gemiddelde waarde hetzelfde als van rij 1 OrderAmount.
Voor Rij2 zoekt het naar de huidige rijwaarde (7199.61) en de hoogste waarde rij 1 (7577.9). Het berekent het gemiddelde voor deze twee bedragen.
Voor rij 3 zoekt het naar de huidige waarde (6847,66) en een hogere bedragwaarde dan deze waarde, namelijk 7199,61 en 7577,90. Het berekent het gemiddelde hiervan en geeft als resultaat.
|
CustomerCity |
CustomerName |
Rang |
OrderAmount |
Cumulatief gemiddelde rijen |
Cumulatief gemiddelde |
|
Chicago |
Marvin |
Rang 1 |
|||
|
Chicago |
Lawrence |
Rang 1 + 2 |
|||
|
Chicago |
Alex |
Rang 1 + 2 + 3 |
|||
|
Chicago |
Jerome |
Rang 1 + 2 + 3 + 4 |
Voer de volgende query uit om dit resultaat te krijgen met onze voorbeeldgegevens.
|
1
2
3
4
5
6
7
8
|
SELECT Customercity,
CustomerName,
OrderAmount,
ROW_NUMBER () OVER (PARTITION BY Customercity
ORDER BY OrderAmount DESC) ALS “Row Number”,
CONVERT (VARCHAR (20), AVG (orderamount) OVER (PARTITION BY Customercity
ORDER BY OrderAmount DESC ROWS UNBOUNDED PRECEDING), 1) AS CumulativeAvg
FROM.;
|
Conclusie
In dit artikel hebben we de SQL PARTIION BY-clausule en de vergelijking met GROUP BY-clausule onderzocht. We leerden ook het gebruik ervan met een paar voorbeelden. Ik hoop dat je dit artikel nuttig vindt en stel gerust al je vragen in de reacties hieronder.
- Auteur
- Recente berichten
Hij is de maker van een van de grootste gratis online verzamelingen artikelen over één onderwerp, met zijn 50-delige serie over SQL Server Always On Availability Groups. Op basis van zijn bijdrage aan de SQL Server-gemeenschap, is hij bekroond met verschillende prijzen, waaronder de prestigieuze “Beste auteur van het jaar”, continu in 2020 en 2021 bij SQLShack.
Raj is altijd geïnteresseerd in nieuwe uitdagingen, dus als je advies nodig hebt hulp bij elk onderwerp dat in zijn geschriften aan bod komt, hij is te bereiken op [email protected]
Bekijk alle berichten van Rajendra Gupta
- Sessietime-outs in SQL Server altijd beschikbaar Groepen – 8 februari 2021
- Kleine en grote versie-upgrades voor AWS RDS SQL Server uitvoeren – 29 januari 2021
- AWS RDS PostgreSQL-instanties implementeren – 27 januari 2021