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

Question

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

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

Answer 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 $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函数（位于||运算符后面）：

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;