Ez a cikk az SQL PARTITION BY záradékot, és különösen a GROUP BY-vel való különbség egy select utasításban. Megvizsgáljuk az SQL PARTITION BY különféle felhasználási eseteit is.
Az SQL PARTITION BY-t használjuk az eredményhalmaz partíciókra történő felosztására és a particionált adatok minden részhalmazának kiszámítására.
Minta előkészítése Adatok
Hozzunk létre egy Orders táblát az SQLShackDemo mintaadatbázisomban, és tegyünk be rekordokat további lekérdezések írására.
Az ApexSQL Generate segítségével mintaadatokat szúrok be ebbe a cikkbe. Kattintson a jobb gombbal a Rendelések táblára, és Tesztadatok generálása.
Elindítja az ApexSQL Generate alkalmazást. Generáltam egy szkriptet az adatok beszúrására a Rendelések táblába. Futtassa ezt a szkriptet 100 rekord beillesztéséhez a Rendelések táblába.
A beszúrási utasítások végrehajtása után a következő képen láthatjuk a Rendelések táblázat adatait.
Az SQL GROUP BY záradékot használjuk az eredmények meghatározott oszlopok szerinti csoportosításához, és olyan összesítő függvényeket használunk, mint az Avg (), Min (), Max () a szükséges értékek kiszámításához.
Csoportosítás szintaxis szerint
|
1
2
3
4
|
SELECT kifejezés, összesítő függvény ()
táblákból FROM
WHERE feltételek
GROUP BY kifejezés szerint
|
Tegyük fel, hogy a következő értékeket szeretnénk megtalálni a Rendelések táblázatban.
- Minimális rendelési érték egy városban
- Maximális rendelési érték egy városban
- Átlagos rendelési érték egy városban
Az érték kiszámításához hajtsa végre a következő lekérdezést a GROUP BY záradékkal.
|
1
2
3
4
5
6
|
SELECT Customercity,
AVG (Orderamount) AS AvgOrderAmount,
MIN (OrderAmount) AS MinOrderAmount,
SUM ( Rendelési összeg) TotalOrderAmount
FROM.
CSOPORTOSÍTÁS Customercity szerint;
|
A következő képernyőképen az átlagot, a minimumot láthatjuk és a CustomerCity szerint csoportosított maximális értékek.
Most hozzá akarjuk adni a kimenethez a CustomerName és a OrderAmount oszlopot is. Vegyük fel ezeket az oszlopokat a select utasításba, és hajtsuk végre a következő kódot.
|
1
2
3
4
5
6
|
SELECT Customercity, CustomerName, OrderAmount,
AVG (Orderamount) AS AvgOrderAmount,
MIN (OrderAmount) AS MinOrderAmount,
SUM (Orderamount) TotalOrderAmount
FROM.
CSOPORTOSÍTÁS Customercity szerint;
|
A lekérdezés végrehajtása után hibaüzenetet kapunk . Az SQL GROUP BY záradékban oszlopot használhatunk a select utasításban, ha azt a Group by clause is használja. Nem engedélyez olyan oszlopot a select záradékban, amely nem része a GROUP BY záradéknak.
Használhatjuk az SQL-t PARTITION BY záradék a probléma megoldására. Fedezzük fel tovább a következő szakaszban.
SQL PARTITION BY
Használhatjuk az SQL PARTITION BY záradékot az OVER záradékkal annak az oszlopnak a megadásához, amelyen összesítést kell végrehajtanunk. . Az előző példában a Group By-t az CustomerCity oszlopban használtuk, és kiszámítottuk az átlag, a minimum és a maximális értékeket.
Futtassuk újra ezt a forgatókönyvet az SQL PARTITION BY záradékkal a következő lekérdezés segítségével.
|
1
2
3
4
5
|
SELECT Customercity,
AVG (Rendelési összeg) OVER (PARTITION BY Customercity) AS AvgOrderAmount,
MIN (OrderAmount) OVER (PARTITION BY Customercity) AS MinOrderAmount,
SUM (Orderamount) OVER (PARTITION BY Customercity) TotalOrderAmount
FROM.
|
A kimenetben a CSOPORT Kötelezettség szerint. Különbséget észlelhet az SQL PARTITION BY és GROUP BY záradék kimenetében.
|
Csoportosítás |
SQL PARTITION BY |
|
Korlátozott számú rekordot kapunk a Group By záradék használatával |
Az összes rekordot a táblában a PARTITION BY záradék használatával kapjuk meg. |
|
Ez ad egyet sor csoportonként az eredményhalmazban. Például a GROUP BY záradékban kapunk egy eredményt az CustomerCity minden csoportjára. |
Összetett oszlopokat ad a megadott táblázat minden rekordjával. 15 rekord van a Rendelések táblázatban. Az SQL PARTITION BY lekérdezési kimenetében 15 sort kapunk Min, Max és átlagos értékekkel együtt. |
Az előző példában hibaüzenetet kapunk, ha megpróbálunk hozzáadni egy olyan oszlopot, amely nem része a GROUP BY záradéknak.
A kötelező oszlopokat felvehetjük egy select utasításba az SQL PARTITION BY záradékkal. . Vegyük fel a CustomerName és a OrderAmount oszlopokat, és hajtsuk végre a következő lekérdezést.
|
1
2
3
4
5
6
7
|
SELECT Customercity,
CustomerName,
OrderAmount,
AVG (Orderamount) OVER (PARTITION BY Customercity) AS AvgOrderAmount ,
MIN (OrderAmount) TÖBB (PARTITION BY Customercity) AS MinOrderAmount,
SUM (Orderamount) OVER (PARTITION BY Customercity) TotalOrderAmount
FROM.;
|
Megkapjuk a CustomerName és a OrderAmount oszlopot a az összesített függvény. Megtaláljuk az összes sort a Rendelések táblázatban is.
A következő képernyőképen a CustomerCity Chicago számára elvégezheti, összesítések (Átl, Min és Max), és értékeket ad meg a megfelelő oszlopokban.
Hasonlóképpen használhatunk más összesítő függvényeket is, például as count, hogy megtudjuk az SQL PARTITION BY záradékkal egy adott város teljes megrendelését.
|
1
2
3
4
5
6
7
8
|
SELECT Customercity,
Ügyfélnév,
OrderAmount,
COUNT (OrderID) TÖBB (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.
|
Láthatjuk az adott város rendelésszámát. Például két megrendelésünk van Austin városából; a 2. értéket mutatja a CountofOrders oszlopban.
PARTITION BY záradék a ROW_NUMBER ()
használja az SQL PARTITION BY záradékot a ROW_NUMBER () függvénnyel, hogy minden sorhoz sorszám legyen. A következő paramétereket definiáljuk a ROW_NUMBER használatához az SQL PARTITION BY záradékkal.
- PARTITION BY oszlop – Ebben a példában szeretnénk particionálni az adatokat az CustomerCity oszlopon
- : A ORDER BY oszlopban meghatározunk egy oszlopot vagy feltételt, amely meghatározza a sorszámot. Ebben a példában az adatokat rendezni akarjuk a OrderAmount oszlopban
|
1
2
3
4
5
6
7
8
9
10
|
SELECT Customercity,
ügyfélnév,
ROW_NUMBER () OVER (PARTITION BY Customercity
ORDER BY OrderAmount DESC) AS “Sorszám”,
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.;
|
A következő képernyőképen láthatjuk a Chicago ügyfélszolgálatát , az 1. sor a 7577.90 összegű rendeléshez tartozik. a sorszámot csökkenő OrderAmount értékkel látja el.
PARTITION BY záradék összesített összes értékkel
Tegyük fel, hogy a partícióban lévő megrendelések összesített összegét szeretné megszerezni.Az összesített összegnek az aktuális és a partíció következő sorának kell lennie.
Például Chicago városában, négy megrendelésünk van.
|
Ügyfélváros |
Ügyfélnév |
Helyezés |
OrderAmount |
Összesített összesített sor |
Összesített összesítés |
|
Chicago |
Marvin |
1. + 2. helyezés |
|||
|
Chicago |
Lawrence |
2. + 3. helyezés |
|||
|
Chicago |
Alex |
3. + 4. helyezés |
|||
|
Chicago |
Jerome |
4. hely |
A következő lekérdezésben megadtuk a megadott ROWS záradékot olvassa el az aktuális sort (a CURRENT ROW használatával) és a következő sort (1 FOLLOWING segítségével). Ezenkívül kiszámítja az összegeket ezeken a sorokon az összeg (Orderamount) használatával az CustomerCity partíciójával (az OVER használatával (PARTITION BY Customercity ORDER BY OrderAmount DESC).
|
1
2
3
4
5
6
7
|
SELECT Customercity,
ügyfélnév,
OrderAmount,
ROW_NUMBER () OVER (PARTITION BY Customercity
ORDER BY OrderAmount DESC) AS “Sorszám”,
ÁTALAKÍTÁS (VARCHAR (20), SUM (rendelési összeg) OVER (PARTITION BY Customercity
MEGRENDELÉS OrderAmount DESC sorokkal az aktuális sor és 1 követés között), 1) AS CumulativeTotal,
|
Hasonlóképpen kiszámíthatjuk a kumulatív átlagot a következő lekérdezést az SQL PARTITION BY záradékkal.
|
1
2
3
4
5
6
7
|
SELECT Customercity,
CustomerName,
OrderAmount,
ROW_NUMBER () OVER (PARTITION BY Customercity
RENDELÉS OrderAmount szerint Desc , 1) AS kumulatívAVG
|
FELTÉTELT SOROK, ELŐZMÉNYEK a PARTITION BY záradékkal
Használhatjuk az SQL PARTITION BY záradékkal lefutott sorokat, amelyek sorban választhatók ki a partícióban az aktuális sor és az r legmagasabb értéke előtt ow az aktuális sor után.
A következő táblázatban láthatjuk az 1. sort; ebben a partícióban nincs magas értékű sor. Ezért az összesített átlagérték megegyezik az 1. sor OrderAmount értékével.
A 2. sor esetében az aktuális sorértéket (7199,61) és a legmagasabb értékű 1. sort (7577,9) várja. Kiszámítja ennek a két összegnek az átlagát.
A 3. sor esetében a jelenlegi értéket (6847,66) és az ennél magasabb értéket keresi, amely 7199,61 és 7577,90. Kiszámítja ezek átlagát, és visszatér.
|
CustomerCity |
Ügyfélnév |
Helyezés |
Rendelésösszeg |
Összesített átlagos sorok |
Halmozott átlag |
|
Chicago |
Marvin |
1. hely |
|||
|
Chicago |
Lawrence |
1. + 2. hely |
|||
|
Chicago |
Alex |
1. + 2 + 3. hely |
|||
|
Chicago |
Jerome |
1. + 2 + 3 + 4. hely |
Hajtsa végre a következő lekérdezést, hogy megkapja ezt az eredményt a mintaadatainkkal.
|
1
2
3
4
5
6
7
8
|
SELECT Customercity,
CustomerName,
OrderAmount,
ROW_NUMBER () OVER (PARTITION BY Customercity
ORDER BY OrderAmount DESC) AS “Sorszám”,
ÁTALAKÍTÁS (VARCHAR (20), AVG (rendelési összeg) OVER (PARTITION BY Customercity
ORDER BY OrderAmount DESC ROWS ONBOUNDED PRECEDING), 1) AS CumulativeAvg
FROM.;
|
Következtetés
Ebben a cikkben feltártuk az SQL PARTIION BY záradékot és annak összehasonlítását a GROUP BY záradékkal. Néhány példával megismertük használatát is. Remélem, hasznosnak találja ezt a cikket, és nyugodtan tegyen fel kérdéseket az alábbi megjegyzésekben.
- Szerző
- Legfrissebb bejegyzések
Ő az egyik legnagyobb ingyenes online témájú cikkgyűjtemény készítője egyetlen témával foglalkozó 50 részes sorozatával az SQL Server Always On Availability Groups-on. Az SQL Server közösséghez való hozzájárulása alapján különféle díjakkal ismerték el, köztük a rangos “Az év legjobb szerzője” címet 2020-ban és 2021-ben folyamatosan az SQLShacknél.
Raj mindig érdekli az új kihívásokat, így ha tanácsadásra van szüksége segítséget nyújt az írásaiban szereplő bármely témában, elérhető a [email protected] címen
Rajendra Gupta összes üzenetének megtekintése
- Munkamenet időkorlátjai az SQL Server rendszerben Csoportok – 2021. február 8.
- Apróbb és nagyobb verzió frissítések végrehajtása az AWS RDS SQL Server számára – 2021. január 29.
- AWS RDS PostgreSQL példányok telepítése – 2021. január 27.