Aggregazione

• Panoramica
• Conteggio delle letture di temperatura
• Soluzione
• Media con NULL
• Soluzione
• Cosa fa questa query?
• Soluzione
• Ordinamento quando si concatena
• Punti chiave

Panoramica

Insegnamento: 10 min
Esercizi: 10 min

Domande

• Come posso calcolare somme, medie e altri valori sommari?

Obiettivi

• Definire l’aggregazione e dare esempi del suo uso.

• Scrivere query che calcolano valori aggregati.

• Tracciare l’esecuzione di una query che esegue l’aggregazione.

• Spiegare come vengono gestiti i dati mancanti durante l’aggregazione.

Ora vogliamo calcolare intervalli e medie per i nostri dati.Sappiamo come selezionare tutte le date dalla tabella Visited:

SELECT dated FROM Visited;

-null-

ma per combinarle, dobbiamo usare una funzione di aggregazione come min o max.Ciascuna di queste funzioni prende un insieme di record come input e produce un singolo record come output:

SELECT min(dated) FROM Visited;

SELECT max(dated) FROM Visited;

min e max sono solo due delle funzioni di aggregazione integrate in SQL.Altre tre sono avg,count e sum:

SELECT avg(reading) FROM Survey WHERE quant = 'sal';

SELECT count(reading) FROM Survey WHERE quant = 'sal';

SELECT sum(reading) FROM Survey WHERE quant = 'sal';

Abbiamo usato count(reading) qui,ma avremmo potuto facilmente contare quanto qualsiasi altro campo della tabella, o anche usare count(*), dato che la funzione non si preoccupa dei valori stessi, ma solo di quanti valori ci sono.

SQL ci permette di fare diverse aggregazioni contemporaneamente.Possiamo, per esempio, trovare la gamma di misure di salinità sensibile:

SELECT min(reading), max(reading) FROM Survey WHERE quant = 'sal' AND reading <= 1.0;

Possiamo anche combinare i risultati aggregati con quelli grezzi, anche se il risultato potrebbe sorprendervi:

SELECT person, count(*) FROM Survey WHERE quant = 'sal' AND reading <= 1.0;

Perché appare il nome di Lake piuttosto che quello di Roerich o di Dyer?La risposta è che quando deve aggregare un campo, ma non gli viene detto come farlo, il gestore del database sceglie un valore effettivo dall’insieme di input.Potrebbe usare il primo elaborato, l’ultimo, o qualcosa di completamente diverso.

Un altro fatto importante è che quando non ci sono valori da aggregare – per esempio, quando non ci sono righe che soddisfano la clausola WHERE – il risultato dell’aggregazione è “non so” piuttosto che zero o qualche altro valore arbitrario:

SELECT person, max(reading), sum(reading) FROM Survey WHERE quant = 'missing';

Un’ultima importante caratteristica delle funzioni di aggregazione è che sono incoerenti con il resto dell’SQL in un modo molto utile.Se aggiungiamo due valori e uno di essi è nullo, il risultato è nullo; per estensione, se usiamo sum per aggiungere tutti i valori di un insieme e uno di questi valori è nullo, anche il risultato dovrebbe essere nullo; è molto più utile, però, che le funzioni di aggregazione ignorino i valori nulli e combinino solo quelli che non sono nulli.Questo comportamento ci permette di scrivere le nostre query come:

SELECT min(dated) FROM Visited;

invece di dover sempre filtrare esplicitamente:

SELECT min(dated) FROM Visited WHERE dated IS NOT NULL;

Aggregare tutti i record contemporaneamente non ha sempre senso.Per esempio, supponiamo di sospettare che ci sia una distorsione sistematica nei nostri dati, e che le letture di radiazione di alcuni scienziati siano più alte di altre.Sappiamo che questo non funziona:

SELECT person, count(reading), round(avg(reading), 2)FROM SurveyWHERE quant = 'rad';

perché il gestore del database seleziona un singolo nome di scienziato arbitrario piuttosto che aggregare separatamente per ogni scienziato.Poiché ci sono solo cinque scienziati, potremmo scrivere cinque query della forma

SELECT person, count(reading), round(avg(reading), 2)FROM SurveyWHERE quant = 'rad'AND person = 'dyer';

ma questo sarebbe noioso, e se mai avessimo un set di dati con cinquanta o cinquecento scienziati, le possibilità di azzeccare tutte quelle interrogazioni sono piccole.

Quello che dobbiamo fare è dire al gestore del database di aggregare le ore per ogni scienziato separatamente usando una clausola GROUP BY:

SELECT person, count(reading), round(avg(reading), 2)FROM SurveyWHERE quant = 'rad'GROUP BY person;

GROUP BY fa esattamente ciò che il suo nome implica: raggruppa tutti i record con lo stesso valore per il campo specificato in modo che l’aggregazione possa elaborare ogni lotto separatamente.Poiché tutti i record di ogni gruppo hanno lo stesso valore per person, non ha più importanza che il gestore del database ne scelga uno arbitrario da visualizzare accanto ai valori aggregati reading.

Così come possiamo ordinare per più criteri contemporaneamente, possiamo anche raggruppare per più criteri.Per ottenere la lettura media per scienziato e quantità misurata, per esempio, basta aggiungere un altro campo alla clausola GROUP BY:

SELECT person, quant, count(reading), round(avg(reading), 2)FROM SurveyGROUP BY person, quant;

Nota che abbiamo aggiunto quant alla lista dei campi visualizzati, poiché altrimenti i risultati non avrebbero molto senso.

Facciamo un passo avanti e rimuoviamo tutte le voci dove non sappiamo chi ha effettuato la misurazione:

SELECT person, quant, count(reading), round(avg(reading), 2)FROM SurveyWHERE person IS NOT NULLGROUP BY person, quantORDER BY person, quant;

Guardando più da vicino, questa query:

1. selezionava i record dalla tabella Surveydove il campo person non era nullo;

2. raggruppava quei record in sottoinsiemi in modo che i valori person e quant in ogni sottoinsieme fossero uguali;

3. ha ordinato quei sottoinsiemi prima per person, e poi all’interno di ogni sottogruppo per quant; e

4. ha contato il numero di record in ogni sottoinsieme, ha calcolato la media reading in ognuno, e ha scelto un valore person e quant da ognuno (non importa quali, poiché sono tutti uguali).

Conteggio delle letture di temperatura

Quante letture di temperatura ha registrato Frank Pabodie e qual era il loro valore medio?

Soluzione

SELECT count(reading), avg(reading) FROM Survey WHERE quant = 'temp' AND person = 'pb';

conteggio(lettura)	avg(lettura)
2	-20.0

Media con NULL

La media di un insieme di valori è la somma dei valori divisa per il numero di valori.Questo significa che la funzione avg restituisce 2,0 o 3,0 quando sono dati i valori 1,0, null e 5,0?

Soluzione

La risposta è 3,0.NULL non è un valore; è l’assenza di un valore.Come tale non è incluso nel calcolo.

Puoi confermarlo, eseguendo questo codice:

SELECT AVG(a) FROM ( SELECT 1 AS a UNION ALL SELECT NULL UNION ALL SELECT 5);

Cosa fa questa query?

Vogliamo calcolare la differenza tra ogni singola lettura di radiazione e la media di tutte le letture di radiazione.Scriviamo la query:

SELECT reading - avg(reading) FROM Survey WHERE quant = 'rad';

Cosa produce in realtà, e puoi pensare al perché?

Soluzione

La query produce solo una riga di risultati quando ciò che vogliamo veramente è un risultato per ciascuna delle letture.La funzione avg() produce solo un singolo valore, e poiché viene eseguita per prima, la tabella è ridotta a una sola riga.Il valore reading è semplicemente un valore arbitrario.

Per ottenere ciò che vogliamo, dovremmo eseguire due query:

SELECT avg(reading) FROM Survey WHERE quant='rad';

Questo produce il valore medio (6.5625), che possiamo poi inserire in una seconda query:

SELECT reading - 6.5625 FROM Survey WHERE quant = 'rad';

Questo produce ciò che vogliamo, ma possiamo combinarlo in una singola query usando le subquery.

SELECT reading - (SELECT avg(reading) FROM Survey WHERE quant='rad') FROM Survey WHERE quant = 'rad';

In questo modo non dobbiamo eseguire due query.

In sintesi quello che abbiamo fatto è sostituire avg(reading) con (SELECT avg(reading) FROM Survey WHERE quant='rad') nella query originale.

Ordinamento quando si concatena

La funzione group_concat(field, separator)concatena tutti i valori in un campo usando il carattere separatore specificato (o ‘,’ se il separatore non è specificato).Usatela per produrre una lista di una riga di nomi di scienziati, come:

William Dyer, Frank Pabodie, Anderson Lake, Valentina Roerich, Frank Danforth

Si può trovare un modo per ordinare la lista per cognome?

Punti chiave

• Utilizzare funzioni di aggregazione per combinare più valori.

• Le funzioni di aggregazione ignorano null valori.

• L’aggregazione avviene dopo il filtraggio.

• Utilizzare GROUP BY per combinare i sottoinsiemi separatamente.

• Se nessuna funzione di aggregazione è specificata per un campo, la query può restituire un valore arbitrario per quel campo.