Come risolvere l'errore "must appear in the GROUP BY clause or be used in an aggregate function" in PostgreSQL

Scoprite cosa causa uno degli errori GROUP BY più comuni di PostgreSQL e come potete risolverlo!

Come utenti di PostgreSQL, sicuramente incontrerete molti messaggi di errore quando scrivete un codice SQL. A volte sono poco chiari, ma per questo non avrete bisogno di un interprete: "must appear in the GROUP BY clause or be used in an aggregate function".

Questo messaggio di errore menziona GROUP BY e le funzioni aggregate. Si tratta di concetti fondamentali di PostgreSQL che è necessario padroneggiare per risolvere questo errore. Naturalmente, correggerlo significa anche non incorrere in questo errore troppo spesso quando si passa a concetti avanzati, come la creazione di report, l'uso di funzioni finestra, CTE e ricorsione, la scrittura di funzioni proprie o il lavoro con le mappe. Tutto questo è trattato nel nostro percorso di apprendimento SQL from A to Z in PostgreSQL. Questo corso interattivo completo vi permetterà di evitare facilmente i messaggi di errore di PostgreSQL. E quando appaiono, sarete in grado di risolverli rapidamente.

Come vedrete presto, anche la soluzione dell'errore citato nel titolo dell'articolo è relativamente semplice. Tuttavia, richiede la comprensione del funzionamento di GROUP BY in PostgreSQL.

Cosa causa l'errore "must appear in the GROUP BY clause or be used in an aggregate function"?

Come sempre, saremo molto pratici e useremo il codice SQL per mostrare le cause dell'errore e come risolverlo.

Set di dati

Il dataset che utilizzeremo è composto da due tabelle. La prima è freelancers:

• id - L'ID del libero professionista e la chiave primaria (PK) della tabella.
• first_name - Il nome del libero professionista.
• last_name - Il cognome del freelance.
• email - L'e-mail del freelance.
• country - Il paese del freelance.

La query CREATE TABLE è disponibile qui.

La seconda tabella si chiama weekly_salary e contiene i dati relativi alla retribuzione settimanale dei freelance. Le colonne sono:

• id - L'ID dello stipendio e la chiave primaria (PK) della tabella.
• freelancers_id - L'ID del freelance e la chiave esterna (FK) della tabella freelance.
• week_start - La data di inizio del calcolo dello stipendio.
• week_end - La data di fine del calcolo dello stipendio.
• paid_amount - L'importo dello stipendio.

Ecco la query per la creazione della tabella.

Lancio dell'errore

Cerchiamo di mostrare il nome e il cognome dei freelance e il numero di stipendi settimanali che hanno ricevuto finora:

SELECT
  first_name, 
  last_name,
  COUNT(freelancers_id) AS number_of_payments
FROM freelancers f
LEFT JOIN weekly_salary ws
ON f.id = ws.freelancers_id;

LEFT JOIN le due tabelle in modo da ottenere tutti i dati richiesti.

Questi dati sono il nome e il cognome. Poi utilizziamo la funzione aggregata COUNT() sulla colonna freelancers_id per contare quante volte compare questo ID. Il numero contato è uguale al numero di stipendi ricevuti dal freelance.

Quindi il conteggio degli stipendi per ogni freelance è... un errore!

La formulazione di questo messaggio di PostgreSQL è abbastanza chiara e riflette la regola generale: le colonne elencate in SELECT devono comparire in GROUP BY. Se non compaiono in GROUP BY, allora devono essere utilizzate nella funzione aggregata.

Come si può vedere, la nostra query non ha affatto GROUP BY. Stiamo usando la funzione aggregata COUNT() e dovremmo definire i gruppi per l'aggregazione, ma non l'abbiamo fatto.

Correzione dell'errore "must appear in the GROUP BY clause or be used in an aggregate function".

L'approccio abituale per risolvere questo errore consiste nello scrivere semplicemente tutte le colonne di SELECT nella clausola GROUP BY. Questo esclude le colonne che sono il risultato della funzione aggregata.

Nel nostro caso, elencando le colonne first_name e last_name in GROUP BY si risolve l'errore.

SELECT 
  first_name, 
  last_name,
  COUNT(freelancers_id) AS number_of_payments
FROM freelancers f
LEFT JOIN weekly_salary ws
ON f.id = ws.freelancers_id
GROUP BY first_name, last_name;

L'unico cambiamento rispetto alla query precedente è che ora si usa GROUP BY. In essa, scriviamo tutte le colonne di SELECT, tranne quella utilizzata nella funzione aggregata. Le funzioni aggregate non sono consentite in GROUP BY - questo mostrerebbe un altro messaggio di errore di Postgres.

La query restituisce l'output di cui sopra. È evidente che abbiamo risolto l'errore. Questo output mostra che Miriam Valetti è stata pagata una volta, Claudio Stratos due volte e così via.

Bonus: l'ottimizzatore di PostgreSQL, o perché questo errore non compare sempre

PostgreSQL utilizza un ottimizzatore. Cerca di "pensare" e di fare cose che voi intendete ma che forse non avete scritto esplicitamente.

L'errore di cui abbiamo parlato non appare in PostgreSQL se si raggruppa in base alla chiave primaria.

Guardate questa query:

SELECT 
  f.id,
  first_name, 
  last_name,
  SUM(paid_amount) AS total_paid_amount
FROM freelancers f
LEFT JOIN weekly_salary ws
ON f.id = ws.freelancers_id
WHERE country = 'Italy'
GROUP BY f.id;

Cerca di restituire l'ID, il nome, il cognome e l'importo totale dello stipendio dei freelance che sono stati pagati finora (la funzione SUM() ). Le due tabelle sono LEFT JOINed e i dati sono filtrati per mostrare solo i freelance italiani.

Quello che sembra strano è GROUP BY. Non abbiamo appena detto che tutte le colonne di SELECT devono comparire anche in GROUP BY?

Questo codice dovrebbe restituire un errore. Eseguiamolo e vediamo:

Sorprendentemente, la query non restituisce alcun errore! Perché?

L'ottimizzatore di PostgreSQL consente di raggruppare per chiave primaria (PK) e di avere le colonne non PK in SELECT. PostgreSQL capisce che le colonne PK determinano i valori di altre colonne della stessa tabella.

Nel nostro caso, la colonna f.id è la PK. È sufficiente che sia presente in GROUP BY; non è necessario includere first_name e last_name (le colonne non PK) in GROUP BY.

Tuttavia, non si dovrebbe fare affidamento su questo: l'ottimizzatore non è così intelligente! Ad esempio, non è in grado di identificare le PK per le sottoquery, le CTE e le viste.

Riscriviamo la query precedente con una sottoquery. La tabella utilizzata potrebbe essere molto più grande, quindi si potrebbe voler filtrare immediatamente solo i freelance italiani. Questo verrà fatto in una sottoquery:

SELECT 
  ifr.id,
  first_name, 
  last_name,
  SUM(paid_amount) AS total_paid_amount
FROM (SELECT *
	FROM freelancers
	WHERE country = 'Italy') AS ifr 
LEFT JOIN weekly_salary ws
ON ifr.id = ws.freelancers_id
GROUP BY ifr.id;

La parte di SELECT è la stessa di prima. Ma invece di utilizzare l'intera tabella freelancers e unirla con weekly_salaryscriviamo il comando ifr (come in 'italy_freelancers').

Questa sottoquery seleziona tutte le colonne della tabella freelancers e filtra i dati per paese in WHERE.

Poiché utilizziamo la sottoquery invece dell'intera tabella, raggruppiamo l'output in base alla tabella freelancers tabella, raggruppiamo l'output in base alla colonna id della subquery.

Questo dovrebbe funzionare, poiché la sottoquery è fondamentalmente una versione limitata della tabella. freelancers della tabella. Tuttavia, PostgreSQL restituisce un errore familiare:

Abbiamo usato questa tabella derivata come una sottoquery, quindi l'ottimizzatore di PostgreSQL non riconosce la sua PK. Se si insiste nell'avere una sottoquery, l'errore si risolve come prima: elencare tutte le altre colonne di SELECT in GROUP BY.

SELECT 
  ifr.id,
  first_name, 
  last_name,
  SUM(paid_amount) AS total_paid_amount
FROM (SELECT *
	FROM freelancers
	WHERE country = 'Italy') AS ifr 
LEFT JOIN weekly_salary ws
ON ifr.id = ws.freelancers_id
GROUP BY ifr.id, first_name, last_name;

La query ora funziona:

Abbiamo imparato a risolvere gli errori. Ora è il momento di evitarli.

Ricevere questi messaggi di errore in PostgreSQL è molto utile, in quanto ci fa imparare. Naturalmente, anche gli utenti PostgreSQL più avanzati vedranno di tanto in tanto questo (o qualsiasi altro) errore, quindi non sarà possibile evitarli completamente.

È importante sapere come correggere gli errori. Tuttavia, l'obiettivo è quello di vedere questo errore GROUP BY il più raramente possibile. Naturalmente, a volte l'ottimizzatore PostgreSQL può salvarvi. È un ottimo strumento da avere, ma si dovrebbe fare affidamento più sulla propria conoscenza che sulla capacità dell'ottimizzatore di leggere la vostra mente. Per garantire ciò, è necessario fare molta pratica per rendere automatica la scrittura delle colonne in SELECT e poi in GROUP BY.

Il sito SQL from A to Z di PostgreSQL aumenterà le vostre conoscenze e la vostra fiducia in esso.

Ci sono anche altre idee su come esercitarsi con PostgreSQL online. Utilizzatelo per diventare il vostro ottimizzatore di codice!