A função string_agg em SQL é uma função de agregação que concatena (une) strings de várias linhas em uma única string com várias opções.
É particularmente útil quando você deseja combinar valores de uma coluna para cada grupo de dados em seus relatórios, e pode tornar o resultado da sua consulta mais fácil de ler, especialmente ao lidar com relacionamentos muitos para muitos. Por exemplo, se você quiser criar um relatório que mostre todas as tags usadas em cada tópico, você pode usar string_agg para concatenar todas as tags de cada tópico em uma única string.
Sintaxe
Aqui está a sintaxe básica de string_agg:
STRING_AGG(expression, delimiter)
A função string_agg aceita dois parâmetros:
expression: O valor a ser concatenado.delimiter: O delimitador a ser inserido entre as strings. O delimitador pode ser uma string ou um caractere.
Exemplo de Uso em Consultas DE
Vamos dar uma olhada em alguns exemplos de consultas que utilizam string_agg
Listar Todas as Categorias em um Site
Esta consulta concatena os campos name e id da tabela categories em uma única string, com cada par name e id separado por ’ : ', e cada par separado por ', '. Os pares são então ordenados por id.
SELECT
-- A função STRING_AGG concatena os campos 'name' e 'id' em uma única string.
-- Cada par 'name' e 'id' é separado por ' : ', e cada par é separado por ', '.
-- Os pares são ordenados por 'id'.
STRING_AGG(name || ' : ' || id, ', ' ORDER BY id) AS category_list
FROM
categories -- Os dados são selecionados da tabela 'categories'.
Exemplo de Resultados:
| category_list |
|---|
| Uncategorized : 1, Site Feedback : 2, Staff : 3, Lounge : 4, Email : 5, Event : 6, Parent Category : 7, Sub Category |
Listar todos os Tópicos e Tags Associadas
Esta consulta seleciona o id da tabela topics e concatena as tags associadas para cada tópico em uma única string. Cada tag é separada por uma vírgula e um espaço, e as tags são ordenadas alfabeticamente. O resultado é agrupado pelo id do tópico e ordenado pelas tags. Se um tópico não tiver tags, a coluna tags será 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
O resultado será uma lista de IDs de tópicos formatados, cada um com uma string de tags associadas ordenadas alfabeticamente. Por exemplo:
Exemplo de Resultados:
| topic | tags |
|---|---|
| Welcome to the Lounge (3) | tag1, tag2 |
| A Very Safe for work post (3) | tag3, tag4, tag,5 |
| This is a blog tagged post (4) | tag3 |
| About the Lounge category (3) | tag5 |
| Welcome to your 14 day standard hosting trial! | NULL |
Explicação Detalhada com Comentários Inline
-- Esta instrução SQL está selecionando dados da tabela 'topics' e 'tags' associadas.
SELECT
t.id topic_id, -- Seleciona o 'id' da tabela 'topics'.
-- A função STRING_AGG concatena as 'tags' associadas para cada tópico em uma única string.
-- Cada tag é separada por uma vírgula e um espaço, e as tags são ordenadas alfabeticamente.
string_agg(tags.name, ', ' ORDER BY tags.name) AS "tags"
FROM
topics t -- Os dados são selecionados da tabela 'topics'.
-- A tabela 'topic_tags' é unida à tabela 'topics' usando 'topic_id'.
LEFT JOIN topic_tags tt ON tt.topic_id = t.id
-- A tabela 'tags' é unida à tabela 'topic_tags' usando 'tag_id'.
LEFT JOIN tags ON tags.id = tt.tag_id
GROUP BY
t.id -- O resultado é agrupado pelo 'id' do tópico.
ORDER BY
"tags" -- O resultado é ordenado por 'tags'.
Usuários que Postaram em Tópicos
Esta consulta retornará uma lista de ids e títulos de tópicos, os nomes de usuário dos usuários que postaram em cada tópico e a contagem de usuários distintos que postaram em cada tópico. Os resultados são então ordenados pela contagem de usuários, em ordem 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
Exemplo de Resultados
| topic | topic_title | users_posted | user_count |
|---|---|---|---|
| Lets make a topic (10) | Lets make a topic | anonymous, user1, user2, user3 | 4 |
| Another Topic (3) | Another Topic | user3, user4, user5 | 3 |
| Discobot test (2) | Discobot test | user6 | 1 |
Explicação Detalhada com Comentários Inline
-- Esta instrução SQL está selecionando dados das tabelas 'topics', 'posts' e 'users'.
SELECT
topics.id AS "topic_id", -- Seleciona o 'id' da tabela 'topics'.
topics.title AS "topic_title", -- Seleciona o 'title' da tabela 'topics'.
-- A função STRING_AGG concatena os nomes de usuário dos usuários que postaram no tópico em uma única string.
-- Cada nome de usuário é separado por uma vírgula e um espaço, e os nomes de usuário são ordenados alfabeticamente.
STRING_AGG(DISTINCT users.username, ', ' ORDER BY users.username) AS "users_posted",
-- Conta o número de usuários distintos que postaram no tópico.
COUNT(DISTINCT users.id) AS "user_count"
FROM
topics -- Os dados são selecionados da tabela 'topics'.
-- A tabela 'posts' é unida à tabela 'topics' usando 'topic_id'.
JOIN posts ON posts.topic_id = topics.id
-- A tabela 'users' é unida à tabela 'posts' usando 'id'.
JOIN users ON users.id = posts.user_id
GROUP BY
topics.id, topics.title -- O resultado é agrupado pelo 'id' e 'title' do tópico.
ORDER BY
"user_count" DESC -- O resultado é ordenado pelo número de usuários, em ordem decrescente.
É isso para este tutorial!
Se você tiver alguma dúvida ou exemplos de como usou string_agg, sinta-se à vontade para compartilhá-los abaixo. 