Lezione sul linguaggio SQL: per la classe VA liceo tecnico del 11 gennaio 2009

Funzioni aggregate

Funzioni aggregate Le funzioni aggregate operano su di un gruppo di valori e

producono dei risultati riepilogativi come una somma ad esempio

Il gruppo di valori può essere costituito da: Tutte le righe di una tabella Le righe selezionate con la clausola WHERE Le righe create con la clausola GROUP BY

Differenza tra query con e senza aggregati

Le query non aggregate elaborano le righe una ad una. Ciascuna riga è elaborata indipendentemente dalle altre e il risultato della query è una selezione opportuna di queste

righe e di alcuni loro attributi

Le query aggregate invece elaborano le righe a gruppi. Costruiscono nuove righe a partire da questi gruppi di righe,

calcolando anche nuovi attributi non presenti nelle righe di input

Le principali funzioni aggregate Avg(x) restituisce il valor medio di tutti i valori, non nulli,

dell’attributo x di un gruppo di righe di input Count(x) conta il numero dei valori non nulli di x di un gruppo

di righe di input Count(*) restituisce il numero di righe del gruppo Max(x) restituisce il valore massimo di tutti i valori

dell’attributo x del gruppo Min(x) restituisce il valore minimo, non nullo,di tutti i valori

dell’attributo x del gruppo Sum(x) restituisce la somma numerica di tutti i valori

dell’attributo x del gruppo Group_concat(x) il risultato è una stringa che è la

concatenazione di tutti i valori non nulli di x

Come si usa una funzione aggregata nella SELECT Esaminiamo in dettaglio il

formato della istruzione SELECT

La funzione aggregata va inserita all’interno di result-column

Cosa c’è dentro result-column Vediamo che nel primo

percorso c’è un * e non c’è la funzione

Nel secondo percorso c’è il nome di una tabella e non c’è la funzione

Pertanto il richiamo della funzione deve trovarsi nel terzo percorso: “expr”

Cosa c’è dentro “expr” nel formato di expr il

richiamo della funzione è un function-name nel sesto percorso possibile

Per eseguire una funzione aggregata Per eseguire una funzione aggregata dovremo pertanto

fare una query al seguente modo: SELECT function-name FROM … Ossia il nome della funzione si mette dopo il comando

SELECT

Come si usa la funzione aggregata Calcoliamo ad esempio il numero

totale dei voti presenti nella tabella “voto” del database “registroVoti.db”

Come dovrà essere fatta la query ?

1) Scegliamo la funzione aggregata count(x),

2) scegliamo la x, l’attributo voto della tabella voto

SELECT count(voto) FROM voto

Il risultato di una funzione aggregata e l’attributo derivato dello schema concettuale

Il risultato di una funzione aggregata ci permette di ottenere un attributo derivato dello schema concettuale.

L’attributo derivato è quello che nello schema concettuale è disegnato con un ovale con contorno tratteggiato

L’attributo derivato è quel valore che non viene memorizzato nella tabella corrispondente all’entità, perché può e deve essere calcolabile.

La funzione aggregata permette quindi di calcolare l’attributo derivato

Per fare esempi significativi di uso delle funzioni aggregate popoliamo la tabella voti

Quanti voti ha preso ciascun studente Supponiamo di voler conoscere quanti voti ha preso ciascun studente del database Scegliamo la funzione count Scegliamo l’attributo x che è la colonna voto Scegliamo il gruppo di righe con la clausola GROUP BY Scegliamo il criterio di raggruppamento: per studente Pertanto la query è fatta così: SELECT idStudente,count(voto) from voto GROUP BY idStudente;

La clausola GROUP BY La clausola GROUP BY si utilizza per suddividere una tabella in gruppi

logici (categorie) E calcolare delle statistiche aggregate per ciascun gruppo La clausola GROUP BY si scrive dopo la WHERE e prima di ORDER BY

Voto medio per ciascun studente Per determinare il voto medio di ciascun studente 1) scegliamo la funzione avg(x) 2) scegliamo l’attributo x che è voto 3) raggruppiamo le righe con ORDER BY 4) scegliamo l’attributo di raggruppamento idStudente Ne risulta la seguente query: SELECT idStudente,avg(voto) as ‘media dei voti’ FROM voto GROUP BY

idStudente;

Media dei voti per studente e per materia Se si vuole la media dei voti per ciascun studente per ciascuna

materia ? Per risolvere questo problema è sufficiente raggruppare le

righe per studente e, nell’ambito di ogni gruppo studente, per materia

Pertanto la soluzione è immediata: GROUP BY idStudente,idMateria

Creazione di alias di colonne con AS Si può usare la clausola AS per creare un alias di colonna Un alias di colonna è un nome alternativo che specifica

come controllare le intestazioni mostrate nel risultato di una query.

È importante usare l’alias quando con le funzioni aggregate si calcola un attributo derivato, che non esiste nella tabella e che necessita di un nome: Avg(voto) AS ‘media dei voti’

Se si vuole conoscere quale è il voto più grande ?

Usiamo la funzione max(voto) SELECT max(voto) from voto

Se si vuole conoscere il voto più grande per ogni studente ?

In questo caso ci vuole la clausola GROUP BY e raggruppare le righe per idStudente

SELECT idStudente, max(voto) as ‘voto massimo’ FROM voto GROUP BY idStudente;

Se si vuole conoscere il voto più basso per ogni studente ? Se si vuole conoscere il voto più basso per ogni studente,

invece di usare la funzione max, usiamo la funzione min(voto)

Come prima raggruppiamo le righe per studente con la clausola GROUP BY idStudente

SELECT idStudente, min(voto) FROM voto GROUP BY idStudente;

Se si vuole conoscere il voto più basso di ogni studente per ogni materia ? In questo caso quello che cambia è il raggruppamento delle righe. Oltre che raggrupparle per studente, dobbiamo raggrupparle per materia. SELECT idStudente, idMateria, min(voto) FROM voto GROUP BY

idStudente,idMateria;

Modificare le precedenti query Modificare tutte le precedenti query per mostrare il

nome dello studente e il nome della materia, invece che i rispettivi codici.

Che cosa bisogna fare ? eseguire tutte le precedenti query, poi modificarle per

raggiungere l’obiettivo di cui sopra Pubblicate nei vostri blog, le query con i risultati.

Fine della lezione