Обсуждение: Aggeregate funtion calculating the average value of each same index of an array column in a table

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,







--
View this message in context:
http://postgresql.1045698.n5.nabble.com/Aggeregate-funtion-calculating-the-average-value-of-each-same-index-of-an-array-column-in-a-table-tp5732388.html
Sent from the PostgreSQL - general mailing list archive at Nabble.com.

On Nov 16, 2012, at 3:52, 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
> ? :
> (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
asum for each array index.  Next level calculate the average for each index.  Final level array_agg to recreate an
array.

David J.

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