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

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

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

PostgreSQL 9.0 或更高版本：

Modern Postgres（自 2010 年起）具有 string_agg（表达式、分隔符）功能，该函数将完全满足提问者的需求：

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

Postgres 9 还添加了在任何聚合表达式中指定子句的功能;否则，您必须对所有结果进行排序或处理未定义的顺序。所以你现在可以写：ORDER BY

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

PostgreSQL 8.4.x：

请注意，对 Postgres 8.4 的支持已于 2014 年结束，因此您可能应该出于比字符串聚合更重要的原因进行升级。

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;

PostgreSQL 8.3.x 及更早版本：

当最初提出这个问题时，没有内置的聚合函数来连接字符串。最简单的自定义实现（由 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;

即使行中的值为 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;

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

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 ...

如果您要升级到 8.4，可能会对以下最新公告列表片段感兴趣：

直到 8.4 推出 超高效原生的，可以添加 array_accum（） 函数 用于滚动的 PostgreSQL 文档 将任何列上升到一个数组中，它可以 然后由应用程序代码使用，或者 与 array_to_string（） 组合成 将其格式化为列表：

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

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

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

跟进 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;

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

如何使用 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 9.0 及更高版本，您可以使用名为 string_agg 的聚合函数。您的新 SQL 应如下所示：

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

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

我正在使用 Jetbrains Rider，从上述示例中复制结果以重新执行很麻烦，因为它似乎将其全部包装在 JSON 中。这会将它们联接到一个更易于运行的语句中

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

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

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

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

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