Denne artikel dækker SQL PARTITION BY-klausul og, især forskellen med GROUP BY i en udvalgt sætning. Vi vil også undersøge forskellige brugstilfælde af SQL PARTITION BY.
Vi bruger SQL PARTITION BY til at opdele resultatsættet i partitioner og udføre beregning på hver delmængde af partitionerede data.
Forberedelse af prøve Data
Lad os oprette en ordretabel i min eksempeldatabase SQLShackDemo og indsætte poster for at skrive yderligere forespørgsler.
Jeg bruger ApexSQL Generate til at indsætte eksempeldata i denne artikel. Højreklik på ordretabellen og generer testdata.
Det starter ApexSQL Generate. Jeg genererede et script til at indsætte data i ordretabellen. Udfør dette script for at indsætte 100 poster i ordretabellen.
Når vi har udført indsætningsudsagn, kan vi se dataene i ordretabellen i det følgende billede.
Vi bruger SQL GROUP BY-klausul til at gruppere resultater efter specificeret kolonne og bruger samlede funktioner såsom Gns (), Min (), Maks () til at beregne de krævede værdier.
Gruppér efter funktionssyntaks
|
1
2
3
4
|
VÆLG udtryk, samlet funktion ()
FRA tabeller
HVOR forhold
GROUP BY-udtryk
|
Antag, at vi vil finde følgende værdier i tabellen Ordrer
- Minimumsordreværdi i en by
- Maksimum ordreværdi i en by
- Gennemsnitlig ordreværdi i en by
Udfør følgende forespørgsel med GROUP BY-klausul for at beregne disse værdier.
|
1
2
3
4
5
6
|
SELECT Customercity,
AVG (Orderamount) AS AvgOrderAmount,
MIN (OrderAmount) AS MinOrderAmount,
SUM ( Orderamount) TotalOrderAmount
FROM.
GROUP BY Customercity;
|
I det følgende skærmbillede kan vi se gennemsnit, minimum og maksimale værdier grupperet efter CustomerCity.
Nu vil vi også tilføje kolonnen CustomerName og OrderAmount i output. Lad os tilføje disse kolonner i selektionssætningen og udføre følgende kode.
|
1
2
3
4
5
6
|
VÆLG Customercity, CustomerName, OrderAmount,
AVG (Orderamount) AS AvgOrderAmount,
MIN (OrderAmount) AS MinOrderAmount,
SUM (Orderamount) TotalOrderAmount
FRA.
GROUP BY Customercity;
|
Når vi udfører denne forespørgsel, får vi en fejlmeddelelse . I SQL GROUP BY-klausulen kan vi bruge en kolonne i select-sætningen, hvis den også bruges i Group by-klausul. Det tillader ikke nogen kolonne i den valgte klausul, der ikke er en del af GROUP BY-klausulen.
Vi kan bruge SQL PARTITION BY-klausul for at løse dette problem. Lad os udforske det nærmere i næste afsnit.
SQL PARTITION BY
Vi kan bruge SQL PARTITION BY-klausulen med OVER-klausulen til at specificere den kolonne, som vi har brug for til at udføre aggregering. . I det forrige eksempel brugte vi Group By med CustomerCity-kolonnen og beregnede gennemsnit-, minimums- og maksimumværdier.
Lad os køre dette scenario igen med SQL PARTITION BY-klausulen ved hjælp af følgende forespørgsel.
|
1
2
3
4
5
|
VÆLG 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.;
|
I output får vi aggregerede værdier svarende til en GROUP Efter klausul. Du bemærker muligvis en forskel i output for SQL PARTITION BY- og GROUP BY-klausuloutput.
|
Grupper efter |
SQL PARTITION BY |
|
Vi får et begrænset antal poster ved hjælp af Group By-klausulen |
Vi får alle poster i en tabel ved hjælp af PARTITION BY-klausulen. |
|
Det giver en række pr. gruppe i resultatsæt. For eksempel får vi et resultat for hver gruppe af CustomerCity i GROUP BY-klausulen. |
Det giver aggregerede kolonner med hver post i den angivne tabel. Vi har 15 poster i ordretabellen. I forespørgselsoutputet af SQL PARTITION BY får vi også 15 rækker sammen med Min, Max og gennemsnitsværdier. |
I det forrige eksempel får vi en fejlmeddelelse, hvis vi forsøger at tilføje en kolonne, der ikke er en del af GROUP BY-klausulen.
Vi kan tilføje krævede kolonner i en vælgesætning med SQL PARTITION BY-klausulen . Lad os tilføje CustomerName og OrderAmount-kolonner og udføre følgende forespørgsel.
|
1
2
3
4
5
6
7
|
VÆLG Customercity,
Kundenavn,
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.;
|
Vi får CustomerName og OrderAmount kolonne sammen med output fra den samlede funktion. Vi får også alle tilgængelige rækker i ordretabellen.
I det følgende skærmbillede kan du for CustomerCity Chicago udføre aggregeringer (Gns., Min og Maks.) og giver værdier i de respektive kolonner.
På samme måde kan vi bruge andre samlede funktioner såsom som optælling for at finde det samlede antal ordrer i en bestemt by med SQL PARTITION BY-klausulen.
|
1
2
3
4
5
6
7
8
|
VÆLG Customercity,
Kundenavn,
OrderAmount,
COUNT (OrderID) OVER (PARTITION BY Customercity) AS CountOfOrders,
AVG (Orderamount) OVER (PARTITION BY Customercity) AS AvgOrderAmount,
MIN (OrderAmount) OVER (PARTITIO N BY Customercity) SOM MinOrderAmount,
SUM (Orderamount) OVER (PARTITION BY Customercity) TotalOrderAmount
FROM.;
|
Vi kan se ordretællinger for en bestemt by. For eksempel har vi derfor to ordrer fra Austin by; det viser værdi 2 i CountofOrders-kolonnen.
PARTITION BY-klausul med ROW_NUMBER ()
Vi kan Brug SQL PARTITION BY-klausul med ROW_NUMBER () -funktionen til at have et rækkenummer for hver række. Vi definerer følgende parametre for at bruge ROW_NUMBER med SQL PARTITION BY-klausulen.
- PARTITION BY-kolonne – I dette eksempel vil vi opdele data i CustomerCity-kolonnen
- Bestil efter : I kolonnen ORDER BY definerer vi en kolonne eller betingelse, der definerer række nummer. I dette eksempel ønsker vi at sortere data i kolonnen OrderAmount
|
1
2
3
4
5
6
7
8
9
10
|
VÆLG kundecity,
Kundenavn,
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.;
|
I det følgende skærmbillede får vi se for CustomerCity Chicago , vi har række nummer 1 for ordre med det højeste beløb 7577.90. det giver række nummer med faldende OrderAmount.
PARTITION BY-klausul med kumulativ totalværdi
Antag, at vi ønsker at få et kumulativt total for ordrer i en partition.Kumulativt total skal være af den aktuelle række og den følgende række i partitionen.
For eksempel i Chicago by, vi har fire ordrer.
|
CustomerCity |
CustomerName |
Rang |
OrderAmount |
Kumulative samlede rækker |
Kumulativt total |
|
Chicago |
Marvin |
Rang 1 +2 |
|||
|
Chicago |
Lawrence |
Rang 2 + 3 |
|||
|
Chicago |
Alex |
Rang 3 + 4 |
|||
|
Chicago |
Jerome |
Rang 4 |
I den følgende forespørgsel har vi den specificerede ROWS-klausul, der skal se forelæs den aktuelle række (ved hjælp af AKTUEL RÆDE) og næste række (ved hjælp af 1 FØLGENDE). Den beregner yderligere summen på disse rækker ved hjælp af sum (Orderamount) med en partition på CustomerCity (ved hjælp af OVER (PARTITION BY Customercity ORDER BY OrderAmount DESC).
|
1
2
3
4
5
6
7
|
VÆLG brugerdefineret,
Kundenavn,
OrderAmount,
ROW_NUMBER () OVER (PARTITION BY Customercity
ORDER BY OrderAmount DESC) AS “Row Number”,
CONVERT (VARCHAR (20), SUM (orderamount) OVER (AFDELING EFTER Customercity
BESTILLING EFTER OrderAmount DESC RÆKER MELLEM AKTUEL RÆDE OG 1 FØLGENDE), 1) SOM KumulativTotal,
|
På samme måde kan vi beregne det kumulative gennemsnit ved hjælp af følgende forespørgsel med SQL PARTITION BY-klausulen.
|
1
2
3
4
5
6
7
|
VÆLG brugerdefineret,
kundenavn,
OrderAmount,
ROW_NUMBER () OVER (PARTITION BY Customercity
ORDER BY OrderAmount DESC) SOM “Række nummer”,
KONVERTER (VARCHAR (20), AVG (ordremængde) OVER (DELING EFTER kundeforhold
BESTILLER EFTER OrderAmount DESC RÆKER MELLEM LØBENDE RÆDE OG 1 FØLGENDE)) , 1) SOM KumulativAVG
|
RÆKER UBUNDET FORUDGÅENDE med PARTITION BY-klausulen
Vi kan bruge RÆKER UGRUNDET FORUDGÅENDE med SQL PARTITION BY-klausulen til at vælge en række i en partition før den aktuelle række og den højeste værdi r ow efter nuværende række.
I den følgende tabel kan vi se for række 1; den har ikke nogen række med en høj værdi i denne partition. Derfor er den kumulative gennemsnitsværdi den samme som i række 1 OrderAmount.
For række 2 ser den efter nuværende rækkeværdi (7199.61) og højeste værdi række 1 (7577.9). Det beregner gennemsnittet for disse to beløb.
For række 3 søger den aktuelle værdi (6847,66) og en højere værdi end denne værdi, der er 7199,61 og 7577,90. Det beregner gennemsnittet af disse og returnerer.
|
CustomerCity |
Kundenavn |
Rang |
OrderAmount |
Kumulative gennemsnitlige rækker |
Kumulativt gennemsnit |
|
Chicago |
Marvin |
Rang 1 |
|||
|
Chicago |
Lawrence |
Rang 1 + 2 |
|||
|
Chicago |
Alex |
Rang 1 + 2 + 3 |
|||
|
Chicago |
Jerome |
Rang 1 + 2 + 3 + 4 |
Udfør følgende forespørgsel for at få dette resultat med vores eksempeldata.
|
1
2
3
4
5
6
7
8
|
SELECT Customercity,
CustomerName,
OrderAmount,
ROW_NUMBER () OVER (PARTITION BY Customercity
ORDER BY OrderAmount DESC) AS “Row Number”,
CONVERT (VARCHAR (20), AVG (orderamount) OVER (PARTITION BY Customercity
ORDER BY OrderAmount DESC ROWS UNBOUNDED PRECEDING), 1) SOM CumulativeAvg
FRA.;
|
Konklusion
I denne artikel undersøgte vi SQL PARTIION BY-klausulen og dens sammenligning med GROUP BY-klausulen. Vi lærte også brugen med et par eksempler. Jeg håber, du finder denne artikel nyttig og er velkommen til at stille spørgsmål i kommentarerne nedenfor
- Forfatter
- Seneste indlæg
Han er skaberen af en af de største gratis online samlinger af artikler om et enkelt emne med sin 50-dels serie om SQL Server Always On Availability Groups. Baseret på hans bidrag til SQL Server-samfundet er han blevet anerkendt med forskellige priser, herunder den prestigefyldte “Årets bedste forfatter” kontinuerligt i 2020 og 2021 hos SQLShack.
Raj er altid interesseret i nye udfordringer, så hvis du har brug for rådgivning hjælp til ethvert emne, der er dækket af hans skrifter, kan han nås på [email protected]
Se alle indlæg af Rajendra Gupta
- Sessionstimeouts i SQL Server Always On Availability Grupper – 8. februar 2021
- Udførelse af mindre og større versionopgraderinger til AWS RDS SQL Server – 29. januar 2021
- Implementering af AWS RDS PostgreSQL-forekomster – 27. januar 2021