Come combinare due funzioni aggregate in SQL

Avete problemi a usare due funzioni aggregate in una query? Questo articolo vi mostrerà come farlo nel modo giusto, anzi, nei due modi giusti.

Nell'analisi dei dati e nella creazione di report, spesso è necessario contare il numero di record o sommarli e quindi calcolare la media di questo conteggio o di questa somma.

Tradotto in logica SQL, si tratta dell'aggregazione di dati aggregati, o aggregazione a più livelli. Per l'aggregazione esistono le funzioni SQL aggregate. E per l'aggregazione a più livelli, si utilizzano (almeno) due funzioni di aggregazione contemporaneamente.

Se siete interessati a un reporting di qualità, avrete bisogno di molto di più delle funzioni aggregate di SQL. Tuttavia, esse sono certamente la base di un buon reporting. Il modo migliore per costruire su queste basi è seguire il nostro corso interattivo. Creating Basic SQL Reports interattivo. Il corso offre un'intera sezione dedicata esclusivamente al calcolo di aggregazioni multilivello in SQL e alla sintesi dei dati mediante le funzioni aggregate. Inoltre, tratta la classificazione dei dati utilizzando CASE WHEN e GROUP BY, il calcolo di più metriche in un report e il confronto tra gruppi all'interno dello stesso report. Con 97 esercizi interattivi, questo corso vi aiuterà a padroneggiare il reporting in SQL!

Per rendere le cose ancora più semplici, vi mostrerò qui come combinare due funzioni aggregate in SQL. Se la vostra conoscenza delle funzioni aggregate è arrugginita, questa guida alle funzioni aggregate di SQL può aiutarvi a seguire questo articolo. Potete anche esercitarvi con questi esempi di funzioni aggregate.

Dati di esempio

Abbiamo una tabella chiamata new_users. Raccoglie dati sui nuovi utenti di un'applicazione nel mercato sudamericano. Le colonne sono:

• id - L'ID di ogni singolo record.
• date - La data di adesione degli utenti.
• number_of_new_users - Il numero di nuovi utenti per data.
• city - La città degli utenti.
• country - Il paese di residenza degli utenti.

Di seguito sono riportate due date in totale, e ogni data ha un numero diverso di utenti provenienti da due paesi e due città in ciascun paese.

Come si possono utilizzare le funzioni AVG() e SUM() per calcolare il numero medio giornaliero di nuovi utenti per paese?

La soluzione ingenua

La logica alla base dell'approccio ingenuo è, in realtà, piuttosto valida. È un peccato che questa soluzione ingenua non sia affatto una soluzione. Presto capirete perché.

Se si pensa solo all'approccio matematico, è semplice: bisogna innanzitutto sommare il numero di nuovi utenti per paese ogni giorno, quindi calcolare la media di tale somma.

Trasferito nelle funzioni SQL, sembra logico scrivere qualcosa di simile:

SELECT country,
 	 AVG(SUM(number_of_new_users)) AS average_new_daily_users
FROM new_users
GROUP BY country;

Perché no, giusto? C'è un motivo molto semplice per non farlo: SQL non consente l'annidamento delle funzioni aggregate. In altre parole, non è possibile utilizzare una funzione aggregata all'interno di una funzione aggregata. È possibile, ma la query restituirà un errore che dice esattamente quello che ho appena detto:

Come si può risolvere questa query per combinare due funzioni aggregate in SQL?

Ci sono due opzioni: usare una sottoquery o usare le Espressioni di tabella comuni (CTE).

Soluzione reale 1: subquery

La prima opzione consiste nel combinare due funzioni aggregate utilizzando una subquery. La subquery è una query all'interno della query principale. Quando si creano i report, di solito si trovano nelle clausole SELECT, FROM, o WHERE.

In questo esempio, la subquery sarà inserita nella clausola FROM.

SELECT country,
	 AVG(ds.sum_new_users) AS average_daily_new_users
FROM (SELECT date,
	  	 country,
	 	 SUM(number_of_new_users) AS sum_new_users
	  FROM new_users
	  GROUP BY date, country) AS ds
GROUP BY country;

Il principio della combinazione di due funzioni aggregate consiste nell'utilizzare la sottoquery per calcolare la statistica "interna". Poi il risultato viene utilizzato nelle funzioni aggregate della query esterna.

Il codice precedente seleziona la data e il paese e calcola la somma della colonna number_of_new_users. Questo restituisce il numero totale giornaliero di nuovi utenti. Ho dato alla sottoquery l'alias ds, che è l'abbreviazione di "somma giornaliera".

Una volta ottenuta la somma giornaliera, ho fatto riferimento a questa nella query esterna calcolando la media della colonna ds.sum_new_users, cioè la media dei nuovi utenti giornalieri. Voglio che questa media sia suddivisa per paese; ecco perché ho raggruppato i dati per la colonna paese.

Soluzione reale 2: CTE

L'altra opzione per combinare le funzioni aggregate in SQL è l'utilizzo di una CTE invece di una subquery. Una CTE è una versione più ordinata e "vicina alla logica matematica" di una subquery. Si tratta di un'espressione che consente di creare un risultato temporaneo, a cui fare riferimento in un'altra istruzione SELECT. Il risultato di una CTE può essere utilizzato come quello di qualsiasi altra tabella. La differenza è che il risultato della CTE esiste solo quando la CTE viene eseguita insieme alla query che la utilizza. Una spiegazione più dettagliata delle CTE si trova nell'articolo Cos'è una CTE?

La query seguente darà lo stesso risultato della soluzione con subquery:

WITH ds AS (
  SELECT date, 
   country,
         SUM(number_of_new_users) AS sum_new_users
  FROM new_users
  GROUP BY date, country)

SELECT country,
       AVG(ds.sum_new_users) AS average_daily_new_users
FROM ds
GROUP BY country;

Ogni CTE viene introdotta con la parola chiave WITH. Lo stesso vale per la mia CTE denominata ds. Dopo la parola chiave AS viene la definizione della CTE. In questo caso, si tratta della stessa istruzione SELECT della subquery dell'esempio precedente. Qui entra in gioco sum(): anche in questo caso, restituirà la somma dei nuovi utenti per paese e data.

La seconda istruzione SELECT fa riferimento a CTE e calcola la media della somma restituita da CTE.

A differenza dell'annidamento della subquery, l'uso della CTE consente di utilizzare le funzioni aggregate nell'ordine logico: prima SUM(), poi AVG(). La funzione "interna" viene utilizzata nella CTE, mentre la seconda istruzione SELECT è per la funzione aggregata "esterna".

Il risultato sarà lo stesso della subquery; non c'è bisogno di mostrarlo di nuovo. Tuttavia, è sempre bene approfondire le differenze tra una subquery e una CTE e fare pratica con le CTE su esempi reali.

Diventare un maestro dell'aggregazione e del reporting dei dati

Il problema dell'utilizzo di due funzioni di aggregazione in SQL è onnipresente. Più il reporting diventa complesso, più si ricorre alle aggregazioni a più livelli. Ora sapete che le subquery e le CTE sono le due soluzioni per questo problema.

Tutte le altre sfumature del reporting possono essere trovate ed esercitate nel nostro corso. Creating Basic SQL Reports corso. I CTE possono rendere il vostro reporting più semplice e sofisticato, quindi seguire il corso è un'idea saggia. Recursive Queries corso è un'idea saggia.