Perché le funzioni di finestra SQL non sono consentite in GROUP BY?

È vero: l'ordine delle operazioni SQL non consente di utilizzare le funzioni di finestra in GROUP BY. Ma è possibile utilizzarle comunque con questo trucco speciale.

Le funzioni di finestra SQL sono una delle caratteristiche più potenti del linguaggio. Tuttavia, la sintassi delle funzioni di finestra non è facile da padroneggiare. Ci sono molti dettagli che possono far inciampare i principianti. Una delle trappole più comuni è il tentativo di utilizzare le funzioni di finestra in GROUP BY.

Errore: Window Functions non sono consentite in GROUP BY

Immaginate di avere una tabella, midterm che memorizza i risultati di un test di metà anno sostenuto dagli studenti. Le colonne della tabella sono name (il nome) dello studente e il numero di points (punti) che lo studente ha ottenuto nel test. Si desidera dividere gli studenti in quattro gruppi di uguali dimensioni in base ai risultati del test: il 25% con il punteggio più alto è in un gruppo, il 25% successivo nel secondo gruppo, il terzo 25% nel terzo gruppo e il 25% con il punteggio più basso nell'ultimo gruppo. In ultimo si vuole vedere la fascia di punteggio e il numero di studenti in ciascun gruppo.

Ecco come risulterebbe la query:

SELECT
 ntile(4) OVER (ORDER BY points),
 min(points),
 max(points),
 count(*)
FROM midterm
GROUP BY ntile(4) OVER (ORDER BY points);

La funzione NTILE() divide gli studenti in gruppi e assegna a ogni studente il numero (da 1 a 4) del suo gruppo. Si desidera quindi raggruppare gli studenti in base al numero del loro gruppo e calcolare il minimo, il massimo e il conteggio per ogni gruppo. Tuttavia, quando si esegue questa query, si ottiene un errore:

ERROR:  window functions are not allowed in GROUP BY
LINE 7: GROUP BY ntile(4) OVER (ORDER BY points);

Perché Window Functions non è ammesso in GROUP BY

Il motivo per cui le funzioni di finestra non sono consentite in GROUP BY è dovuto all'ordine delle operazioni in SQL. Le clausole di una query SQL sono elaborate in un ordine diverso da quello in cui sono scritte nella query. L'ordine completo delle operazioni in SQL è:

• FROM, JOIN
• WHERE
• GROUP BY
• Funzioni aggregate
• HAVING
• Funzioni di finestra
• SELECT
• DISTINCT
• UNION/INTERSECT/EXCEPT
• ORDER BY
• OFFSET
• LIMIT/FETCH/TOP

Una query SQL determina prima di tutto le tabelle su cui eseguire la query, poi applica i filtri WHERE ed esegue l'operazione GROUP BY. In seguito procede a calcolare le funzioni aggregate, i filtri HAVING e infine le funzioni di finestra. Quindi, nel momento in cui GROUP BY viene valutato, le funzioni di finestra non sono ancora state calcolate!

In pratica, è possibile fare riferimento direttamente alle funzioni di finestra SQL solo nelle clausole SELECT e ORDER BY.

L'ordine delle operazioni SQL è una delle cose più importanti da ricordare quando si scrive una query con le funzioni di finestra. Se non si usano spesso le funzioni di finestra, è facile dimenticarsene; per questo motivo abbiamo inserito un promemoria sull'ordine delle operazioni nella nostra scheda di riferimento sulle Window Functions SQL. Assicuratevi di salvarlo tra i preferiti se usate le funzioni di finestra ogni tanto!

SQL non consente di inserire le funzioni di finestra in GROUP BY ma esiste un modo per ovviare a questo impedimento...

Come utilizzare Window Functions in GROUP BY

È possibile correggere la query in modo che restituisca il risultato desiderato? Sì. La soluzione consiste nell'utilizzare una sottoquery per calcolare la funzione di finestra desiderata nella query principale. Ecco il nostro esempio modificato:

SELECT 
  quartile,
  min(points),
  max(points),
  count(*)
FROM 
  (SELECT
     ntile(4) OVER (ORDER BY points) AS quartile,
     points
     FROM midterm) groups
GROUP BY quartile;

Nella sottoquery, utilizziamo la funzione NTILE() per assegnare gli studenti ai gruppi. Nella query principale, calcoliamo le statistiche: il minimo, il massimo e il numero di studenti.

Un'altra opzione è quella di utilizzare un'espressione di tabella comune (CTE), in questo modo:

WITH groups AS (
  SELECT
     ntile(4) OVER (ORDER BY points) AS quartile,
     points
  FROM midterm
) 
SELECT 
  quartile,
  min(points),
  max(points),
  count(*)
FROM groups
GROUP BY quartile;

La query è simile alla versione precedente con la sottoquery. Tuttavia, una CTE ci permette di definire la query ausiliaria prima della query principale; questo rende il codice più leggibile.

Padroneggiare Window Functions con LearnSQL.com

Se volete imparare le funzioni di finestra, vi consigliamo il nostro corso interattivo Window Functions. Imparerete a utilizzare le funzioni di finestra e a evitare gli errori più comuni da principianti. Se non siete sicuri che le funzioni di finestra facciano al caso vostro, leggete perché dovreste impararle o questo articolo sul nostro corso Window Functions.

Se conoscete già le funzioni di finestra, scaricate la nostra scheda di riferimento sulle Window Functions SQL.