Thread: Average of Array?
Is there a function that returns the average of the elements of an array? I'm thinking of something that would work like the avg() aggregate function where it returns the average of all non-NULL values. Can't find anything like it in the docs, and I'd like to make sure I'm not missing something. Thanks, -- Lee Hachadoorian PhD Student, Geography Program in Earth & Environmental Sciences CUNY Graduate Center
Lee Hachadoorian <lee.hachadoorian@gmail.com> writes:
> Is there a function that returns the average of the elements of an
> array? I'm thinking of something that would work like the avg()
> aggregate function where it returns the average of all non-NULL
> values. Can't find anything like it in the docs, and I'd like to make
> sure I'm not missing something.
You could unnest() the array and then apply the aggregate to the result.
unnest() is only built in in 8.4 and later, but it's not terribly hard
to write your own in previous versions.
regards, tom lane
Hello 2010/6/25 Lee Hachadoorian <lee.hachadoorian@gmail.com>: > Is there a function that returns the average of the elements of an > array? I'm thinking of something that would work like the avg() > aggregate function where it returns the average of all non-NULL > values. Can't find anything like it in the docs, and I'd like to make > sure I'm not missing something. it doesn't exists, but it is simple to develop it CREATE OR REPLACE FUNCTION array_avg(double precision[]) RETURNS double precision AS $$ SELECT avg(v) FROM unnest($1) g(v) $$ LANGUAGE sql; Regards Pavel Stehule > > Thanks, > > -- > Lee Hachadoorian > PhD Student, Geography > Program in Earth & Environmental Sciences > CUNY Graduate Center > > -- > Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) > To make changes to your subscription: > http://www.postgresql.org/mailpref/pgsql-sql >
Thanks Tom & Pavel, these are very helpful. On Fri, Jun 25, 2010 at 12:29 PM, Pavel Stehule <pavel.stehule@gmail.com> wrote: > Hello > > 2010/6/25 Lee Hachadoorian <lee.hachadoorian@gmail.com>: >> Is there a function that returns the average of the elements of an >> array? I'm thinking of something that would work like the avg() >> aggregate function where it returns the average of all non-NULL >> values. Can't find anything like it in the docs, and I'd like to make >> sure I'm not missing something. > > it doesn't exists, but it is simple to develop it > > CREATE OR REPLACE FUNCTION array_avg(double precision[]) > RETURNS double precision AS $$ > SELECT avg(v) FROM unnest($1) g(v) > $$ LANGUAGE sql; > > Regards > > Pavel Stehule > >> >> Thanks, >> >> -- >> Lee Hachadoorian >> PhD Student, Geography >> Program in Earth & Environmental Sciences >> CUNY Graduate Center >> >> -- >> Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) >> To make changes to your subscription: >> http://www.postgresql.org/mailpref/pgsql-sql >> > -- Lee Hachadoorian PhD Student, Geography Program in Earth & Environmental Sciences CUNY Graduate Center
Lee Hachadoorian pisze: > Is there a function that returns the average of the elements of an > array? I'm thinking of something that would work like the avg() > aggregate function where it returns the average of all non-NULL > values. Can't find anything like it in the docs, and I'd like to make > sure I'm not missing something. > > Thanks, > > Use unnest() -> http://www.postgresql.org/docs/8.4/interactive/functions-array.html # select avg(unnest) from (select unnest(ARRAY[1,2,null,3])) a; avg --------------------2.0000000000000000 (1 row)