如何在PostgreSQL“分组依据”查询中连接字符串字段的字符串？

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

COMPANY_ID   EMPLOYEE
1            Anna, Bill
2            Carol, Dave

#1 楼

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（8.4版之前的版本）：

遇到此问题，寻找9.0之前版本的数据库的兼容性填充程序时，可以在string_agg中实现除ORDER BY子句之外的所有内容。

因此，使用以下定义，该功能应与9中的功能相同。 x Postgres DB：

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  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
);

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

在9.0之前，没有内置的聚合函数来连接字符串。最简单的自定义实现（由Vajda Gabo在此邮件列表中的建议，以及许多其他建议）是使用内置的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;

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

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;

#2 楼

如何使用Postgres内置数组函数？至少在8.4上可以立即使用：

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

#3 楼

从PostgreSQL 9.0开始，您可以使用称为string_agg的聚合函数。您的新SQL应该看起来像这样：

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

#4 楼

我对这个答案不屑一顾，因为我经过一番搜索发现了它：

我不知道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;

#5 楼

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

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 = ''

）;

然后将其用作：

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

#6 楼

如果您要升级到8.4，则可能需要关注最新的公告列表片段：


在8.4发行具有
超级本机的版本之前，您可以添加
PostgreSQL文档中的array_accum（）函数用于将任意列滚动到数组中，然后可以将其
供应用程序代码使用，或者
与array_to_string组合使用（）格式化为列表：

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


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

#7 楼

使用Postgres文档跟踪Kev的答案：

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

再次使用字符串连接的自定义聚合函数：您需要记住，select语句将以任何顺序放置行，因此您需要在from语句中使用order by子句进行子选择，并且然后使用带有group by子句的外部select来聚合字符串，因此：

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

#9 楼

我发现此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;

#10 楼

对PostgreSQL和Google BigQuery SQL使用STRING_AGG函数：

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

#11 楼

根据PostgreSQL 9.0及更高版本，您可以使用称为string_agg的聚合函数。您的新SQL应该看起来像这样：

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

#12 楼

您也可以使用格式化功能。它也可以隐式地处理文本，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

#13 楼

我使用的是Jetbrains Rider，复制上面示例的结果以重新执行很麻烦，因为它似乎都将其包装在JSON中。这使它们成为一个易于运行的语句

select string_agg('drop table if exists "' || tablename || '" cascade', ';') 
from pg_tables where schemaname != $$pg_catalog$$ and tableName like $$rm_%$$

#14 楼

如果您在不支持string_agg的Amazon Redshift上，请尝试使用listagg。

SELECT company_id, listagg(EMPLOYEE, ', ') as employees
FROM EMPLOYEE_table
GROUP BY company_id;