Come raggruppare per più colonne in SQL

Quando si analizzano set di dati di grandi dimensioni, spesso si creano raggruppamenti e si applicano funzioni aggregate per trovare totali o medie. In questi casi, l'uso della clausola GROUP BY con più colonne dispiega tutto il suo potenziale.

GROUP BY è una clausola del comando SELECT. Consente di calcolare varie statistiche per un gruppo di righe. Ad esempio, è possibile utilizzare GROUP BY con una tabella di dipendenti per sapere quanti dipendenti appartengono a ciascun sesso. Oppure si può raggruppare in base a più colonne per determinare l'età media dei veicoli per ogni marca e modello in una tabella veicoli_flotta. In questo articolo esamineremo in dettaglio il funzionamento del raggruppamento per colonne multiple.

Questo articolo presuppone che sappiate già come utilizzare GROUP BY in una query SQL. Non conoscete GROUP BY? Il modo migliore per imparare questo e altri costrutti SQL di base è il nostro corso interattivo SQL Basics. Esso contiene 129 esercizi pratici. Ogni esercizio contiene una breve spiegazione e un compito da risolvere. Ogni volta che si completano gli esercizi, si acquisisce fiducia nelle proprie competenze in SQL. Questo corso è anche un ottimo modo per ripassare le funzioni di base di SQL se le vostre conoscenze sono un po' arrugginite.

Cominciamo con un ripasso su un semplice caso d'uso di GROUP BY.

GRUPPO PER 1 colonna

Ogni combinazione dei valori delle colonne specificate nella clausola GROUP BY costituisce un gruppo; il comando SELECT con una clausola GROUP BY visualizza una singola riga per ogni gruppo. È bene notare che GROUP BY consente di applicare funzioni aggregate a colonne non incluse nel sottoinsieme in sospeso.

Vediamo un esempio. Ho creato una tabella chiamata WorldWideFriends che contiene i dati dei miei amici in diverse parti del mondo:

Vorrei utilizzare le informazioni contenute in questa tabella per fare delle ricerche, ad esempio per ottenere un elenco dei paesi in cui vivono i miei amici, compreso il numero di amici che vivono in ciascun paese.

Se volessi sapere quanti amici ho in ciascun Paese, utilizzerei GROUP BY insieme alla funzione aggregata COUNT():

SELECT 
  Country, 
  COUNT(*) AS HowMany
FROM WorldWideFriends
GROUP BY Country;

Questa query mi dà un insieme di risultati che condensano le righe con lo stesso paese in una sola riga, mentre COUNT(*) mi dice quante righe ripetute ci sono per ogni paese:

La query di cui sopra mi fornisce le informazioni di cui avrei bisogno se, ad esempio, dovessi scegliere in quale Paese viaggiare per incontrare il maggior numero possibile di amici. Se volete saperne di più sull'uso di base di GROUP BY, vi consiglio di leggere i nostri articoli su Cos'è il GROUP BY in SQL e Come usare il GROUP BY.

Tuttavia, anche se viaggio in un paese in cui vivono molti dei miei amici, questi ultimi potrebbero trovarsi in stati diversi. Potrei non avere il tempo di spostarmi da uno stato all'altro per visitarli tutti. Quindi, devo affinare la ricerca per trovare la posizione geografica in cui c'è una maggiore concentrazione di amici.

GRUPPO PER 2 colonne

Ora ho bisogno di sapere come sono distribuiti i miei amici in base allo stato e al paese. Posso scoprirlo aggiungendo la colonna State alla precedente GROUP BY Paese (separandole con le virgole) e nella clausola SELECT. La query si presenta così:

SELECT 
  Country, 
  State, 
  COUNT(*) AS HowMany
FROM WorldWideFriends
GROUP BY Country, State;

Osservando i risultati di questa query, si può notare che alcuni dei paesi che prima apparivano in una sola riga ora appaiono in più righe. Il motivo è che quando si aggiunge il campo State, la query deve assemblare i gruppi con le righe che hanno lo stesso valore sia in Country che in State.

Nella query precedente, la riga corrispondente a "Colombia" aveva un valore 3 nel campo HowMany. In questo caso, "Colombia" appare in due righe con valori diversi per lo Stato: uno per "Antioquia" e l'altro per "Cundinamarca". Nel campo HowMany, la riga corrispondente ad "Antioquia" indica 1, mentre quella corrispondente a "Cundinamarca" indica 2. Questo s

Ciò significa che, nell'elenco disaggregato, ci sono due righe con Country = "Colombia" e State = "Cundinamarca", e solo una con Country = "Colombia" e State = "Antioquia".

La somma dei valori di HowMany di queste due righe corrisponde logicamente al precedente valore di HowMany per la riga corrispondente a "Colombia". Lo stesso vale per tutti gli altri Paesi che sono divisi in più righe con stati diversi.

GRUPPO PER COLONNE MULTIPLE

Infine, se l'intenzione è quella di rendere il mio viaggio il più breve possibile e di visitare comunque il maggior numero di amici, è sufficiente aggiungere la colonna City alla mia query - sia nella SELECT che nella GROUP BY - per vedere quali città hanno il maggior numero di amici

SELECT 
  Country, 
  State, 
  City, 
  COUNT(*) AS HowMany
FROM WorldWideFriends
GROUP BY Country, State, City;

Quando si aggiungono colonne a GROUP BY, il numero di righe nel risultato aumenta. Questo perché aumenta il numero di combinazioni di valori possibili. Quando aggiungo la colonna City all'SQL GROUP BY, la dimensione del risultato cresce notevolmente:

In questo caso, penso che sarebbe meglio vedere solo le città in cui ci sono più di un amico. Quindi, per riepilogare i risultati, utilizzerò la clausola HAVING. Questa clausola mi consente di impostare una condizione sui risultati delle funzioni aggregate quando si usa GROUP BY. In questo caso, la condizione da applicare sarà che il numero di amici sia maggiore di 1 (COUNT(*) > 1). Dopo aver incorporato la clausola HAVING, la query si presenta come segue:

SELECT Country, State, City, COUNT(*) AS HowMany
FROM WorldWideFriends
GROUP BY Country, State, City
HAVING COUNT(*) > 1;

In questo modo, il risultato della query si riduce a una singola riga che mi mostra l'unica città in cui è presente più di un amico:

Altri modi di usare GROUP BY con più colonne

È frequente utilizzare GROUP BY con più colonne quando due o più colonne di un risultato di query formano una gerarchia di classificazioni a più livelli. Tali gerarchie si trovano in molti settori, come ad esempio:

• Dati di vendita dettagliati con la data di vendita suddivisa in anno, trimestre e mese.
• Il catalogo dei prodotti di un produttore organizzato per famiglia, marca, linea e modello.
• Il libro paga dei dipendenti di un'azienda organizzato per direzione, settore, reparto.

In tutti questi casi, è possibile utilizzare diversi sottoinsiemi di colonne nel sito GROUP BY per passare dal generale al particolare.

Utilizzo di GROUP BY per più colonne: Raggruppare una gerarchia

Vediamo un esempio di risultati di dati sulle vendite. Supponiamo di avere una vista chiamata ViewSales che restituisce le seguenti informazioni:

È facile notare che i primi campi di questa tabella formano una gerarchia, con l'anno come livello più alto e la data come livello più basso. Utilizzando GROUP BY e la funzione SUM(), si possono ottenere gli importi totali delle vendite per Year, per Quarter, per Month o per Date. Se si desidera ottenere le unità totali vendute e il prezzo medio unitario per Year e Quarter, è necessario specificare queste due colonne in SELECT e in GROUP BY:

SELECT 
  Year, 
  Quarter, 
  SUM(Quantity) AS TotalQty, 
  AVG(Unit_Price) as AvgUnit_Prc
FROM ViewSales
GROUP BY Year, Quarter;

Il risultato sarà:

Si noti che, sebbene vi sia un ordine gerarchico, i dati nelle diverse colonne di raggruppamento sono indipendenti l'uno dall'altro. Ciò significa che se si raggruppa solo per Quarter invece che per Year più Quarter, i calcoli aggregati combineranno le informazioni dello stesso trimestre per tutti gli anni (cioè tutti i Q2 avranno una riga):

SELECT 
  Quarter, 
  SUM(Quantity) AS TotalQty,
  AVG(Unit_Price) as AvgUnit_Prc
FROM ViewSales
GROUP BY Quarter;

Non si tratta di un errore, ma solo di capire che i risultati esprimono intuizioni diverse. La seconda query consente di confrontare le prestazioni delle vendite tra i diversi trimestri, indipendentemente dall'anno (ad esempio, per individuare i fattori stagionali che influenzano le vendite nello stesso periodo dell'anno), mentre la prima confronta le vendite per ogni anno e trimestre.

Utilizzo di GROUP BY per più colonne: Raggruppamento non gerarchico

Nell'esempio precedente, abbiamo visto che il raggruppamento per più colonne ci permette di passare dal generale al particolare quando abbiamo set di dati con colonne che formano una gerarchia di dati. Ma nelle situazioni in cui un insieme di risultati è composto da colonne che non formano una gerarchia, l'uso di GROUP BY con più colonne ci permette di scoprire verità nascoste in grandi insiemi di dati; combina attributi che a prima vista non sono correlati tra loro.

Per esempio, immaginiamo di avere una tabella chiamata Downloads che contiene informazioni sulle persone che hanno scaricato film da un servizio di streaming negli ultimi due anni. La tabella contiene una riga per ogni download e ogni riga include le seguenti informazioni su ogni persona che ha scaricato un film:

• Age
• Gender
• Nationality

Ogni riga contiene anche gli attributi relativi a ciascun film scaricato:

• Genre
• Year
• Country

Utilizzando GROUP BY con diverse di queste colonne e la funzione COUNT(*), possiamo rilevare le correlazioni tra le colonne. Per esempio, per scoprire le preferenze dei generi cinematografici in base all'età, si può digitare:

SELECT 
  Age, 
  Genre, 
  COUNT(*) AS Downloads
FROM Downloads
GROUP BY Age, Genre

Come risultato otterremo qualcosa di simile:

Potremmo anche utilizzare le colonne di GROUP BY 3, per scoprire (ad esempio) le preferenze di genere per sesso e nazionalità:

SELECT 
  Gender, 
  Nationality, 
  Genre, 
  COUNT(*) AS Downloads
FROM Downloads
GROUP BY Gender, Nationality, Genre

E otterremmo qualcosa di simile:

GROUP BY con più colonne restituisce informazioni sfaccettate

GROUP BY è uno strumento potente per estrarre informazioni da grandi insiemi di dati difficili da manipolare in altro modo. Utilizzando le colonne multiple di GROUP BY, è possibile sfruttare tutto il suo potenziale per esporre le verità di un set di dati, consentendo di vederne le diverse sfaccettature. Per farlo con successo, è fondamentale capire - e saper spiegare - cosa rappresenta un insieme di risultati SQL raggruppati per colonne multiple.

Se avete intenzione di fare un lavoro serio di analisi dei dati, allora dovreste seguire il nostro corso interattivo SQL Basics per conoscere tutti gli strumenti che SQL può offrire. Inoltre, seguite questi link se avete bisogno di ulteriori spiegazioni su GROUP BY o volete vedere altri esempi di GROUP BY in SQL.