7 errori comuni di GROUP BY

State commettendo questi errori con la clausola GROUP BY in SQL? Scoprite quali sono, come evitarli e come risolverli.

L'istruzione GROUP BY di SQL può svelare rapidamente potenti informazioni sui dati. All'inizio, l'uso di GROUP BY può sembrare semplice, ad esempio per la creazione di report SQL di base da presentare ai responsabili delle decisioni aziendali. Ma mentre si impara questa potente funzione, si rischia di rimanere intrappolati in strani errori o di ottenere risultati errati causati da istruzioni GROUP BY scritte in modo improprio. Se vi sembra che i conti non tornino con l'uso di GROUP BY, continuate a leggere. In questo articolo spiegherò gli errori più comuni di GROUP BY e come evitarli.

Smettete di commettere questi 7 errori comuni di GROUP BY

1. Dimenticare GROUP BY con le funzioni aggregate

Si utilizzano le istruzioni di SELECT con la clausola GROUP BY quando si desidera raggruppare e organizzare le righe in gruppi specifici ed eseguire quindi un calcolo specifico per ciascun gruppo.

L'errore GROUP BY più comune è dimenticare di scrivere GROUP BY all'interno dell'istruzione SELECT.

Ecco un esempio. Immaginiamo di avere una tabella ricetteche contiene 100 record e sei colonne. Questa tabella memorizza il numero di visualizzazioni (no_of_views) per ogni ricetta pubblicata su un famoso sito web culinario:

tabella delle ricette

Ecco una breve descrizione delle colonne della tabella:

• meal_category - La categoria della ricetta (zuppa, bevande, dessert, ecc.).
• name - Il nome della ricetta.
• author - Il nome dell'autore.
• no_of_views - Il numero di visualizzazioni (totale pagine/ricette visualizzate) nel mese corrente.
• no_of_views_lst_mth - Il numero di visualizzazioni (totale pagine/ricette visualizzate) nel mese precedente.
• author_id - Il numero ID univoco dell'autore.

Supponiamo di voler contare il numero di ricette in ogni categoria di pasti. Se si scrive l'istruzione in questo modo (senza GROUP BY alla fine)...

SELECT 
  meal_category,
  count(*) AS total_recipes 
FROM recipes ;

... la maggior parte dei motori SQL darà un errore. Alcuni motori, tuttavia, daranno risultati strani e indesiderati. Sto usando MySQL e quando eseguo questa istruzione, ottengo questo:

Risultato senza GROUP BY

100 è il conteggio totale di tutte le ricette dell'intero set di dati e la categoria "antipasto freddo" è solo una categoria su dieci. Per correggere questo tipo di errore, è necessario aggiungere un GROUP BY meal_category alla fine dell'istruzione. (Altrimenti, il risultato in MySQL non ha senso).

La SELECT corretta si presenta in questo modo:

SELECT 
  meal_category,
  count(*) AS total_recipes 
FROM recipes
GROUP BY meal_category ;

Ecco una breve spiegazione di ciò che sta accadendo:

• I record vengono uniti in base alla categoria del pasto. Per esempio, i dessert sono un gruppo, le zuppe un altro, i piatti principali un altro ancora, ecc. La colonna meal_category è specificata dopo GROUP BY; è anche elencata in SELECT.
• Per ogni gruppo, utilizziamo COUNT(*) per contare il numero totale di ricette in quel gruppo.

Non mi addentrerò nella sintassi, ma vi suggerisco di leggere GROUP BY in SQL Explained o Using GROUP BY in SQL per maggiori dettagli.

Come si può vedere, il risultato è quello atteso:

Risultato valido di GROUP BY

2. Confondere WHERE e HAVING

Forse si desidera visualizzare solo le categorie di pasti che hanno più di 10 ricette. Molti principianti scriverebbero questa query:

SELECT 
  meal_category,
  count(*) AS total_recipes 
FROM recipes
WHERE count(*) > 10 
GROUP BY meal_category ;

Questa istruzione restituirà un errore perché non è possibile utilizzare funzioni aggregate in una clausola WHERE. WHERE si utilizza con GROUP BY quando si desidera filtrare le righe prima di raggrupparle.

Nel nostro esempio, vogliamo filtrare le righe dopo il raggruppamento; in casi come questo, dobbiamo utilizzare la clausola HAVING:

SELECT 
  meal_category,
  count(*) AS total_recipes 
FROM recipes
           GROUP BY meal_category
HAVING count(*) > 10  ;

Questo fraintendimento sulla differenza tra HAVING e WHERE è il secondo errore più comune con GROUP BY.

Chiariamo questa differenza con altri due esempi.

Esempio 1 - Come visualizzare le categorie dei pasti con più di 1M di visualizzazioni

Un'istruzione che visualizzi solo le categorie con più di 1 milione di visualizzazioni totali di pagina può essere scritta in questo modo:

SELECT 
  meal_category,
  sum(no_of_views) AS total 
FROM recipes 
GROUP BY meal_category
HAVING sum(no_of_views) >1000000;

In questo caso utilizziamo HAVING perché vogliamo filtrare i record dopo che sono stati raggruppati. Il risultato è presentato di seguito:

Esempio con HAVING

Esempio 2 - Prestazioni di John in ogni categoria di pasti

Questa query estrae solo le ricette di John e calcola le sue prestazioni:

SELECT 
  meal_category, 
  sum(no_of_views) AS total 
FROM recipes 
WHERE author = ‘John’ 
GROUP BY meal_category;

Usiamo WHERE perché dobbiamo filtrare i record (in modo da ottenere solo i dati di John) prima di mettere i record in gruppi per categoria di pasto. Ecco come appare il risultato:

I KPI di John

HAVING e WHERE sono ben descritti nei nostri articoli Qual è la differenza tra le clausole WHERE e HAVING in SQL? e 5 esempi di GROUP BY. Se volete vedere altri esempi su questo argomento, vi suggerisco di iniziare da lì.

3. Elencare una colonna in SELECT ma non in GROUP BY

Supponiamo di voler vedere il numero totale di visualizzazioni per meal_category e author. Possiamo farlo, basta aggiungere la colonna autore alla nostra query precedente:

SELECT 
  meal_category,
  author,
  sum(no_of_views) AS total 
FROM recipes 
GROUP BY meal_category;

Certo che no: nella maggior parte dei motori SQL si verificherà un errore. Per esempio, Oracle vi dirà "errore: Non è un'espressione GROUP BY". Perché questo errore di confusione? Cosa manca qui?

Il motore SQL non sa come calcolare il totale per ogni autore perché non lo abbiamo incluso nella clausola GROUP BY; l'attributo author non è elencato nella clausola GROUP BY. Questo è un altro errore comune con i GROUP BY.

Correggiamo la query ed eseguiamola ancora una volta:

SELECT 
  meal_category,
  author,
  sum(no_of_views) AS total 
FROM recipes 
GROUP BY meal_category, author;

Il risultato è:

Ora sembra tutto a posto. Ricordate che le colonne non aggregate elencate in SELECT devono essere elencate anche in .GROUP BYNel nostro caso, le colonne non aggregate sono meal_category e author, che ora si trovano in SELECT e GROUP BY.

Non si elencano le colonne che si trovano all'interno di funzioni aggregate in GROUP BY. Nel nostro esempio, la colonna no_of_views è usata nella funzione aggregata SUM() e quindi non è elencata nella clausola GROUP BY.

Per ulteriori informazioni su questo argomento, consultare l'articolo Clausola GROUP BY: Quanto la conoscete? Spiega perché le colonne selezionate devono comparire nella clausola GROUP BY. Inoltre, Come risolvere un errore "Non è un'espressione GROUP BY" fornisce altri esempi relativi a questo tipo di errore.

4. Non raggruppare per una chiave univoca

Ora proviamo un'altra cosa. Supponiamo di voler ottenere il numero medio di pagine viste per ogni autore di ricette. La seguente query calcola il numero medio totale di pagine viste per ogni autore utilizzando il nome dell'autore:

SELECT 
  author,
  avg(no_of_views) 
FROM recipes 
GROUP BY author;

Osservando il risultato, si noterà che Lisa ha una media di 116101,5 pagine viste:

GROUP BY author - ma i nomi non sono unici

Tuttavia, nella nostra tabella abbiamo due autori di nome Lisa. Quando raggruppiamo i risultati in base alla colonna autore, entrambe le Lisa vengono valutate insieme. Perché? Perché stiamo usando una colonna non univoca in GROUP BY. Ciò significa che non tutti i valori del raggruppamento devono essere univoci. Se vogliamo vedere la media di ogni Lisa separatamente, dobbiamo aggiungere author_id (una colonna univoca) all'elenco GROUP BY:

SELECT 
  author, author_id
  avg(no_of_views) 
FROM recipes 
GROUP BY author, author_id;

Ora vediamo come le ricette di Lisa(id=11) siano molto più viste delle ricette di Lisa(id=5):

GROUP BY con autore e author_id

È importante pensare sempre alle chiavi di raggruppamento. I valori di raggruppamento devono essere unici e devono rappresentare ogni gruppo nel modo desiderato. Altrimenti, si otterranno risultati imprecisi e confusi e forse un errore GROUP BY.

5. Confondere COUNT(distinct) e COUNT(*)

Se siete curiosi di vedere il numero totale di autori per ogni categoria di pasto, potete scrivere un'istruzione GROUP BY per calcolarlo. Utilizziamo COUNT(*) e recuperiamo il numero di autori per ogni categoria:

SELECT 
  meal_category, 
  count(*) 
FROM recipes 
GROUP BY meal_category;

Ecco il risultato, ma non è quello che vi aspettavate, vero?

Si tratta del numero totale di ricette in ogni categoria, non del numero totale di autori. Perché? Perché COUNT(*) conta tutte le righe di ogni gruppo. La tabella ricette contiene informazioni a recipe livello: ogni record è una ricetta. Questa query conta le ricette (righe) in ogni categoria, non gli autori delle ricette.

Un autore può avere molte ricette in ogni categoria, quindi per ottenere le informazioni desiderate è necessario contare gli autori distinti (usando COUNT(distinct author) invece di COUNT(*)) all'interno di ogni gruppo. Questo è un errore molto comune di GROUP BY.

Quindi, quando si dovrebbero usare COUNT(*), COUNT(expression) e COUNT(distinct expression)?

Vediamo un esempio:

SELECT 
  meal_category, 
  count(distinct author), 
  count(author),
  count(*) 
FROM recipes 
GROUP BY meal_category;

La differenza tra COUNT(*) e COUNT(expression) è visibile se si eseguono calcoli su una colonna con alcuni valori mancanti. In presenza di valori mancanti, COUNT(*) conterà tutti i record di un gruppo, mentre COUNT(expression) conterà solo i valori non nulli.

Nell'esempio precedente, COUNT(*) e COUNT(author) danno lo stesso risultato perché la colonna autore non ha valori NULL.

COUNT(distinct author) fornisce il numero di autori distinti per ogni categoria, che non è lo stesso di COUNT(*). Ad esempio, la categoria antipasto freddo contiene sei ricette di due autori distinti. COUNT(*) conta il numero di ricette (record) in ogni categoria, mentre COUNT(distinct author) conta il numero di autori distinti.

Quindi, se si desidera visualizzare il numero totale di autori distinti per ogni categoria di pasto, utilizzare COUNT(distinct author). Ecco la query corretta:

SELECT 
  meal_category, 
  count(distinct author)
FROM recipes 
GROUP BY meal_category;
GROUP BY meal_category;

Per una spiegazione più dettagliata, vedere Qual è la differenza tra COUNT(*), COUNT(1), COUNT(nome colonna) e COUNT(nome colonna DISTINCT)?

6. Problemi nell'uso di funzioni aggregate con NULL

Questo è un altro problema di "valore mancante". Supponiamo di voler calcolare il numero totale medio di visualizzazioni del mese precedente per ogni categoria. Il vostro collega ha calcolato queste cifre, ma vorrebbe che voi verificaste il risultato.

Ecco la vostra domanda:

SELECT
      meal_category,
      avg(no_of_views_lst_mth) as average,
  FROM recipes 
GROUP BY meal_category;

E ciò che si ottiene è...

Il risultato sembra corretto e siete sicuri della correttezza della vostra domanda. Tuttavia, il vostro collega ha ottenuto cifre leggermente diverse:

Cosa è successo? Perché questi risultati diversi?

In poche parole, i risultati diversi derivano da interpretazioni diverse dei valori mancanti.

La colonna no_of_views_lst_mth rappresenta il numero di pagine viste totali nel mese precedente. Se una ricetta è stata creata nel mese corrente, questa colonna sarà nulla per quella riga.

Ad esempio, la ricetta Banana Cheesecake di Ally è stata scritta nel mese corrente, quindi non ci sono statistiche per il mese precedente:

La cheesecake alla banana è stata pubblicata nel mese corrente.

Torniamo ora alle medie e ai loro diversi risultati. Le medie sono calcolate come la somma totale di no_of_views_lst_mth divisa per il numero totale di record. Se si utilizza la funzione AVG() e sono presenti dei NULL, il motore ignora i NULL ed esegue i calcoli senza di essi. Questo è ciò che è successo quando è stata eseguita la query: i NULL sono stati omessi. In alcuni casi, è necessario sostituire i NULL con 0 (perché lo impone la logica aziendale); questo è ciò che ha fatto il collega, che ha prodotto cifre leggermente diverse. Ecco la query del collega:

SELECT
      meal_category,
           avg(CASE WHEN no_of_views_lst_mth is null 
            THEN 0 
            ELSE no_of_views_lst_mth END) AS average
FROM recipes 
GROUP BY meal_category;

Si noti come le medie di queste due query trattino i NULL in modo diverso. Ad esempio, la categoria "dessert" contiene dei NULL. Pertanto, la prima query omette queste righe e non le conta per il numero totale di righe; si ottiene così il valore 144349,72. La seconda query sostituisce tutti i NULL con zero e conta queste righe nella media, ottenendo un valore inferiore di 129914,75.

Direi che entrambe le query possono essere valide, a seconda di come si desidera calcolare le medie.

7. Utilizzo di COUNT(*) con GROUP BY e una LEFT JOIN

L'uso di GROUP BY con un'istruzione LEFT JOIN può creare molta confusione, soprattutto con COUNT(). Vediamo come funzionano COUNT(*) e COUNT(expression) in una LEFT JOIN.

Supponiamo che qualcuno del marketing abbia la seguente tabella, recipes_campaigns. Essa contiene informazioni sul numero di campagne eseguite su ciascuna categoria di pasti nel mese corrente:

ricette_campagna

Oltre ai dati contenuti in recipes_campaignsl'addetto al marketing vuole anche vedere il numero di ricette per ogni categoria di pasto. A tal fine, sono necessarie le informazioni della tabella recipes tabella. Quindi uniamo le due tabelle e calcoliamo il numero di ricette utilizzando COUNT(*), in questo modo:

SELECT 
      a.meal_category,
      count(*),
     FROM recipes_campaigns a 
LEFT JOIN recipes b ON a.meal_category=b.meal_category
GROUP BY a.meal_category;

Ecco il risultato:

Non è quello che ci aspettavamo. La tabella recipe non contiene ricette nella categoria "brunch", quindi perché abbiamo ottenuto quell'1 nel risultato? Questo accade perché COUNT() viene applicato al risultato di LEFT JOIN! Quando si LEFT JOIN due tabelle, la categoria "brunch" sarà presente nel risultato, anche se nella tabella non ci sono ricette o categorie corrispondenti. recipe tabella.

Come possiamo risolvere questo problema? Se usiamo COUNT(expression) invece di COUNT(*), otterremo il risultato desiderato:

SELECT 
      a.meal_category,
      count(author_id),
FROM recipes_campaigns a 
LEFT JOIN recipes b ON a.meal_category=b.meal_category
GROUP BY a.meal_category;

Una volta eseguito, si ottiene:

Qui, COUNT(author_id) conta solo i valori non NULL in author_id dopo che è stato eseguito LEFT JOIN. Non c'è alcun valore in author_id per la categoria "brunch"; in altre parole, è NULL e il risultato per quella categoria è 0.

È possibile risolvere gli errori GROUP BY!

Attraverso diversi esempi, abbiamo esplorato il GROUP BY e gli errori più comuni che i principianti spesso commettono. Spero che ora abbiate un'idea più chiara di come funziona GROUP BY e di cosa causa quegli strani errori o risultati confusi.

GROUP BY è davvero molto importante nella creazione dei report. Se volete imparare a costruire dei buoni report, vi consiglio il nostro Creating Basic SQL Reports corso. Contiene molti esercizi interattivi che consentono di acquisire esperienza nella scrittura di query GROUP BY per i report. E una maggiore esperienza riduce certamente la possibilità di errori!