Thread: user aggregate function ( calculate the average value of each index of an array column )

Le jeudi 15 novembre 2012 à 19:10 +0100, Myoung-Ah KANG a écrit :
>
>
> 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 values
> column.
>

-- create new 'expanded' table with values unnested
select id, generate_series(1,2), unnest(values) into expanded from
mytable ;

-- calculate the average for each value of the array
-- and re-aggregate the result into an array
with t1 as (
select generate_series, avg(unnest) as average from expanded group by
generate_series order by generate_series
)
select array_agg(average) from t1;


                 array_agg
-------------------------------------------
 {20.0000000000000000,30.0000000000000000}


>
>
> 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}
>
>
>
>
>
> I searched in the documentation for “array functions” but I could not
> find functions useful for me...
>
>
>
> Thank you so much,
>
>
>
>            Lea
>
>

--
Vincent Veyron
http://marica.fr/
Logiciel de gestion des assurances sinistres et des dossiers contentieux pour le service juridique