SQL 如何在 PostgreSQL 'group by' 查询中连接字符串字段的字符串？

Question

提问by Guy C

I am looking for a way to concatenate the strings of a field within a group by query. So for example, I have a table:

我正在寻找一种通过查询连接组内字段字符串的方法。例如，我有一张桌子：

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

and I wanted to group by company_id to get something like:

我想按 company_id 分组以获得类似的信息：

COMPANY_ID   EMPLOYEE
1            Anna, Bill
2            Carol, Dave

There is a built-in function in mySQL to do this group_concat

mySQL 中有一个内置函数来做这个group_concat

Answer 1

回答by Neall

PostgreSQL 9.0 or later:

PostgreSQL 9.0 或更高版本：

Recent versions of Postgres (since late 2010) have the string_agg(expression, delimiter)function which will do exactly what the question asked for, even letting you specify the delimiter string:

Postgres 的最新版本（自 2010 年末）具有的string_agg(expression, delimiter)功能可以完全满足问题的要求，甚至可以让您指定分隔符字符串：

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

Postgres 9.0 also added the ability to specify an ORDER BYclause in any aggregate expression; otherwise, the order is undefined. So you can now write:

Postgres 9.0 还增加了在任何聚合表达式中指定ORDER BY子句的能力；否则，订单未定义。所以你现在可以写：

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

Or indeed:

或者确实：

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

PostgreSQL 8.4 or later:

PostgreSQL 8.4 或更高版本：

PostgreSQL 8.4 (in 2009) introduced the aggregate function array_agg(expression)which concatenates the values into an array. Then array_to_string()can be used to give the desired result:

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`for pre-8.4 versions:

`string_agg`对于 8.4 之前的版本：

In case anyone comes across this looking for a compatibilty shim for pre-9.0 databases, it is possible to implement everything in string_aggexcept the ORDER BYclause.

如果有人在为 9.0 之前的数据库寻找兼容性垫片时遇到此问题，则可以实现string_agg除ORDER BY子句之外的所有内容。

So with the below definition this should work the same as in a 9.x Postgres DB:

因此，使用以下定义，这应该与 9.x Postgres DB 中的工作方式相同：

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

But this will be a syntax error:

但这将是一个语法错误：

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

Tested on PostgreSQL 8.3.

在 PostgreSQL 8.3 上测试。

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

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

Custom variations (all Postgres versions)

自定义变体（所有 Postgres 版本）

Prior to 9.0, there was no built-in aggregate function to concatenate strings. The simplest custom implementation (suggested by Vajda Gabo in this mailing list post, among many others) is to use the built-in textcatfunction (which lies behind the ||operator):

在 9.0 之前，没有内置的聚合函数来连接字符串。最简单的自定义实现（由 Vajda Gabo 在此邮件列表帖子中建议）是使用内置textcat函数（位于||运算符后面）：

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

Here is the CREATE AGGREGATEdocumentation.

这是CREATE AGGREGATE文档。

This simply glues all the strings together, with no separator. In order to get a ", " inserted in between them without having it at the end, you might want to make your own concatenation function and substitute it for the "textcat" above. Here is one I put together and tested on 8.3.12:

这只是将所有字符串粘合在一起，没有分隔符。为了在它们之间插入一个“,”而没有最后，您可能需要创建自己的连接函数并将其替换为上面的“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;

This version will output a comma even if the value in the row is null or empty, so you get output like this:

即使行中的值为 null 或空，此版本也会输出逗号，因此您会得到如下输出：

a, b, c, , e, , g

If you would prefer to remove extra commas to output this:

如果您希望删除额外的逗号来输出：

a, b, c, e, g

Then add an ELSIFcheck to the function like this:

然后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;

Answer 2

回答by Markus D?ring

How about using Postgres built-in array functions? At least on 8.4 this works out of the box:

使用 Postgres 内置数组函数怎么样？至少在 8.4 上，这是开箱即用的：

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

Answer 3

回答by dirbacke

As from PostgreSQL 9.0 you can use the aggregate function called string_agg. Your new SQL should look something like this:

从 PostgreSQL 9.0 开始，您可以使用名为string_agg的聚合函数。您的新 SQL 应如下所示：

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

Answer 4

回答by Guy C

I claim no credit for the answer because I found it after some searching:

我声称答案没有功劳，因为我在一些搜索后找到了它：

What I didn't know is that PostgreSQL allows you to define your own aggregate functions with CREATE AGGREGATE

我不知道的是 PostgreSQL 允许您使用CREATE AGGREGATE定义自己的聚合函数

This poston the PostgreSQL list shows how trivial it is to create a function to do what's required:

PostgreSQL 列表上的这篇文章显示了创建一个函数来执行所需的操作是多么的微不足道：

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

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

Answer 5

回答by bortzmeyer

As already mentioned, creating your own aggregate function is the right thing to do. Here is my concatenation aggregate function (you can find details in French):

如前所述，创建自己的聚合函数是正确的做法。这是我的串联聚合函数（您可以在法语中找到详细信息）：

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

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

);

And then use it as:

然后将其用作：

SELECT company_id, concatenate(employee) AS employees FROM ...

Answer 6

回答by Kev

This latest announcement list snippet might be of interest if you'll be upgrading to 8.4:

如果您要升级到 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 文档中添加 array_accum() 函数，将任何列卷成一个数组，然后可以被应用程序代码使用，或者结合 array_to_string() 进行格式化它作为一个列表：
http://www.postgresql.org/docs/current/static/xaggr.html

I'd link to the 8.4 development docs but they don't seem to list this feature yet.

我会链接到 8.4 开发文档，但他们似乎还没有列出这个功能。

Answer 7

回答by Kev

Following up on Kev's answer, using the Postgres docs:

使用 Postgres 文档跟进 Kev 的回答：

First, create an array of the elements, then use the built-in array_to_stringfunction.

首先，创建一个元素数组，然后使用内置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;

Answer 8

回答by Kev

Following yet again on the use of a custom aggregate function of string concatenation: you need to remember that the select statement will place rows in any order, so you will need to do a sub selectin the fromstatement with an order byclause, and then an outer selectwith a group byclause to aggregate the strings, thus:

再次关注字符串连接的自定义聚合函数的使用：您需要记住，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

Answer 9

回答by Kev

I found this PostgreSQL documentation helpful: http://www.postgresql.org/docs/8.0/interactive/functions-conditional.html.

我发现这个 PostgreSQL 文档很有帮助：http: //www.postgresql.org/docs/8.0/interactive/functions-conditional.html。

In my case, I sought plain SQL to concatenate a field with brackets around it, if the field is not empty.

就我而言，如果该字段不为空，我会寻求纯 SQL 来连接带有括号的字段。

select itemid, 
  CASE 
    itemdescription WHEN '' THEN itemname 
    ELSE itemname || ' (' || itemdescription || ')' 
  END 
from items;

Answer 10

回答by Valentin Podkamennyi

Use STRING_AGGfunction for PostgreSQLand Google BigQuery SQL:

使用PostgreSQL和Google BigQuery SQLSTRING_AGG函数：

SELECT company_id, STRING_AGG(employee, ', ')
FROM employees
GROUP BY company_id;

SQL 如何在 PostgreSQL 'group by' 查询中连接字符串字段的字符串？

提问by Guy C

回答by Neall

PostgreSQL 9.0 or later:

PostgreSQL 9.0 或更高版本：

PostgreSQL 8.4 or later:

PostgreSQL 8.4 或更高版本：

`string_agg`for pre-8.4 versions:

`string_agg`对于 8.4 之前的版本：

Custom variations (all Postgres versions)

自定义变体（所有 Postgres 版本）

回答by Markus D?ring

回答by dirbacke

回答by Guy C

回答by bortzmeyer

回答by Kev

回答by Kev

回答by Kev

回答by Kev

回答by Valentin Podkamennyi

相关推荐

最近更新

标签

SQL 如何在 PostgreSQL 'group by' 查询中连接字符串字段的字符串？

提问by Guy C

回答by Neall

PostgreSQL 9.0 or later:

PostgreSQL 9.0 或更高版本：

PostgreSQL 8.4 or later:

PostgreSQL 8.4 或更高版本：

string_aggfor pre-8.4 versions:

string_agg对于 8.4 之前的版本：

Custom variations (all Postgres versions)

自定义变体（所有 Postgres 版本）

回答by Markus D?ring

回答by dirbacke

回答by Guy C

回答by bortzmeyer

回答by Kev

回答by Kev

回答by Kev

回答by Kev

回答by Valentin Podkamennyi

相关推荐

查找第二大值的最简单的 SQL 查询是什么？

我可以使用什么 SQL 服务器函数来获取 nvarchar(max) 列的字符或字节长度？

SQL 计算左连接中连接的行数

SQL 查询，计数为 0

相关推荐

最近更新

标签

`string_agg`for pre-8.4 versions:

`string_agg`对于 8.4 之前的版本：