Vector (array) Addition In Postgres

I have a column with numeric[] values which all have the same size. I'd like to take their element-wise average. By this I mean that the average of {1, 2, 3}, {-1, -2, -3}, and {3,

Solution 1:

I've written an extension to do vector addition (and subtraction, multiplication, division, and powers) with fast C functions. You can find it on Github or PGXN.

Given two arrays a and b you can say vec_add(a, b). You can also add either side to a scalar, e.g. vec_add(a, 5).

If you want a SUM aggregate function instead you can find that in aggs_for_vecs, also on PGXN.

Finally if you want to sum up all the elements of a single array, you can use aggs_for_arrays (PGXN).

Solution 2:

I discovered a solution on my own which is probably the one I will use.

First, we can define a function for adding two vectors:

CREATEOR REPLACE FUNCTION vec_add(arr1 numeric[], arr2 numeric[])
RETURNSnumeric[] AS
$$
SELECTarray_agg(result)
FROM (SELECT tuple.val1 + tuple.val2 ASresultFROM (SELECTUNNEST($1) AS val1
                   ,UNNEST($2) AS val2
                   ,generate_subscripts($1, 1) AS ix) tuple
      ORDERBY ix) inn;
$$ LANGUAGESQL IMMUTABLE STRICT;

and a function for multiplying by a constant:

CREATEOR REPLACE FUNCTION vec_mult(arr numeric[], mul numeric)
RETURNSnumeric[] AS
$$
SELECTarray_agg(result)
FROM (SELECT val * $2ASresultFROM (SELECTUNNEST($1) AS val
                   ,generate_subscripts($1, 1) as ix) t
      ORDERBY ix) inn;
$$ LANGUAGESQL IMMUTABLE STRICT;

Then we can use the PostgreSQL statement CREATE AGGREGATE to create the vec_sum function directly:

CREATE AGGREGATE vec_sum(numeric[]) (
    SFUNC = vec_add
    ,STYPE = numeric[]
);

And finally, we can find the average as:

SELECT vec_mult(vec_sum(vector), 1 / count(vector)) FROM A;

Solution 3:

from http://www.postgresql.org/message-id/4C2504A3.4090502@wp.pl

select avg(unnested) from (select unnest(vector) as unnested from A) temp;

---

Edit: I think I now understand the question better.

Here is a possible solution drawing heavily upon: https://stackoverflow.com/a/8767450/3430807 I don't consider it elegant nor am I sure it will perform well:

Test data:

CREATETABLE A
  (vector numeric[], id serial)
;

INSERTINTO A
  VALUES
    ('{1, 2, 3}'::numeric[])
    ,('{4, 5, 6}'::numeric[])
    ,('{7, 8, 9}'::numeric[])
;

Query:

selectavg(vector[temp.index])
from    A as a
join
    (select generate_subscripts(vector, 1) as index
              , id
        from    A) as temp on temp.id = a.id
groupby temp.index