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

On Nov 16, 2012, at 3:52, LEA KANG <makang71(at)gmail(dot)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
> ? :
> (Suppose that avg_mytable is the aggregation function name.)
>
> Ex1) Select avg_mytable (values) from mytable ;
>
> avg_mytable (values)
> ------------------------
> { 20, 30}
>
>
> (Explication of the results: 20 because (10+20+30)/3 , 30 because
> (0+30+60)/3)
>
> Ex2) Select type, avg_mytable (values) from mytable Group by type ;
>
> Type | avg_mytable (values)
> ---------------------------------------------
> 1 | { 15, 15}
> 2 | { 30, 60}
>
>
> Thank you so much,
>
>

In a sub-query unnest the array and use generate_series to keep track of the array index position. Next level perform a sum for each array index. Next level calculate the average for each index. Final level array_agg to recreate an array.

David J.