Re: Aggeregate funtion calculating the average value of each same index of an array column in a table

On 2012-11-16, LEA KANG <makang71@gmail.com> wrote:
> Hi,
>
> I have a table with several lines as following;
>
> -    Create table mytable (type number ,  values  integer [2]) ;
>
> -    Insert into mytable values (1,  ‘{ 10, 0 }’ );
> -    Insert into mytable values (1,  ‘{ 20, 30 }’ );
> -    Insert into mytable values (2,  ‘{30,  60}’ );
>
> (In fact, the array size is very big (ex. values [10000]) but the size is
> fix.   In order to simplify the example, I used an array integer [2]).

>
> I would like to obtain the average value of each index of the array column,
> values column.
>
> Is it possible to create an aggregate function which can works as following
> ? :

yes it's absolutely possible, see "create aggregate" in the manual.

last time I looked arrays were O(n^2) in plpgsql, so don't use that
language if performance is important.

may want to use bigint[] for the accumulaqtor array and possibly
float[] for the result.

--
⚂⚃ 100% natural