Вопрос:

Как конкатенировать строки поля строки в запросе PostgreSQL?

sql postgresql group-by string-aggregation

260428 просмотра

12 ответа

2089 Репутация автора

Я ищу способ объединить строки поля внутри группы по запросу. Так, например, у меня есть таблица:

ID   COMPANY_ID   EMPLOYEE
1    1            Anna
2    1            Bill
3    2            Carol
4    2            Dave

и я хотел сгруппировать company_id, чтобы получить что-то вроде:

COMPANY_ID   EMPLOYEE
1            Anna, Bill
2            Carol, Dave

В mySQL есть встроенная функция для этого group_concat

Автор: Guy C Источник Размещён: 04.09.2008 02:27

Ответы (12)


13 плюса

2089 Репутация автора

Я не отвечаю за ответ, потому что нашел его после некоторых поисков:

То, что я не знал, это то, что PostgreSQL позволяет вам определять свои собственные агрегатные функции с помощью CREATE AGGREGATE

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

CREATE AGGREGATE textcat_all(
  basetype    = text,
  sfunc       = textcat,
  stype       = text,
  initcond    = ''
);

SELECT company_id, textcat_all(employee || ', ')
FROM mytable
GROUP BY company_id;
Автор: Guy C Размещён: 04.09.2008 02:35

432 плюса

19675 Репутация автора

Решение

PostgreSQL 9.0 или новее:

В последних версиях Postgres (с конца 2010 года) есть string_agg(expression, delimiter)функция, которая будет выполнять именно то, что задал вопрос, даже если вы укажете строку разделителя:

SELECT company_id, string_agg(employee, ', ')
FROM mytable
GROUP BY company_id;

Postgres 9.0 также добавил возможность указывать ORDER BYпредложение в любом агрегированном выражении ; в противном случае порядок не определен. Итак, теперь вы можете написать:

SELECT company_id, string_agg(employee, ', ' ORDER BY employee)
FROM mytable
GROUP BY company_id;

Или действительно:

SELECT string_agg(actor_name, ', ' ORDER BY first_appearance)

PostgreSQL 8.4 или новее:

PostgreSQL 8.4 (в 2009 году) представил агрегированную функцию,array_agg(expression) которая объединяет значения в массив. Затем array_to_string()можно использовать для получения желаемого результата:

SELECT company_id, array_to_string(array_agg(employee), ', ')
FROM mytable
GROUP BY company_id;

string_agg для версий до 9.0:

В случае, если кто-то столкнется с этим, ищет подходящую прокладку для баз данных до 9.0, можно реализовать все, string_aggкроме ORDER BYпредложения.

Таким образом, с нижеследующим определением это должно работать так же, как в DB 9.x Postgres:

SELECT string_agg(name, '; ') AS semi_colon_separated_names FROM things;

Но это будет синтаксическая ошибка:

SELECT string_agg(name, '; ' ORDER BY name) AS semi_colon_separated_names FROM things;
--> ERROR: syntax error at or near "ORDER"

Протестировано на PostgreSQL 8.3.

CREATE FUNCTION string_agg_transfn(text, text, text)
    RETURNS text AS 
    $$
        BEGIN
            IF $1 IS NULL THEN
                RETURN $2;
            ELSE
                RETURN $1 || $3 || $2;
            END IF;
        END;
    $$
    LANGUAGE plpgsql IMMUTABLE
COST 1;

CREATE AGGREGATE string_agg(text, text) (
    SFUNC=string_agg_transfn,
    STYPE=text
);

Пользовательские варианты (все версии Postgres)

До 9.0 не было встроенной агрегированной функции для конкатенации строк. Простейшая пользовательская реализация ( предложенная Вайдой Габо в этом списке рассылки , среди многих других) заключается в использовании встроенной textcatфункции (которая лежит за ||оператором):

CREATE AGGREGATE textcat_all(
  basetype    = text,
  sfunc       = textcat,
  stype       = text,
  initcond    = ''
);

Вот CREATE AGGREGATEдокументация.

Это просто склеивает все строки вместе без разделителя. Чтобы получить «,», вставленный между ними, не имея его в конце, вы можете создать свою собственную функцию конкатенации и заменить ее на «textcat» выше. Вот один, который я собрал и испытал на 8.3.12:

CREATE FUNCTION commacat(acc text, instr text) RETURNS text AS $$
  BEGIN
    IF acc IS NULL OR acc = '' THEN
      RETURN instr;
    ELSE
      RETURN acc || ', ' || instr;
    END IF;
  END;
$$ LANGUAGE plpgsql;

Эта версия будет выдавать запятую, даже если значение в строке равно null или пустое, поэтому вы получаете результат следующим образом:

a, b, c, , e, , g

Если вы хотите удалить дополнительные запятые для вывода этого:

a, b, c, e, g

Затем добавьте ELSIFпроверку функции следующим образом:

CREATE FUNCTION commacat_ignore_nulls(acc text, instr text) RETURNS text AS $$
  BEGIN
    IF acc IS NULL OR acc = '' THEN
      RETURN instr;
    ELSIF instr IS NULL OR instr = '' THEN
      RETURN acc;
    ELSE
      RETURN acc || ', ' || instr;
    END IF;
  END;
$$ LANGUAGE plpgsql;
Автор: Neall Размещён: 04.09.2008 03:03

7 плюса

25223 Репутация автора

Как уже упоминалось, создание собственной агрегатной функции - это правильная вещь. Вот моя совокупная функция сцепления (вы можете найти детали на французском языке ):

CREATE OR REPLACE FUNCTION concat2(text, text) RETURNS text AS '
    SELECT CASE WHEN $1 IS NULL OR $1 = \'\' THEN $2
            WHEN $2 IS NULL OR $2 = \'\' THEN $1
            ELSE $1 || \' / \' || $2
            END; 
'
 LANGUAGE SQL;

CREATE AGGREGATE concatenate (
  sfunc = concat2,
  basetype = text,
  stype = text,
  initcond = ''

);

И затем используйте его как:

SELECT company_id, concatenate(employee) AS employees FROM ...
Автор: bortzmeyer Размещён: 09.12.2008 07:54

5 плюса

8503 Репутация автора

Этот последний фрагмент списка объявлений может представлять интерес, если вы будете обновлять до 8.4:

До тех пор, пока 8.4 не выйдет с суперэффективным родным, вы можете добавить функцию array_accum () в документации PostgreSQL для свертывания любого столбца в массив, который затем может использоваться кодом приложения или в сочетании с array_to_string () для форматирования это как список:

http://www.postgresql.org/docs/current/static/xaggr.html

Я бы связался с документами по разработке 8.4, но они, похоже, пока не перечисляют эту функцию.

Автор: Kev Размещён: 09.02.2009 01:22

3 плюса

0 Репутация автора

Я нашел эту документацию PostgreSQL полезной: http://www.postgresql.org/docs/8.0/interactive/functions-conditional.html .

В моем случае я искал простой SQL для конкатенации поля с помощью скобок вокруг него, если поле не пустое.

select itemid, 
  CASE 
    itemdescription WHEN '' THEN itemname 
    ELSE itemname || ' (' || itemdescription || ')' 
  END 
from items;
Автор: David Размещён: 19.02.2009 04:01

5 плюса

0 Репутация автора

Следуя ответам Кева, используя документы Postgres:

Сначала создайте массив элементов, затем используйте встроенную array_to_stringфункцию.

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

select array_to_string(array_accum(name),'|') from table group by id;
Автор: Florian Размещён: 19.05.2009 12:13

5 плюса

0 Репутация автора

Еще раз повторив использование настраиваемой агрегированной функции конкатенации строк: вам нужно помнить, что оператор select будет размещать строки в любом порядке, поэтому вам нужно будет сделать дополнительный выбор в инструкции from с предложением order by и затем внешний выбор с помощью предложения group by для объединения строк, таким образом:

SELECT custom_aggregate(MY.special_strings)
FROM (SELECT special_strings, grouping_column 
        FROM a_table 
        ORDER BY ordering_column) MY
GROUP BY MY.grouping_column
Автор: Aaron Sheldon Размещён: 03.09.2009 04:24

84 плюса

849 Репутация автора

Как насчет использования встроенных функций массива Postgres? По крайней мере, на 8.4 это получается из коробки:

SELECT company_id, array_to_string(array_agg(employee), ',')
FROM mytable
GROUP BY company_id;
Автор: Markus Döring Размещён: 18.02.2010 10:55

17 плюса

1127 Репутация автора

Как и в PostgreSQL 9.0, вы можете использовать агрегированную функцию string_agg . Ваш новый SQL должен выглядеть примерно так:

SELECT company_id, string_agg(employee, ', ')
FROM mytable
GROUP BY company_id;

Автор: dirbacke Размещён: 26.05.2011 01:44

0 плюса

18 Репутация автора

Согласно версии PostgreSQL 9.0 и выше вы можете использовать агрегированную функцию, называемую string_agg. Ваш новый SQL должен выглядеть примерно так:

SELECT company_id, string_agg(employee, ', ')
    FROM mytable GROUP BY company_id;
Автор: Gobinath Размещён: 01.08.2017 09:02

0 плюса

41 Репутация автора

Вы также можете использовать функцию формата. Который также может косвенно заботиться о преобразовании типа текста, int и т. Д. Сам по себе.

create or replace function concat_return_row_count(tbl_name text, column_name text, value int)
returns integer as $row_count$
declare
total integer;
begin
    EXECUTE format('select count(*) from %s WHERE %s = %s', tbl_name, column_name, value) INTO total;
    return total;
end;
$row_count$ language plpgsql;


postgres=# select concat_return_row_count('tbl_name','column_name',2); --2 is the value
Автор: Sandip Debnath Размещён: 30.08.2018 11:35

0 плюса

1149 Репутация автора

Я использую Jetbrains Rider, и это было трудным копированием результатов из приведенных выше примеров для повторного выполнения, потому что, казалось, все это было в JSON. Это объединяет их в одно утверждение, которое было легче запускать

select string_agg('drop table if exists "' || tablename || '" cascade', ';') 
from pg_tables where schemaname != $$pg_catalog$$ and tableName like $$rm_%$$
Автор: Damien Sawyer Размещён: 04.12.2018 03:31
Вопросы из категории :
32x32