MySQL Funzioni aggregate: SOMMA, CONTA, AVG & MAX

Che cosa sono le funzioni aggregate in MySQL?

An funzione di aggregazione Legge molte righe di una singola colonna e le aggrega in un unico valore. Le funzioni di aggregazione riguardano principalmente:

• 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. COUNT
2. SUM
3. AVG
4. MIN
5. MAX

Una sola regola vale per tutte e cinque: Le funzioni aggregate ignorano i valori NULL. COUNT(*) è l'unica eccezione, e di seguito ne analizzeremo il motivo.

Perché utilizzare le funzioni aggregate

I diversi livelli organizzativi hanno esigenze informative differenti. I dirigenti di livello superiore sono generalmente interessati ai dati complessivi, non ai singoli dettagli.

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 volte in cui ciascun film viene noleggiato in un mese.

Tutti i report sopra riportati derivano da funzioni aggregate. Analizziamoli uno per uno nel dettaglio.

CONTEGGIO

La funzione CONTA.VALORI restituisce il numero totale di valori nel campo specificato, sia per i tipi di dati numerici che non numerici. Come ogni funzione di aggregazione, COUNT(colonna) esclude i valori NULL.

COUNT(*) è una forma speciale che restituisce il conteggio di tutte le righe in una tabella. Conta anche NULL e i duplicati, perché conta le righe anziché i valori.

La tabella movierentals contiene i seguenti dati:

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 questo in MySQL banco di lavoro Il test con myflixdb restituisce 3, perché tre righe contengono movie_id 2.

Parola chiave DISTINTA

COUNT risponde a “quanti”. La domanda successiva è solitamente “quanti diverso "uno", ed è a questo che serve DISTINCT.

La parola chiave DISTINCT omette i duplicati dai nostri risultati per gruppoping valori identici insieme, esattamente come suggerisce l'illustrazione sopra.

Innanzitutto, eseguiamo una semplice query.

SELECT `movie_id` FROM `movierentals`;

Ora la stessa query con la parola chiave DISTINCT:

SELECT DISTINCT `movie_id` FROM `movierentals`;

DISTINCT omette i record duplicati:

COUNT vs COUNT(*) vs COUNT(DISTINCT): Quale dovresti usare?

DISTINCT può anche essere posizionato interno una funzione aggregata, ed è qui che la maggior parte dei principianti si perde track delle quali vengono effettivamente contate. I quattro moduli seguenti vengono tutti eseguiti sulla stessa tabella di cinque righe "noleggi film" mostrata in precedenza, eppure non restituiscono tutti lo stesso numero. La differenza si riduce a due domande: il modulo conta le righe o i valori e mantiene i duplicati?

SELECT COUNT(*) AS `all_rows`,
       COUNT(`return_date`) AS `returned_rows`,
       COUNT(DISTINCT `movie_id`) AS `unique_movies`
FROM `movierentals`;

Suggerimento: Utilizzare COUNT(*) per contare le righe, COUNT(colonna) quando un valore NULL deve significare "non applicabile" e COUNT(DISTINCT colonna) per i valori univoci. L'opposto di DISTINCT è ALL, che è l'impostazione predefinita e quindi raramente viene utilizzata.

Funzione MIN

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

Supponiamo di voler conoscere l'anno di uscita del film più vecchio presente nella nostra collezione. MySQLLa funzione MIN ci fornisce questo.

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

Risultato:

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 conoscere l'anno di uscita dell'ultimo film presente nel nostro database. L'esempio seguente lo restituisce.

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

Risultato:

Funzione SUM

MIN e MAX selezionano un valore esistente da una colonna. SUM e AVG Calcola un nuovo numero utilizzando l'intera colonna.

Supponiamo di voler conoscere l'importo totale dei pagamenti effettuati finora. MySQL SUM funzione restituisce la somma di tutti i valori nella colonna specificata. SOMMA funziona solo su campi numericie I valori NULL vengono esclusi dal risultato.

La tabella seguente mostra i dati contenuti nella tabella dei pagamenti.

La query mostrata di seguito recupera tutti i pagamenti effettuati e li somma in un unico risultato: 2500 + 2000 + 6000 = 10500.

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

Risultato:

AVG funzione

Migliori 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 utilizzare la seguente query, che divide il totale di 10500 per le tre righe di pagamento non NULL.

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

Risultato:

⚠️ Attenzione: AVG divide per il numero di righe non NULL, non per il conteggio delle righe della tabella. Un valore NULL viene saltato anziché contato come zero, il che fa aumentare silenziosamente la media. Utilizzare AVG(SE NULL(`importo_pagato`, 0)) quando un valore mancante significa zero.

Esempio pratico: combinazione di funzioni di aggregazione con GROUP BY

Ciascuna funzione sopra ha restituito una cifra per l'intera tabella. Aggiungendo un RAGGRUPPA PER la clausola restituisce una cifra per gruppo invece — ed è così che si costruiscono i report reali.

L'esempio seguente raggruppa i membri per nome, quindi conta il numero totale di pagamenti, l'importo medio del pagamento e il totale complessivo degli importi dei pagamenti per ciascun membro.

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.

La query unisce le due tabelle nella clausola WHERE, secondo il vecchio stile di join con virgola. Il codice moderno scrive la stessa logica come un esplicito GIUNZIONE INTERNA … SU. Nota inoltre che ogni colonna non aggregata nell'elenco SELECT deve apparire in GROUP BY, oppure MySQL 5.7 e versioni successive rifiutano la query sotto ONLY_FULL_GROUP_BY. Vedi il ufficiale MySQL riferimento alla funzione aggregata.

DOMANDE FREQUENTI