Questo articolo tratterà la clausola SQL PARTITION BY e, in particolare, la differenza con GROUP BY in unistruzione select. Esploreremo anche vari casi duso di SQL PARTITION BY.
Usiamo SQL PARTITION BY per dividere il set di risultati in partizioni ed eseguire calcoli su ogni sottoinsieme di dati partizionati.
Preparazione del campione Dati
Creiamo una tabella Ordini nel mio database di esempio SQLShackDemo e inseriamo i record per scrivere ulteriori query.
Uso ApexSQL Generate per inserire dati di esempio in questo articolo. Fai clic con il tasto destro sulla tabella Ordini e Genera dati di prova.
Avvia ApexSQL Generate. Ho generato uno script per inserire i dati nella tabella Ordini. Esegui questo script per inserire 100 record nella tabella Orders.
Una volta eseguite le istruzioni insert, possiamo vedere i dati nella tabella Orders nellimmagine seguente.
Usiamo la clausola SQL GROUP BY per raggruppare i risultati in base alla colonna specificata e utilizziamo funzioni aggregate come Avg (), Min (), Max () per calcolare i valori richiesti.
Sintassi della funzione Raggruppa per
|
1
2
3
4
|
SELECT espressione, funzione aggregata ()
FROM tabelle
WHERE condizioni
GROUP BY espressione
|
Supponiamo di voler trovare i seguenti valori nella tabella Ordini
- Valore minimo dellordine in una città
- Valore massimo dellordine in una città
- Valore medio dellordine in una città
Esegui la seguente query con la clausola GROUP BY per calcolare questi valori.
|
1
2
3
4
5
6
|
SELEZIONA Customercity,
AVG (Orderamount) AS AvgOrderAmount,
MIN (OrderAmount) AS MinOrderAmount,
SUM ( Orderamount) TotalOrderAmount
FROM.
GROUP BY Customercity;
|
Nello screenshot seguente, possiamo vedere Media, Minima e i valori massimi raggruppati per CustomerCity.
Ora, vogliamo aggiungere anche le colonne CustomerName e OrderAmount nelloutput. Aggiungiamo queste colonne nellistruzione select ed eseguiamo il codice seguente.
|
1
2
3
4
5
6
|
SELEZIONA Customercity, CustomerName, OrderAmount,
AVG (Orderamount) AS AvgOrderAmount,
MIN (OrderAmount) AS MinOrderAmount,
SUM (Orderamount) TotalOrderAmount
DA.
GROUP BY Customercity;
|
Una volta eseguita questa query, otteniamo un messaggio di errore . Nella clausola SQL GROUP BY, possiamo utilizzare una colonna nellistruzione select se viene utilizzata anche nella clausola Group by. Non consente alcuna colonna nella clausola select che non faccia parte della clausola GROUP BY.
Possiamo usare lSQL Clausola PARTITION BY per risolvere questo problema. Esploriamola ulteriormente nella prossima sezione.
SQL PARTITION BY
Possiamo usare la clausola SQL PARTITION BY con la clausola OVER per specificare la colonna su cui dobbiamo eseguire laggregazione . Nellesempio precedente, abbiamo utilizzato Group By con la colonna CustomerCity e abbiamo calcolato i valori medio, minimo e massimo.
Rieseguiamo questo scenario con la clausola SQL PARTITION BY utilizzando la seguente 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.;
|
Nelloutput, otteniamo valori aggregati simili a GROUP Per clausola. Si potrebbe notare una differenza nelloutput delloutput della clausola SQL PARTITION BY e GROUP BY.
|
Raggruppa per |
SQL PARTITION BY |
|
Otteniamo un numero limitato di record utilizzando la clausola Group By |
Otteniamo tutti i record in una tabella utilizzando la clausola PARTITION BY. |
|
Ne dà uno riga per gruppo nel set di risultati. Ad esempio, otteniamo un risultato per ogni gruppo di CustomerCity nella clausola GROUP BY. |
Fornisce colonne aggregate con ogni record nella tabella specificata. Abbiamo 15 record nella tabella Ordini. Nelloutput della query di SQL PARTITION BY, otteniamo anche 15 righe insieme ai valori Min, Max e medio. |
Nellesempio precedente, otteniamo un messaggio di errore se proviamo ad aggiungere una colonna che non fa parte della clausola GROUP BY.
Possiamo aggiungere colonne obbligatorie in unistruzione select con la clausola SQL PARTITION BY . Aggiungiamo le colonne CustomerName e OrderAmount ed eseguiamo la seguente query.
|
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.;
|
Otteniamo la colonna CustomerName e OrderAmount insieme alloutput di la funzione aggregata. Otteniamo anche tutte le righe disponibili nella tabella Ordini.
Nello screenshot seguente, puoi per CustomerCity Chicago, esegue aggregazioni (Avg, Min e Max) e fornisce i valori nelle rispettive colonne.
Allo stesso modo, possiamo usare altre funzioni di aggregazione come come conteggio per scoprire il numero totale di ordini in una determinata città con la clausola SQL PARTITION BY.
|
1
2
3
4
5
6
7
8
|
SELEZIONA 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.;
|
Possiamo vedere il conteggio degli ordini per una particolare città. Ad esempio, abbiamo quindi due ordini dalla città di Austin; mostra il valore 2 nella colonna CountofOrders.
PARTITION BY clausola con ROW_NUMBER ()
Possiamo utilizzare la clausola SQL PARTITION BY con la funzione ROW_NUMBER () per avere un numero di riga di ogni riga. Definiamo i seguenti parametri per utilizzare ROW_NUMBER con la clausola SQL PARTITION BY.
- Colonna PARTITION BY: in questo esempio, vogliamo partizionare i dati sulla colonna CustomerCity
- Order By : Nella colonna ORDER BY, definiamo una colonna o una condizione che definisce il numero di riga. In questo esempio, vogliamo ordinare i dati nella colonna OrderAmount
|
1
2
3
4
5
6
7
8
9
10
|
SELEZIONA 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.;
|
Nello screenshot seguente vediamo CustomerCity Chicago , abbiamo la riga numero 1 per lordine con limporto massimo 7577,90. fornisce il numero di riga con OrderAmount decrescente.
PARTITION BY clausola con valore totale cumulativo
Supponiamo di desidera ottenere un totale cumulativo per gli ordini in una partizione.Il totale cumulativo deve essere della riga corrente e della riga successiva nella partizione.
Ad esempio, nella città di Chicago, abbiamo quattro ordini.
|
CustomerCity |
CustomerName |
Classifica |
Importo ordini |
Righe totali cumulative |
Totale cumulativo |
|
Chicago |
Marvin |
Grado 1 +2 |
|||
|
Chicago |
Lawrence |
Grado 2 + 3 |
|||
|
Chicago |
Alex |
Grado 3 + 4 |
|||
|
Chicago |
Jerome |
Grado 4 |
Nella seguente query, abbiamo la clausola ROWS specificata da se selezionare la riga corrente (utilizzando RIGA CORRENTE) e la riga successiva (utilizzando 1 SEGUENTE). Calcola ulteriormente la somma su quelle righe utilizzando sum (Orderamount) con una partizione su CustomerCity (utilizzando OVER (PARTITION BY Customercity ORDER BY OrderAmount DESC).
|
1
2
3
4
5
6
7
|
SELEZIONA 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 TRA CORRENTE ROW E 1 SEGUENTE), 1) AS CumulativeTotal,
|
Allo stesso modo, possiamo calcolare la media cumulativa utilizzando il seguente query con la clausola SQL PARTITION BY.
|
1
2
3
4
5
6
7
|
SELEZIONA 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 BETWEEN ROW E 1 FOLLOWING) , 1) AS CumulativeAVG
|
ROWS UNBOUNDED PRECEDING con la clausola PARTITION BY
Possiamo usare ROWS UNBOUNDED PRECEDING con la clausola SQL PARTITION BY per selezionare una riga in una partizione prima della riga corrente e del valore più alto r ow dopo la riga corrente.
Nella tabella seguente, possiamo vedere la riga 1; non ha alcuna riga con un valore alto in questa partizione. Pertanto, il valore medio cumulativo è lo stesso della riga 1 OrderAmount.
Per Row2, cerca il valore della riga corrente (7199,61) e il valore più alto della riga 1 (7577,9). Calcola la media per questi due importi.
Per la riga 3, cerca il valore corrente (6847,66) e un valore di importo superiore a questo valore che è 7199,61 e 7577,90. Calcola la media di questi e restituisce.
|
CustomerCity |
CustomerName |
Rank |
OrderAmount |
Righe medie cumulative |
Media cumulativa |
|
Chicago |
Marvin |
Grado 1 |
|||
|
Chicago |
Lawrence |
Grado 1 + 2 |
|||
|
Chicago |
Alex |
Rango 1 + 2 + 3 |
|||
|
Chicago |
Jerome |
Grado 1 + 2 + 3 + 4 |
Esegui la seguente query per ottenere questo risultato con i nostri dati di esempio.
|
1
2
3
4
5
6
7
8
|
SELEZIONA 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) AS CumulativeAvg
FROM.;
|
Conclusione
In questo articolo, abbiamo esplorato la clausola SQL PARTIION BY e il suo confronto con la clausola GROUP BY. Abbiamo anche imparato il suo utilizzo con alcuni esempi. Spero che questo articolo ti sia utile e non esitare a porre qualsiasi domanda nei commenti di seguito
- Autore
- Post recenti
È lautore di una delle più grandi raccolte online gratuite di articoli su un singolo argomento, con la sua serie di 50 parti sui gruppi di disponibilità Always On di SQL Server. Sulla base del suo contributo alla comunità di SQL Server, è stato riconosciuto con vari premi tra cui il prestigioso “Miglior autore dellanno” ininterrottamente nel 2020 e 2021 a SQLShack.
Raj è sempre interessato a nuove sfide, quindi se hai bisogno di consulenza aiuto su qualsiasi argomento trattato nei suoi scritti, può essere raggiunto a [email protected]
Visualizza tutti i post di Rajendra Gupta
- Timeout della sessione in SQL Server Always On Availability Gruppi – 8 febbraio 2021
- Esecuzione di aggiornamenti delle versioni minori e maggiori per AWS RDS SQL Server – 29 gennaio 2021
- Distribuzione di istanze PostgreSQL di AWS RDS – 27 gennaio 2021