string_agg(expression, separator) unisce i valori di ogni gruppo in una sola stringa, e array_agg(expression) li raccoglie in un array. Metti ORDER BY dentro la chiamata per controllare l'ordine: string_agg(name, ', ' ORDER BY name).
IT restituisce Bo, Kim, Li e {6100,4800,3900}; Sales restituisce Ana, Raj e {5000,4200}. Entrambe sono normali funzioni di aggregazione come count o sum: lavorano per gruppo con GROUP BY, e sull'intera tabella senza.
Ordinare i valori
Senza ORDER BY dentro l'aggregato, i valori arrivano nell'ordine in cui arrivano le righe, che può cambiare da un'esecuzione all'altra. Un ORDER BY in fondo alla query ordina le righe del risultato, non i valori dentro ogni stringa. L'ORDER BY interno va dopo l'ultimo argomento, senza virgola:
string_agg(name, ', ' ORDER BY hired_on DESC)
array_agg(name ORDER BY score DESC, name)
Togliere i duplicati con DISTINCT
Ada ha comprato una penna due volte, ma DISTINCT la elenca una volta sola: book, pen. Con DISTINCT, l'ORDER BY può usare solo il valore aggregato. Ordinare per un'altra colonna fallisce con "in an aggregate with DISTINCT, ORDER BY expressions must appear in argument list".
Come si comportano NULL e gruppi vuoti
string_agg salta i valori NULL e restituisce Bo, Li. array_agg li tiene: {Bo,Li,NULL}. Per togliere i NULL da array_agg, aggiungi FILTER (WHERE name IS NOT NULL).
Su zero righe entrambe restituiscono NULL, non una stringa vuota o un array vuoto. Avvolgile quando chi le usa si aspetta un valore: COALESCE(string_agg(...), '') oppure COALESCE(array_agg(...), '{}').
Aggregare solo alcune righe con FILTER
FILTER (WHERE ...) limita un aggregato alle righe che corrispondono, mentre il resto della query le vede tutte:
IT ha 3 persone ed elenca Bo, Li; Sales ne ha 2 ed elenca Ana.
Affiancare una lista a ogni riga di un'altra tabella
Per mostrare ogni cliente con la lista dei suoi prodotti, aggrega in una subquery, così la query esterna non ha bisogno di GROUP BY:
SELECT c.id, c.name, c.email,
(SELECT string_agg(o.product, ', ' ORDER BY o.product)
FROM orders o
WHERE o.customer_id = c.id) AS products
FROM customers c;
I clienti senza ordini restano nel risultato, con products a NULL. Una join con GROUP BY li tiene solo se è una LEFT JOIN.
Errori comuni e varianti
- Numeri:
string_aggaccetta testo, quindistring_agg(id, ',')fallisce con "function string_agg(integer, unknown) does not exist". Fai un cast:string_agg(id::text, ','). - Primi N per gruppo: taglia l'array ordinato,
(array_agg(name ORDER BY salary DESC))[1:2]. - Output JSON:
json_aggejsonb_aggraccolgono le righe in un array JSON, ejsonb_object_agg(key, value)costruisce un oggetto; vedi JSONB. - Hai già un array:
array_to_string(arr, ', ')unisce un array esistente, eunnestlo trasforma di nuovo in righe (array). - Versione finestra: entrambe funzionano anche come funzioni finestra, per esempio
string_agg(name, ', ') OVER (PARTITION BY dept)per mostrare l'intera lista su ogni riga.
Domande frequenti
Qual è l'equivalente di GROUP_CONCAT in PostgreSQL?
string_agg(column, ','). A differenza di GROUP_CONCAT di MySQL, il separatore è obbligatorio e l'ordinamento va dentro la chiamata: string_agg(name, ', ' ORDER BY name).
Come uso string_agg con i numeri?
Converti il valore in testo: string_agg(id::text, ','). string_agg accetta solo testo (o bytea), quindi con un argomento intero fallisce con "function string_agg(integer, unknown) does not exist".
string_agg ha un limite di lunghezza?
Nessun limite a parte; il risultato è un normale valore text, che può arrivare a 1 GB. GROUP_CONCAT di MySQL tronca a group_concat_max_len, mentre PostgreSQL non tronca.
Come ottengo i primi N valori con array_agg?
Ordina dentro l'aggregato e taglia il risultato: (array_agg(name ORDER BY score DESC))[1:3] restituisce i primi tre nomi di ogni gruppo.