Adding Vector (array) to Postgres

Question

I have a column with values numeric[]that are the same size. I would like to accept their average size. By this I mean that the mean

{1, 2, 3}, {-1, -2, -3}, and {3, 3, 3}

should be {1, 1, 1}. It is also interesting how to summarize these elements, although I expect that any solution for one will be a solution for the other.

(NB: the length of the arrays is fixed in one table, but can vary between tables. Therefore, I need a solution that does not imply a specific length.)

My initial guess is that I have to use it somehow unnest, as unnestapplied to a column numeric[]aligns all arrays. Therefore, I would like to think that there is a good way to use this using some window processing function + group byto highlight the individual components of each array and sum them up.

-- EXAMPLE DATA
CREATE TABLE A
  (vector numeric[])
;

INSERT INTO A
  VALUES
    ('{1, 2, 3}'::numeric[])
    ,('{-1, -2, -3}'::numeric[])
    ,('{3, 3, 3}'::numeric[])
;

Answer 1

I discovered a solution on my own that is likely to be used by me.

First, we can define a function to add two vectors:

CREATE OR REPLACE FUNCTION vec_add(arr1 numeric[], arr2 numeric[])
RETURNS numeric[] AS
$$
SELECT array_agg(result)
FROM (SELECT tuple.val1 + tuple.val2 AS result
      FROM (SELECT UNNEST($1) AS val1
                   ,UNNEST($2) AS val2
                   ,generate_subscripts($1, 1) AS ix) tuple
      ORDER BY ix) inn;
$$ LANGUAGE SQL IMMUTABLE STRICT;

and a function to multiply by a constant:

CREATE OR REPLACE FUNCTION vec_mult(arr numeric[], mul numeric)
RETURNS numeric[] AS
$$
SELECT array_agg(result)
FROM (SELECT val * $2 AS result
      FROM (SELECT UNNEST($1) AS val
                   ,generate_subscripts($1, 1) as ix) t
      ORDER BY ix) inn;
$$ LANGUAGE SQL IMMUTABLE STRICT;

PostgreSQL CREATE AGGREGATE vec_sum:

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

, , :

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

Answer 2

from http://www.postgresql.org/message-id/[e-mail]

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

---

: , .

, : fooobar.com/questions/37113/... , :

:

CREATE TABLE A
  (vector numeric[], id serial)
;

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

Query:

select  avg(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
group by temp.index

Answer 3

( , , ) . Github PGXN.

a b, vec_add(a, b). , . vec_add(a, 5).

If you want to use the aggregate function SUM, you can find that in aggs_for_vecs , also on PGXN .

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