STRING_AGG in SQL: Concatenate Grouped Rows with a Delimiter and ORDER BY

Иногда вместо «строки на каждое значение» нужна одна ячейка: список тегов через запятую, перечень email'ов в заказе, имена участников проекта. Это задача агрегации строк — взять все значения внутри группы и склеить их в одну строку через разделитель. В PostgreSQL для этого есть STRING_AGG, в MySQL — GROUP_CONCAT, в ClickHouse — связка arrayStringConcat(groupArray(...)). Разберём базу, сортировку, дедупликацию и типичные грабли.

Базовый STRING_AGG в PostgreSQL

Возьмём схему интернет-магазина: users, orders и теги товаров. Простейший случай — собрать список email'ов всех пользователей в одну строку:

SELECT STRING_AGG(email, ', ') AS all_emails
FROM users;

STRING_AGG(expression, delimiter) принимает два аргумента: что склеивать и чем разделять. Оба должны быть text (или совместимыми типами). Если значение не строковое, приведите его явно через ::text или CAST:

SELECT STRING_AGG(id::text, ',') AS user_ids
FROM users;

Чаще всего агрегация идёт вместе с GROUP BY. Например, для каждого заказа собрать названия купленных товаров:

SELECT
  o.id AS order_id,
  STRING_AGG(oi.product_name, ', ') AS products
FROM orders o
JOIN order_items oi ON oi.order_id = o.id
GROUP BY o.id;

Результат: одна строка на заказ, а в колонке products — «Mouse, Keyboard, Monitor». STRING_AGG, как и любая агрегатная функция, пропускает NULL: строки с product_name IS NULL просто не попадут в результат, разделитель вокруг них не появится. Это удобно, но иногда маскирует пропуски в данных.

ORDER BY внутри агрегата

Без явного порядка СУБД склеивает значения в произвольном порядке — он может меняться от запроса к запросу. Если порядок важен (а в отчётах он почти всегда важен), добавьте ORDER BY внутри скобок агрегата:

SELECT
  o.id AS order_id,
  STRING_AGG(oi.product_name, ', ' ORDER BY oi.product_name) AS products
FROM orders o
JOIN order_items oi ON oi.order_id = o.id
GROUP BY o.id;

Сортировать можно и по другому столбцу — не обязательно по тому, что склеиваем. Часто удобно собрать товары в порядке добавления:

SELECT
  o.id AS order_id,
  STRING_AGG(oi.product_name, ', ' ORDER BY oi.added_at DESC) AS products
FROM orders o
JOIN order_items oi ON oi.order_id = o.id
GROUP BY o.id;

Уберём дубликаты через DISTINCT — он ставится прямо перед выражением. Но есть ограничение: с DISTINCT сортировать можно только по самому склеиваемому выражению.

SELECT
  customer_country,
  STRING_AGG(DISTINCT currency, ', ' ORDER BY currency) AS currencies
FROM orders
GROUP BY customer_country;

MySQL: GROUP_CONCAT

В MySQL аналог называется GROUP_CONCAT, и синтаксис у него свой: разделитель задаётся через SEPARATOR, а сортировка — через ORDER BY внутри функции.

SELECT
  o.id AS order_id,
  GROUP_CONCAT(oi.product_name ORDER BY oi.product_name SEPARATOR ', ') AS products
FROM orders o
JOIN order_items oi ON oi.order_id = o.id
GROUP BY o.id;

DISTINCT тоже поддерживается: GROUP_CONCAT(DISTINCT currency SEPARATOR ', '). По умолчанию разделитель — запятая, поэтому SEPARATOR можно опустить, если он вас устраивает.

• Главная ловушка MySQL: результат обрезается по group_concat_max_len (по умолчанию 1024 байта), причём молча, без ошибки. На длинных списках вы получите урезанную строку. Поднимите лимит на сессию: SET SESSION group_concat_max_len = 1000000;.

ClickHouse: arrayStringConcat(groupArray())

В ClickHouse нет прямого аналога STRING_AGG — используется композиция из двух функций. Сначала groupArray() собирает значения группы в массив, затем arrayStringConcat() склеивает массив в строку с разделителем:

SELECT
  order_id,
  arrayStringConcat(groupArray(product_name), ', ') AS products
FROM order_items
GROUP BY order_id;

Для сортировки используйте arraySort поверх собранного массива, а для уникальности — groupUniqArray вместо groupArray:

SELECT
  customer_country,
  arrayStringConcat(arraySort(groupUniqArray(currency)), ', ') AS currencies
FROM orders
GROUP BY customer_country;

Помните, что arrayStringConcat работает только со строками: числовые поля приводите через toString(...) до сборки в массив, иначе получите ошибку типа.

Гранулярность и подводные камни

Главная общая ловушка — дублирование из-за JOIN. Если в orders приджойнить ещё и таблицу платежей, каждая строка заказа размножится по числу платежей, и STRING_AGG без DISTINCT повторит товары несколько раз. Лечится либо DISTINCT, либо агрегацией в подзапросе до соединения:

SELECT
  o.id,
  pr.products,
  SUM(p.amount) AS paid
FROM orders o
JOIN payments p ON p.order_id = o.id
JOIN (
  SELECT order_id, STRING_AGG(product_name, ', ' ORDER BY product_name) AS products
  FROM order_items
  GROUP BY order_id
) pr ON pr.order_id = o.id
GROUP BY o.id, pr.products;

Коротко о главном:

• STRING_AGG и аналоги пропускают NULL — оберните в COALESCE, если пропуски значимы.
• Без ORDER BY внутри агрегата порядок не гарантирован.
• MySQL молча режет результат по group_concat_max_len.
• Следите за гранулярностью JOIN, иначе получите дубли в склеенной строке.