Come utilizzare GROUP BY e ORDER BY in SQL

Introduzione

I database SQL (Structured Query Language) possono memorizzare e gestire molti dati attraverso numerose tabelle. Con set di dati grandi, è importante capire come ordinare i dati, specialmente per analizzare i risultati o organizzare i dati per report o comunicazioni esterne.

Due istruzioni comuni in SQL che aiutano a ordinare i dati sono GROUP BY e ORDER BY. Un’istruzione GROUP BY ordina i dati raggruppandoli in base alle colonne specificate nella query e viene utilizzata con funzioni di aggregazione. Un ORDER BY ti permette di organizzare i set di risultati in ordine alfabetico o numerico e in ordine ascendente o discendente.

In questo tutorial, ordinerai i risultati delle query in SQL utilizzando le istruzioni GROUP BY e ORDER BY. Inoltre, praticherai l’implementazione delle funzioni di aggregazione e della clausola WHERE nelle tue query per ordinare ulteriormente i risultati.

Prerequisiti

Per seguire questa guida, avrai bisogno di un computer che esegua un tipo qualsiasi di sistema di gestione di database relazionali (RDBMS) che utilizzi SQL. Le istruzioni e gli esempi in questo tutorial sono stati validati utilizzando l’ambiente seguente:

• A server running Ubuntu 20.04, with a non-root user with sudo administrative privileges and firewall enabled. Follow our Initial Server Setup with Ubuntu 20.04 to get started.
• MySQL installato e protetto sul server. Segui la nostra guida Come Installare MySQL su Ubuntu 20.04 per configurarlo. Questa guida presume che tu abbia anche configurato un utente MySQL non-root, come indicato nel Passaggio 3 di questa guida.

Per esercitarti nel ordinare i risultati dei dati in questo tutorial, avrai bisogno di un database e di una tabella caricati con dati di esempio. Se non ne hai uno pronto per l’inserimento, puoi leggere la seguente sezione Connessione a MySQL e Configurazione di un Database di Esempio per imparare come creare un database e una tabella. Questo tutorial farà riferimento a questo database di esempio e alla tabella in tutto il testo.

Connessione a MySQL e Configurazione di un Database di Esempio

Se il tuo database SQL è in esecuzione su un server remoto, effettua l’accesso SSH al tuo server dal tuo computer locale:

ssh sammy@your_server_ip

Successivamente, apri il prompt di MySQL, sostituendo sammy con le informazioni del tuo account utente MySQL:

mysql -u sammy -p

Crea un database chiamato movieDB:

CREATE DATABASE movieDB;

Se il database è stato creato con successo, riceverai l’output seguente:

Output
Query OK, 1 row affected (0.01 sec)

Per selezionare il database movieDB, esegui il seguente statement USE:

USE movieDB;

Output
Database changed

Dopo aver selezionato il database, crea una tabella al suo interno. Per l’esempio di questo tutorial, creeremo una tabella che memorizza informazioni sulle proiezioni di un cinema locale. Questa tabella avrà sette colonne:

• theater_id: memorizza valori del tipo di dato int per le sale proiezioni di ciascun cinema e fungerà da chiave primaria della tabella, il che significa che ogni valore in questa colonna funzionerà come identificatore univoco per la rispettiva riga.
• date: utilizza il tipo di dato DATE per memorizzare la data specifica per anno, mese e giorno in cui è stata proiettata un film. Questo tipo di dato rispetta i seguenti parametri: quattro cifre per l’anno e un massimo di due cifre per il mese e il giorno (YYYY-MM-DD).
• time: rappresenta l’orario di proiezione programmato del film con il tipo di dato TIME, indicando ore, minuti e secondi (HH:MM:SS).
• movie_name: memorizza il nome del film utilizzando il tipo di dato varchar con un massimo di 40 caratteri.
• movie_genre: utilizza il tipo di dati varchar con un massimo di 30 caratteri, per contenere le informazioni sul genere rispettivo di ciascun film.
• guest_total: mostra il numero totale di ospiti che hanno partecipato a una proiezione cinematografica con il tipo di dati int.
• ticket_cost: utilizza il tipo di dati decimal, con una precisione di quattro e una scala di uno, il che significa che i valori in questa colonna possono avere quattro cifre e due cifre alla destra del punto decimale. Questa colonna rappresenta il costo del biglietto per la specifica proiezione cinematografica.

Crea una tabella chiamata movie_theater che contenga ciascuna di queste colonne eseguendo il seguente comando CREATE TABLE:

CREATE TABLE movie_theater (
theater_id int, 
date DATE,
time TIME, 
movie_name varchar(40),
movie_genre varchar(30),
guest_total int,
ticket_cost decimal(4,2),
PRIMARY KEY (theater_id)
); 

Successivamente, inserisci alcuni dati di esempio nella tabella vuota:

INSERT INTO movie_theater
(theater_id, date, time, movie_name, movie_genre, guest_total, ticket_cost)
VALUES
(1, '2022-05-27', '10:00:00', 'Top Gun Maverick', 'Action', 131, 18.00),
(2, '2022-05-27', '10:00:00', 'Downton Abbey A New Era', 'Drama', 90, 18.00),
(3, '2022-05-27', '10:00:00', 'Men', 'Horror', 100, 18.00),
(4, '2022-05-27', '10:00:00', 'The Bad Guys', 'Animation', 83, 18.00),
(5, '2022-05-28', '09:00:00', 'Top Gun Maverick', 'Action', 112, 8.00),
(6, '2022-05-28', '09:00:00', 'Downton Abbey A New Era', 'Drama', 137, 8.00),
(7, '2022-05-28', '09:00:00', 'Men', 'Horror', 25, 8.00),
(8, '2022-05-28', '09:00:00', 'The Bad Guys', 'Animation', 142, 8.00),
(9, '2022-05-28', '05:00:00', 'Top Gun Maverick', 'Action', 150, 13.00),
(10, '2022-05-28', '05:00:00', 'Downton Abbey A New Era', 'Drama', 118, 13.00),
(11, '2022-05-28', '05:00:00', 'Men', 'Horror', 88, 13.00),
(12, '2022-05-28', '05:00:00', 'The Bad Guys', 'Animation', 130, 13.00);

Output
Query OK, 12 rows affected (0.00 sec)
Records: 12  Duplicates: 0  Warnings: 0

Una volta inseriti i dati, sei pronto per iniziare a ordinare i risultati delle query in SQL.

Utilizzando GROUP BY

La funzione di una dichiarazione GROUP BY è quella di raggruppare i record con valori condivisi. Una dichiarazione GROUP BY è sempre utilizzata con una funzione di aggregazione in una query. Come potresti ricordare, una funzione di aggregazione riassume le informazioni e restituisce un singolo risultato. Ad esempio, puoi richiedere il conteggio totale o la somma di una colonna e ciò produrrà un singolo valore nel tuo risultato. Con una clausola GROUP BY, puoi implementare la funzione di aggregazione per ottenere un valore di risultato per ciascun gruppo desiderato.

GROUP BY è utile per restituire più risultati desiderati ordinati dai gruppi specificati, anziché solamente da una singola colonna. Inoltre, GROUP BY deve sempre seguire l’istruzione FROM e la clausola WHERE, se si sceglie di utilizzarne una. Ecco un esempio di come è strutturata una query con GROUP BY e una funzione di aggregazione:

SELECT column_1, AGGREGATE_FUNCTION(column_2) FROM table GROUP BY column_1;

Per illustrare come è possibile utilizzare le istruzioni GROUP BY, supponiamo di essere responsabili della campagna pubblicitaria per diversi film in uscita e si desidera valutare il successo degli sforzi di marketing. Si chiede quindi a un cinema locale di condividere i dati raccolti dagli ospiti il venerdì e il sabato. Inizia con la revisione dei dati eseguendo SELECT e il simbolo * per selezionare “ogni colonna” dalla tabella movie_theater:

SELECT * FROM movie_theater;

Output
+------------+------------+----------+-------------------------+-------------+-------------+-------------+
| theater_id | date       | time     | movie_name              | movie_genre | guest_total | ticket_cost |
+------------+------------+----------+-------------------------+-------------+-------------+-------------+
|          1 | 2022-05-27 | 10:00:00 | Top Gun Maverick        | Action      |         131 |       18.00 |
|          2 | 2022-05-27 | 10:00:00 | Downton Abbey A New Era | Drama       |          90 |       18.00 |
|          3 | 2022-05-27 | 10:00:00 | Men                     | Horror      |         100 |       18.00 |
|          4 | 2022-05-27 | 10:00:00 | The Bad Guys            | Animation   |          83 |       18.00 |
|          5 | 2022-05-28 | 09:00:00 | Top Gun Maverick        | Action      |         112 |        8.00 |
|          6 | 2022-05-28 | 09:00:00 | Downton Abbey A New Era | Drama       |         137 |        8.00 |
|          7 | 2022-05-28 | 09:00:00 | Men                     | Horror      |          25 |        8.00 |
|          8 | 2022-05-28 | 09:00:00 | The Bad Guys            | Animation   |         142 |        8.00 |
|          9 | 2022-05-28 | 05:00:00 | Top Gun Maverick        | Action      |         150 |       13.00 |
|         10 | 2022-05-28 | 05:00:00 | Downton Abbey A New Era | Drama       |         118 |       13.00 |
|         11 | 2022-05-28 | 05:00:00 | Men                     | Horror      |          88 |       13.00 |
|         12 | 2022-05-28 | 05:00:00 | The Bad Guys            | Animation   |         130 |       13.00 |
+------------+------------+----------+-------------------------+-------------+-------------+-------------+
12 rows in set (0.00 sec)

Anche se questi dati sono utili, si desidera effettuare una valutazione più approfondita e ordinare i risultati per alcune colonne specifiche.

Dato che si è lavorato su film di diversi generi, si è interessati a sapere quanto sono stati ben accolti dagli spettatori. In particolare, si vuole conoscere la media di persone che hanno visto ciascun genere di film. Utilizzare SELECT per recuperare i vari tipi di film dalla colonna movie_genre. Applicare quindi la funzione di aggregazione AVG sulla colonna guest_total, utilizzare AS per creare un alias per una colonna chiamata average, e includere l’istruzione GROUP BY per raggruppare i risultati per movie_genre. Raggruppare in questo modo fornirà i risultati medi per ciascun genere di film:

SELECT movie_genre, AVG(guest_total) AS average
FROM movie_theater 
GROUP BY movie_genre;

Output
+-------------+----------+
| movie_genre | average  |
+-------------+----------+
| Action      | 131.0000 |
| Drama       | 115.0000 |
| Horror      |  71.0000 |
| Animation   | 118.3333 |
+-------------+----------+
4 rows in set (0.00 sec)

Questa output fornisce le quattro medie per ogni genere all’interno del gruppo movie_genre. In base a queste informazioni, i film Action hanno attirato il numero medio più alto di ospiti per proiezione.

Successivamente, supponiamo tu voglia misurare i ricavi del teatro in due giorni separati. La seguente query restituisce i valori dalla colonna date, così come i valori restituiti dalla funzione di aggregazione SUM. In particolare, la funzione di aggregazione SUM racchiuderà un’equazione matematica tra parentesi per moltiplicare (utilizzando l’operatore *) il numero totale di ospiti per il costo di un biglietto, rappresentato come: SUM(guest_total * ticket_cost). Questa query include la clausola AS per fornire l’alias total_revenue per la colonna restituita dalla funzione di aggregazione. Quindi completa la query con l’istruzione GROUP BY per raggruppare i risultati della query per la colonna date:

SELECT date, SUM(guest_total * ticket_cost) 
AS total_revenue 
FROM movie_theater 
GROUP BY date;

Output
+------------+---------------+
| date       | total_revenue |
+------------+---------------+
| 2022-05-27 |       7272.00 |
| 2022-05-28 |       9646.00 |
+------------+---------------+
2 rows in set (0.00 sec)

Dato che hai usato GROUP BY per raggruppare la colonna date, il tuo output fornisce i risultati per il totale dei ricavi dalle vendite dei biglietti per ogni giorno, in questo caso $7,272 per venerdì 27 maggio e $9,646 per sabato 28 maggio.

Ora immagina di voler focalizzare e analizzare un film: The Bad Guys. In questo scenario, vuoi capire come l’orario e i punti di prezzo influenzano la scelta di una famiglia di guardare un film d’animazione. Per questa query, utilizza la funzione di aggregazione MAX per recuperare il massimo ticket_cost, assicurandoti di includere AS per creare l’alias per la colonna price_data. Successivamente, utilizza la clausola WHERE per restringere i risultati per movie_name solo a “The Bad Guys”, e usa AND per determinare anche gli orari dei film più popolari in base ai numeri di guest_total superiori a 100 con l’operatore di confronto >. Infine, completa la query con l’istruzione GROUP BY e raggruppa per time:

SELECT time, MAX(ticket_cost) AS price_data 
FROM movie_theater
WHERE movie_name = "The Bad Guys" 
AND guest_total > 100
GROUP BY time;

Output
+----------+------------+
| time     | price_data |
+----------+------------+
| 09:00:00 |       8.00 |
| 05:00:00 |      13.00 |
+----------+------------+
2 rows in set (0.00 sec)

Secondo questo output, più ospiti hanno partecipato al film The Bad Guys al primo spettacolo del mattino alle 9:00, che aveva il prezzo del biglietto più conveniente di $8.00. Tuttavia, questi risultati mostrano anche che gli ospiti del film hanno pagato il prezzo del biglietto più alto di $13.00 alle 17:00, suggerendo che le famiglie preferiscano spettacoli che non siano troppo tardi nel corso della giornata e siano disposte a pagare un po’ di più per un biglietto. Questo sembra essere una valutazione equa se confrontata con l’orario delle 22:00 quando il film The Bad Guys aveva solo 83 ospiti e il prezzo del biglietto era di $18.00. Queste informazioni possono essere utili per fornire al responsabile del cinema prove che l’apertura di più spettacoli mattutini e serali può aumentare la presenza delle famiglie che scelgono in base all’orario e al prezzo preferiti.

Si noti che anche se GROUP BY viene quasi sempre utilizzato con una funzione di aggregazione, possono esserci eccezioni, sebbene improbabili. Tuttavia, se volessi raggruppare i tuoi risultati senza una funzione di aggregazione, puoi utilizzare l’istruzione DISTINCT per ottenere lo stesso risultato. Una clausola DISTINCT rimuove eventuali duplicati in un set di risultati restituendo i valori unici nella colonna, e può essere utilizzata solo con un’istruzione SELECT. Ad esempio, se volessi raggruppare tutti i film insieme per nome, potresti farlo con la seguente query:

SELECT DISTINCT movie_name FROM movie_theater;

Output
+-------------------------+
| movie_name              |
+-------------------------+
| Top Gun Maverick        |
| Downton Abbey A New Era |
| Men                     |
| The Bad Guys            |
+-------------------------+
4 rows in set (0.00 sec)

Come ricordi dall’aver visualizzato tutti i dati nella tabella, c’erano duplicati dei nomi dei film poiché c’erano più proiezioni. Pertanto, DISTINCT ha rimosso quei duplicati e ha raggruppato efficacemente i valori unici sotto la singola colonna movie_name. Questo è efficacemente identico alla seguente query, che include un’istruzione GROUP BY:

SELECT movie_name FROM movie_theater GROUP BY movie_name;

Ora che hai praticato l’utilizzo di GROUP BY con le funzioni di aggregazione, successivamente imparerai come ordinare i risultati della tua query con l’istruzione ORDER BY.

USANDO ORDER BY

La funzione dello statement ORDER BY è ordinare i risultati in ordine ascendente o discendente in base alle colonne specificate nella query. A seconda del tipo di dati memorizzato dalla colonna specificata dopo di esso, ORDER BY li organizzerà in ordine alfabetico o numerico. Per impostazione predefinita, ORDER BY ordinerà i risultati in ordine ascendente; se preferisci l’ordine discendente, tuttavia, devi includere la parola chiave DESC nella tua query. Puoi anche utilizzare lo statement ORDER BY con GROUP BY, ma deve venire dopo per funzionare correttamente. Similmente a GROUP BY, ORDER BY deve anche venire dopo lo statement FROM e la clausola WHERE. La sintassi generale per utilizzare ORDER BY è la seguente:

SELECT column_1, column_2 FROM table ORDER BY column_1;

Continuiamo con i dati di esempio per il cinema e pratichiamo il sorting dei risultati con ORDER BY. Inizia con la seguente query che recupera i valori dalla colonna guest_total e organizza quei valori numerici con uno statement ORDER BY:

SELECT guest_total FROM movie_theater 
ORDER BY guest_total;

Output
+-------------+
| guest_total |
+-------------+
|          25 |
|          83 |
|          88 |
|          90 |
|         100 |
|         112 |
|         118 |
|         130 |
|         131 |
|         137 |
|         142 |
|         150 |
+-------------+
12 rows in set (0.00 sec)

Dal momento che la tua query ha specificato una colonna con valori numerici, lo statement ORDER BY ha organizzato i risultati in ordine numerico e ascendente, a partire da 25 sotto la colonna guest_total.

Se preferisci ordinare la colonna in ordine decrescente, aggiungeresti la parola chiave DESC alla fine della query. Inoltre, se desideri ordinare i dati in base ai valori dei caratteri sotto movie_name, dovresti specificarlo nella tua query. Eseguiamo quel tipo di query utilizzando ORDER BY per ordinare la colonna movie_name con valori dei caratteri in ordine decrescente. Ordina ulteriormente i risultati includendo una clausola WHERE per recuperare i dati sui film in programmazione alle 10:00 pm dalla colonna time:

SELECT movie_name FROM movie_theater
WHERE time = '10:00:00' 
ORDER BY movie_name DESC;

Output
+-------------------------+
| movie_name              |
+-------------------------+
| Top Gun Maverick        |
| The Bad Guys            |
| Men                     |
| Downton Abbey A New Era |
+-------------------------+
4 rows in set (0.01 sec)

Questo set di risultati elenca le quattro diverse proiezioni cinematografiche alle 10:00 pm in ordine alfabetico decrescente, a partire da Top Gun Maverick fino a Downtown Abbey A New Era.

Per questa prossima query, combina le istruzioni ORDER BY e GROUP BY con la funzione di aggregazione SUM per generare risultati sul totale dei ricavi ricevuti per ogni film. Tuttavia, diciamo che il cinema ha sbagliato il conteggio totale degli ospiti e ha dimenticato di includere feste speciali che avevano preacquistato e riservato biglietti per un gruppo di 12 persone per ogni proiezione.

In questa query usa SUM e includi i 12 ospiti aggiuntivi ad ogni proiezione del film implementando l’operatore di aggiunta + e poi aggiungi 12 al guest_total. Assicurati di racchiudere questo in parentesi. Poi, moltiplica questo totale per il ticket_cost con l’operatore *, e completa l’equazione matematica chiudendo le parentesi alla fine. Aggiungi la clausola AS per creare l’alias per la nuova colonna intitolata total_revenue. Quindi, usa GROUP BY per raggruppare i risultati di total_revenue per ogni film basato sui dati recuperati dalla colonna movie_name. Infine, usa ORDER BY per organizzare i risultati sotto la nuova colonna total_revenue in ordine ascendente:

SELECT movie_name, SUM((guest_total + 12) * ticket_cost) 
AS total_revenue
FROM movie_theater 
GROUP BY movie_name 
ORDER BY total_revenue;

Output
+-------------------------+---------------+
| movie_name              | total_revenue |
+-------------------------+---------------+
| Men                     |       3612.00 |
| Downton Abbey A New Era |       4718.00 |
| The Bad Guys            |       4788.00 |
| Top Gun Maverick        |       5672.00 |
+-------------------------+---------------+
4 rows in set (0.00 sec)

Questo insieme di risultati ci dice il ricavo totale per ogni film con le vendite di biglietti aggiuntive di 12 ospiti e organizza le vendite totali dei biglietti in ordine ascendente da quello più basso a quello più alto. Da questo, apprendiamo che Top Gun Maverick ha ricevuto il maggior numero di vendite di biglietti, mentre Men ha ricevuto il minor numero. Nel frattempo, i film The Bad Guys e Downton Abbey A New Era erano molto vicini nelle vendite totali dei biglietti.

In questa sezione, hai esercitato vari modi per implementare l’istruzione ORDER BY e come specificare l’ordine che preferisci, come ordini ascendenti e discendenti sia per i valori di dati carattere che numerici. Hai anche imparato come includere la clausola WHERE per restringere i tuoi risultati, e hai eseguito una query utilizzando sia le istruzioni GROUP BY che ORDER BY con una funzione aggregata ed un’equazione matematica.

Conclusione

Comprendere come utilizzare le istruzioni GROUP BY e ORDER BY è importante per ordinare i risultati e i dati. Che tu voglia organizzare più risultati sotto un unico gruppo, ordinare una delle tue colonne in ordine alfabetico e decrescente, o fare entrambi contemporaneamente; dipende da te e dai tuoi risultati desiderati. Hai anche appreso altri modi per ordinare ulteriormente i risultati con la clausola WHERE. Se vuoi saperne di più, dai un’occhiata al nostro tutorial su Come Usare i Jolly in SQL per praticare il filtraggio dei risultati con la clausola LIKE.