Функция string_agg в SQL является агрегатной функцией, которая конкатенирует (объединяет) строки из нескольких строк в одну строку с различными опциями.
Она особенно полезна, когда нужно объединить значения из столбца для каждой группы данных в отчетах, а также может сделать результат запроса более читаемым, особенно при работе с отношениями «многие ко многим». Например, если вы хотите создать отчет, показывающий все теги, использованные в каждой теме, вы можете использовать string_agg, чтобы объединить все теги для каждой темы в одну строку.
Синтаксис
Вот базовый синтаксис string_agg:
STRING_AGG(expression, delimiter)
Функция string_agg принимает два параметра:
expression: Значение, которое нужно конкатенировать.delimiter: Разделитель, который будет вставляться между строками. Разделитель может быть строкой или символом.
Примеры использования в запросах DE
Давайте рассмотрим несколько примеров запросов, использующих string_agg.
Список всех категорий на сайте
Этот запрос конкатенирует поля name и id из таблицы categories в одну строку, где каждая пара name и id разделена символом ’ : ', а каждая пара — символом ', '. Пары затем упорядочиваются по id.
SELECT
-- Функция STRING_AGG конкатенирует поля 'name' и 'id' в одну строку.
-- Каждая пара 'name' и 'id' разделена символом ' : ', а каждая пара — символом ', '.
-- Пары упорядочены по 'id'.
STRING_AGG(name || ' : ' || id, ', ' ORDER BY id) AS category_list
FROM
categories -- Данные выбираются из таблицы 'categories'.
Пример результатов:
| category_list |
|---|
| Uncategorized : 1, Site Feedback : 2, Staff : 3, Lounge : 4, Email : 5, Event : 6, Parent Category : 7, Sub Category |
Список всех тем и связанных тегов
Этот запрос выбирает id из таблицы topics и конкатенирует связанные tags для каждой темы в одну строку. Каждый тег разделяется запятой и пробелом, а теги упорядочиваются по алфавиту. Результат группируется по id темы и упорядочивается по тегам. Если у темы нет тегов, столбец tags будет содержать 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
Результатом будет список отформатированных идентификаторов тем, каждый из которых содержит строку связанных тегов, упорядоченных по алфавиту. Например:
Пример результатов:
| 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 |
Подробное объяснение с комментариями внутри кода
-- Этот SQL-запрос выбирает данные из таблицы 'topics' и связанных таблиц 'tags'.
SELECT
t.id topic_id, -- Выбираем 'id' из таблицы 'topics'.
-- Функция STRING_AGG конкатенирует связанные 'tags' для каждой темы в одну строку.
-- Каждый тег разделяется запятой и пробелом, а теги упорядочиваются по алфавиту.
string_agg(tags.name, ', ' ORDER BY tags.name) AS "tags"
FROM
topics t -- Данные выбираются из таблицы 'topics'.
-- Таблица 'topic_tags' выполняется левым соединением по 'topic_id' из таблицы 'topics'.
LEFT JOIN topic_tags tt ON tt.topic_id = t.id
-- Таблица 'tags' выполняется левым соединением по 'tag_id' из таблицы 'topic_tags'.
LEFT JOIN tags ON tags.id = tt.tag_id
GROUP BY
t.id -- Результат группируется по 'id' темы.
ORDER BY
"tags" -- Результат упорядочивается по 'tags'.
Пользователи, опубликовавшие сообщения в темах
Этот запрос вернет список id и title тем, username пользователей, опубликовавших сообщения в каждой теме, а также количество уникальных пользователей, опубликовавших сообщения в каждой теме. Результаты затем упорядочиваются по количеству пользователей в порядке убывания.
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
Пример результатов
| 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 |
Подробное объяснение с комментариями внутри кода
-- Этот SQL-запрос выбирает данные из таблиц 'topics', 'posts' и 'users'.
SELECT
topics.id AS "topic_id", -- Выбираем 'id' из таблицы 'topics'.
topics.title AS "topic_title", -- Выбираем 'title' из таблицы 'topics'.
-- Функция STRING_AGG конкатенирует username пользователей, опубликовавших сообщения в теме, в одну строку.
-- Каждый username разделяется запятой и пробелом, а username упорядочиваются по алфавиту.
STRING_AGG(DISTINCT users.username, ', ' ORDER BY users.username) AS "users_posted",
-- Считаем количество уникальных пользователей, опубликовавших сообщения в теме.
COUNT(DISTINCT users.id) AS "user_count"
FROM
topics -- Данные выбираются из таблицы 'topics'.
-- Таблица 'posts' выполняется соединением по 'topic_id' из таблицы 'topics'.
JOIN posts ON posts.topic_id = topics.id
-- Таблица 'users' выполняется соединением по 'id' из таблицы 'posts'.
JOIN users ON users.id = posts.user_id
GROUP BY
topics.id, topics.title -- Результат группируется по 'id' и 'title' темы.
ORDER BY
"user_count" DESC -- Результат упорядочивается по количеству пользователей в порядке убывания.
На этом всё для данного руководства!
Если у вас есть вопросы или примеры того, как вы использовали string_agg, пожалуйста, не стесняйтесь поделиться ими ниже. 