sql - 从 Postgres 中的数组列聚合的不同值数组

Question

假设我们(在 PostgreSQL 9.1 中)有一个带有一些标识符的表，一个类型为 integer[] 的列和其他一些类型为 integer(或任何其他可以求和的列)(至少一个，尽管可能有更多) ).

目标是为“可求和”列的每个标识符总和提供一个聚合，以及一个包含数组列的所有不同元素的数组。

我能找到的唯一方法是在子查询的数组列上使用 unnest 函数，而不是将它与另一个聚合“可求和”列的子查询连接起来。

一个简单的例子如下:

CREATE TEMP TABLE a (id integer, aint integer[], summable_val integer);
INSERT INTO a VALUES
(1, array[1,2,3], 5),
(2, array[2,3,4], 6),
(3, array[3,4,5], 2),
(1, array[7,8,9], 19);

WITH u AS (
SELECT id, unnest(aint) as t FROM a GROUP BY 1,2
),
d AS (
SELECT id, array_agg(distinct t) ar FROM u GROUP BY 1),
v as (
SELECT id, sum(summable_val) AS val
FROM a GROUP BY 1
)
SELECT v.id, v.val, d.ar
FROM v
JOIN d
ON   v.id = d.id;

上面的代码达到了我的预期，但问题是我们可以做得更好吗？此解决方案的主要缺点是它会读取和聚合表两次，这对于较大的表来说可能很麻烦。

一般问题的其他一些解决方案是避免使用数组列并为每个数组成员聚合“可求和”列，然后在聚合中使用 array_agg - 但至少现在我想坚持这种阵列方式。

提前感谢您的任何想法。

Answer 1

查询可能会快一点(我想)但我看不到任何显着的优化:

select a.id, sum(summable_val) val, ar
from
    (select id, array_agg(distinct t) ar 
        from 
        (select id, unnest(aint) as t from a group by 1,2) u
    group by 1) x
    join a on x.id = a.id
group by 1,3