Postgresql GROUP_CONCAT эквивалент?

191

У меня есть таблица, и я бы хотел потянуть одну строку на один идентификатор с конкатенированными значениями полей.

В моей таблице, например, у меня есть следующее:

TM67 | 4  | 32556
TM67 | 9  | 98200
TM67 | 72 | 22300
TM99 | 2  | 23009
TM99 | 3  | 11200

И я хотел бы вывести:

TM67 | 4,9,72 | 32556,98200,22300
TM99 | 2,3    | 23009,11200

В MySQL мне удалось использовать агрегатную функцию GROUP_CONCAT, но это, похоже, не работает здесь... Есть ли эквивалент для PostgreSQL или другой способ выполнить это?

Показать ещё 2 комментария
Теги:
group-concat
string-aggregation

6 ответов

201
Лучший ответ

Это, вероятно, хорошая отправная точка (только версия 8.4+):

SELECT id_field, array_agg(value_field1), array_agg(value_field2)
FROM data_table
GROUP BY id_field

array_agg возвращает массив, но вы можете CAST, чтобы текст и редактировать по мере необходимости (см. пояснения ниже).

До версии 8.4 вы должны сами определить ее перед использованием:

CREATE AGGREGATE array_agg (anyelement)
(
    sfunc = array_append,
    stype = anyarray,
    initcond = '{}'
);

(перефразировано из документации PostgreSQL)

Разъяснения:

  • Результатом приведения массива в текст является то, что результирующая строка начинается и заканчивается фигурными фигурными скобками. Эти скобки необходимо удалить каким-либо способом, если они не желательны.
  • Листинг ANYARRAY to TEXT лучше всего имитирует вывод CSV, поскольку элементы, содержащие встроенные запятые, имеют двойные кавычки в выводе в стандартном стиле CSV. Ни array_to_string(), ни string_agg() (добавленная функция "group_concat" в 9.1) не содержат строки со встроенными запятыми, что приводит к некорректному количеству элементов в результирующем списке.
  • Новая функция 9.1 string_agg() НЕ передает первые результаты в ТЕКСТ. Поэтому "string_agg (value_field)" генерирует ошибку, если value_field является целым числом. "string_agg (value_field:: text)" потребуется. Метод array_agg() требует только одного приведения после агрегации (а не приведения на значение).
Поделиться
  • 0
    И в 9.0 у вас будет listagg ()
  • 5
    Чтобы получить CSV, запрос должен быть: SELECT id_field, array_to_string (array_agg (value_field1), ','), array_to_string (array_agg (value_field2), ',') FROM data_table GROUP BY id_field
Показать ещё 2 комментария
219

Начиная с 9.0 это еще проще:

SELECT id, 
       string_agg(some_column, ',')
FROM the_table
GROUP BY id
Поделиться
  • 26
    Обратите внимание, что синтаксис также позволяет вам указывать порядок значений в строке (или массиве, используя array_agg ), например, string_agg(some_column, ',' ORDER BY some_column) или даже string_agg(surname || ', ' || forename, '; ' ORDER BY surname, forename)
  • 3
    Удивительно, что distinct работает с string_agg, так что можно использовать string_agg(distinct some_solumn, ',')
37 
SELECT array_to_string(array(SELECT a FROM b),', ');

Будет делать также.

Поделиться
  • 4
    Этот также работает до 9.0 - протестировал с 8.4.5
  • 0
    Отлично !!! Работает для меня. Teste также Postgres 8.3.6. Танки!
Показать ещё 1 комментарий
12

Попробуйте вот так:

select field1, array_to_string(array_agg(field2), ',')
from table1
group by field1;
Поделиться
0

Мое предложение в postgresql

SELECT cpf || ';' || nome || ';' || telefone  
FROM (
      SELECT cpf
            ,nome
            ,STRING_AGG(CONCAT_WS( ';' , DDD_1, TELEFONE_1),';') AS telefone 
      FROM (
            SELECT DISTINCT * 
            FROM temp_bd 
            ORDER BY cpf DESC ) AS y
      GROUP BY 1,2 ) AS x
Поделиться
  • 1
    Почему вы делаете ORDER BY во внутреннем запросе? В любом случае заказ не потеряется?
-2

и версия для работы с типом массива:

select
  array_to_string(
    array(select distinct unnest(zip_codes) from table),
    ', '
);
Поделиться
  • 0
    Двойной ответ, @max_spy сказал то же самое пять лет назад
  • 0
    @ EmilVikström: вы имеете право ошибаться, но читайте внимательно. Он не только отличается, но я привел пример, который работает с типом массива - например, zip_codes - character varying(5)[] . Кроме того, я проверил, что для моей цели - необходимо использовать unnest, в противном случае вы увидите ERROR: cannot accumulate arrays of different dimensionality .

Ещё вопросы

Сообщество Overcoder
Наверх
Меню