Tutto quello che c'è da sapere sull'istruzione SQL GROUP BY

Gran parte della potenza dei database relazionali deriva dal filtraggio dei dati e dall'unione delle tabelle. Questo è il motivo per cui rappresentiamo in primo luogo quelle relazioni. Ma i moderni sistemi di database forniscono un'altra tecnica preziosa: il raggruppamento.

Il raggruppamento consente di estrarre informazioni di riepilogo da un database. Ti consente di combinare i risultati per creare dati statistici utili. Il raggruppamento ti evita di scrivere codice per casi comuni come la media degli elenchi di cifre. E può creare sistemi più efficienti.

---

---

Cosa fa la clausola GROUP BY?

GROUP BY, come suggerisce il nome, raggruppa i risultati in un insieme più piccolo. I risultati sono costituiti da una riga per ogni valore distinto della colonna raggruppata. Possiamo mostrarne l'utilizzo esaminando alcuni dati di esempio con righe che condividono alcuni valori comuni.

come cambiare le icone di collegamento di Windows 10

Quello che segue è un database molto semplice con due tabelle che rappresentano gli album dei record. Puoi impostare un database di questo tipo con scrivere uno schema di base per il sistema di database scelto. Il album la tabella ha nove righe con una chiave primaria ID colonna e colonne per nome, artista, anno di pubblicazione e vendite:

+----+---------------------------+-----------+--------------+-------+
| id | name | artist_id | release_year | sales |
+----+---------------------------+-----------+--------------+-------+
| 1 | Abbey Road | 1 | 1969 | 14 |
| 2 | The Dark Side of the Moon | 2 | 1973 | 24 |
| 3 | Rumours | 3 | 1977 | 28 |
| 4 | Nevermind | 4 | 1991 | 17 |
| 5 | Animals | 2 | 1977 | 6 |
| 6 | Goodbye Yellow Brick Road | 5 | 1973 | 8 |
| 7 | 21 | 6 | 2011 | 25 |
| 8 | 25 | 6 | 2015 | 22 |
| 9 | Bat Out of Hell | 7 | 1977 | 28 |
+----+---------------------------+-----------+--------------+-------+

Il artisti la tabella è ancora più semplice. Ha sette righe con colonne ID e nome:

+----+---------------+
| id | name |
+----+---------------+
| 1 | The Beatles |
| 2 | Pink Floyd |
| 3 | Fleetwood Mac |
| 4 | Nirvana |
| 5 | Elton John |
| 6 | Adele |
| 7 | Meat Loaf |
+----+---------------+

Puoi comprendere vari aspetti di GROUP BY con un semplice set di dati come questo. Naturalmente, un set di dati della vita reale avrebbe molte, molte più righe, ma i principi rimangono gli stessi.

Raggruppamento per una singola colonna

Diciamo che vogliamo scoprire quanti album abbiamo per ogni artista. Inizia con un tipico SELEZIONARE query per recuperare la colonna artist_id:

---

---

SELECT artist_id FROM albums

Ciò restituisce tutte e nove le righe, come previsto:

+-----------+
| artist_id |
+-----------+
| 1 |
| 2 |
| 3 |
| 4 |
| 2 |
| 5 |
| 6 |
| 6 |
| 7 |
+-----------+

Per raggruppare questi risultati per artista, aggiungi la frase GROUP BY artist_id :

---

---

SELECT artist_id FROM albums GROUP BY artist_id

Che dà i seguenti risultati:

+-----------+
| artist_id |
+-----------+
| 1 |
| 2 |
| 3 |
| 4 |
| 5 |
| 6 |
| 7 |
+-----------+

Ci sono sette righe nel set di risultati, ridotte dal totale di nove nel album tavolo. Ogni unico id_artista ha una sola riga. Infine, per ottenere i conteggi effettivi, aggiungi CONTARE(*) alle colonne selezionate:

SELECT artist_id, COUNT(*)
FROM albums
GROUP BY artist_id
+-----------+----------+
| artist_id | COUNT(*) |
+-----------+----------+
| 1 | 1 |
| 2 | 2 |
| 3 | 1 |
| 4 | 1 |
| 5 | 1 |
| 6 | 2 |
| 7 | 1 |
+-----------+----------+

I risultati raggruppano due coppie di righe per gli artisti con ID 2 e 6 . Ognuno ha due album nel nostro database.

Imparentato: Il cheat sheet dei comandi SQL essenziali per principianti

Come accedere ai dati raggruppati con una funzione di aggregazione

Potresti aver usato il CONTARE funzione prima, in particolare nel CONTARE(*) forma come visto sopra. Recupera il numero di risultati in un set. Puoi usarlo per ottenere il numero totale di record in una tabella:

SELECT COUNT(*) FROM albums
+----------+
| COUNT(*) |
+----------+
| 9 |
+----------+

COUNT è una funzione aggregata. Questo termine si riferisce a funzioni che traducono valori da più righe in un unico valore. Sono spesso usati insieme all'istruzione GROUP BY.

Invece di contare semplicemente il numero di righe, possiamo applicare una funzione di aggregazione ai valori raggruppati:

SELECT artist_id, SUM(sales)
FROM albums
GROUP BY artist_id
+-----------+------------+
| artist_id | SUM(sales) |
+-----------+------------+
| 1 | 14 |
| 2 | 30 |
| 3 | 28 |
| 4 | 17 |
| 5 | 8 |
| 6 | 47 |
| 7 | 28 |
+-----------+------------+

Le vendite totali mostrate sopra per gli artisti 2 e 6 sono le vendite dei loro album multipli combinate:

SELECT artist_id, sales
FROM albums
WHERE artist_id IN (2, 6)
+-----------+-------+
| artist_id | sales |
+-----------+-------+
| 2 | 24 |
| 2 | 6 |
| 6 | 25 |
| 6 | 22 |
+-----------+-------+

Raggruppamento per più colonne

Puoi raggruppare per più di una colonna. Includi semplicemente più colonne o espressioni, separate da virgole. I risultati verranno raggruppati in base alla combinazione di queste colonne.

SELECT release_year, sales, count(*)
FROM albums
GROUP BY release_year, sales

Ciò produrrà in genere più risultati rispetto al raggruppamento per una singola colonna:

+--------------+-------+----------+
| release_year | sales | count(*) |
+--------------+-------+----------+
| 1969 | 14 | 1 |
| 1973 | 24 | 1 |
| 1977 | 28 | 2 |
| 1991 | 17 | 1 |
| 1977 | 6 | 1 |
| 1973 | 8 | 1 |
| 2011 | 25 | 1 |
| 2015 | 22 | 1 |
+--------------+-------+----------+

Nota che, nel nostro piccolo esempio, solo due album hanno lo stesso anno di uscita e lo stesso conteggio delle vendite (28 nel 1977).

Utili funzioni di aggregazione

A parte COUNT, diverse funzioni funzionano bene con GROUP. Ogni funzione restituisce un valore in base ai record appartenenti a ciascun gruppo di risultati.

• COUNT() restituisce il numero totale di record corrispondenti.
• SUM() restituisce il totale di tutti i valori nella colonna data sommati.
• MIN() restituisce il valore più piccolo in una determinata colonna.
• MAX() restituisce il valore più grande in una determinata colonna.
• AVG() restituisce la media media. È l'equivalente di SUM() / COUNT().

Puoi anche usare queste funzioni senza una clausola GROUP:

come aggiungere app a vizio smart tv non nell'app store

SELECT AVG(sales) FROM albums
+------------+
| AVG(sales) |
+------------+
| 19.1111 |
+------------+

Utilizzo di GROUP BY con una clausola WHERE

Proprio come con un normale SELECT, puoi comunque utilizzare WHERE per filtrare il set di risultati:

SELECT artist_id, COUNT(*)
FROM albums
WHERE release_year > 1990
GROUP BY artist_id +-----------+----------+
| artist_id | COUNT(*) |
+-----------+----------+
| 4 | 1 |
| 6 | 2 |
+-----------+----------+

Ora hai solo gli album pubblicati dopo il 1990, raggruppati per artista. Puoi anche utilizzare un join con la clausola WHERE, indipendentemente dal GROUP BY:

SELECT r.name, COUNT(*) AS albums
FROM albums l, artists r
WHERE artist_id=r.id
AND release_year > 1990
GROUP BY artist_id +---------+--------+
| name | albums |
+---------+--------+
| Nirvana | 1 |
| Adele | 2 |
+---------+--------+

Tieni presente, tuttavia, che se provi a filtrare in base a una colonna aggregata:

SELECT r.name, COUNT(*) AS albums
FROM albums l, artists r
WHERE artist_id=r.id
AND albums > 2
GROUP BY artist_id;

Riceverai un errore:

ERROR 1054 (42S22): Unknown column 'albums' in 'where clause'

Le colonne basate sui dati aggregati non sono disponibili per la clausola WHERE.

Utilizzo della clausola HAVING

Quindi, come si filtra il set di risultati dopo che è avvenuto un raggruppamento? Il AVENDO clausola risponde a questa esigenza:

SELECT r.name, COUNT(*) AS albums
FROM albums l, artists r
WHERE artist_id=r.id
GROUP BY artist_id
HAVING albums > 1;

Nota che la clausola HAVING viene dopo GROUP BY. Altrimenti, è essenzialmente una semplice sostituzione di WHERE con HAVING. I risultati sono:

+------------+--------+
| name | albums |
+------------+--------+
| Pink Floyd | 2 |
| Adele | 2 |
+------------+--------+

Puoi ancora utilizzare una condizione WHERE per filtrare i risultati prima del raggruppamento. Funzionerà insieme a una clausola HAVING per il filtraggio dopo il raggruppamento:

SELECT r.name, COUNT(*) AS albums
FROM albums l, artists r
WHERE artist_id=r.id
AND release_year > 1990
GROUP BY artist_id
HAVING albums > 1;

Solo un artista nel nostro database ha pubblicato più di un album dopo il 1990:

+-------+--------+
| name | albums |
+-------+--------+
| Adele | 2 |
+-------+--------+

Combinazione dei risultati con GROUP BY

L'istruzione GROUP BY è una parte incredibilmente utile del linguaggio SQL. Può fornire informazioni di riepilogo sui dati, ad esempio per una pagina di contenuti. È un'ottima alternativa al recupero di grandi quantità di dati. Il database gestisce bene questo carico di lavoro extra poiché il suo stesso design lo rende ottimale per il lavoro.

Una volta compreso il raggruppamento e come unire più tabelle, sarai in grado di utilizzare la maggior parte della potenza di un database relazionale.

Condividere Condividere Tweet E-mail Come eseguire query su più tabelle di database contemporaneamente con join SQL

Scopri come utilizzare i join SQL per semplificare le query, risparmiare tempo e farti sentire un utente esperto di SQL.

come eliminare la cache su Android

Leggi Avanti Argomenti correlati

• Programmazione
• SQL

Circa l'autore Bobby Jack(58 articoli pubblicati)

Bobby è un appassionato di tecnologia che ha lavorato come sviluppatore software per quasi due decenni. È appassionato di giochi, lavora come editor di recensioni presso Switch Player Magazine ed è immerso in tutti gli aspetti dell'editoria online e dello sviluppo web.

Altro da Bobby Jack

Iscriviti alla nostra Newsletter

Iscriviti alla nostra newsletter per suggerimenti tecnici, recensioni, ebook gratuiti e offerte esclusive!

Clicca qui per iscriverti