La funzione string_agg in SQL è una funzione di aggregazione che concatena (unisce) stringhe da più righe in un’unica stringa con varie opzioni.
È particolarmente utile quando si desidera combinare valori da una colonna per ciascun gruppo di dati nei tuoi report e può rendere il risultato della query più facile da leggere, specialmente quando si gestiscono relazioni molti-a-molti. Ad esempio, se si desidera creare un report che mostri tutti i tag utilizzati in ciascun argomento, è possibile utilizzare string_agg per concatenare tutti i tag per ciascun argomento in un’unica stringa.
Sintassi
Ecco la sintassi di base di string_agg:
STRING_AGG(espressione, delimitatore)
La funzione string_agg accetta due parametri:
espressione: Il valore da concatenare.delimitatore: Il delimitatore da inserire tra le stringhe. Il delimitatore può essere una stringa o un carattere.
Esempio di utilizzo nelle query DE
Diamo un’occhiata ad alcuni esempi di query che utilizzano string_agg
Elenca tutte le categorie su un sito
Questa query concatena i campi name e id dalla tabella categories in un’unica stringa, con ogni coppia name e id separata da ’ : ', e ogni coppia separata da ', '. Le coppie sono quindi ordinate per id.
SELECT
-- La funzione STRING_AGG concatena i campi 'name' e 'id' in un'unica stringa.
-- Ogni coppia 'name' e 'id' è separata da ' : ', e ogni coppia è separata da ', '.
-- Le coppie sono ordinate per 'id'.
STRING_AGG(name || ' : ' || id, ', ' ORDER BY id) AS category_list
FROM
categories -- I dati vengono selezionati dalla tabella 'categories'.
Esempio di risultati:
| category_list |
|---|
| Non categorizzato : 1, Feedback sito : 2, Staff : 3, Lounge : 4, Email : 5, Evento : 6, Categoria padre : 7, Sottocategoria |
Elenca tutti gli argomenti e i tag associati
Questa query seleziona l’id dalla tabella topics e concatena i tags associati per ciascun argomento in un’unica stringa. Ogni tag è separato da una virgola e uno spazio, e i tag sono ordinati alfabeticamente. Il risultato è raggruppato per l’id dell’argomento e ordinato per tag. Se un argomento non ha tag, la colonna tags sarà NULL
SELECT t.id topic_id,
string_agg(tags.name, ', ' ORDER BY tags.name) AS "tags"
FROM topics t
LEFT JOIN topic_tags tt ON tt.topic_id = t.id
LEFT JOIN tags ON tags.id = tt.tag_id
GROUP BY t.id
ORDER BY tags
Il risultato sarà un elenco di ID argomento formattati, ciascuno con una stringa di tag associati ordinati alfabeticamente. Ad esempio:
Esempio di risultati:
| topic | tags |
|---|---|
| Benvenuto nella Lounge (3) | tag1, tag2 |
| Un post molto sicuro per il lavoro (3) | tag3, tag4, tag,5 |
| Questo è un post di blog (4) | tag3 |
| Informazioni sulla categoria Lounge (3) | tag5 |
| Benvenuto nella tua prova standard di 14 giorni! | NULL |
Spiegazione dettagliata con commenti inline
-- Questa istruzione SQL seleziona dati dalla tabella 'topics' e dai 'tags' associati.
SELECT
t.id topic_id, -- Seleziona l''id' dalla tabella 'topics'.
-- La funzione STRING_AGG concatena i 'tags' associati per ciascun argomento in un'unica stringa.
-- Ogni tag è separato da una virgola e uno spazio, e i tag sono ordinati alfabeticamente.
string_agg(tags.name, ', ' ORDER BY tags.name) AS "tags"
FROM
topics t -- I dati vengono selezionati dalla tabella 'topics'.
-- La tabella 'topic_tags' viene unita a sinistra in base a 'topic_id' dalla tabella 'topics'.
LEFT JOIN topic_tags tt ON tt.topic_id = t.id
-- La tabella 'tags' viene unita a sinistra in base a 'tag_id' dalla tabella 'topic_tags'.
LEFT JOIN tags ON tags.id = tt.tag_id
GROUP BY
t.id -- Il risultato è raggruppato per l''id' dell'argomento.
ORDER BY
"tags" -- Il risultato è ordinato per 'tags'.
Utenti che hanno postato negli argomenti
Questa query restituirà un elenco di id e titoli di topic, gli username degli utenti che hanno postato in ciascun argomento e il conteggio di utenti distinti che hanno postato in ciascun argomento. I risultati sono quindi ordinati per conteggio utenti, in ordine decrescente.
SELECT
topics.id AS "topic_id",
topics.title AS "topic_title",
STRING_AGG(DISTINCT users.username, ', ' ORDER BY users.username) AS "users_posted",
COUNT(DISTINCT users.id) AS "user_count"
FROM
topics
JOIN
posts ON posts.topic_id = topics.id
JOIN
users ON users.id = posts.user_id
GROUP BY
topics.id, topics.title
ORDER BY
"user_count" DESC
Esempio di risultati
| topic | topic_title | users_posted | user_count |
|---|---|---|---|
| Creiamo un argomento (10) | Creiamo un argomento | anonimo, utente1, utente2, utente3 | 4 |
| Altro argomento (3) | Altro argomento | utente3, utente4, utente5 | 3 |
| Test Discobot (2) | Test Discobot | utente6 | 1 |
Spiegazione dettagliata con commenti inline
-- Questa istruzione SQL seleziona dati dalle tabelle 'topics', 'posts' e 'users'.
SELECT
topics.id AS "topic_id", -- Seleziona l''id' dalla tabella 'topics'.
topics.title AS "topic_title", -- Seleziona il 'title' dalla tabella 'topics'.
-- La funzione STRING_AGG concatena gli username degli utenti che hanno postato nell'argomento in un'unica stringa.
-- Ogni username è separato da una virgola e uno spazio, e gli username sono ordinati alfabeticamente.
STRING_AGG(DISTINCT users.username, ', ' ORDER BY users.username) AS "users_posted",
-- Conta il numero di utenti distinti che hanno postato nell'argomento.
COUNT(DISTINCT users.id) AS "user_count"
FROM
topics -- I dati vengono selezionati dalla tabella 'topics'.
-- La tabella 'posts' viene unita in base a 'topic_id' dalla tabella 'topics'.
JOIN posts ON posts.topic_id = topics.id
-- La tabella 'users' viene unita in base a 'id' dalla tabella 'posts'.
JOIN users ON users.id = posts.user_id
GROUP BY
topics.id, topics.title -- Il risultato è raggruppato per l''id' e 'title' dell'argomento.
ORDER BY
"user_count" DESC -- Il risultato è ordinato per numero di utenti, in ordine decrescente.
Questo è tutto per questo tutorial!
Se hai domande o esempi su come hai utilizzato string_agg, non esitare a condividerli qui sotto. 