COUNT OVER PARTITION BY: Spiegazione con 3 esempi pratici

In SQL, la combinazione della funzione COUNT() con la clausola OVER() e PARTITION BY apre le porte a un modo completamente diverso di contare le righe. In questo articolo imparerete a eseguire più conteggi in un'unica query e a calcolare espressioni basate sul risultato della funzione COUNT.

In questo articolo verrà illustrato come utilizzare la funzione COUNT() combinata con le clausole OVER() e PARTITION BY. Per saperne di più su questo argomento, vi consiglio il nostro corso interattivo Window Functions. Contiene oltre 200 esercizi interattivi sull'uso della clausola OVER() con le funzioni di finestra. Al termine di questo corso, potrete affrontare questo argomento con facilità e sentirvi a vostro agio nell'uso delle funzioni finestra nei database SQL.

Esempio n. 1: Introduzione all'uso di COUNT OVER PARTITION BY

Supponiamo di avere una tabella chiamata order con un record per ogni ordine di vendita ricevuto in un negozio di animali. La tabella ha colonne come order_id, order_date, customer_id, salesperson_id, ship_address, ship_state e amount_paid.

La seguente query mostra gli ordini ricevuti dall'azienda nel primo semestre del 2023. Si noti che abbiamo aggiunto una colonna extra chiamata orders_this_customer che mostra il numero totale di ordini inviati da ciascun cliente durante questo periodo.

SELECT 
  order_id,
  order_date,
  customer_id,
  amount_paid,
 COUNT(*) OVER (PARTITION BY customer_id) AS orders_this_customer
FROM order
WHERE order_date >= '2023-01-01' AND order_date <= '2023-06-30';

In SQL, si usa la funzione COUNT() da sola o combinata con la clausola GROUP BY per contare le righe in un insieme di risultati o in un gruppo di righe. OVER() e PARTITION BY applicano la funzione COUNT() a un gruppo o a righe definite da PARTITION BY. Nel nostro esempio, il gruppo è definito dai valori della colonna customer_id. La funzione COUNT() conta il numero di ordini con lo stesso customer_id.

La combinazione di COUNT() e OVER(PARTITION BY) è più potente rispetto all'uso della sola funzione COUNT() perché consente di ottenere il numero di righe per ogni valore specifico di una colonna.

Quando si usano OVER() e PARTITION BY, non è necessario usare la clausola GROUP BY per raggruppare i record; ciò consente di avere insiemi di risultati a livello di riga. Ogni riga del set di risultati conterrà informazioni a livello di ordine, ma il valore della colonna orders_this_customer conterrà il numero totale di ordini effettuati da ciascun cliente.

In altre parole, stiamo combinando i dati a livello di report con quelli a livello di cliente nella stessa riga. È possibile aggiungere altri dati di livello utilizzando colonne diverse nella clausola PARTITION BY. Di seguito è riportato un risultato parziale di questa query:

Prima di concludere questa sezione, vorrei suggerire l'articolo La funzione SQL COUNT() spiegata con 7 esempi, dove si possono trovare molti esempi di query che utilizzano la funzione COUNT(). Per i lettori che desiderano approfondire l'argomento, consiglio l'articolo Come usare la funzione SQL PARTITION BY con OVER, in cui è possibile trovare una spiegazione chiara con esempi delle clausole OVER() e PARTITION BY.

Esempio n. 2: Calcolo dei totali in base a criteri diversi

Prima di continuare, mostriamo l'ordine completo della tabella. Ogni riga della tabella rappresenta un ordine ricevuto dal negozio di animali. Una riga di ordine ha una customer_id, una salesperson_id, una order_date, una ship_state e una ship_city, tra le altre colonne; le altre colonne sono autoesplicative. Ecco una vista parziale della tabella:

Supponiamo che il negozio di animali, alla fine del mese, voglia avere un report con tutti gli ordini. Per ogni riga, vuole anche mostrare due campi calcolati: il numero totale di ordini venduti in quel giorno e il numero totale di ordini venduti da questo venditore. La query per ottenere questo report è la seguente:

SELECT 
  order_id,
  order_date,
  customer_id,
  salesperson_id,
  COUNT(1) OVER (PARTITION BY order_date) as orders_per_day,
  COUNT(1) OVER (PARTITION BY salesperson_id) as orders_per_salesperson
FROM order
WHERE order_date between '2023-06-01' AND '2023-06-30';

Il risultato della query è mostrato di seguito:

Nella query precedente è stata utilizzata l'espressione COUNT(1), che funziona come COUNT(*). La clausola PARTITION BY indica i criteri di raggruppamento delle righe da contare.

Per ottenere il campo orders_per_day, si utilizza la clausola OVER (PARTITION BY order_date); per il campo orders_per_salesperson, si utilizza la clausola OVER (PARTITION BY salesperson_id).

In questo caso, si noti che l'unica differenza è il campo che viene inserito dopo la clausola PARTITION BY. Questo campo definisce i criteri per il conteggio delle righe; cioè, tutte le righe con lo stesso valore in questo campo saranno contate insieme.

L'effetto di questi due campi nel risultato della query è che aggiungiamo due campi di diverso livello di granularità alla riga; il campo orders_per_salesperson è un totale raggruppato per venditore, mentre il campo orders_per_day è un totale raggruppato per data dell'ordine.

Nota: COUNT(DISTINCT) non funziona con OVER(PARTITION BY)

Esistono alcune varianti della funzione COUNT(), come COUNT(*), COUNT(1) o COUNT(DISTINCT). Per saperne di più, consultate il nostro articolo Qual è la differenza tra COUNT(*), COUNT(1), COUNT(nome colonna) e COUNT(nome colonna DISTINCT)? Troverete una spiegazione chiara di questi diversi modi di utilizzare la funzione COUNT().

In alcuni casi, ad esempio se abbiamo bisogno di contare quante persone diverse hanno effettuato ordini in un determinato giorno, potremmo pensare di utilizzare l'espressione COUNT(DISTINCT customer_id) OVER (PARTITION BY order_date). Tuttavia, è importante chiarire che COUNT(DISTINCT) OVER(PARTITION BY) non è supportato dai database più diffusi (come PostgreSQL, SQL Server e Snowflake, tra gli altri).

Esempio n. 3: Uso di COUNT() con OVER nelle espressioni

Nel prossimo esempio di query, calcoleremo alcune percentuali usando espressioni aritmetiche con due diversi COUNTs.

Supponiamo che il reparto marketing voglia indirizzare in modo specifico la prossima campagna di marketing a un gruppo preciso di clienti. Per farlo, ha bisogno di alcune metriche sui risultati commerciali: la percentuale di ordini ricevuti da ogni stato, la percentuale di ordini ricevuti per ogni famiglia di prodotti e la combinazione di entrambe le metriche (ad esempio, la percentuale di ordini ricevuti dal Texas per la famiglia di prodotti DOG). La query per ottenere questo report è la seguente:

SELECT DISTINCT
  ship_state,
  product_family,
  COUNT(1) OVER () AS total_orders,
  COUNT(1) OVER (PARTITION BY ship_state) state_orders,
  COUNT(1) OVER (PARTITION BY ship_state) / COUNT(1) OVER () AS state_percentage,
  COUNT(1) OVER (PARTITION BY product_family) AS family_orders,
  COUNT(1) OVER (PARTITION BY product_family) / COUNT(1) OVER () AS family_percentage
FROM   order
ORDER BY ship_state, product_family

Nella query precedente, il campo total_orders utilizza l'espressione COUNT(1) OVER () per calcolare la quantità totale di ordini ricevuti dall'azienda. Poi, nel campo state_orders, l'espressione COUNT() viene utilizzata per ottenere la quantità di ordini ricevuti dallo stato della riga corrente (ad esempio, se il valore ship_state della riga corrente è TX (Texas), verranno mostrati tutti gli ordini provenienti dal Texas); questo campo è molto simile a quello calcolato negli esempi precedenti.

La parte interessante di questo esempio di query sta nell'espressione del campo state_percentage, che utilizza due COUNTs per calcolare una percentuale. In altre parole, stiamo dividendo il numero di ordini dello stato corrente (espressione rossa) per il numero totale di ordini (espressione blu). Negli ultimi due campi, ripetiamo lo stesso approccio per il campo product_family.

Il risultato della query è riportato di seguito:

Utilizzo di OVER PARTITION BY con COUNT() e altre funzioni

In questo articolo abbiamo trattato la funzione COUNT() combinata con le clausole OVER e PARTITION BY. Tuttavia, in SQL esistono molte altre funzioni che possono essere combinate con PARTITION BY. Queste funzioni sono chiamate funzioni finestra e il nostro corsoWindow Functions offre un eccellente materiale didattico su di esse. Il corso contiene oltre 200 esercizi interattivi e offre un tutorial completo sulle funzioni finestra.

Se conoscete già le funzioni delle finestre e volete fare più pratica, vi consiglio il nostro set di esercitazioni Window Functions . Questo corso è stato progettato per offrire una pratica completa delle funzioni di finestra su esempi reali. I suoi 100 esercizi su tre diverse serie di dati assomigliano ai problemi che si incontrano nel mondo reale.

Se cercate altre risorse sulle funzioni finestra, date un'occhiata al nostro SQL Window Functions Cheat Sheet e alle Le 10 migliori domande di intervista sulle funzioni SQL Window Functions, dove potrete trovare materiale per prepararvi a un colloquio di lavoro in SQL.