postgresql – 成对数组和聚合函数？

发布时间：2020-12-13 16:13:04 所属栏目：百科来源：网络整理

导读：我有一个数组作为一列的表,我想将数组元素加在一起： create table regres(a int[] not null); insert into regres values ('{1,2,3}'),('{9,12,13}'); select * from regres; a----------- {1,3} {9,13} 我希望结果如下： {10,14,16} 那是：{1 9,2 12,3 3}.

我有一个数组作为一列的表,我想将数组元素加在一起：

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

我希望结果如下：

{10,14,16}

那是：{1 9,2 12,3 3}.

这样的功能在某处已经存在吗？插入扩展看起来是一个很好的候选者,但这样的功能尚不存在.

预计数组的长度在24到31个元素之间,所有元素都是NOT NULL,并且数组本身也总是NOT NULL.所有元素都是基本的int.每个聚合将有两行以上.在查询中,所有数组都将具有相同数量的元素.不同的查询将具有不同数量的元素.

我的实现目标是：x86_64-unknown-linux-gnu上的PostgreSQL 9.1.13,由gcc编译(Ubuntu / Linaro 4.6.3-1ubuntu5)4.6.3,64位

Postgres 9.3中的一般解决方案,适用于任意数量的元素阵列.
单个元素或整个数组也可以为NULL：

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

这使用了隐含的LATERAL JOIN(Postgres 9.3).
使用您的示例值：

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

非平凡的例子：

SELECT ARRAY (
   SELECT sum(arr[rn])
   FROM  (
      VALUES
        ('{1,13}'),('{1,1,33}'),('{NULL,NULL}'),(NULL)
      ) t(arr),1) AS rn
   GROUP BY rn
   ORDER BY rn
   );

使用WITH ORDINALITY在9.4中更简单

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

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列表中的set-returns函数不是标准SQL,并且有些人不赞成.因此,使用上述替代品与当前的Postgres.

SQL Fiddle.