成对数组求和聚合函数？ [英] Pairwise array sum aggregate function?

问题描述

我有一张表，其中的数组为一列，我想将数组元素加在一起：

I have a table with arrays as one column, and I want to sum the array elements together:

> 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扩展看起来像是一个不错的候选者，但是这种功能尚不存在。

Does such a function already exist somewhere? The intagg extension looked like a good candidate, but such a function does not already exist.

数组的长度预计在24到31个元素之间，所有元素都是不为空，并且数组本身也始终为不为空。所有元素都是基本的 int 。每个聚合将有两行以上。在查询中，所有数组将具有相同数量的元素。不同的查询将具有不同数量的元素。

The arrays are expected to be between 24 and 31 elements in length, all elements are NOT NULL, and the arrays themselves will also always be NOT NULL. All elements are basic int. There will be more than two rows per aggregate. All arrays will have the same number of elements, in a query. Different queries will have different number of elements.

我的实现目标是：x86_64-unknown-linux-gnu上的PostgreSQL 9.1.13，由gcc编译（Ubuntu / Linaro 4.6 .3-1ubuntu5）4.6.3，64位

My implementation target is: PostgreSQL 9.1.13 on x86_64-unknown-linux-gnu, compiled by gcc (Ubuntu/Linaro 4.6.3-1ubuntu5) 4.6.3, 64-bit

推荐答案

Postgres中的一般解决方案 9.3 + 用于具有任意数量元素的任意数量数组。

单个元素或整个数组也可以为NULL：

A general solution in Postgres 9.3+ for any number of arrays with any number of elements.
Individual elements or the the whole array can be NULL, too:

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

这利用了隐式 横向联接 （Postgres 9.3+）。

使用您的示例值：

This makes use of an implicit LATERAL JOIN (Postgres 9.3+).
With your example values:

SELECT ARRAY (
   SELECT sum(arr[rn])
   FROM  (
      VALUES
        ('{1,2,3}'::int[])
       ,('{9,12,13}')
      ) t(arr)
    , generate_subscripts(t.arr, 1) AS rn
   GROUP BY rn
   ORDER BY rn
   );

不平凡的例子：

SELECT ARRAY (
   SELECT sum(arr[rn])
   FROM  (
      VALUES
        ('{1,2,3}'::int[])
       ,('{9,12,13}')
       ,('{1,1,1, 33}')
       ,('{NULL,NULL}')
       ,(NULL)
      ) t(arr)
    , generate_subscripts(t.arr, 1) AS rn
   GROUP BY rn
   ORDER BY rn
   );

在9.4+版本中使用 有序

Simpler in 9.4+ using WITH ORDINALITY

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

Postgres 9.1

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

在更高版本中也是如此，但是<$ c $中的 set-returning函数c> SELECT 列表不是标准的SQL，有些人对此并不满意。因此，请在当前的Postgres中使用上述替代方法。

The same works in later versions, but set-returning functions in the SELECT list are not standard SQL and frowned upon by some. So use above alternatives with current Postgres.

SQL Fiddle。

相关答案以及更多说明：

Related answers with more explanation:

• 带有元素编号的PostgreSQL unnest（）


• PostgreSQL中是否有类似zip（）的函数，该函数结合了两个数组？

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

这篇关于成对数组求和聚合函数？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！