Introduzione all'uso delle funzioni aggregate SQL con le JOIN

In precedenza, abbiamo discusso il metodo uso delle funzioni aggregate SQL con l'istruzione GROUP BY. I lettori abituali del nostro blog ricorderanno anche il nostro recente articolo tutorial sulle JOIN. Se siete un po' arrugginiti su entrambi gli argomenti, vi invito a rivederli prima di continuare questo articolo. Questo perché approfondiremo ulteriormente funzioni aggregate abbinandole alle JOIN. Questo binomio consente di sfruttare appieno le possibilità delle funzioni aggregate di SQL e di eseguire calcoli su più tabelle in un'unica query.

Cosa fanno le funzioni aggregate SQL?

Ecco una rapida panoramica delle funzioni aggregate SQL più comuni:

È inoltre importante ricordare che l'istruzione GROUP BY, quando viene utilizzata con gli aggregati, calcola i valori che sono stati raggruppati per colonna. (Per maggiori informazioni, vedere Guida per principianti alle funzioni aggregate di SQL.) È possibile utilizzare GROUP BY con una qualsiasi delle funzioni di cui sopra. Per esempio, nell'esempio seguente utilizziamo la funzione MIN():

SELECT MIN(column_name)
FROM table_name 
GROUP BY group_column

In questo modo si recupera il valore minimo trovato in column_name per ogni serie di valori in un gruppo basato sulla colonna group_column. La stessa idea vale per le funzioni MAX, SUM, AVG e COUNT.

JOIN genitore-figlio

Ora analizziamo alcune situazioni comuni in cui si utilizzano le JOIN group by con le funzioni aggregate. Se avete letto A Beginner's Guide to SQL Aggregate Functions, il diagramma seguente vi sarà già familiare:

Se avete già utilizzato questo modello (ad esempio con gli esempi dell'articolo precedente), assicuratevi di cancellare tutti i record esistenti dalla vostra tabella. Per farlo, si possono eseguire i seguenti comandi:

TRUNCATE cities;
TRUNCATE users;

Inseriamo alcuni dati nuovi nelle tabelle:

INSERT INTO `cities` VALUES
    (1,'Miami'),
    (2,'Orlando'),
    (3,'Las Vegas'),
    (4,'Coyote Springs');
INSERT INTO `users` VALUES
    (1,1,'John','Doe',22),
    (2,1,'Albert','Thomson',15),
    (3,2,'Robert','Ford',65),
    (4,3,'Samantha','Simpson',9),
    (5,2,'Carlos','Bennet',42),
    (6,2,'Mirtha','Lebrand',81),
    (7,3,'Alex','Gomez',31);

Abbiamo una tabella chiamata users e un'altra tabella chiamata cities. Queste due tabelle hanno qualcosa in comune: un valore numerico dell'id della città. Questo valore è memorizzato nella colonna id della tabella cities e nella colonna city_id della tabella . users della tabella . La colonna city_id contiene un riferimento (anche detto chiave esterna) che collega un record utente a una città. Questi record corrispondenti ci permettono di JOIN entrambe le tabelle.

In altre parole, conosciamo la città di un utente quando prendiamo il record dalla tabella cities che ha un valore id uguale al valore in users.city_id. Nella seguente query, possiamo vedere questo aspetto in azione:

SELECT  cities.*, users.*
FROM cities
JOIN users
  ON cities.id = users.city_id;

Poiché la tabella users si collega a una città tramite la chiave esterna city_id, possiamo dire che un utente appartiene a una città e quindi la città ha molti utenti. Si tratta di una relazione genitore-figlio (città-utenti); la tabella users condivide un collegamento con la tabella cities tabella.

Tenendo presente questa relazione, vediamo come calcolare alcuni interessanti dati riassuntivi che collegano le due tabelle.

Aggregato + GROUP BY + JOIN

Ora iniziamo ad affrontare alcune situazioni pratiche in cui si GROUPvalori da tabelle JOINda tabelle modificate.

MIN + GROUP BY + JOIN

Il calcolo di valori basati su record figli raggruppati da una colonna genitore è piuttosto comune. Costruiamo una query che recuperi il valore più basso di users.age (record figlio) per ogni cityname (record padre):

SELECT cities.cityname, MIN(users.age)
FROM cities
JOIN users
  ON cities.id = users.city_id
GROUP BY cities.cityname

Questo restituirà:

C'è qualcosa di molto importante da sottolineare sul modo in cui funziona il JOIN. Sarà più evidente se consideriamo tutte le città:

SELECT cities.cityname
FROM cities

Come si può vedere, "Coyote Springs" non è stata elencata prima perché non ha utenti. Se si volesse far comparire questa città nei risultati riassunti, si dovrebbe usare un'opzione LEFT JOIN al suo posto:

SELECT cities.cityname, MIN(users.age)
FROM cities
LEFT JOIN users
  ON cities.id = users.city_id
GROUP BY cities.cityname

Questo restituirà:

Se questo ha senso o meno dipende dal caso d'uso, ma è importante tenere a mente questa situazione quando si uniscono le tabelle.

MAX + GROUP BY + JOIN

Possiamo trovare l'età maggiore per ogni città usando la funzione MAX():

SELECT cities.cityname, MAX(users.age)
FROM cities
LEFT JOIN users
  ON cities.id = users.city_id
GROUP BY cities.cityname

La query di cui sopra recupererà:

Si noti che ho usato LEFT JOIN. Voglio un elenco di tutte le città, non solo di quelle con record utente associati.

SOMMA + GRUPPO PER + JOIN

Vediamo ora come totalizzare le età per ogni città. Per farlo, possiamo utilizzare la funzione SUM():

SELECT cities.cityname, SUM(users.age)
FROM cities
LEFT JOIN users
  ON cities.id = users.city_id
GROUP BY cities.cityname

Che restituisce:

COUNT + GROUP BY + JOIN

Supponiamo di voler vedere il numero di utenti in ogni città. Utilizzeremo la funzione COUNT(), in questo modo:

SELECT cities.cityname, COUNT(users.id)
FROM cities
LEFT JOIN users
  ON cities.id = users.city_id
GROUP BY cities.cityname

Che restituisce:

MEDIA + GRUPPO PER + JOIN

Utilizzando il numero di utenti di ogni città (COUNT) e il SUM delle età combinate degli utenti di ogni città, possiamo calcolare l'età media per ogni città. È sufficiente dividere la somma delle età per il numero di utenti di ogni città:

SELECT 
    cities.cityname,
    SUM(users.age) AS sum,
    COUNT(users.id) AS count,
    SUM(users.age) / COUNT(users.id) AS average
FROM cities
LEFT JOIN users
  ON cities.id = users.city_id
GROUP BY cities.cityname

Restituzione:

Si noti come la somma e la media calcolata diano come risultato un valore NULL per Coyote Springs. Questo perché Coyote Springs non ha utenti e quindi la colonna riassuntiva non può calcolare un valore numerico.

AVG + GROUP BY + JOIN

L'esempio precedente ha utilizzato un calcolo inserito per trovare un'età media per ogni città. Avremmo invece potuto utilizzare la funzione AVG(), come mostrato di seguito:

SELECT cities.cityname, AVG(users.age)
FROM cities
LEFT JOIN users
  ON cities.id = users.city_id
GROUP BY cities.cityname

In questo modo si ottengono gli stessi valori dell'esempio precedente:

Filtrare i risultati

A volte è necessario filtrare le righe in base a determinate condizioni. In questo tipo di query, ci sono tre fasi in cui è possibile farlo: WHERE, HAVING, e JOIN.

A seconda della situazione, ciascuna di queste opzioni può avere un risultato diverso. È importante capire quale utilizzare quando si desidera un risultato specifico. Vediamo alcuni esempi per illustrare questo aspetto.

Uso del predicato JOIN

Otteniamo il numero di utenti sotto i 30 anni in ogni città. Utilizzeremo LEFT JOIN per recuperare le città senza record di utenti:

SELECT cityname, COUNT(users.id)
FROM cities
LEFT JOIN users
  ON cities.id = users.city_id
  AND users.age < 30
GROUP BY cities.cityname
ORDER BY cities.cityname;

La condizione di includere solo gli utenti con età inferiore a 30 anni è impostata nel predicato JOIN. Il risultato è il seguente:

Tutte le città sono elencate e solo gli utenti con età compresa nell'intervallo restituiscono un numero non nullo. Le città senza utenti che corrispondono ai nostri criteri restituiscono uno zero.

Cosa sarebbe successo se avessimo inserito la stessa condizione di filtraggio nella clausola WHERE?

Utilizzo delle condizioni WHERE

Se inserissimo le stesse condizioni nella clausola WHERE, il risultato sarebbe questo:

SELECT cityname, COUNT(users.id)
FROM cities
LEFT JOIN users
  ON cities.id = users.city_id 
WHERE users.age < 30
GROUP BY cities.cityname
ORDER BY cities.cityname;

Il risultato sarà:

Non è quello che mi aspettavo; volevo ottenere TUTTE le città e il conteggio dei rispettivi utenti di età inferiore a 30 anni. Anche se una città non avesse avuto utenti, avrebbe dovuto essere elencata con un conteggio pari a zero, come restituito dal predicato JOIN.

Il motivo per cui non vengono restituiti questi record è che WHERE viene applicata dopo il parametro JOIN. Poiché la condizione users.age < 30 rimuove tutti i record di "Coyote Springs" e "Orlando", il calcolo riassuntivo non può includere questi valori. Solo "Las Vegas" e "Miami" soddisfano le condizioni di WHERE, quindi vengono restituiti solo "Las Vegas" e "Miami".

Al contrario, quando la condizione viene applicata nel predicato JOIN, i record degli utenti che non corrispondono all'età vengono rimossi prima che le due tabelle vengano unite. Quindi tutte le città sono abbinate alle colonne degli utenti, come ci si aspetterebbe quando si usa un predicato LEFT JOIN. Ciò significa che tutte le città faranno parte dei risultati; solo i record utente che non soddisfano la condizione users.age < 30 vengono filtrati. In questo caso, il predicato JOIN restituisce il risultato desiderato.

Utilizzo delle condizioni HAVING

Lo abbiamo accennato nel primo articolo, ma lo ripeteremo qui: l'uso della clausola WHERE per filtrare le colonne riassunte non funziona. Guardate l'esempio qui sotto.

SELECT cityname, COUNT(users.id)
FROM cities
LEFT JOIN users
  ON cities.id = users.city_id
WHERE COUNT(users.id) > 2
GROUP BY cities.cityname
ORDER BY cities.cityname;

In questo modo il database emette un reclamo come questo di MySQL:

Error Code: 1111. Invalid use of group function

Utilizzate invece la clausola HAVING:

SELECT cityname, COUNT(users.id)
FROM cities
LEFT JOIN users
  ON cities.id = users.city_id
GROUP BY cities.cityname
HAVING COUNT(users.id) > 2
ORDER BY cities.cityname;

Questo restituisce i record desiderati (solo le città con più di due utenti):

Gestire i NULL

Oltre ai casi limite già presentati, è importante considerare qualcosa che non è così ovvio. Torniamo all'esempio di COUNT():

SELECT cities.cityname, COUNT(users.id)
FROM cities
LEFT JOIN users
  ON cities.id = users.city_id
GROUP BY cities.cityname

Questo restituisce:

Se avessi usato COUNT(*) invece di COUNT(users.id), sarebbe stato generato il conteggio totale delle righe. Questo avrebbe dato un valore non voluto, in questo caso un falso "1" per "Coyote Springs". Questo risultato è dovuto alla natura di LEFT JOIN. Ecco un esempio:

SELECT cities.cityname, COUNT(*)
FROM cities
LEFT JOIN users
  ON cities.id = users.city_id
GROUP BY cities.cityname

Questo restituirebbe:

Quindi COUNT(*) conta un "1" per Coyote Springs perché LEFT JOIN restituisce una riga con valori NULL. Ricordate che in COUNT(*) una riga con NULL viene comunque conteggiata.

Per lo stesso motivo, COUNT(users.id) restituisce il conteggio previsto di "0"; il valore della colonna users.id è nullo per Coyote Springs.

In altre parole, utilizzare sempre Count(column) con questo tipo di query.

Un ultimo consiglio su come lavorare con le funzioni aggregate di SQL

Infine, vorrei aggiungere che per lavorare con le funzioni aggregate SQL, soprattutto quando si usa JOIN, è necessario conoscere bene l'SQL e i dati con cui si lavora. Provate prima le query in un sottoinsieme più piccolo dei vostri dati per confermare che tutti i calcoli funzionino come previsto. Se possibile, verificare alcuni risultati rispetto a un valore di riferimento per convalidare i risultati delle query.

Tenere presente che l'uso delle condizioni nel predicato JOIN (dopo ON) non è la stessa cosa del filtraggio in WHERE (o dell'uso di HAVING). Questi possono creare sottili (o meno sottili) differenze nei dati riassunti, che potrebbero causare errori difficili da individuare. Prestate particolare attenzione alle scelte di filtraggio.

Come sempre, grazie per la lettura e sentitevi liberi di condividere le vostre esperienze nella sezione commenti.