BASI DI DATI

Linguaggio SQL
Parte 2 – Funzioni di Aggregazione
[Link]@[Link]
 Valori aggregati
TabAbitanti
• Come posso calcolare il Citta Regione Abitanti
totale degli abitanti delle Roma Lazio 2'546'804
città elencate nella tabella?
Milano Lombardia 1'256'211
• Come posso contare il
Napoli Campania 1'004'500
numero di città che hanno
più di 1'000'000 di abitanti? Salerno Campania 138'188
• SQL mette a disposizione le Bergamo Lombardia 113'143
“funzioni di aggregazione” Latina Lazio 107'898
Varese Lombardia 80'511

2
 SQL Aggregate Functions
F.A.
• Funzioni di aggregazione
• Input: un insieme di tuple
• Output: un’unica tupla/valore
• Esempi
• Nome funzione Output
• SUM(attributo) La somma dei valori dell'attributo
• COUNT(*) Il numero di tuple (righe)
• AVG(attributo) La media dei valori dell'attributo
• MAX(attributo) Il massimo tra i valori dell'attributo
• MIN(attributo) Il minimo tra i valori dell'attributo
• ...
3
 Esempi
• SELECT SUM(Abitanti) FROM TabAbitanti
TabAbitanti; Citta Regione Abitanti
• Risultato: 5'247'255
Roma Lazio 2'546'804
• SELECT COUNT(*) FROM
TabAbitanti; Milano Lombardia 1'256'211
• Risultato: 7 Napoli Campania 1'004'500
• SELECT AVG(Abitanti) FROM Salerno Campania 138'188
TabAbitanti; Bergamo Lombardia 113'143
• Risultato: 749'607.8571
Latina Lazio 107'898
• SELECT MAX(Abitanti) FROM
TabAbitanti; Varese Lombardia 80'511
• Risultato: 2'546'804
4
 Funzioni di aggregazione e clausola WHERE
TabAbitanti
• Posso impiegare funzioni
di aggregazione e clausola WHERE Citta Regione Abitanti
• Le tuple che soddisfano la Roma Lazio 2'546'804
clausola WHERE, entrano a far Milano Lombardia 1'256'211
parte dell'insieme di tuple sul
quale è calcolata la funzione Napoli Campania 1'004'500
aggregata Salerno Campania 138'188
• Esempi Bergamo Lombardia 113'143
• SELECT COUNT(*) FROM TabAbitanti
WHERE Abitanti>1’000’000; Latina Lazio 107'898
• Risultato: 3 Varese Lombardia 80'511
• SELECT SUM(Abitanti)
FROM TabAbitanti WHERE Abitanti > 1’000’000;
• Risultato: 4'807'515
5
 Domanda
Nome Cogn. Reddito
Mario Rossi 45.000
Maria Rossi 35.000
• Data la tabella persone à Giovanni Verdi 40.000
• … e data la query Anna Verdi 45.000

• SELECT COUNT(*), SUM(Reddito)

FROM persone;

COUNT(*) SUM(Reddito) COUNT(*) 4

4 165.000 SUM(Reddito) 165.000

• Qual è il risultato tra le tabelle qua sopra?

 DA uno a più (sotto)gruppi
• Le funzioni di aggregazione permettono di calcolare
valori a partire da un gruppo/insieme (di dati)
• Fino ad ora abbiamo lavorato sull’insieme completo
dei dati di una tabella
• E' possibile “lavorare” contemporaneamente su
sottogruppi di dati della tabella
• Occorre un modo per individuare i sottogruppi à
Analizziamo la clausola GROUP BY
7
 Esempio
Nome Cognome Dipartimento Stipendio
• Data la tabella Mario Rossi Amministrazione 45

Impiegato: Carlo Bianchi Produzione 36

Giuseppe Verdi Amministrazione 40

Franco Neri Distribuzione 45

Carlo Rossi Direzione 80

Lorenzo Lanzi Direzione 73

Paolo Borroni Amministrazione 40

Marco Franco Produzione 46

8
 Clausola Group By
• Se eseguo la query 1) Dipartimento Stipen-
dio
• SELECT Dipartimento, SUM(Stipendio)
FROM Impiegato Amministrazione 45
GROUP BY Dipartimento; Amministrazione 40

• E’ come se si 2) Dipartimento Sum(Stipendio) Amministrazione 40

svolgessero 2 Produzione 36

passaggi Amministrazione 125 Produzione 46

Produzione 82 Distribuzione 45
Distribuzione 45 Direzione 80
Direzione 153 Direzione 73

9
 Altro esempio Vendite
• Data la tabella à citta data incasso_
• Con la seguente query giorn
• SELECT citta FROM vendite; Milano … 1300
• Ottengo questo risultato: Milano … 1200
• Milano
• Milano Torino … 1100
• Torino Milano … 300
• Milano
• Se eseguo la query La GROUP BY, stampa
• SELECT citta FROM vendite una sola tupla per ogni
GROUP BY citta; insieme individuato,
• Risultato: anche se non vengono
• Milano applicate funzioni di
• Torino aggregazione
10
 Attributi – GROUP BY Vendite
• Vediamo ora un problema (importante per citta data incasso_
giorn
capire il funzionamento della GROUP BY). Con
Milano … 1300
• SELECT citta, incasso_giorn
FROM vendite WHERE incasso_giorn>1000; Milano … 1200
• Ottengo: Torino … 1100
• Cosa succede se aggiungo la clausola Milano … 300
GROUP BY citta?
• SELECT citta, incasso_giorn FROM vendite citta incasso_
giorn
WHERE incasso_giorn>1000 GROUP BY citta; Risultato
Milano 1300
• Osservazioni Milano 1200
• Per il gruppo Milano deve apparire una sola riga Torino 1100
• Ma nella colonna incassi, quale dei valori apparirà?
• à Se provate ad eseguire la query otterrete (a seconda del DBMS)
– un errore
– un valore scelto a caso 11
 Attributi – GROUP BY
• Osservate invece questa query Risultato
SELECT citta, SUM(incasso_giorn) citta sum(incasso_giorn)
FROM vendite Milano 2500
WHERE incasso_giorn>1000 Torino 1100
GROUP BY citta;
• Quando si utilizza la clausola GROUP BY, gli attributi da visualizzare
dovrebbero essere:
• Quelli elencati nella clausola GROUP BY
• Quelli a cui si applicano le funzioni di aggregazione
• Motivo: se si creano dei raggruppamenti, si è interessati solamente:
• agli attributi che caratterizzano il raggruppamento (gli attributi di gruppo)
• ai valori aggregati calcolati sui raggruppamenti,
• non interessano i “dettagli interni “ ai vari gruppi
12
 Group BY su più variabili
• Posso raggruppare per più variabili
SELECT citta, data, SUM(incasso_giorn)
FROM vendite
WHERE incasso_giorn>1000
GROUP BY citta, data;
Risultato
citta data sum(incasso_giorn)
Milano 12/01/2006 1300
Milano 13/01/2006 1200
Torino 12/01/2006 1100
… … …
• Posso combinare GROUP BY con ORDER BY 13
 Note su GROUP BY e funzioni di aggregazione
•Attenzione: per usare le funzioni di aggregazione
non è necessario utilizzare la GROUP BY
• (vedi esempi iniziali su città e abitanti)

•Nella stessa select non è possibile/consigliabile

far vedere contemporaneamente valori di gruppo
e valori di singoli elementi
14
 HAVING
• Le funzioni di aggregazione (i risultati dell’uso di GROUP BY,
SUM(), MIN(), AVG(), …) producono dei valori in output
• A volte occorre effettuare ulteriori filtraggi sui valori
aggregati
• la WHERE non può essere usata …
• … seleziona solo i valori a monte delle operazioni di
aggregazione
• Posso esprimere delle ulteriori condizioni sui valori
aggregati con la clausola HAVING
SELECT citta, SUM(incasso_giorn)
FROM vendite
GROUP BY citta
HAVING SUM (incasso_giorn) > 100.000 AND citta <> 'Milano’;
15
 Where o Having ???
• La query
SELECT citta, SUM(incasso_giorn)
FROM vendite
GROUP BY citta
HAVING SUM (incasso_giorn) > 100.000 AND
citta <> 'Milano’;
• è equivalente a
SELECT citta, SUM(incasso_giorn)
FROM vendite
WHERE citta <> 'Milano'
GROUP BY citta
HAVING SUM (incasso_giorn) > 100.000;
• Risposta: si 16
 Where o Having 2
•Le condizioni relative a valori aggregati possono
essere inserite solo nella HAVING
•Le condizioni che insistono su tuple singole
possono essere inserite sia nella WHERE sia nella
HAVING
• (quando possibile) è meglio spostarle nella WHERE
per ragioni di performance
•…
17
 SQL Operatori Matematici
• In una query sql è possibile impiegare gli operatori matematici
• Esempio:
• Data la tabella vendite (ricavo, costo, qt, data, …)
• La Query seguente
SELECT product_id, (ricavo-costo)*qt FROM vendite;
• mostra per ogni tupla l'utile totale conseguito
utile =(ricavo unitario – costo unitario)*quantità
• Operatori
Simbolo Esempio Risultato Nome
+ 7 + 7 = 14 Addizione
- 7-7 =0 Sottrazione
* 7 * 7 = 49 Moltiplicazione
/ 7/7 =1 Divisione
• E' possibile combinare gli operatori aggregati con gli operatori matematici es.,
SELECT sum((ricavo-costo)*qt) FROM vendite; 18
 Riepilogo SELECT
SELECT ListaAttributiOEspressioni
FROM ListaTabelle
[WHERE CondizioniSemplici]
[GROUP BY ListaAttributiDiRaggruppamento]
[HAVING CondizioniAggregate]
[ORDER BY ListaAttributiDiOrdinamento];

19