MySQL Clausole GROUP BY e HAVING con esempi

Cos'è la clausola GROUP BY di SQL?

La clausola GROUP BY è un comando SQL utilizzato per raggruppare le righe che hanno gli stessi valoriViene inserita all'interno dell'istruzione SELECT e viene normalmente utilizzata insieme alle funzioni di aggregazione per generare report riepilogativi dal database.

Ecco cosa fa: riassume i dati conservati nel database. Le query che contengono la clausola GROUP BY sono chiamate query raggruppate e restituiscono una singola riga per ogni elemento raggruppato.

Sintassi SQL GROUP BY

Ora che lo scopo della clausola è chiaro, esaminiamo la sintassi di una query raggruppata di base.

SELECT statements... GROUP BY column_name1[, column_name2, ...] [HAVING condition];

QUI

• "Istruzioni SELECT…" è lo standard SELEZIONA SQL query del comando.
• "RAGGRUPPA PER nome_colonna1" è la clausola che esegue il gruppoping in base al nome della colonna1.
• "[, nome_colonna2, …]" è facoltativo e rappresenta altri nomi di colonna quando il gruppoping viene fatto su più di una colonna.
• "[Condizione di avere]" è facoltativo e viene utilizzato per limitare le righe interessate dalla clausola GROUP BY. È simile a Dove la clausola, tranne che viene applicato dopo il gruppoping.

Grouping Utilizzando una singola colonna

Il modo più rapido per vedere l'effetto della clausola SQL GROUP BY è confrontare una query non raggruppata con una raggruppata. Inizia con una query semplice che restituisce ogni voce relativa al genere nella tabella dei membri.

SELECT `gender` FROM `members`;

Vengono restituite nove righe e ogni valore viene ripetuto. Supponiamo di volere invece i valori univoci per il genere. La query seguente aggiunge la clausola GROUP BY.

SELECT `gender` FROM `members` GROUP BY `gender`;

Eseguendo lo script precedente in MySQL banco di lavoro rispetto a myflixdb ci fornisce i seguenti risultati.

Si noti che sono state restituite solo due righe, perché la tabella contiene solo due tipi di genere. La clausola GROUP BY ha raggruppato tutti i membri "Maschio" e ha restituito una singola riga per essi, e ha fatto lo stesso con i membri "Femmina".

Grouping Utilizzo di più colonne

Grouping Un'analisi basata su una sola colonna è spesso troppo generica per un report reale. La clausola GROUP BY accetta un elenco di colonne separate da virgole e la combinazione dei loro valori definisce ciascun gruppo.

Supponiamo di voler ottenere un elenco di valori category_id per i film e i corrispondenti anni di uscita. Osserviamo innanzitutto l'output di questa semplice query.

SELECT `category_id`, `year_released` FROM `movies`;

Le righe evidenziate mostrano che il risultato contiene duplicati. Eseguendo la stessa query con GROUP BY, questi vengono rimossi.

SELECT `category_id`, `year_released` FROM `movies` GROUP BY `category_id`, `year_released`;

Eseguendo lo script precedente in MySQL L'analisi con Workbench su myflixdb fornisce i seguenti risultati, mostrati di seguito.

La clausola GROUP BY opera sia su category_id che su year_released per identificare unica righe. Le due righe duplicate per la categoria 6 nel 2007 sono state accorpate in una sola.

Regola del pollice: Se l'ID della categoria è lo stesso ma l'anno di pubblicazione è diverso, la riga viene considerata univoca. Se l'ID della categoria e l'anno di pubblicazione sono uguali per più di una riga, le righe sono duplicate e ne viene visualizzata solo una.

Grouping e funzioni aggregate

Rimuovere i duplicati è utile, ma il vero potere del raggruppamentoping appare quando è abbinato a funzioni aggregate. Una funzione di aggregazione calcola un valore per ogni gruppo: COUNT conta le righe, SUM somma i valori e AVG, MIN e MAX descrivono la dispersione.

Supponiamo di voler ottenere il numero totale di membri maschi e femmine presenti nel database. Lo script seguente esegue questa operazione.

SELECT `gender`, COUNT(`membership_number`) FROM `members` GROUP BY `gender`;

Eseguendo lo script precedente in MySQL L'analisi con Workbench su myflixdb ci fornisce i seguenti risultati.

Le righe sono raggruppate in base a ciascun valore univoco del genere e il numero di righe all'interno di ciascun gruppo viene conteggiato dalla funzione di aggregazione COUNT. I nove record dei membri vengono poi raggruppati in due righe di riepilogo.

Limitare i risultati delle query utilizzando la clausola HAVING

GroupingNon sempre si desidera ottenere risultati per ogni riga di una tabella. Talvolta il report deve essere limitato a un determinato criterio, ed è questo il compito della clausola HAVING.

Supponiamo di voler conoscere tutti gli anni di uscita per la categoria di film con ID 8. Lo script seguente permette di ottenere questo risultato.

SELECT * FROM `movies` GROUP BY `category_id`, `year_released` HAVING `category_id` = 8;

Eseguendo lo script precedente in MySQL L'analisi con Workbench su myflixdb fornisce i seguenti risultati, mostrati di seguito.

Solo i film con ID categoria 8 sono stati mantenuti dalla condizione HAVING.

Attenzione: MySQL 5.7 e versioni successive abilitano la modalità ONLY_FULL_GROUP_BY per impostazione predefinita e, in tale modalità, SELECT * con una clausola GROUP BY viene rifiutata, perché movie_id, title e director non sono né raggruppati né aggregati. In produzione, denominare esplicitamente le colonne raggruppate, ad esempio SELEZIONA category_id, year_released DA movies GRUPPO PER category_id, year_released AVENDO category_id = 8;

DOVE vs AVERE vs GRUPPO PER vs ORDINA PER

I principianti spesso confondono queste quattro clausole, perché tutte plasmano l'insieme dei risultati. La differenza sta in quando MySQL Le seguenti clausole vengono applicate: WHERE viene eseguita prima del raggruppamento delle righe, HAVING viene eseguita dopo e ORDER BY viene eseguita per ultima.

La conseguenza pratica è di natura prestazionale. Il filtraggio con WHERE rimuove le righe prima del gruppoping Il lavoro inizia, quindi una condizione che non dipende da un risultato aggregato va inserita in WHERE piuttosto che in HAVING.

DOMANDE FREQUENTI