Come usare SUM() con OVER(PARTITION BY) in SQL

Scoprite i casi d'uso reali della funzione SUM() con la clausola OVER(PARTITION BY). Imparate la sintassi e guardate 5 diversi esempi.

Utilizziamo le funzioni SQL window per eseguire operazioni su gruppi di dati. Queste operazioni includono le funzioni matematiche SUM(), COUNT(), AVG() e altre ancora. In questo articolo spiegheremo cosa fanno SUM() e OVER(PARTITION BY) in SQL. Vi mostreremo i casi d'uso più comuni nelle applicazioni reali per determinare il rapporto tra il valore di una singola riga e il valore totale, calcolare i totali progressivi e trovare un totale d'ordine personalizzato che includa gli sconti per alcuni prodotti.

Per una guida approfondita all'uso di SUM() con OVER() e altre funzioni di finestra, seguite il nostro corso interattivo Window Functions interattivo. Spiega in dettaglio tutti i concetti delle funzioni finestra SQL e contiene oltre 200 esercizi.

Iniziamo.

La clausola OVER() in SQL

In SQL, la clausola OVER() viene utilizzata per introdurre le funzioni finestra. La sintassi generale è:

SELECT …
  <window function> OVER(...)
…

OVER() indica al database che vogliamo usare le funzioni finestra. La funzione finestra può essere una funzione aggregata, come SUM(), o un'altra funzione finestra.

Una funzione finestra lavora su una "cornice di finestre", ovvero un insieme di righe relative alla riga corrente. OVER() definisce la cornice di finestre per ogni riga. Una clausola OVER() vuota indica al database che l'intero insieme di risultati è la cornice della finestra.

Vediamo un esempio della sintassi di SUM() OVER(). Utilizzeremo la tabella employees dai dati di esempio forniti da Oracle Live SQL. Questa tabella è composta dalle seguenti colonne:

• emp_id è l'identificativo del dipendente.
• name è il nome del dipendente.
• job è il titolo del lavoro.
• dept_id è l'identificativo del reparto.
• salary è lo stipendio del dipendente.

È possibile utilizzare la funzione SUM() con la clausola OVER() per ottenere lo stipendio totale di tutti i dipendenti e visualizzare lo stipendio totale accanto allo stipendio di ciascun dipendente.

SELECT 
  emp_id, 
  name, 
  job, 
  dept_id, 
  salary, 
  SUM(salary) OVER() AS total_salary
FROM employees;

La funzione SUM() viene eseguita per ogni riga della tabella. Ogni volta che viene richiamata, recupera le informazioni sullo stipendio da tutte le righe della tabella per calcolare l'importo totale dello stipendio. Lo stipendio totale viene visualizzato accanto ai dettagli di ogni singola riga. I dettagli delle singole righe vengono conservati e visualizzati accanto al salario totale.

In questo esempio, la finestra (l'insieme di righe su cui opera SUM() ) è l'intero set di dati. È possibile aggiungere altre clausole in OVER() per modificare la cornice della finestra.

Esempio 1A: Calcolo della somma per ciascun gruppo - SUM() con OVER(PARTITION BY ...)

La clausola OVER() può contenere dettagli sul modo in cui si desidera partizionare i dati. Si usa la clausola PARTITION BY in OVER() per dividere i dati in partizioni, o gruppi. L'uso di PARTITION BY è simile a quello di GROUP BY, in quanto le righe vengono suddivise in gruppi in base al valore di alcune colonne. Quando si usa SUM() OVER(PARTITION BY …), si può calcolare la somma dei valori per ogni gruppo, o partizione, di dati. Ad esempio, è possibile calcolare lo stipendio totale per ogni reparto:

SELECT 
  emp_id, 
  name, 
  job, 
  dept_id, 
  salary,
  SUM(salary) OVER(PARTITION BY dept_id) AS dept_total_salary
FROM employees;

Ecco il risultato:

Utilizziamo SUM(salary) OVER(PARTITION BY dept_id) per ottenere lo stipendio totale per reparto. La clausola PARTITION BY divide le righe in gruppi in base alla colonna dept_id. Le righe con dept_id uguale a 10 vengono inserite in un gruppo (contrassegnato in giallo nella tabella precedente), le righe con dept_id uguale a 20 vengono inserite in un altro gruppo (contrassegnato in verde) e infine le righe con dept_id uguale a 30 vengono inserite in un altro gruppo (contrassegnato in rosso). La funzione SUM() calcola la somma delle righe in ogni gruppo.

Si noti che quando si utilizza SUM() OVER(PARTITION BY), si conservano i dettagli delle singole righe. È possibile, ad esempio, vedere i dettagli del dipendente Ford: la sua posizione, il suo stipendio e il confronto con gli stipendi totali del suo reparto.

Questo è l'uso più tipico di SUM() OVER(PARTITION BY): si calcola il valore della somma per ogni gruppo di dati e si conservano i dettagli delle singole righe. Vediamo un esempio simile.

Esempio 1B: Calcolo dello stipendio per ciascun titolo di lavoro

Possiamo usare SUM(salary) OVER(PARTITION BY job) per ottenere lo stipendio totale per ogni titolo di lavoro. Diamo un'occhiata:

SELECT 
  emp_id, 
  name, 
  job, 
  dept_id, 
  salary,
  SUM(salary) OVER(PARTITION BY job) AS job_total_salary
FROM employees;

Ecco il risultato:

Questa volta, le righe sono raggruppate in base al valore della mansione anziché all'ID del reparto. I dipendenti con la stessa posizione lavorativa vengono inseriti in un gruppo e si calcola il salario totale delle persone che occupano questa posizione. La funzione SUM() viene applicata a tutti gli stipendi di ciascun gruppo: lo stipendio totale del gruppo "Manager" è la somma di 2450, 2850 e 2975, che sono gli stipendi dei tre manager presenti nella nostra tabella.

Esempio 2: Calcolo del rapporto tra il valore di una singola riga e il totale

In genere, si vuole vedere il confronto tra ogni singola riga e la somma totale. Calcoliamo la percentuale di ogni singolo stipendio rispetto al totale degli stipendi del proprio reparto.

SELECT 
  emp_id, 
  name, 
  job, 
  dept_id, 
  salary,
  ROUND(100.0 * salary / SUM(salary) OVER(PARTITION BY dept_id), 2) 
           AS percentage_of_dept_total_salary
FROM employees;

Questa volta, le righe sono raggruppate in base al valore job anziché all'ID del reparto. I dipendenti con la stessa posizione lavorativa vengono inseriti in un gruppo e si calcola la retribuzione totale delle persone che occupano questa posizione. La funzione SUM() viene applicata a tutti gli stipendi di ciascun gruppo: lo stipendio totale del gruppo "Manager" è la somma di 2450, 2850 e 2975, che sono gli stipendi dei tre manager presenti nella nostra tabella.

Esempio 2: Calcolo del rapporto tra il valore di una singola riga e il totale

In genere, si vuole vedere il confronto tra ogni singola riga e la somma totale. Calcoliamo la percentuale di ogni singolo stipendio rispetto al totale degli stipendi del proprio reparto.

SELECT 
  emp_id, 
  name, 
  job, 
  dept_id, 
  salary,
  ROUND(100.0 * salary / SUM(salary) OVER(PARTITION BY dept_id), 2) 
           AS percentage_of_dept_total_salary
FROM employees;

Possiamo vedere, ad esempio, che l'analista Scott ha uno stipendio individuale di 3000, pari al 27,59% degli stipendi totali del suo reparto.

Ecco la ripartizione delle funzioni utilizzate per svolgere questo compito:

• Prendiamo ogni singolo stipendio e lo dividiamo per lo stipendio totale del reparto:

salary / SUM(salary) OVER(PARTITION BY dept_id)

• Per ottenere una percentuale, la moltiplichiamo per il 100%:
  
  • 0 * salary / SUM(salary) OVER(PARTITION BY dept_id)
• Successivamente, utilizziamo la funzione ROUND() per ottenere due cifre decimali:

ROUND(100.0 * salary / SUM(salary) OVER(PARTITION BY dept_id), 2)

Il calcolo del rapporto tra la singola riga e la somma di un gruppo è un altro caso d'uso molto comune di SUM() con OVER(PARTITION BY). In modo simile, si può calcolare la differenza tra il totale del gruppo e la singola riga.

Qual è la differenza tra SUM() con OVER(PARTITION BY …) e l'uso di SUM() con GROUP BY?

La clausola PARTITION BY svolge un compito simile a quello della clausola GROUP BY. Sia OVER(PARTITION BY) che GROUP BY dividono il set di dati in partizioni, o gruppi. Quando si usa la funzione SUM(), entrambe le espressioni calcolano la somma per ogni gruppo. Quindi, quale scegliere?

Quando si utilizza la clausola GROUP BY, i dettagli delle singole righe vengono ridotti. Guardate:

SELECT 
  job, 
  SUM(salary) AS total_salary
FROM employees
GROUP BY job;

In questo caso, si ottiene il valore totale dello stipendio per ogni titolo di lavoro senza considerare gli stipendi dei singoli dipendenti.

Tuttavia, se si desidera confrontare il valore della somma totale con i valori delle singole righe (ad esempio, per calcolare il rapporto tra il valore individuale e quello totale), la strada da percorrere è OVER(PARTITION BY …), che restituisce tutte le singole righe insieme al valore della somma totale per ogni riga. Il valore della somma totale può variare a seconda della partizione a cui appartiene la riga.

SELECT 
  name, 
  job, 
  salary, 
  SUM(salary) OVER(PARTITION BY job) AS total_salary
FROM employees;

Il valore total_salary corrisponde ai risultati della query precedente con GROUP BY. Ma qui è possibile vedere anche i singoli stipendi. Ad esempio, ci sono due analisti che guadagnano 6000 in totale; ognuno di loro guadagna 3000.

La regola generale è la seguente: se si vuole conoscere solo il valore della somma per ogni gruppo e non si è interessati ai dettagli di ogni singola riga, si deve usare la clausola GROUP BY. Se si è interessati sia alla somma di ciascun gruppo sia ai dettagli delle singole righe, si deve usare la clausola SUM() OVER(PARTITION BY).

Esempio 3: Calcolo di un totale progressivo usando SUM() con OVER(PARTITION BY)

Un altro uso comune della sintassi SUM() OVER(...) è il calcolo del totale progressivo.

Un totale progressivo è la somma cumulativa dei numeri precedenti di una colonna. Si usano i totali progressivi per calcolare valori che si accumulano nel tempo. Ad esempio, con un totale progressivo si può calcolare l'utilizzo mensile dei dati mobili aggiungendo il valore di ogni giorno successivo alla somma dei valori dei giorni precedenti. Allo stesso modo, è possibile calcolare come il numero di utenti registrati aumenti ogni giorno o come le entrate totali aumentino con ogni transazione.

Per calcolare il totale progressivo utilizziamo la sintassi SUM() con OVER(PARTITION BY … ORDER BY …).

Vediamo un esempio. Utilizzeremo la tabella orders dai dati di esempio forniti da Oracle Live SQL. Questa tabella memorizza i dati storici degli ordini. Ogni ordine ha una data (order_date), un rappresentante (sales_rep_id) e un valore totale (order_total).

Qui calcoliamo il valore totale corrente per ogni rappresentante di vendita:

SELECT 
  order_date, 
  sales_rep_id, 
  order_total,
  SUM(order_total) OVER(PARTITION BY sales_rep_id ORDER BY order_date) 
          AS running_total
FROM orders;

Per fare ciò, si suddivide la tabella per rappresentante e si ordina ogni suddivisione per data:

	SUM(order_total) OVER(PARTITION BY sales_rep_id ORDER BY order_date)

Il nuovo elemento della sintassi è ORDER BY. Definisce come vengono ordinate le righe in ogni riquadro della finestra. La funzione SUM() viene applicata a ogni riga, sommando il valore attuale order_total al valore precedente running_total; i totali progressivi vengono calcolati separatamente per ogni partizione (in questo caso, per ogni ID rappresentante).

Osserviamo più da vicino l'addetto alle vendite con ID 153. Il suo primo ordine è stato effettuato il 29 marzo. Il loro primo ordine è stato effettuato il²⁹ marzo e aveva un valore totale di 10794,6. A questo punto, il totale progressivo (per ogni ID di vendita) è stato calcolato separatamente per ogni partizione. A questo punto, il totale corrente (la somma) è uguale al valore dell'ordine. Il secondo ordine è stato effettuato il¹⁶ agosto per un valore di 78279,6; ora il totale corrente è uguale alla somma dei valori del primo e del secondo ordine (10794,6 + 78279,6 = 89074,2). Dopo il terzo ordine, il totale corrente è uguale al totale precedente più il valore del terzo ordine (89074,2 + 129 = 89203,2). Questo processo è simile per l'addetto alle vendite con l'ID 154.

Il calcolo di un totale progressivo è uno schema comune quando si utilizza SQL per l'analisi dei dati. Potete leggere come calcolare un totale progressivo in SQL in un'altra parte del blog.

Esempio 4: Conteggio degli oggetti nelle categorie personalizzate

La funzione SUM() viene spesso abbinata all'istruzione CASE WHEN per contare gli oggetti in categorie personalizzate. Ad esempio, si potrebbe voler calcolare il totale degli stipendi dei dipendenti che occupano posizioni dirigenziali in un reparto e visualizzarlo accanto ai dati di ciascun dipendente. È possibile ottenere questo risultato utilizzando SUM() OVER(PARTITION BY) in combinazione con CASE WHEN.

Esaminiamo innanzitutto l'istruzione CASE WHEN. È simile all'istruzione if, comune a molti linguaggi di programmazione. Si usa per definire il valore di un'espressione in situazioni o casi diversi.

Nell'esempio seguente, usiamo l'istruzione CASE WHEN per identificare ogni dipendente come dirigente (manager e presidenti) o come dipendente normale (tutte le altre posizioni lavorative). Date un'occhiata:

SELECT 
  emp_id, 
  name, 
  job, 
  dept_id, 
  CASE WHEN job IN (‘PRESIDENT’, ‘MANAGER’) 
    THEN ‘MANAGEMENT’ 
    ELSE ‘REGULAR’ END AS emp_type
  salary
FROM employees;

L'istruzione CASE valuta tutte le condizioni di WHEN. Se trova la condizione corrispondente, restituisce il valore nel ramo THEN. Se non trova la condizione corrispondente, restituisce il valore indicato dopo ELSE. Nel nostro esempio, i dipendenti che ricoprono la posizione di presidente o manager sono etichettati come "Management". A tutte le altre posizioni lavorative viene assegnata l'etichetta 'Regular'. Ecco il risultato della query:

Per saperne di più su CASE, leggete il nostro articolo Come usare CASE in SQL.

È inoltre possibile utilizzare l'istruzione CASE con SUM() per sommare i valori delle categorie personalizzate. Ecco la query:

SELECT
  dept_id,
  SUM(
    CASE WHEN job IN (‘PRESIDENT’, ‘MANAGER’) 
    THEN salary
    ELSE 0 END) 
    AS dept_management_salary,
  SUM(
    CASE WHEN job IN (‘PRESIDENT’, ‘MANAGER’) 
    THEN 0
    ELSE salary END) 
    AS dept_regular_salary
FROM employees
GROUP BY dept_id;

Per i dipendenti in posizione dirigenziale, la prima istruzione CASE restituisce il valore della colonna salary. La funzione SUM() in combinazione con GROUP BY somma quindi tutti gli stipendi dei dipendenti con mansioni dirigenziali con la stessa dept_id. In questo modo, si calcola lo stipendio totale dei dirigenti per reparto. La seconda istruzione CASE calcola lo stipendio totale del reparto per tutti i dipendenti regolari.

È possibile calcolare lo stipendio totale dei dipendenti in posizione dirigenziale in un reparto e visualizzarlo accanto ai dettagli dei dipendenti utilizzando SUM() con PARTITION BY:

SELECT 
  emp_id, 
  name, 
  job, 
  dept_id, 
  CASE WHEN job IN (‘PRESIDENT’, ‘MANAGER’) 
    THEN ‘MANAGEMENT’ 
    ELSE ‘REGULAR’ END AS emp_type
  salary,
  SUM(
    CASE WHEN job IN (‘PRESIDENT’, ‘MANAGER’) 
    THEN salary 
    ELSE 0 END) OVER(PARTITION BY dept_id) AS dept_management_salary
FROM employees;

Le righe vengono suddivise in gruppi in base alla colonna dept_id. Ci sono tre gruppi, uno per ogni reparto. La funzione SUM() viene applicata all'espressione CASE WHEN. Invece di calcolare il salario totale di ogni reparto, si calcola il salario totale dei dipendenti con posizioni dirigenziali nel reparto. La funzione CASE restituisce 0 per i dipendenti regolari (la somma non viene incrementata) e il valore dello stipendio per i dipendenti dirigenti. Questo schema può essere utilizzato in molte situazioni diverse. Infatti, nella prossima sezione vedremo un altro esempio di combinazione di CASE WHEN con SUM() e OVER(PARTITION BY).

Esempio 5: Calcolo del totale di un ordine con sconti usando SUM() con CASE WHEN

In questo esempio, vogliamo calcolare il valore totale di ogni ordine, compresi gli sconti per alcuni articoli dell'ordine. Utilizzeremo la sintassi SUM() OVER(PARTITION BY …) per sommare tutti i valori degli articoli per ordine. Per incorporare gli sconti sui prodotti, utilizzeremo l'istruzione CASE WHEN.

Utilizziamo la tabella order_items dai dati di esempio forniti da Oracle Live SQL. Questa tabella memorizza tutti gli articoli dell'ordine (product_id) appartenenti a ciascun ordine (order_id). Contiene informazioni sul prezzo unitario del prodotto (unit_price) e sulla quantità dell'ordine (quantity).

Ora vogliamo calcolare i valori dell'ordine: uno per ogni prodotto dell'ordine e un totale per l'ordine; il totale dell'ordine deve includere eventuali sconti applicati ai prodotti.

Il totale dell'ordine è uguale al prezzo unitario moltiplicato per la quantità dell'ordine. Tuttavia, quando viene applicato uno sconto a un prodotto, si utilizzerà un prospetto CASE WHEN per tenerne conto.

SELECT 
  order_id, 
  product_id, 
  unit_price, 
  quantity,   

  SUM(CASE 
     -- 20% discount for this product
     WHEN product_id = 3143 THEN unit_price*quantity*0.8
     -- 40% discount for this product
     WHEN product_id = 3106 THEN unit_price*quantity*0.6
     ELSE unit_price*quantity END)
  OVER(PARTITION BY order_id, product_id) 
        AS order_product_total_with_discount,

  SUM(CASE 
     -- 20% discount for this product
     WHEN product_id = 3143 THEN unit_price*quantity*0.8
     -- 40% discount for this product
     WHEN product_id = 3106 THEN unit_price*quantity*0.6
     ELSE unit_price*quantity END)
  OVER(PARTITION BY order_id) 
        AS order_total_with_discount    

FROM order_items;

Per calcolare il totale per prodotto in un ordine, si utilizza la seguente sintassi:

SUM(CASE 
       WHEN product_id=3143 THEN unit_price*quantity*0.8 -- 20% discount 
       WHEN product_id=3106 THEN unit_price*quantity*0.6 -- 40% discount 
       ELSE unit_price*quantity END)
OVER(PARTITION BY order_id, product_id) AS order_product_total_with_discount

Passiamo l'istruzione CASE WHEN come argomento alla funzione SUM(); se l'ID prodotto della riga corrente è 3143, applichiamo uno sconto del 20%; per l'ID prodotto 3106, lo sconto è del 40%. Successivamente, si suddivide il set di dati in base all'ID dell'ordine e all'ID del prodotto per ottenere i valori totali di ciascun prodotto di un ordine.

Si noti che nella query vengono utilizzate due clausole PARTITION BY diverse. Per calcolare il valore totale del prodotto, si partiziona il set di dati per ID ordine e ID prodotto. Per calcolare il totale per ordine, si partiziona il set di dati solo per ID ordine. Il valore totale dell'ordine è uguale per tutte le righe con lo stesso ID ordine. Cioè, se si sommano tutti i valori totali dei prodotti di un determinato ordine, si ottiene il valore totale dell'ordine. Ad esempio, per l'ID ordine 2355, si ottiene quanto segue: 9200 + 10545 + 16337.2 + 3572 + 3230 + 211.2 + 216.7 + 4975 + 46226.4 = 94513.5.

Ecco alcuni esempi della funzione SUM() con CASE WHEN.

SOMMA() con OVER() e PARTITION BY

Ora che sapete come utilizzare la funzione SOMMA() e le sue varie opzioni di sintassi, potete provarla voi stessi.

Abbiamo presentato esempi su come calcolare una percentuale del valore di una singola riga rispetto al totale della partizione e abbiamo mostrato come calcolare il totale corrente e il totale dell'ordine personalizzato. Vi invitiamo a fare pratica con i vostri dati.

Per saperne di più, provate il nostro corso interattivo Window Functions che spiega in dettaglio tutti i concetti delle funzioni delle finestre. Oppure date un'occhiata al nostro Window Functions Cheat Sheet se volete un riferimento rapido e semplice per le funzioni finestra di SQL.

Buona fortuna!