Search Postgresql Archives

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

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



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 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}

 

 

I searched in the documentation for “array functions” but I could not find functions useful for me...

 

Thank you so much,

 

           Lea


[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]
[Index of Archives]     [Postgresql Jobs]     [Postgresql Admin]     [Postgresql Performance]     [Linux Clusters]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Books]     [PHP Databases]     [Postgresql & PHP]     [Yosemite]
  Powered by Linux