Funzioni SQL Window vs. GROUP BY: Qual è la differenza?

Un'idea sbagliata molto comune tra gli utenti di SQL è che non ci sia molta differenza tra le funzioni SQL window e le funzioni aggregate o la clausola GROUP BY. Tuttavia, le differenze sono molto significative.

Forse l'unico punto in comune tra le funzioni GROUP BY e le funzioni finestra è che entrambe consentono di eseguire una funzione (come AVG, MAX, MIN o COUNT) su un gruppo di record. Direi che il potere speciale delle funzioni finestra è che ci permettono di ottenere risultati che altrimenti sarebbero quasi impossibili da raggiungere.

In questo articolo esamineremo l'uso delle funzioni finestra rispetto a GROUP BY e delle funzioni finestra rispetto alle funzioni aggregate.

Rapido ripasso di GROUP BY

La clausola GROUP BY consente di raggruppare un insieme di record in base ad alcuni criteri e di applicare una funzione (ad esempio AVG o MAX) a ciascun gruppo, ottenendo un risultato per ogni gruppo di record. Vediamo un esempio.

Abbiamo una tabella chiamata employee con un totale di cinque dipendenti e tre dipartimenti:

fig1: la tabella dei dipendenti

Supponiamo di voler ottenere il salario medio per reparto e il salario massimo per ogni reparto. Dovremmo utilizzare la seguente query:

SELECT Department, 
       avg(salary) as average,
       max(salary) as top_salary
FROM employee
GROUP BY department

L'immagine seguente mostra il risultato:

GROUP BY vs Window Functions

Quando si confrontano le funzioni finestra e GROUP BY, è essenziale ricordare che GROUP BY comprime i singoli record in gruppi; dopo aver utilizzato GROUP BY, non è possibile fare riferimento a nessun campo individuale perché è compresso. Più avanti parleremo in modo approfondito di questo argomento. Per ora, ci limitiamo a ricordare che le funzioni della finestra non comprimono i singoli record.

Quindi, se si vuole creare un report con il nome di un dipendente, il suo stipendio e il massimo stipendio del suo reparto, non è possibile farlo con GROUP BY. I singoli record di ciascun dipendente vengono compressi dalla clausola GROUP BY department. Per questo tipo di report è necessario utilizzare le funzioni di finestra, argomento della prossima sezione.

Se volete approfondire le sfumature di SQL GROUP BY e dei report, vi consigliamo il nostro corso interattivo Creating Basic SQL Reports.

Il potere di Window Functions

Le funzioni finestra sono una potente caratteristica di SQL. Permettono di applicare funzioni come AVG, COUNT, MAX e MIN a un gruppo di record, lasciando comunque accessibili i singoli record. Dal momento che i singoli record non vengono compressi, è possibile creare query che mostrano i dati del singolo record insieme al risultato della funzione finestra. Questo è ciò che rende le funzioni finestra così potenti.

Supponiamo di voler ottenere un elenco di nomi di dipendenti, stipendi e lo stipendio più alto nei loro reparti.

SELECT 	employee_name,
department,
salary,
max(salary) OVER (PARTITION BY department) as top_salary
FROM		employee

L'immagine successiva mostra il risultato:

Nella query precedente, abbiamo usato una funzione finestra:

max(salary) OVER (PARTITION BY department) as top_salary

La funzione finestra è MAX() e l'abbiamo applicata all'insieme di record definiti dalla clausola OVER (PARTITION BY department)che sono i record con lo stesso valore nel campo reparto. Infine, abbiamo rinominato la colonna top_salary. Nel risultato della query, abbiamo righe per i singoli dipendenti. Se avessimo usato GROUP BY invece delle funzioni finestra, avremmo avuto righe per ogni reparto.

Le funzioni finestra hanno una sintassi piuttosto prolissa; se volete approfondire, vi suggerisco il corso "Window Functions", che è un tutorial passo-passo che vi guida attraverso le funzioni finestra di SQL con esempi ed esercizi.

Window Functions contro le funzioni aggregate

Se confrontiamo le funzioni finestra e le funzioni aggregate, notiamo una caratteristica molto potente delle funzioni finestra: le funzioni posizionali. Esse consentono di ottenere il valore di una colonna da altri record della stessa finestra. Si tratta di una capacità davvero straordinaria, che consente agli utenti di SQL di creare report complessi in poche righe. Vediamo brevemente due di queste funzioni: LEAD() e LAG().

La funzione LAG() restituisce il valore della colonna del record precedente nella finestra, mentre LEAD() restituisce il valore della colonna del record successivo nella finestra. È molto importante che la finestra sia ordinata in base alla colonna di destra se si desidera utilizzare queste funzioni.

Vediamo un esempio di utilizzo di queste funzioni. Supponiamo di avere una tabella che contiene le azioni di una società con il loro valore di mercato in un determinato momento. La tabella potrebbe avere questo aspetto:

fig2: la tabella delle azioni

Supponiamo di voler creare un report che mostri il valore di ogni azione con il suo valore precedente e la percentuale di variazione rispetto al valore precedente. Possiamo farlo utilizzando la funzione LEAD() per ottenere il valore precedente dell'azione. Si noti che si usa ORDER BY timestamp quando si definisce la partizione (cioè la finestra dei record). Torneremo su questo punto più avanti.

SELECT 	
  share_symbol,
  timestamp,
  value,
  LAG(value) OVER (PARTITION BY share_symbol ORDER BY timestamp ) AS previous_value, 
  TRUNC(((value - (LAG(value) OVER (PARTITION BY share_symbol ORDER BY timestamp )))*100)/value,2) AS percentage_variation
FROM share

Si noti che le colonne previous_value e percentage_variation sono colonne calcolate. Utilizzano valori provenienti da record diversi della stessa tabella.

Quando si utilizzano funzioni posizionali, è molto importante inserire una clausola ORDER BY insieme alla clausola PARTITION (come abbiamo fatto nella query precedente). Se non si usa la giusta ORDER BY, i risultati possono essere sbagliati. Perché? Perché le funzioni posizionali lavorano in base all'ordine dei record nella finestra.

Esaminiamo meglio questo aspetto. La funzione FIRST_VALUE() restituisce il valore di una colonna dal primo record della finestra. LAG() Come sappiamo, la funzione restituisce il valore della colonna del record precedente nella finestra. L'ordine corretto della finestra è fondamentale; immaginate cosa si otterrebbe altrimenti da queste funzioni! Nel nostro esempio, vogliamo il valore di mercato cronologico precedente per un'azione specifica. Abbiamo quindi utilizzato ORDER BY timestamp. Se omettiamo ORDER BY o ordiniamo per un'altra colonna, il risultato sarebbe sbagliato.

In alcuni casi specifici, le funzioni posizionali possono restituire valori errati a causa di una finestra parzialmente popolata. Esistono poi altre funzioni finestra, come RANK(), NTH_VALUE() e LAST_VALUE(). Non c'è spazio per trattare tutto questo in questa sede, ma vi suggerisco di dare un'occhiata a questo articolo che spiega le funzioni finestra e a questi esempi di funzioni finestra per saperne di più.

Chiudere la finestra

In questo articolo abbiamo esplorato le differenze tra le funzioni finestra e GROUP BY. Abbiamo esaminato esempi con diverse funzioni aggregate e a finestra. Abbiamo anche parlato di un'importante limitazione della clausola GROUP BY, ovvero il "collasso dei record". Questa limitazione non è presente nelle funzioni finestra, consentendo agli sviluppatori SQL di combinare i dati a livello di record con i risultati delle funzioni finestra nella stessa query.

Un altro vantaggio delle funzioni finestra è la possibilità di combinare valori di query provenienti da record diversi (della stessa finestra) nella stessa riga del set di risultati.

Se siete interessati a saperne di più sulle funzioni finestra, vi suggerisco il corso Window Functions dove è possibile imparare le funzioni finestra di SQL con esercizi interattivi e spiegazioni dettagliate.