Thread: Aggeregate funtion calculating the average value of each same index of an array column in a table
Aggeregate funtion calculating the average value of each same index of an array column in a table
From
LEA KANG
Date:
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.
Re: Aggeregate funtion calculating the average value of each same index of an array column in a table
From
David Johnston
Date:
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.
Re: Aggeregate funtion calculating the average value of each same index of an array column in a table
From
Jasen Betts
Date:
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