Panoramica della clausola SQL PARTITION BY

Questo articolo tratterà la clausola SQL PARTITION BY e, in particolare, la differenza con GROUP BY in un'istruzione select. Esploreremo anche vari casi d'uso 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 dei dati del campione

Creiamo una tabella Ordini nel mio database di esempio SQLShackDemo e inseriamo i record per scrivere ulteriori query.

 
1
2
3
4
5
6
7
8
9
10
Use SQLShackDemo
Go
CREATE TABLE [dbo].[Orders]
(
    [orderid] INT,
    [Orderdate] DATE,
    [CustomerName] VARCHAR(100),
    [Customercity] VARCHAR(100),
    [Orderamount] MONEY
)

Uso ApexSQL Generate per inserire dati di esempio in questo articolo. Fare clic con il tasto destro sulla tabella Ordini e Genera dati di prova .

Genera dati di testo utilizzando ApexSQL Generate

Lancia ApexSQL Generate. Ho generato uno script per inserire i dati nella tabella Ordini. Esegui questo script per inserire 100 record nella tabella Ordini.

 
1
2
3
4
5
6
7
USE [SQLShackDemo]
GO
INSERT [dbo].[Orders]  VALUES (216090, CAST(N'1826-12-19' AS Date), N'Edward', N'Phoenix', 4713.8900)
GO
INSERT [dbo].[Orders]  VALUES (508220, CAST(N'1826-12-09' AS Date), N'Aria', N'San Francisco', 9832.7200)
GO

Una volta eseguite le istruzioni di inserimento, possiamo vedere i dati nella tabella Ordini nell'immagine seguente.

Dati di esempio

Utilizziamo la clausola SQL GROUP BY per raggruppare i risultati in base alla colonna specificata e utilizzare funzioni di aggregazione come Avg (), Min (), Max () per calcolare i valori richiesti.

Sintassi della funzione Raggruppa per

 
1
2
3
4
SELECT expression, aggregate function ()
FROM tables
WHERE conditions
GROUP BY expression

Supponiamo di voler trovare i seguenti valori nella tabella Ordini

  • Valore minimo dell'ordine in una città
  • Valore massimo dell'ordine in una città
  • Valore medio degli ordini in una città

Eseguire la seguente query con la clausola GROUP BY per calcolare questi valori.

 
1
2
3
4
5
6
SELECT Customercity,
       AVG(Orderamount) AS AvgOrderAmount,
       MIN(OrderAmount) AS MinOrderAmount,
       SUM(Orderamount) TotalOrderAmount
FROM [dbo].[Orders]
GROUP BY Customercity;

Nello screenshot seguente, possiamo vedere i valori medio, minimo e massimo raggruppati per CustomerCity.

Output della clausola SQL Group By

Ora, vogliamo aggiungere anche le colonne CustomerName e OrderAmount nell'output. Aggiungiamo queste colonne nell'istruzione select ed eseguiamo il codice seguente.

 
1
2
3
4
5
6
SELECT Customercity, CustomerName ,OrderAmount,
       AVG(Orderamount) AS AvgOrderAmount,
       MIN(OrderAmount) AS MinOrderAmount,
       SUM(Orderamount) TotalOrderAmount
FROM [dbo].[Orders]
GROUP BY Customercity;

Una volta eseguita questa query, otteniamo un messaggio di errore. Nella clausola SQL GROUP BY, possiamo utilizzare una colonna nell'istruzione select se viene utilizzata anche nella clausola Group by. Non consente alcuna colonna nella clausola select che non faccia parte della clausola GROUP BY.

Errore nell'output della clausola SQL Group By.

Possiamo utilizzare la clausola SQL PARTITION BY per risolvere questo problema. Cerchiamo di esplorarlo 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 l'aggregazione. Nell'esempio precedente, abbiamo utilizzato Raggruppa per con la colonna CustomerCity e calcolato i valori medi, minimi e massimi.

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 [dbo].[Orders];

Nell'output, otteniamo valori aggregati simili a una clausola GROUP By. È possibile notare una differenza nell'output dell'output della clausola SQL PARTITION BY e GROUP BY.

Esempio di clausola SQL PARTITION 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.

Fornisce una 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. Nell'output della query di SQL PARTITION BY, otteniamo anche 15 righe insieme ai valori Min, Max e medio.

Nell'esempio precedente, otteniamo un messaggio di errore se proviamo ad aggiungere una colonna che non fa parte della clausola GROUP BY.

Possiamo aggiungere le colonne richieste in un'istruzione 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 [dbo].[Orders];

Otteniamo CustomerName e OrderAmount colonna insieme con l'uscita della funzione aggregata. Otteniamo anche tutte le righe disponibili nella tabella Ordini.

Esempio di SQL PARTITION BY

Nello screenshot seguente, puoi per CustomerCity Chicago , esegue aggregazioni (Avg, Min e Max) e fornisce valori nelle rispettive colonne.

Esempi di valori medi, minimi e massimi

Allo stesso modo, possiamo usare altre funzioni aggregate come count per scoprire il numero totale di ordini in una particolare città con la clausola 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(PARTITION BY Customercity) AS MinOrderAmount,
       SUM(Orderamount) OVER(PARTITION BY Customercity) TotalOrderAmount
FROM [dbo].[Orders];

Possiamo vedere il conteggio degli ordini per una determinata città. Ad esempio, abbiamo quindi due ordini dalla città di Austin; mostra il valore 2 nella colonna CountofOrders .

Esempio di conteggio degli ordini

Clausola PARTITION BY con ROW_NUMBER ()

Possiamo usare 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, si desidera suddividere i dati nella 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
SELECT 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 [dbo].[Orders];

Nello screenshot seguente, vediamo per CustomerCity Chicago , abbiamo la riga numero 1 per l'ordine con l'importo massimo 7577.90. fornisce il numero di riga con OrderAmount decrescente.

Numero di RIGA utilizzando SQL PARTITION BY

Clausola PARTITION BY con valore totale cumulativo

Supponiamo di voler ottenere un totale cumulativo per gli ordini in una partizione. Il totale cumulativo dovrebbe essere della riga corrente e della riga successiva nella partizione.

Esempio di valore totale cumulativo

Ad esempio, nella città di Chicago, abbiamo quattro ordini.

CustomerCity

Nome del cliente

Rango

Ammontare dell'ordine

Righe totali cumulative

Totale cumulativo

Chicago

Marvin

1

7577.9

Grado 1 +2

14777.51

Chicago

Lawrence

2

7199.61

Grado 2 + 3

14047.21

Chicago

Alex

3

6847.66

Grado 3 + 4

8691.49

Chicago

Jerome

4

1843.83

Grado 4

1843.83

Nella seguente query, abbiamo la clausola ROWS specificata per selezionare la riga corrente (utilizzando CURRENT ROW) e la riga successiva (utilizzando 1 FOLLOWING). Calcola inoltre 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
SELECT 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 BETWEEN CURRENT ROW AND 1 FOLLOWING), 1) AS CumulativeTotal,

 

Esempi di valori medi cumulativi

Allo stesso modo, possiamo calcolare la media cumulativa utilizzando la seguente query con la clausola SQL PARTITION BY .

 
1
2
3
4
5
6
7
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 BETWEEN CURRENT ROW AND 1 FOLLOWING), 1) AS CumulativeAVG

 

esempio medio cumulativo

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 la riga con il valore più alto dopo la riga corrente.

Nella tabella seguente, possiamo vedere per 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 dei rendimenti.

 

CustomerCity

Nome del cliente

Rango

Ammontare dell'ordine

Righe medie cumulative

Media cumulativa

Chicago

Marvin

1

7577.9

Grado 1

7577.90

Chicago

Lawrence

2

7199.61

Grado 1 + 2

7388.76

Chicago

Alex

3

6847.66

Grado 1 + 2 + 3

7208.39

Chicago

Jerome

4

1843.83

Grado 1 + 2 + 3 + 4

5867.25

Esegui la seguente query per ottenere questo risultato con i nostri dati di esempio.

 
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) AS CumulativeAvg
FROM [dbo].[Orders];

 

ROWS UNBOUNDED PRECEDING esempio

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 trovi utile questo articolo e sentiti libero di porre qualsiasi domanda nei commenti qui sotto

Torna su
Categoria

SQL SERVER (994)


Autore

https://www.sqlshack.com/sql-partition-by-clause-overview/


Data pubblicazione.

23/04/2021



Recensioni

Articolo non ancora recensito