成对数组求和聚合函数? [英] 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屋!