PostgreSQL group concat: аналог string_agg и примеры использования

[kirilljsx]

В PostgreSQL нет прямой функции GROUP_CONCAT, как в MySQL, но есть отличный аналог - string_agg. Эта функция позволяет объединять строки из группы в одну с нужным разделителем. Полезно для отчетов, где нужно собрать списки имен, тегов или ID в компактный вид.

С ее помощью решаются задачи группировки данных без лишних JOIN или подзапросов. Вы получите чистые результаты для дашбордов или экспорта. Давайте разберем, как это работает на примерах с таблицами сотрудников.

Основы string_agg в PostgreSQL

Функция string_agg собирает значения из столбца в строку, разделяя их указанным символом. Она работает с GROUP BY, чтобы агрегировать по ключу. В отличие от MySQL GROUP_CONCAT, string_agg более гибкая и поддерживает ORDER BY внутри.

Рассмотрим таблицу employees: id, name, age, salary. Без группировки она вернет все имена через запятую. С GROUP BY по возрасту - списки имен для каждого возраста. Это упрощает анализ, когда строки нужно свернуть в одну.

• Базовый синтаксис: string_agg(column, 'разделитель')
• С группировкой: Добавьте GROUP BY для категорий
• С сортировкой: string_agg(column ORDER BY sort_col, 'разделитель') - значения идут в нужном порядке

Пример запроса	Результат	Описание
SELECT string_agg(name, ', ') FROM employees	user1, user2, user3, user4, user5, user6	Все имена в одной строке
SELECT age, string_agg(name, ', ') FROM employees GROUP BY age	23: user1, user2, user3 27: user5	Группировка по возрасту

Альтернатива через array_agg

Если string_agg не справляется с особыми случаями, используйте array_agg с array_to_string. Array_agg собирает значения в массив, а array_to_string преобразует его в строку. Это полезно для сложных типов или когда нужен контроль над NULL.

Представьте many-to-many связь: таблица section_firms с sid и fid. Array_agg соберет sid в массив по fid, потом склеит через запятую. Такой подход переносим между проектами и работает быстрее на больших данных. В PostgreSQL это стандартный паттерн.

• Создание агрегата (опционально): CREATE AGGREGATE array_accum(anyelement) (... )
• Запрос: array_to_string(array_agg(sid ORDER BY sid), ',')
• Преимущества: Массивы позволяют фильтровать дубли или сортировать независимо

Функция	Когда использовать	Пример
string_agg	Стандартное объединение строк	string_agg(name, ‘-’)
array_agg + array_to_string	Сложная логика, NULL, порядок	array_to_string(array_agg(DISTINCT name), ', ')
GROUP_CONCAT (MySQL)	Только для сравнения	Не в PostgreSQL

Сравнение с MySQL и миграция

MySQL GROUP_CONCAT склеивает строки с SEPARATOR, но имеет лимит на длину (по умолчанию 1024 байта). В PostgreSQL string_agg таких жестких ограничений нет, плюс ORDER BY внутри агрегата. При миграции просто замените GROUP_CONCAT на string_agg.

Возьмем таблицу с товарами: SELECT category, string_agg(product_name, '; ') FROM items GROUP BY category. Получим списки продуктов по категориям. Добавьте DISTINCT для уникальности или WHERE для фильтра. Это экономит строки в результате и упрощает фронтенд.

Важный нюанс: string_agg игнорирует NULL по умолчанию, но array_agg их включает - проверяйте данные.

• Лимиты: PostgreSQL - group_concat_limit регулируется, MySQL - фиксировано
• ORDER BY: В string_agg указывается внутри, в MySQL - отдельно
• DISTINCT: string_agg(DISTINCT col, ',') - убирает повторы

Сценарий	PostgreSQL	MySQL
Базовое	string_agg(name, ‘,’)	GROUP_CONCAT(name)
С порядком	string_agg(name ORDER BY id, ‘,’)	GROUP_CONCAT(name ORDER BY id)
Разделитель	string_agg(name, ‘-’)	GROUP_CONCAT(name SEPARATOR ‘-’)

Практические примеры запросов

Давайте применим на реальных данных. Таблица employees с 6 записями: возьмем подмножество по id 3-5 - user3,user4,user5. Или группировку по salary: для 500 - список имен.

Эти запросы решают задачи отчетов, где нужно JSON-подобные списки или CSV-строки. Комбинируйте с оконными функциями для продвинутого анализа. Не забывайте индексы на GROUP BY столбцах для скорости.

1. Фильтр + агрегат: SELECT string_agg(name, ', ') FROM employees WHERE age > 25
2. С DISTINCT: SELECT salary, string_agg(DISTINCT name, ', ') FROM employees GROUP BY salary
3. Полный: SELECT age, salary, string_agg(name ORDER BY id, ' | ') FROM employees GROUP BY age, salary

Запрос	Вывод
По id 3-5	user3, user4, user5
По age=23	user1, user2, user3
С ‘-’	user1-user2-user3

Тонкости производительности и ошибок

На больших таблицах string_agg нагружает память - лимитируйте ROWS или используйте подзапросы. Ошибка ‘string_agg exceeds max length’? Увеличьте work_mem в postgresql.conf. Для NULL используйте COALESCE.

Тестируйте на выборках: EXPLAIN ANALYZE покажет план. Array_agg может быть быстрее на простых случаях. Это базовые инструменты для бэкенда.

• Оптимизация: Индексы на GROUP BY, LIMIT в подзапросах
• NULL: string_agg(COALESCE(col, ''), ',')
• Длина: SET work_mem = '256MB' перед запросом

Когда array_to_string выручает больше

String_agg идеальна для строк, но для массивов или JSON - array_agg с array_to_string. Соберете ID в строку для IN в следующем запросе. Или экспорт в CSV без парсинга.

В продакшене комбинируйте с CTE: сначала фильтр, потом агрегат. Подумать стоит о кастомных агрегатах для повторяющихся задач - они ускоряют разработку.

Масштабирование на сложные случаи

Для группировки похожих объектов по атрибутам: сначала найди группы, потом string_agg атрибутов. Это для рекомендаций или кластеризации. Легко интегрируется с Python или JS на фронте.