sql - Vector (array) addition in Postgres -

sql - Vector (array) addition in Postgres -

i have column numeric[] values have same size. i'd take element-wise average. mean average of

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

should {1, 1, 1}. of involvement how sum these element-wise, although expect solution 1 solution other.

(nb: length of arrays fixed within single table, may vary between tables. need solution doesn't assume length.)

my initial guess should using unnest somehow, since unnest applied numeric[] column flattens out arrays. i'd think there's nice way utilize sort of windowing function + group by pick out individual components of each array , sum them.

-- illustration info create table (vector numeric[]) ; insert values ('{1, 2, 3}'::numeric[]) ,('{-1, -2, -3}'::numeric[]) ,('{3, 3, 3}'::numeric[]) ;

i discovered solution on own 1 use.

first, can define function adding 2 vectors:

create or replace function vec_add(arr1 numeric[], arr2 numeric[]) returns numeric[] $$ select array_agg(result) (select tuple.val1 + tuple.val2 result (select unnest($1) val1 ,unnest($2) val2 ,generate_subscripts($1, 1) ix) tuple order ix) inn; $$ language sql immutable strict;

and function multiplying constant:

create or replace function vec_mult(arr numeric[], mul numeric) returns numeric[] $$ select array_agg(result) (select val * $2 result (select unnest($1) val ,generate_subscripts($1, 1) ix) t order ix) inn; $$ language sql immutable strict;

then can utilize postgresql statement create aggregate create vec_sum function directly:

create aggregate vec_sum(numeric[]) ( sfunc = vec_add ,stype = numeric[] );

and finally, can find average as:

select vec_mult(vec_sum(vector), 1 / count(vector)) a;

sql arrays postgresql vectorization