Qual è la differenza tra un GROUP BY e un PARTITION BY?

Le funzioni finestra sono un'ottima aggiunta all'SQL e possono semplificarvi la vita se sapete come usarle correttamente. Oggi ci occuperemo delle differenze tra un GROUP BY e un PARTITION BY. Cominceremo con le nozioni di base e vi porteremo lentamente a un punto in cui potrete continuare a fare ricerche da soli.

PARTITION BY vs. GROUP BY

Le clausole PARTITION BY e GROUP BY sono utilizzate frequentemente in SQL quando è necessario creare un report complesso. Sebbene la restituzione dei dati sia utile (e persino necessaria) in molti casi, spesso sono necessari calcoli più complessi. È qui che entrano in gioco GROUP BY e PARTITION BY. Sebbene siano molto simili, in quanto entrambi eseguono raggruppamenti, vi sono differenze fondamentali. In questo articolo analizzeremo queste differenze.

GROUP BY

La clausola GROUP BY viene utilizzata nelle query SQL per definire gruppi basati su alcuni criteri. Questi criteri sono quelli che di solito si trovano come categorie nei report. Esempi di criteri di raggruppamento sono:

• raggruppare tutti i dipendenti in base al loro livello di stipendio annuale
• raggruppare tutti i treni in base alla loro prima stazione
• raggruppare le entrate e le uscite per mese
• raggruppare gli studenti in base alla classe a cui sono iscritti.

L'uso della clausola GROUP BY trasforma i dati in un nuovo insieme di risultati in cui i record originali vengono inseriti in gruppi diversi in base ai criteri forniti. Per maggiori dettagli sulla clausola GROUP BY, consultare questo articolo.

Possiamo eseguire alcune azioni o calcoli aggiuntivi su questi gruppi, la maggior parte dei quali sono strettamente correlati alle funzioni aggregate. Le funzioni aggregate servono ad aggregare i dati e quindi a perdere i dettagli originali nel risultato della query. Esistono molte funzioni aggregate, ma quelle più comunemente utilizzate sono COUNT, SUM, AVG, MIN e MAX.

Se volete esercitarvi a usare la clausola GROUP BY, vi consigliamo il nostro corso interattivo Creare report in SQL. Le funzioni aggregate e la clausola GROUP BY sono essenziali per la scrittura di report in SQL.

Consideriamo il seguente esempio. Qui abbiamo la train con le informazioni sui treni, la tabella journey con le informazioni sui viaggi effettuati dai treni e la tabella con le informazioni sui percorsi per i treni. route tabella con le informazioni sugli itinerari dei viaggi. Osservare i dati e il modo in cui le tabelle sono collegate:

Eseguiamo la seguente query che restituisce le informazioni sui treni e sui relativi viaggi utilizzando le tabelle train e la tabella journey e le tabelle.

SELECT
        train.id,
        train.model,
        journey.*
FROM train
INNER JOIN journey ON journey.train_id = train.id
ORDER BY
        train.id ASC;

Ecco il risultato:

Si può notare che il treno con id = 1 ha 5 righe diverse, il treno con id = 2 ha 4 righe diverse, ecc.

Ora eseguiamo una query con le stesse due tabelle utilizzando una GROUP BY.

SELECT
  	train.id,
	train.model,
	COUNT(*) AS routes
FROM train
INNER JOIN journey ON journey.train_id = train.id
GROUP BY
  	train.id,
	train.model
ORDER BY
  	train.id ASC;

Il risultato è il seguente:

Dal risultato della query, si può notare che abbiamo aggregato le informazioni, che ci dicono il numero di percorsi per ogni treno. Nel processo, abbiamo perso i dettagli a livello di riga della tabella journey della tabella.

È possibile confrontare questo set di risultati con quello precedente e verificare che il numero di righe restituite dalla prima query (numero di percorsi) corrisponda alla somma dei numeri della colonna aggregata (routes) del risultato della seconda query.

Sebbene sia possibile utilizzare le funzioni aggregate in una query senza la clausola GROUP BY, questa è necessaria nella maggior parte dei casi. Le funzioni aggregate funzionano in questo modo:

1. Si generano gruppi con l'istruzione GROUP BY specificando una o più colonne che hanno lo stesso valore all'interno di ciascun gruppo.
2. La funzione aggregata calcola il risultato.
3. Le righe originali vengono "collassate". È possibile accedere alle colonne dell'istruzione GROUP BY e ai valori prodotti dalle funzioni aggregate, ma i dettagli originali a livello di riga non sono più presenti.

Il "collasso" delle righe va bene nella maggior parte dei casi. A volte, tuttavia, è necessario combinare i dettagli originali a livello di riga con i valori restituiti dalle funzioni aggregate. Questo può essere fatto con le sottoquery, collegando le righe della tabella originale con l'insieme risultante dalla query utilizzando le funzioni aggregate. In alternativa, si può tentare un approccio diverso, come vedremo in seguito.

PARTITION BY

A seconda delle esigenze, è possibile utilizzare PARTITION BY nelle nostre query per calcolare valori aggregati sui gruppi definiti. PARTITION BY viene combinato con OVER() e le funzioni di windows per calcolare i valori aggregati. È molto simile a GROUP BY e alle funzioni aggregate, ma con un'importante differenza: quando si utilizza PARTITION BY, i dettagli a livello di riga vengono conservati e non vengono compressi. In altre parole, si hanno ancora a disposizione i dettagli originali a livello di riga e i valori aggregati. Tutte le funzioni aggregate possono essere utilizzate come funzioni finestra.

Osserviamo la seguente query. Oltre a train e journeyora incorporiamo anche la tabella delle rotte.

SELECT
  	train.id,
	train.model,
	route.name,
	route.from_city,
	route.to_city,
	COUNT(*) OVER (PARTITION BY train.id ORDER BY train.id) AS routes,
	COUNT(*) OVER () AS routes_total
FROM train
INNER JOIN journey ON journey.train_id = train.id
INNER JOIN route ON journey.route_id = route.id;

Ecco il risultato della query:

Dal set di risultati si notano alcuni punti importanti:

• Non abbiamo utilizzato una GROUP BY ma abbiamo comunque ottenuto valori aggregati (routes e routes_total).
• Abbiamo le stesse colonne (id e model) della GROUP BY della query precedente, ma i dettagli originali a livello di riga sono stati conservati. I valori aggregati sono ripetuti in tutte le righe con gli stessi valori di id e modello. Questo è prevedibile; ad esempio, abbiamo 5 record di viaggio per id = 1, tutti con valori identici per queste colonne.
• Anche i valori delle colonne name, from_city e to_city sono diversi per un determinato valore di id. Se avessimo usato un GROUP BY sulle colonne id e model, questi dettagli a livello di riga sarebbero andati persi.
• COUNT(*) OVER () AS routes_total ha prodotto lo stesso conteggio aggregato, 30, di COUNT e GROUP BY. In questo set di risultati, tuttavia, questo valore è incluso in ogni riga.
• La parte COUNT(*) OVER (PARTITION BY train.id ORDER BY train.id) AS routes è molto interessante. Con la clausola PARTITION BY abbiamo definito il gruppo su cui utilizzare questa funzione finestra. Pertanto, nella colonna routes, abbiamo un conteggio delle righe solo per quel gruppo. Le funzioni finestra vengono applicate dopo che le righe sono state filtrate, mantenendo così i dettagli a livello di riga e definendo i gruppi tramite PARTITION BY.

L'uso di funzioni aggregate standard come funzioni finestra con la parola chiave OVER() consente di combinare i valori aggregati e di mantenere i valori delle righe originali. È possibile ottenere lo stesso risultato utilizzando le funzioni aggregate, ma ciò richiede subquery per ogni gruppo o partizione.

È importante notare che tutte le funzioni aggregate standard possono essere utilizzate come funzioni finestra.

Window Functions

Oltre alle funzioni aggregate, esistono altre importanti funzioni finestra, come:

• ROW_NUMBER(). Restituisce il numero di sequenza della riga nell'insieme dei risultati.
• RANK(). Simile a ROW_NUMBER(), ma può accettare una colonna come argomento. L'ordine di rango viene determinato in base al valore di questa colonna. Se due o più righe hanno lo stesso valore in questa colonna, queste righe ottengono tutte lo stesso rango. Il rango successivo continuerà dal numero equivalente di righe in su; ad esempio, se due righe condividono un rango pari a 10, il rango successivo sarà 12.
• DENSE_RANK(). Molto simile a RANK(), tranne che per l'assenza di "spazi vuoti". Nell'esempio precedente, se due righe condividono un rango di 10, il rango successivo sarà 11. .
• NTILE. Utilizzato per calcolare quartili, decili o qualsiasi altro percentile.
• LAG & LEAD. Utilizzato per estrarre i valori dalla riga precedente (LAG) o da quella successiva (LEAD).

Non esiste una regola generale su quando utilizzare le funzioni delle finestre, ma è possibile sviluppare una certa sensibilità. Consiglio vivamente di seguire il corso Window Functions ; lì troverete tutti i dettagli che desiderate conoscere!

PARTITION BY e GROUP BY: Somiglianze e differenze

Anche se nella maggior parte dei casi utilizziamo un GROUP BY, ci sono molti casi in cui un PARTITION BY sarebbe una scelta migliore. In alcuni casi, si può usare un GROUP BY utilizzando delle sottoquery per simulare un PARTITION BY, ma si rischia di ottenere query molto complesse.

Concludiamo con le somiglianze e le differenze più importanti:

• Somiglianza: Entrambi sono utilizzati per restituire valori aggregati.
• Differenza: L'uso di una clausola GROUP BY comprime le righe originali; per questo motivo, non è possibile accedere ai valori originali in un secondo momento nella query. D'altra parte, l'uso di una clausola PARTITION BY mantiene i valori originali e consente di produrre valori aggregati.
• Differenza: La clausola PARTITION BY è combinata con le funzioni OVER() e windows per aggiungere molte altre funzionalità.