string_agg(expression, separator) склеивает значения каждой группы в одну строку, а array_agg(expression) собирает их в массив. Чтобы управлять порядком, поставьте ORDER BY внутрь вызова: string_agg(name, ', ' ORDER BY name).
Для IT получится Bo, Kim, Li и {6100,4800,3900}, для Sales Ana, Raj и {5000,4200}. Обе функции обычные агрегатные, как count или sum: с GROUP BY они работают по группам, без него по всей таблице.
Порядок значений
Без ORDER BY внутри агрегата значения идут в том порядке, в каком приходят строки, и он может меняться от запуска к запуску. ORDER BY в конце запроса сортирует строки результата, а не значения внутри каждой строки. Внутренний ORDER BY пишется после последнего аргумента, без запятой:
string_agg(name, ', ' ORDER BY hired_on DESC)
array_agg(name ORDER BY score DESC, name)
Удаление дубликатов через DISTINCT
Ada купила ручку дважды, но DISTINCT выводит её один раз: book, pen. С DISTINCT в ORDER BY можно использовать только агрегируемое значение. Сортировка по другому столбцу падает с ошибкой «in an aggregate with DISTINCT, ORDER BY expressions must appear in argument list».
Как ведут себя NULL и пустые группы
string_agg пропускает значения NULL и возвращает Bo, Li. array_agg их сохраняет: {Bo,Li,NULL}. Чтобы убрать NULL из array_agg, добавьте FILTER (WHERE name IS NOT NULL).
На нуле строк обе функции возвращают NULL, а не пустую строку или пустой массив. Оборачивайте их, если вызывающий код ждёт значение: COALESCE(string_agg(...), '') или COALESCE(array_agg(...), '{}').
Агрегация только части строк через FILTER
FILTER (WHERE ...) ограничивает одну агрегатную функцию подходящими строками, а остальная часть запроса видит все строки:
В IT 3 человека, а в списке Bo, Li; в Sales 2 человека, а в списке Ana.
Список для каждой строки другой таблицы
Чтобы показать каждого клиента со списком его товаров, агрегируйте в подзапросе, тогда внешнему запросу не нужен GROUP BY:
SELECT c.id, c.name, c.email,
(SELECT string_agg(o.product, ', ' ORDER BY o.product)
FROM orders o
WHERE o.customer_id = c.id) AS products
FROM customers c;
Клиенты без заказов остаются в результате, а products у них NULL. Соединение с GROUP BY сохранит их, только если это LEFT JOIN.
Частые ошибки и варианты
- Числа:
string_aggпринимает текст, поэтомуstring_agg(id, ',')падает с ошибкой «function string_agg(integer, unknown) does not exist». Приведите тип:string_agg(id::text, ','). - Первые N в группе: возьмите срез упорядоченного массива,
(array_agg(name ORDER BY salary DESC))[1:2]. - Результат в JSON:
json_aggиjsonb_aggсобирают строки в массив JSON, аjsonb_object_agg(key, value)строит объект; см. JSONB. - Массив уже есть:
array_to_string(arr, ', ')склеивает существующий массив, аunnestпревращает его обратно в строки (массивы). - Оконная версия: обе функции работают и как оконные, например
string_agg(name, ', ') OVER (PARTITION BY dept), чтобы показать весь список в каждой строке.
Часто задаваемые вопросы
Какой аналог GROUP_CONCAT в PostgreSQL?
string_agg(column, ','). В отличие от GROUP_CONCAT в MySQL, разделитель обязателен, а порядок указывается внутри вызова: string_agg(name, ', ' ORDER BY name).
Как использовать string_agg с числами?
Приведите значение к тексту: string_agg(id::text, ','). string_agg принимает только текст (или bytea), поэтому с целочисленным аргументом падает с ошибкой «function string_agg(integer, unknown) does not exist».
Есть ли у string_agg ограничение длины?
Отдельного ограничения нет; результат это обычное значение text, которое вмещает до 1 ГБ. GROUP_CONCAT в MySQL обрезает результат по group_concat_max_len, а PostgreSQL ничего не обрезает.
Как получить первые N значений через array_agg?
Отсортируйте внутри агрегата и возьмите срез результата: (array_agg(name ORDER BY score DESC))[1:3] вернёт три первых имени в каждой группе.