sql - array of distinct values aggregated from an array column in Postgres -

sql - array of distinct values aggregated from an array column in Postgres -

suppose have (in postgresql 9.1) table identifier, column of type integer[] , other columns (at to the lowest degree one, although there might more) of type integer (or other can summed).

the goal have aggregate giving each identifier sum of "summable" column , array of distinct elements of array column.

the way can find utilize unnest function on array column in subquery , bring together subquery aggregating "summable" columns.

a simple illustration follows:

create temp table (id integer, aint integer[], summable_val integer); insert 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); u ( select id, unnest(aint) t grouping 1,2 ), d ( select id, array_agg(distinct t) ar u grouping 1), v ( select id, sum(summable_val) val grouping 1 ) select v.id, v.val, d.ar v bring together d on v.id = d.id;

the code above intended question can better? main drawback of solution reads , aggregate table twice might troublesome larger tables.

some other solution general problem avoid using array column , agregate "summable" column each array fellow member , utilize array_agg in aggregation - @ to the lowest degree i'd stick array way.

thanks in advance ideas.

the query may little bit faster (i suppose) cannot see remarkable optimizations:

select a.id, sum(summable_val) val, ar (select id, array_agg(distinct t) ar (select id, unnest(aint) t grouping 1,2) u grouping 1) x bring together on x.id = a.id grouping 1,3

sql arrays postgresql aggregation