PostgreSQL - summing arrays by index

Question

I have an array of doubles column (double precision[]) in PostgreSQL that keeps half hour values for a day. So each array holds 48 values. I need an efficient query that is summing all this array columns by index and produces a new 48 array index as explained below

A = double[48] = {3,2,0,3....1}
B = double[48] = {1,0,3,2....5}

RESULT = double[48] = {A[0] + B[0], A[1] + B[1],...,A[47] + B[47]}

Thank you!

Answer 1

I would use unnest together with array_agg, like this: SQL Fiddle

PostgreSQL 9.3.1 Schema Setup:

create table t (
A double precision[5],
B double precision[5]);

insert into t values
('{3,2,0,3,1}', '{1,0,3,2,5}');

Query 1:

with c as(
select unnest(a) a, unnest(b) b from t)
select array_agg(a) a, array_agg(b) b, array_agg(a + b) c from c

Results:

|         A |         B |         C |
|-----------|-----------|-----------|
| 3,2,0,3,1 | 1,0,3,2,5 | 4,2,3,5,6 |

As commented below, the query above will work well for the arrays of the same size. Otherwise it may produce an unexpected result.

If you need to support the arrays of different size, use this query:

with a as(
  select unnest(a) a from t),
b as(
  select unnest(b) b from t),
ar as(
  select a, row_number() over() r from a),
br as(
  select b, row_number() over() r from b),
c as(
  select ar.a, br.b from ar inner join br on ar.r = br.r)
select array_agg(a) a, array_agg(b) b, array_agg(a + b) c from c;

Answer 2

Using @cha's schema, here's how to do it in PostgreSQL 9.4 using support for unnest ... with ordinality:

SELECT array_agg(unnest_a.unnest_a + unnest_b.unnest_b ORDER BY unnest_a.ordinality)
FROM t,
     LATERAL unnest(a) WITH ORDINALITY AS unnest_a 
     INNER JOIN LATERAL unnest(b) WITH ORDINALITY AS unnest_b 
     ON (unnest_a.ordinality = unnest_b.ordinality);

Or, to handle arrays of uneven length using a FULL OUTER JOIN:

SELECT array_agg(
         coalesce(unnest_a.unnest_a,0) + 
         coalece(unnest_b.unnest_b,0) 
         ORDER BY unnest_a.ordinality
       )
FROM t,
     LATERAL unnest(a) WITH ORDINALITY AS unnest_a 
     FULL OUTER JOIN LATERAL unnest(b) WITH ORDINALITY AS unnest_b 
     ON (unnest_a.ordinality = unnest_b.ordinality);

For PostgreSQL 9.3, I'd just use PL/Python:

create or replace function sumarrays(a float8[], b float8[]) returns float8[] language plpythonu as $$
return [ (ax or 0) + (bx or 0) for (ax, bx) in map(None, a, b) ]
$$;

To support 9.3 without using extension procedure languages you can replace each LATERAL unnest ... WITH ORDINALITY with a subquery using row_number(), e.g.

    LATERAL unnest(a) WITH ORDINALITY AS unnest_a 

becomes:

    (SELECT unnest_a, row_number() OVER () AS ordinality
     FROM unnest(a) AS unnest_a) AS unnest_a

Technically there's no gurarantee that PostgreSQL will process the unnested rows in order, so row_number() OVER () is kind of risky, but in practice it's fine with all current PostgreSQL versions.

The best solution to this would really be to write a simple C extension, though.