成对数组求和聚合函数? [英] 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 个元素之间，所有元素都是 NOT NULL，并且数组本身也总是 NOT NULL.所有元素都是基本的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.
我的实现目标是:PostgreSQL 9.1.13
My implementation target is: PostgreSQL 9.1.13
推荐答案
一般解决方案，适用于具有任意数量元素的任意数量的数组.单个元素或整个数组也可以为 NULL:
General solutions 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(elem)
FROM tbl t
, unnest(t.arr) WITH ORDINALITY x(elem, rn)
GROUP BY rn
ORDER BY rn
);
见:
这使用了一个隐式的LATERAL JOIN
This makes use of an implicit LATERAL JOIN
SELECT ARRAY (
SELECT sum(arr[rn])
FROM tbl t
, generate_subscripts(t.arr, 1) AS rn
GROUP BY rn
ORDER BY rn
);
见:
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 开始应该没问题.见:
The same works in later versions, but set-returning functions in the SELECT list are not standard SQL and were frowned upon by some. Should be OK since Postgres 10, though. See:
相关:
这篇关于成对数组求和聚合函数?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
