Я ищу способ конкатенации строк поля внутри группы по запросу. Так, например, у меня есть таблица:
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
В последних версиях 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 (в 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: В случае, если кто-то столкнется с этим, ищет string_agg
прокладку для баз данных до 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
);
До 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;
Как использовать встроенные функции массива Postgres? По крайней мере, на 8.4 это получается из коробки:
SELECT company_id, array_to_string(array_agg(employee), ',')
FROM mytable
GROUP BY company_id;
Как и в PostgreSQL 9.0, вы можете использовать агрегированную функцию string_agg. Ваш новый SQL должен выглядеть примерно так:
SELECT company_id, string_agg(employee, ', ')
FROM mytable
GROUP BY company_id;
Я не претендую на кредит, потому что нашел его после некоторого поиска:
То, что я не знал, это то, что 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;
Как уже упоминалось, создание собственной агрегатной функции - это правильная вещь. Вот моя агрегированная функция конкатенации (вы можете найти подробности на французском языке):
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 ...
Следуя еще раз об использовании настраиваемой агрегированной функции конкатенации строк: вам нужно помнить, что оператор select будет размещать строки в любом порядке, поэтому вам нужно будет сделать sub select в инструкции from с порядком, а затем внешний выбор с предложением 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
В ответ на ответ Kev, используя документы 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;
Этот последний фрагмент списка объявлений может представлять интерес, если вы будете обновлять до 8.4:
До тех пор, пока 8.4 не выйдет с суперэффективным родным, вы можете добавить функцию array_accum() в документации PostgreSQL для свертывания любого столбца в массив, который затем может использоваться кодом приложения или в сочетании с array_to_string() для форматирования это как список:
Я бы связался с документами по разработке 8.4, но они, похоже, пока не перечисляют эту функцию.
В случае, если кто-либо столкнется с этим, ищет совместимость для баз данных до 9.0, можно реализовать все в string_agg
, за исключением предложения ORDER BY
(которое является расширением синтаксиса, также введенным в 9.0).
Таким образом, с приведенным ниже определением это будет работать так же, как в 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
);
Я нашел эту документацию 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;
Я использую Jetbrains Rider, и это было трудным копированием результатов из приведенных выше примеров для повторного выполнения, потому что, казалось, все это было в JSON. Это объединяет их в одно утверждение, которое было легче запускать
select string_agg('drop table if exists "' || tablename || '" cascade', ';')
from pg_tables where schemaname != $$pg_catalog$$ and tableName like $$rm_%$$
Вы также можете использовать функцию формата. Который также может косвенно заботиться о преобразовании типа текста, 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
В соответствии с версией PostgreSQL 9.0 и выше вы можете использовать функцию aggregate, называемую string_agg. Ваш новый SQL должен выглядеть примерно так:
SELECT company_id, string_agg(employee, ', ')
FROM mytable GROUP BY company_id;