Acest articol va acoperi clauza SQL PARTITION BY și, în special, diferența cu GROUP BY într-o declarație selectată. De asemenea, vom explora diverse cazuri de utilizare a SQL PARTITION BY.
Folosim SQL PARTITION BY pentru a împărți setul de rezultate în partiții și pentru a efectua calcule pe fiecare subset de date partiționate.
Pregătirea eșantionului Date
Permiteți-ne să creăm un tabel de comenzi în baza mea de date SQLShackDemo și să inserăm înregistrări pentru a scrie alte interogări.
Folosesc ApexSQL Generate pentru a insera date eșantion în acest articol. Faceți clic dreapta pe tabelul Comenzi și Generați date de testare.
Lansează ApexSQL Generate. Am generat un script pentru a insera date în tabelul Comenzi. Executați acest script pentru a insera 100 de înregistrări în tabelul Comenzi.
Odată ce executăm instrucțiuni de inserare, putem vedea datele din tabelul Comenzi din imaginea următoare.
Folosim clauza SQL GROUP BY pentru a grupa rezultatele după coloana specificată și folosim funcții agregate precum Avg (), Min (), Max () pentru a calcula valorile necesare.
Sintaxa funcției de grupare
|
1
2
3
4
|
expresie SELECT, funcție agregată ()
din tabele
UNDE condiții
GROUP BY expression
|
Să presupunem că dorim să găsim următoarele valori în tabelul Comenzi
- Valoarea minimă a comenzii într-un oraș
- Valoarea maximă a comenzii într-un oraș
- Valoarea medie a comenzii într-un oraș
Executați următoarea interogare cu clauza GROUP BY pentru a calcula aceste valori.
|
1
2
3
4
5
6
|
SELECT Customercity,
AVG (Orderamount) AS AvgOrderAmount,
MIN (OrderAmount) AS MinOrderAmount,
SUM ( Orderamount) TotalOrderAmount
FROM.
GROUP BY Customercity;
|
În următoarea captură de ecran, putem vedea Media, Minima și valorile maxime grupate în funcție de CustomerCity.
Acum, dorim să adăugăm și coloana CustomerName și OrderAmount în ieșire. Să adăugăm aceste coloane în instrucțiunea select și să executăm următorul cod.
|
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;
|
Odată ce executăm această interogare, primim un mesaj de eroare . În clauza SQL GROUP BY, putem utiliza o coloană în instrucțiunea select dacă este utilizată și în clauza Group by. Nu permite nicio coloană din clauza de selectare care nu face parte din clauza GROUP BY.
Putem utiliza SQL Clauza PARTITION BY pentru a rezolva această problemă. Să o explorăm mai departe în secțiunea următoare.
SQL PARTITION BY
Putem folosi clauza SQL PARTITION BY cu clauza OVER pentru a specifica coloana pe care trebuie să realizăm agregarea . În exemplul anterior, am folosit grupul By cu coloana CustomerCity și am calculat valorile medii, minime și maxime.
Să reluăm acest scenariu cu clauza SQL PARTITION BY folosind următoarea interogare. id = „abfb20057f”>
|
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.;
|
În rezultat, obținem valori agregate similare cu o GRUPUL Prin clauză. Este posibil să observați o diferență de ieșire a ieșirii clauzei SQL PARTITION BY și GROUP BY.
|
Grupați după |
SQL PARTITION BY |
|
Obținem un număr limitat de înregistrări folosind clauza Group By |
Primim toate înregistrările într-un tabel folosind clauza PARTITION BY. |
|
Oferă una rând pe grup în setul de rezultate. De exemplu, obținem un rezultat pentru fiecare grup de CustomerCity în clauza GROUP BY. |
Oferă coloane agregate cu fiecare înregistrare din tabelul specificat. Avem 15 înregistrări în tabelul Comenzi. În rezultatul interogării SQL PARTITION BY, obținem și 15 rânduri împreună cu valorile minime, maxime și medii. |
În exemplul anterior, primim un mesaj de eroare dacă încercăm să adăugăm o coloană care nu face parte din clauza GROUP BY.
Putem adăuga coloanele necesare într-o instrucțiune select cu clauza SQL PARTITION BY . Să adăugăm coloane CustomerName și OrderAmount și să executăm următoarea interogare.
|
1
2
3
4
5
6
7
|
SELECT Customercity,
CustomerName,
OrderAmount,
AVG (Orderamount) PESTE (PARTITION BY Customercity) AS AvgOrderAmount ,
MIN (OrderAmount) OVER (PARTITION BY Customercity) AS MinOrderAmount,
SUM (Orderamount) OVER (PARTITION BY Customercity) TotalOrderAmount
FROM.;
|
Obținem coloana CustomerName și OrderAmount împreună cu rezultatul funcția agregată. De asemenea, obținem toate rândurile disponibile în tabelul Comenzi.
În următoarea captură de ecran, puteți pentru CustomerCity Chicago, funcționează agregări (Avg, Min și Max) și oferă valori în coloanele respective.
În mod similar, putem folosi alte funcții de agregare, cum ar fi ca număr pentru a afla numărul total de comenzi dintr-un anumit oraș cu clauza SQL PARTITION BY.
|
1
2
3
4
5
6
7
8
|
SELECT 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.;
|
Putem vedea numărul de ordine pentru un anumit oraș. De exemplu, avem două comenzi din orașul Austin, prin urmare; arată valoarea 2 în coloana CountofOrders.
Clauza PARTITION BY cu ROW_NUMBER ()
Putem utilizați clauza SQL PARTITION BY cu funcția ROW_NUMBER () pentru a avea un număr de rând pentru fiecare rând. Definim următorii parametri pentru a utiliza ROW_NUMBER cu clauza SQL PARTITION BY.
- Coloana PARTITION BY – În acest exemplu, dorim să partiționăm datele din coloana CustomerCity
- Order By : În coloana ORDER BY, definim o coloană sau o condiție care definește numărul rândului. În acest exemplu, dorim să sortăm datele din coloana OrderAmount
|
1
2
3
4
5
6
7
8
9
10
|
SELECT Customercity,
CustomerName,
ROW_NUMBER () OVER (PARTITION BY Customercity
ORDER BY OrderAmount DESC) AS „Number Row”,
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.;
|
În următoarea captură de ecran, vom vedea pentru CustomerCity Chicago , avem rândul numărul 1 pentru comanda cu cea mai mare sumă 7577,90. oferă numărul rândului cu OrderAmount descendent.
Clauza PARTITION BY cu valoare totală cumulativă
Să presupunem că doriți să obțineți un total cumulativ pentru comenzile dintr-o partiție.Totalul cumulativ trebuie să fie al rândului curent și al rândului următor din partiție.
De exemplu, în orașul Chicago, avem patru comenzi.
|
CustomerCity |
CustomerName |
Rang |
OrderAmount |
Rânduri totale cumulate |
Total cumulativ |
|
Chicago |
Marvin |
Rang 1 +2 |
|||
|
Chicago |
Lawrence |
Rang 2 + 3 |
|||
|
Chicago |
Alex |
Rang 3 + 4 |
|||
|
Chicago |
Jerome |
Rang 4 |
În următoarea interogare, am introdus clauza ROWS specificată citiți rândul curent (folosind RÂNDUL ACTUAL) și rândul următor (folosind 1 URMĂTOARE). În continuare calculează suma pe aceste rânduri folosind suma (Orderamount) cu o partiție pe CustomerCity (folosind OVER (PARTITION BY Customercity ORDER BY OrderAmount DESC).
|
1
2
3
4
5
6
7
|
SELECT Customercity,
CustomerName,
OrderAmount,
ROW_NUMBER () OVER (PARTITION BY Customercity
ORDER BY OrderAmount DESC) AS „Number Row”,
CONVERT (VARCHAR (20), SUM (orderamount) PESTE (PARTIȚIE ÎN DOMENIU
COMANDĂ PRIN COMANDĂ Rânduri DESC ÎNTRE RÂNDUL ACTUAL ȘI 1 URMĂTOR), 1) AS CumulativeTotal,
|
În mod similar, putem calcula media cumulativă folosind în urma interogării cu clauza SQL PARTITION BY.
|
1
2
3
4
5
6
7
|
SELECT Customercity,
CustomerName,
OrderAmount,
ROW_NUMBER () OVER (PARTITION BY Customercity
ORDER BY OrderAmount DESC) CA „Număr rând”,
CONVERT (VARCHAR (20), AVG (orderamount) PESTE (PARTIȚIE ÎN funcție de client
ORDER BY OrderAmount DESC ROWS ENTRE RANDUL ACTUAL ȘI 1 URMĂTOARE) , 1) AS CumulativeAVG
|
ROWS UNBOUNDED PRECEDING cu clauza PARTITION BY
Putem folosi ROWS UNBOUNDED PRECEDING cu clauza SQL PARTITION BY pentru a selecta un rând într-o partiție înainte de rândul curent și cea mai mare valoare r după rândul curent.
În tabelul următor, putem vedea pentru rândul 1; nu are niciun rând cu o valoare ridicată în această partiție. Prin urmare, valoarea medie cumulată este aceeași cu cea a rândului 1 OrderAmount.
Pentru rândul 2, caută valoarea rândului curent (7199.61) și cea mai mare valoare rândul 1 (7577.9). Calculează media pentru aceste două sume.
Pentru rândul 3, caută valoarea curentă (6847,66) și o valoare a sumei mai mare decât această valoare care este 7199,61 și 7577,90. Calculează media acestora și returnează.
|
CustomerCity |
CustomerName |
Rank |
OrderAmount |
Rânduri medii cumulative |
Media cumulativă |
|
Chicago |
Marvin |
Rang 1 |
|||
|
Chicago |
Lawrence |
Rang 1 + 2 |
|||
|
Chicago |
Alex |
Rang 1 + 2 + 3 |
|||
|
Chicago |
Jerome |
Rang 1 + 2 + 3 + 4 |
Executați următoarea interogare pentru a obține acest rezultat cu datele noastre eșantion.
|
1
2
3
4
5
6
7
8
|
SELECT Customercity,
CustomerName,
OrderAmount,
ROW_NUMBER () OVER (PARTITION BY Customercity
ORDER BY OrderAmount DESC) CA „Număr rând”,
CONVERT (VARCHAR (20), AVG (orderamount) PESTE (PARTIȚIE ÎN DOMENIU
ORDER BY OrderAmount DESC ROWS UNBOUNDED PRECEDING), 1) AS CumulativeAvg
FROM.;
|
Concluzie
În acest articol, am explorat clauza SQL PARTIION BY și comparația acesteia cu clauza GROUP BY. De asemenea, am învățat utilizarea acestuia cu câteva exemple. Sper că veți găsi acest articol util și nu ezitați să puneți întrebări în comentariile de mai jos
- Autor
- Postări recente
El este creatorul uneia dintre cele mai mari colecții online gratuite de articole pe un singur subiect, cu seria sa de 50 de părți despre SQL Server Always On Availability Groups. Pe baza contribuției sale la comunitatea SQL Server, a fost recunoscut cu diverse premii, inclusiv prestigiosul „Cel mai bun autor al anului” continuu în 2020 și 2021 la SQLShack.
Raj este mereu interesat de noi provocări, deci dacă aveți nevoie de consultanță ajutor pe orice subiect acoperit în scrierile sale, el poate fi contactat la [email protected]
Vezi toate mesajele lui Rajendra Gupta
- Timpul sesiunii în SQL Server întotdeauna disponibil Grupuri – 8 februarie 2021
- Efectuarea actualizărilor versiunii minore și majore pentru AWS RDS SQL Server – 29 ianuarie 2021
- Implementarea instanțelor AWS RDS PostgreSQL – 27 ianuarie 2021