How to concatenate strings of a string field in a PostgreSQL 'group by' query?
我正在寻找一种通过查询来连接一个组内字段字符串的方法。 例如，我有一张桌子：
1
2
3
4
5
| ID COMPANY_ID EMPLOYEE
1 1 Anna
2 1 Bill
3 2 Carol
4 2 Dave |
我想按company_id分组以获取类似信息：
1
2
3
| COMPANY_ID EMPLOYEE
1 Anna, Bill
2 Carol, Dave |
mySQL中有一个内置函数来执行此group_concat
PostgreSQL 9.0或更高版本：
Postgres的最新版本(自2010年末开始)具有string_agg(expression, delimiter)函数，该函数将完全执行问题的要求，甚至允许您指定分隔符字符串：
1
2
3
| SELECT company_id, string_agg(employee, ', ')
FROM mytable
GROUP BY company_id; |
Postgres 9.0还增加了在任何聚合表达式中指定ORDER BY子句的功能。否则，顺序是不确定的。因此，您现在可以编写：
1
2
3
| SELECT company_id, string_agg(employee, ', ' ORDER BY employee)
FROM mytable
GROUP BY company_id; |
或者确实是：
1
| SELECT string_agg(actor_name, ', ' ORDER BY first_appearance) |
PostgreSQL 8.4或更高版本：
PostgreSQL 8.4(2009年)引入了聚合函数array_agg(expression)，该函数将值连接到一个数组中。然后array_to_string()可用于给出所需的结果：
1
2
3
| SELECT company_id, array_to_string(array_agg(employee), ', ')
FROM mytable
GROUP BY company_id; |
9.0以下版本的string_agg：
如果有人遇到这种情况，希望为9.0之前的数据库提供兼容的填充程序，则可以在string_agg中实现除ORDER BY子句以外的所有内容。
因此，使用以下定义，该方法应与9.x Postgres DB中的相同：
1
| SELECT string_agg(name, '; ') AS semi_colon_separated_names FROM things; |
但这将是语法错误：
1
2
| SELECT string_agg(name, '; ' ORDER BY name) AS semi_colon_separated_names FROM things;
--> ERROR: syntax error at or near"ORDER" |
已在PostgreSQL 8.3上测试。
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
| 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之前，没有内置的聚合函数来连接字符串。最简单的自定义实现(由Vajda Gabo在此邮件列表中的建议，以及许多其他建议)是使用内置的textcat函数(位于||运算符后面)：
1
2
3
4
5
6
| CREATE AGGREGATE textcat_all(
basetype = text,
sfunc = textcat,
stype = text,
initcond = ''
); |
这是CREATE AGGREGATE文档。
这只是将所有琴弦粘在一起，没有分隔符。为了使它们之间没有插入"，"，您可能想要创建自己的串联函数，并将其替换为上面的" textcat"。这是我整理并在8.3.12上测试过的一个：
1
2
3
4
5
6
7
8
9
| 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或为空，此版本也会输出逗号，因此您将获得如下输出：
如果您希望删除多余的逗号以输出此内容：
然后将ELSIF检查添加到如下函数：
1
2
3
4
5
6
7
8
9
10
11
| 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上可以立即使用：
1
2
3
| SELECT company_id, array_to_string(array_agg(employee), ',')
FROM mytable
GROUP BY company_id; |
从PostgreSQL 9.0开始，您可以使用称为string_agg的聚合函数。您的新SQL应该看起来像这样：
1
2
3
| SELECT company_id, string_agg(employee, ', ')
FROM mytable
GROUP BY company_id; |
我对这个答案不屑一顾，因为我经过一番搜索发现了它：
我不知道的是PostgreSQL允许您使用CREATE AGGREGATE定义自己的聚合函数
PostgreSQL列表上的该帖子显示了创建一个函数来执行所需的操作是多么简单：
1
2
3
4
5
6
7
8
9
10
| CREATE AGGREGATE textcat_all(
basetype = text,
sfunc = textcat,
stype = text,
initcond = ''
);
SELECT company_id, textcat_all(employee || ', ')
FROM mytable
GROUP BY company_id; |
如前所述，创建自己的聚合函数是正确的事情。这是我的串联聚合函数(您可以在法语中找到详细信息)：
1
2
3
4
5
6
7
8
9
10
11
12
13
| 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 = '' |
);
然后将其用作：
1
| SELECT company_id, concatenate(employee) AS employees FROM ... |
再次使用字符串连接的自定义聚合函数：您需要记住，select语句将以任何顺序放置行，因此您需要在from语句中使用order by子句进行子选择，并且然后使用带有group by子句的外部select来聚合字符串，因此：
1
2
3
4
5
| 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 |
使用Postgres文档跟踪Kev的答案：
首先，创建一个元素数组，然后使用内置的array_to_string函数。
1
2
3
4
5
6
7
8
| 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，则可能需要关注最新的公告列表片段：
Until 8.4 comes out with a
super-effient native one, you can add
the array_accum() function in the
PostgreSQL documentation for rolling
up any column into an array, which can
then be used by application code, or
combined with array_to_string() to
format it as a list:
http://www.postgresql.org/docs/current/static/xaggr.html
我将链接到8.4开发文档，但他们似乎还没有列出此功能。
我发现此PostgreSQL文档很有帮助：http://www.postgresql.org/docs/8.0/interactive/functions-conditional.html。
就我而言，如果该字段不为空，我希望使用普通的SQL将带有括号的字段连接起来。
1
2
3
4
5
6
| SELECT itemid,
CASE
itemdescription WHEN '' THEN itemname
ELSE itemname || ' (' || itemdescription || ')'
END
FROM items; |
对PostgreSQL和Google BigQuery SQL使用STRING_AGG函数：
1
2
3
| SELECT company_id, STRING_AGG(employee, ', ')
FROM employees
GROUP BY company_id; |
如果您在不支持string_agg的Amazon Redshift上，请尝试使用listagg。
1
2
3
| SELECT company_id, listagg(EMPLOYEE, ', ') AS employees
FROM EMPLOYEE_table
GROUP BY company_id; |
我正在使用Jetbrains Rider，将上述示例的结果复制到重新执行起来很麻烦，因为它似乎都将其包装在JSON中。 这将它们合并为一个更易于运行的语句
1
2
| SELECT string_agg('drop table if exists"' || tablename || '" cascade', ';')
FROM pg_tables WHERE schemaname != $$pg_catalog$$ AND tableName LIKE $$rm_%$$ |
您也可以使用格式化功能。它也可以隐式地处理文本，int等类型的类型转换。
1
2
3
4
5
6
7
8
9
10
11
12
| 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及更高版本，您可以使用称为string_agg的聚合函数。您的新SQL应该看起来像这样：
1
2
| SELECT company_id, string_agg(employee, ', ')
FROM mytable GROUP BY company_id; |
|
