Funzioni aggregate in MySQL

Le funzioni aggregate sono tutto

• Esecuzione di calcoli su più righe
• Di una singola colonna di una tabella
• E restituendo un singolo valore.

Lo standard ISO definisce cinque (5) funzioni aggregate vale a dire;

1) CONTEGGIO
2) SOMMA
3) AVG
4) MINIMO
5) MASSIMO

Perché utilizzare le funzioni aggregate

Da una prospettiva aziendale, i diversi livelli dell'organizzazione hanno requisiti informativi diversi. I manager di livello superiore sono solitamente interessati a conoscere cifre intere e non necessariamente i dettagli individuali.

Le funzioni aggregate ci consentono di produrre facilmente dati riepilogati dal nostro database.

Ad esempio, dal nostro database myflix, la direzione potrebbe richiedere i seguenti report

• Film meno noleggiati.
• Film più noleggiati.
• Numero medio di film noleggiati in un mese.

Produciamo facilmente i report di cui sopra utilizzando funzioni aggregate.

Esaminiamo in dettaglio le funzioni aggregate.

CONTEGGIO

La funzione COUNT restituisce il numero totale di valori nel campo specificato. Funziona sia su tipi di dati numerici che non numerici. Tutte le funzioni aggregate per impostazione predefinita escludono valori nulli prima di lavorare sui dati.

COUNT (*) è un'implementazione speciale della funzione COUNT che restituisce il conteggio di tutte le righe in una tabella specificata. COUNT (*) considera anche null e duplicati.

La tabella mostrata di seguito mostra i dati nella tabella dei noleggi di film

numero di riferimento	data_transazione	data di ritorno	numero_iscrizione	id_film	film_ restituito
11	20-06-2012	NULL	1	1	0
12	22-06-2012	25-06-2012	1	2	0
13	22-06-2012	25-06-2012	3	2	0
14	21-06-2012	24-06-2012	2	2	0
15	23-06-2012	NULL	3	3	0

Supponiamo di voler ottenere il numero di volte in cui il film con id 2 è stato noleggiato

SELECT COUNT(`movie_id`)  FROM `movierentals` WHERE `movie_id` = 2;

Eseguendo la query precedente in MySQL banco di lavoro su myflixdb ci fornisce i seguenti risultati.

COUNT('movie_id')



3

Parola chiave DISTINTA

La parola chiave DISTINCT che ci consente di omettere duplicati dai nostri risultati. Ciò si ottiene raggruppando insieme valori simili.

Per apprezzare il concetto di Distinct, eseguiamo una semplice query

SELECT `movie_id` FROM `movierentals`;




movie_id


1


2


2


2


3

Ora eseguiamo la stessa query con la parola chiave distinta-

SELECT DISTINCT `movie_id` FROM `movierentals`;

Come mostrato di seguito, distinte omette i record duplicati dai risultati.

movie_id



1


2


3

Funzione MIN

La funzione MINIMO restituisce il valore più piccolo nel campo della tabella specificata.

Ad esempio, supponiamo di voler conoscere l'anno in cui è uscito il film più vecchio della nostra libreria, possiamo utilizzare MySQLla funzione MIN per ottenere l'informazione desiderata.

La seguente query ci aiuta a raggiungere questo obiettivo

SELECT MIN(`year_released`) FROM `movies`;

Eseguendo la query precedente in MySQL workbench su myflixdb ci fornisce i seguenti risultati.

MIN('year_released')



2005

Funzione MAX

Proprio come suggerisce il nome, la funzione MAX è l'opposto della funzione MIN. Esso restituisce il valore più grande dal campo della tabella specificata.

Supponiamo di voler ottenere l'anno in cui è stato rilasciato l'ultimo film nel nostro database. Possiamo facilmente utilizzare la funzione MAX per raggiungere questo obiettivo.

L'esempio seguente restituisce l'ultimo anno di uscita del film.

SELECT MAX(`year_released`)  FROM `movies`;

Eseguendo la query precedente in MySQL workbench che utilizza myflixdb ci fornisce i seguenti risultati.

MAX('year_released')



2012

Funzione SUM

Supponiamo di volere un report che fornisca l'importo totale dei pagamenti effettuati finora. Possiamo usare il MySQL SUM funzione che restituisce la somma di tutti i valori nella colonna specificata. SOMMA funziona solo su campi numerici. I valori null sono esclusi dal risultato restituito.

La tabella seguente mostra i dati nella tabella dei pagamenti:

pagamento_id	numero_iscrizione	data di pagamento	descrizione	importo pagato	numero_di_riferimento_esterno
1	1	23-07-2012	Pagamento del noleggio del film	2500	11
2	1	25-07-2012	Pagamento del noleggio del film	2000	12
3	3	30-07-2012	Pagamento del noleggio del film	6000	NULL

La query mostrata di seguito ottiene tutti i pagamenti effettuati e li somma per restituire un unico risultato.

SELECT SUM(`amount_paid`) FROM `payments`;

Eseguendo la query precedente in MySQL workbench su myflixdb fornisce i seguenti risultati.

SUM('amount_paid')



10500

AVG funzione

MySQL AVG funzione restituisce la media dei valori in una colonna specificata. Proprio come la funzione SOMMA, it funziona solo su tipi di dati numerici.

Supponiamo di voler trovare l'importo medio pagato. Possiamo usare la seguente query:

SELECT AVG(`amount_paid`)  FROM `payments`;

Eseguendo la query precedente in MySQL workbench, ci fornisce i seguenti risultati.

AVG('amount_paid')



3500

Rompicapo

Pensi che le funzioni aggregate siano facili. Prova questo!

L'esempio seguente raggruppa i membri in base al nome, conta il numero totale dei pagamenti, l'importo medio dei pagamenti e il totale generale degli importi dei pagamenti.

SELECT m.`full_names`,COUNT(p.`payment_id`) AS  `paymentscount`,AVG(p.`amount_paid`)  AS `averagepaymentamount`,SUM(p.`amount_paid`)  AS `totalpayments` FROM members m, payments p WHERE m.`membership_number` = p.`membership_number` GROUP BY m.`full_names`;

Eseguendo l'esempio precedente in MySQL workbench ci fornisce i seguenti risultati.

Sintesi

• MySQL supporta tutte le cinque (5) funzioni aggregate standard ISO COUNT, SUM, AVG, MIN e MAX.
• SOMMA e AVG le funzioni funzionano solo su dati numerici.
• Se desideri escludere valori duplicati dai risultati della funzione aggregata, utilizza la parola chiave DISTINCT. La parola chiave ALL include anche i duplicati. Se non viene specificato nulla, viene assunto ALL come impostazione predefinita.
• Le funzioni aggregate possono essere utilizzate insieme ad altre clausole SQL come RAGGRUPPA PER