成对数组求和聚合函数？

Question

我有一个表格，其中一列是数组，我想将数组元素相加：

> create table regres(a int[] not null);
> insert into regres values ('{1,2,3}'), ('{9, 12, 13}');
> select * from regres;
     a
-----------
 {1,2,3}
 {9,12,13}

我希望得到的结果是：

{10, 14, 16}

即: {1 + 9, 2 + 12, 3 + 13}。

是否已经存在这样的函数？扩展intagg看起来是一个好的选择，但这样的函数不存在。

预期数组长度在24到31个元素之间，所有元素都为NOT NULL，数组本身也总是NOT NULL。所有元素都是基本的int类型。每个聚合会有两行以上。在查询中，所有数组将具有相同数量的元素。不同的查询将具有不同数量的元素。

我的实现目标是：PostgreSQL 9.1.13

- François Beausoleil

---

也发布到pgsql-general邮件列表：[成对数组求和聚合函数？] (http://www.postgresql.org/message-id/[e-mail]) - François Beausoleil

如果您可以接受一些冗长的输入：select array[sum(a[1]), sum(a[2]), sum(a[3]), sum(a[4]), ....] from regres; - user330315

@a_horse_with_no_name 我没有想到那个。很棒的超越常规的思维方式。我生成查询，所以编写这样的选择子句并不是问题。 - François Beausoleil

Answer 1

通用解决方案适用于任意数量的数组和任意数量的元素。单个元素或整个数组也可以为NULL：

在9.4+版本中使用WITH ORDINALITY更加简单

SELECT ARRAY (
   SELECT sum(elem)
   FROM  tbl t
       , unnest(t.arr) WITH ORDINALITY x(elem, rn)
   GROUP BY rn
   ORDER BY rn
   );

参见：

• PostgreSQL unnest() with element number

Postgres 9.3+

这里使用了隐式LATERAL JOIN。

SELECT ARRAY (
   SELECT sum(arr[rn])
   FROM   tbl t
        , generate_subscripts(t.arr, 1) AS rn
   GROUP  BY rn
   ORDER  BY rn
   );

请参考：

• PostgreSQL中LATERAL JOIN和子查询的区别是什么？

Postgres 9.1

SELECT ARRAY (
   SELECT sum(arr[rn])
   FROM  (
      SELECT arr, generate_subscripts(arr, 1) AS rn
      FROM   tbl t
      ) sub
   GROUP  BY rn
   ORDER  BY rn
   );

后续版本中同样适用此方法，但是 SELECT 列表中的返回集函数不符合标准SQL，并且受到一些人的反对。然而在Postgres 10之后应该可以，详情请参见：

• What is the expected behaviour for multiple set-returning functions in SELECT clause?

db<>fiddle here
_{Old sqlfiddle}

相关：

• Is there something like a zip() function in PostgreSQL that combines two arrays?

- Erwin Brandstetter

---

优雅地使用了Lateral Join。 - Pavel Stehule

谢谢，但我使用的是9.1版本。我计划在今年晚些时候进行迁移，但在此期间，是否可以实现类似的功能？ - François Beausoleil

@FrançoisBeausoleil：添加了9.1版本。 - Erwin Brandstetter

Answer 2

如果您需要更好的性能并且可以安装Postgres扩展，则agg_for_vecs C扩展程序提供了一个vec_to_sum函数，应该能够满足您的需求。它还提供了各种聚合函数，如min，max，avg和var_samp，这些函数操作的是数组而不是标量。

- sebastibe

Answer 3

我知道原先的问题和答案已经有些年头了，但是对于其他看到这个的人来说……最优雅和灵活的解决方案是创建一个自定义聚合函数。如果你只需要单个结果数组，Erwin提供了一些非常好的简单解决方案，但是在GROUP BY等情况下无法转换为可以包括其他表列和聚合的解决方案。

使用自定义array_add函数和array_sum聚合函数：

CREATE OR REPLACE FUNCTION array_add(_a numeric[], _b numeric[])
  RETURNS numeric[]
AS
$$
BEGIN
  RETURN ARRAY(
    SELECT coalesce(a, 0) + coalesce(b, 0)
    FROM unnest(_a, _b) WITH ORDINALITY AS x(a, b, n)
    ORDER BY n
  );
END
$$ LANGUAGE plpgsql;

CREATE AGGREGATE array_sum(numeric[])
(
  sfunc = array_add,
  stype = numeric[],
  initcond = '{}'
);

那么（使用您的示例中的名称）：

SELECT array_sum(a) a_sums
FROM regres;

返回您的求和数组，它可以像其他聚合函数一样在任何地方使用，因此如果您的表还有一个想按照分组的列name和另一个数字数组列b：

SELECT name, array_sum(a) a_sums, array_sum(b) b_sums
FROM regres
GROUP BY name;

您使用内置的 sum 函数并选择 sum(a[1]), sum(a[2]), sum(a[3]) 无法获得与编译为 C 函数实现的 array_add 函数相同的性能。但是，在您无法添加自定义 C 函数（例如受管云数据库，例如 AWS RDS）或不聚合大量行的情况下，可能不会注意到差异。

- reads0520