Come usare la clausola SQL PARTITION BY con OVER

Al centro di ogni chiamata di funzione finestra c'è una clausola OVER che definisce come vengono costruite le finestre dei record. All'interno della clausola OVER, può essere presente una sottoclausola opzionale PARTITION BY che definisce i criteri per identificare i record da includere in ciascuna finestra. Continuate a leggere e fate un passo importante per accrescere le vostre competenze in SQL!

Che cos'è la clausola PARTITION BY in SQL?

L'espressione SQL PARTITION BY è una sottoclausola della clausola OVER, utilizzata in quasi tutte le invocazioni di funzioni finestra come AVG(), MAX() e RANK(). Come molti lettori probabilmente sapranno, le funzioni finestra operano su cornici di finestre che sono insiemi di righe che possono essere diverse per ogni record del risultato della query. È qui che entra in gioco la sottoclausola SQL PARTITION BY, utilizzata per definire quali record devono far parte della cornice della finestra associata a ciascun record del risultato.

Questo articolo spiega la sottoclausola SQL PARTITION BY e il suo utilizzo con alcuni esempi. Poiché è profondamente legata alle funzioni finestra, si consiglia di leggere prima alcuni articoli sulle funzioni finestra, come "SQL Window Function Example With Explanations", dove si trovano molti esempi. Se volete saperne di più sulle funzioni finestra, c'è anche un interessante articolo con molti puntatori ad altri articoli sulle funzioni finestra.

La prima cosa su cui concentrarsi è la sintassi. Ecco come utilizzare la clausola SQL PARTITION BY:

SELECT
    ,
     OVER(PARTITION BY  [ORDER BY ])
FROM table;

Vediamo un esempio che utilizza la clausola PARTITION BY. Utilizzeremo la seguente tabella chiamata car_list_prices:

Per ogni autovettura vogliamo ottenere la marca, il modello, il prezzo, il prezzo medio di tutte le autovetture e il prezzo medio dello stesso tipo di autovettura (per avere un'idea più precisa del prezzo di una determinata autovettura rispetto alle altre). Ecco la query:

SELECT
    car_make,
    car_model,
    car_price,
    AVG(car_price) OVER() AS "overall average price",
    AVG(car_price) OVER (PARTITION BY car_type) AS "car type average price"
FROM car_list_prices

Il risultato della query è il seguente:

La query di cui sopra utilizza due funzioni finestra. La prima viene utilizzata per calcolare il prezzo medio di tutte le auto presenti nel listino. Utilizza la funzione finestra AVG() con una clausola OVER vuota, come si vede nell'espressione seguente:

AVG(car_price) OVER() AS "overall average price"

La seconda funzione finestra viene utilizzata per calcolare il prezzo medio di una specifica car_type vettura come standard, premium, sport, ecc. In questo caso si utilizza una clausola OVER con una sottoclausola PARTITION BY come si vede in questa espressione:

AVG(car_price) OVER (PARTITION BY car_type) AS "car type average price"

Le funzioni della finestra sono molto potenti, vero? Se volete imparare di più facendo esercizi ben preparati, vi suggerisco il corso Window Functionsdove potrete imparare a conoscere e ad utilizzare le funzioni finestra nei database SQL.

Approfondimento della clausola SQL PARTITION BY

La clausola GROUP BY raggruppa un insieme di record in base a dei criteri. Ciò consente di applicare una funzione (ad esempio, AVG() o MAX()) a gruppi di record per ottenere un risultato per gruppo.

A titolo di esempio, si supponga di voler ottenere il prezzo medio e il prezzo massimo per ogni marca. Utilizzare la seguente query:

SELECT car_make,
       AVG(car_price) AS average_price,
       MAX(car_price) AS top_price
FROM   car_list_prices
GROUP BY car_make

Ecco il risultato di questa query:

Rispetto alle funzioni finestra, GROUP BY comprime i singoli record in un gruppo. Di conseguenza, non è possibile fare riferimento a nessun campo di un singolo record, cioè solo alle colonne della clausola GROUP BY.

Ad esempio, si vuole creare un report con il modello, il prezzo e il prezzo medio della marca. Non è possibile farlo usando GROUP BY, perché i singoli record di ogni modello sono collassati a causa della clausola GROUP BY car_make. Per un'operazione di questo tipo, è necessario utilizzare le funzioni di finestra, come si vede nell'esempio seguente:

SELECT car_make,
       car_model,
       car_price,
       AVG(car_price) OVER (PARTITION BY car_make) AS average_make
FROM   car_list_prices

Il risultato di questa query è il seguente:

Per chi volesse approfondire, suggerisco l'articolo "Qual è la differenza tra un GROUP BY e un PARTITION BY?" con molti esempi che utilizzano le funzioni aggregate e le funzioni finestra.

Oltre alla clausola PARTITION BY, esiste un'altra clausola chiamata ORDER BY che stabilisce l'ordine dei record all'interno della finestra. Alcune funzioni finestra richiedono una clausola ORDER BY. Ad esempio, le funzioni finestra LEAD() e LAG() richiedono che la finestra dei record sia ordinata, poiché accedono al record precedente o successivo a quello corrente.

Un riquadro di finestra è composto da diverse righe definite dai criteri della clausola PARTITION BY. Tuttavia, è possibile specificare dei limiti o dei confini alla cornice della finestra, come si vede nell'immagine seguente:

I limiti inferiori e superiori nella clausola OVER possono essere:

• UNBOUNDED PRECEDING
• n PRECEDING
• CURRENT ROW
• n FOLLOWING
• UNBOUNDED FOLLOWING

Quando non si specifica alcun limite in una clausola OVER, la cornice della finestra viene costruita sulla base di alcuni valori limite predefiniti. Questi dipendono dalla sintassi utilizzata per chiamare la funzione finestra. La tabella seguente mostra i limiti predefiniti della cornice della finestra.

Esiste un articolo dettagliato intitolato "SQL Window Functions Cheat Sheet" in cui si possono trovare molti dettagli sulla sintassi ed esempi sui diversi limiti della cornice della finestra.

La clausola SQL PARTITION BY in azione

In questa sezione vengono mostrati alcuni esempi della clausola SQL PARTITION BY. Tutti si basano sulla tabella paris_london_flightsutilizzata da una compagnia aerea per analizzare i risultati commerciali di questa rotta per gli anni 2018 e 2019. Ecco un sottoinsieme dei dati:

Esempio 1

La prima query genera un report che include il sito flight_number, aircraft_model con la quantità di passeggeri trasportati e le entrate totali. La query è riportata di seguito:

SELECT DISTINCT
       flight_number,
       aircraft_model,
	SUM(num_of_passengers) OVER (PARTITION BY flight_number, aircraft_model)
                                                            AS total_passengers,
	SUM(total_revenue) OVER (PARTITION BY flight_number, aircraft_model)
                                                            AS total_revenue
FROM paris_london_flights
ORDER BY flight_number, aircraft_model;

Poiché il totale dei passeggeri trasportati e il totale dei ricavi sono generati per ogni possibile combinazione di flight_number e aircraft_model, utilizziamo la seguente clausola PARTITION BY per generare un insieme di record con lo stesso numero di volo e modello di aeromobile:

OVER (PARTITION BY flight_number, aircraft_model)

Quindi, per ogni insieme di record, applichiamo le funzioni finestra SUM(num_of_passengers) e SUM(total_revenue) per ottenere le metriche total_passengers e total_revenue mostrate nel prossimo set di risultati.

Esempio 2

Nella prossima query, mostriamo l'evoluzione del business confrontando le metriche di un mese con quelle del mese precedente. Creiamo un report utilizzando le funzioni finestra per mostrare la variazione mensile dei passeggeri e dei ricavi.

WITH year_month_data AS (
  SELECT DISTINCT
	   EXTRACT(YEAR FROM scheduled_departure) AS year,
	   EXTRACT(MONTH FROM scheduled_departure) AS month,
	   SUM(number_of_passengers)
              OVER (PARTITION BY EXTRACT(YEAR FROM scheduled_departure),
                                  EXTRACT(MONTH FROM scheduled_departure)
                   ) AS passengers
   FROM  paris_london_flights
  ORDER BY 1, 2
)
SELECT  year,
        month,
	 passengers,
	 LAG(passengers) OVER (ORDER BY year, month) passengers_previous_month,
	 passengers - LAG(passengers) OVER (ORDER BY year, month) AS passengers_delta
FROM year_month_data;

Nella query di cui sopra, utilizziamo una clausola WITH per generare una CTE (CTE è l'acronimo di common table expressions ed è un tipo di query per generare una tabella virtuale che può essere utilizzata nel resto della query). I dati vengono inseriti in una tabella virtuale chiamata year_month_datache ha 3 colonne: year, month, e passengers con il totale dei passeggeri trasportati nel mese.

Quindi, la seconda query (che prende in input la CTE year_month_data come input) genera il risultato della query. La colonna passeggeri contiene il totale dei passeggeri trasportati associati al record corrente. Con la funzione finestra LAG(passenger), si ottiene il valore della colonna passeggeri del record precedente a quello attuale. ORDER BY year and mont h:

LAG(passengers) OVER (ORDER BY year, month)
passengers_previous_month

Si ottiene il numero di passeggeri dal record precedente, corrispondente al mese precedente. Poi, abbiamo il numero di passeggeri per il mese corrente e per quello precedente. Infine, nell'ultima colonna, si calcola la differenza tra i due valori per ottenere la variazione mensile dei passeggeri.

Esempio 3

Per l'ultimo esempio, esaminiamo i ritardi dei voli. Vogliamo ottenere diverse medie di ritardo per spiegare le ragioni dei ritardi.

Utilizziamo una CTE per calcolare una colonna chiamata month_delay con il ritardo medio per ogni mese e otteniamo il modello di aereo. Poi, nella query principale, otteniamo le diverse medie, come si vede di seguito:

WITH paris_london_delays AS (
  SELECT DISTINCT
	   aircraft_model,
	   EXTRACT(YEAR FROM scheduled_departure) AS year,
	   EXTRACT(MONTH FROM scheduled_departure) AS month,
	   AVG(real_departure - scheduled_departure) AS month_delay
   FROM  paris_london_flights
   GROUP BY 1, 2, 3
)
SELECT  DISTINCT
     aircraft_model,
     year,
     month,
     month_delay AS monthly_avg_delay,
     AVG(month_delay) OVER (PARTITION BY aircraft_model, year) AS year_avg_delay,
     AVG(month_delay) OVER (PARTITION BY year) AS year_avg_delay_all_models,
     AVG(month_delay) OVER (PARTITION BY aircraft_model, year 
                               ORDER BY month
                               ROWS BETWEEN 3 PRECEDING AND CURRENT ROW
                            ) AS rolling_average_last_4_months

FROM paris_london_delays
ORDER BY 1,2,3

Questa query calcola diverse medie. La prima è la media per modello di aeromobile e anno, che è molto chiara. La seconda è la media per anno di tutti i modelli di aereo. Si noti che nella clausola PARTITION BY viene utilizzata solo la colonna anno. La terza e ultima media è la media mobile, in cui si utilizzano gli ultimi 3 mesi e il mese corrente (cioè la riga) per calcolare la media con la seguente espressione:

AVG(month_delay) OVER (PARTITION BY aircraft_model, year
                               ORDER BY month
                               ROWS BETWEEN 3 PRECEDING AND CURRENT ROW
                           ) AS rolling_average_last_4_months

La clausola ROWS BETWEEN 3 PRECEDING AND CURRENT ROW in PARTITION BY limita il numero di righe (cioè di mesi) da includere nella media: i 3 mesi precedenti e il mese corrente. Di seguito è possibile vedere un risultato parziale di questa query:

L'articolo "The RANGE Clause in SQL Window Functions: 5 Practical Examples" spiega come definire un sottoinsieme di righe nella finestra utilizzando RANGE invece di ROWS, con diversi esempi. Un altro articolo interessante è "Common SQL Window Functions: Using Partitions With Ranking Functions" in cui viene trattata in dettaglio la clausola PARTITION BY.

Il potere di Window Functions e della clausola SQL PARTITION BY

Le funzioni finestra sono una risorsa molto potente del linguaggio SQL e la clausola SQL PARTITION BY svolge un ruolo centrale nel loro utilizzo. In questo articolo abbiamo illustrato il funzionamento di questa clausola e mostrato diversi esempi utilizzando diverse sintassi.

Prima di chiudere, vi suggerisco un Advanced SQL corso, dove potrete andare oltre le nozioni di base e diventare maestri di SQL. Se volete saperne di più sulla clausola OVER, c'è un articolo completo sull'argomento: "Come definire un riquadro finestra in SQL Window Functions." Migliorate le vostre competenze e accrescete il vostro patrimonio!