Re: 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