pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedAverage of Array?
5+ messages / 4 participants
[nested] [flat]
* Average of Array?
@ 2010-06-25 16:21 Lee Hachadoorian <lee.hachadoorian@gmail.com>
0 siblings, 3 replies; 5+ messages in thread
From: Lee Hachadoorian @ 2010-06-25 16:21 UTC (permalink / raw)
To: pgsql-sql
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
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: Average of Array?
@ 2010-06-25 16:29 Tom Lane <tgl@sss.pgh.pa.us>
parent: Lee Hachadoorian <lee.hachadoorian@gmail.com>
2 siblings, 0 replies; 5+ messages in thread
From: Tom Lane @ 2010-06-25 16:29 UTC (permalink / raw)
To: Lee Hachadoorian <lee.hachadoorian@gmail.com>; +Cc: pgsql-sql
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
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: Average of Array?
@ 2010-06-25 16:29 Pavel Stehule <pavel.stehule@gmail.com>
parent: Lee Hachadoorian <lee.hachadoorian@gmail.com>
2 siblings, 1 reply; 5+ messages in thread
From: Pavel Stehule @ 2010-06-25 16:29 UTC (permalink / raw)
To: Lee Hachadoorian <lee.hachadoorian@gmail.com>; +Cc: pgsql-sql
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
>
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: Average of Array?
@ 2010-06-25 16:34 Lee Hachadoorian <lee.hachadoorian@gmail.com>
parent: Pavel Stehule <pavel.stehule@gmail.com>
0 siblings, 0 replies; 5+ messages in thread
From: Lee Hachadoorian @ 2010-06-25 16:34 UTC (permalink / raw)
To: 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
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: Average of Array?
@ 2010-06-25 19:33 Ireneusz Pluta <ipluta@wp.pl>
parent: Lee Hachadoorian <lee.hachadoorian@gmail.com>
2 siblings, 0 replies; 5+ messages in thread
From: Ireneusz Pluta @ 2010-06-25 19:33 UTC (permalink / raw)
To: Lee Hachadoorian <lee.hachadoorian@gmail.com>; +Cc: pgsql-sql
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)
^ permalink raw reply [nested|flat] 5+ messages in thread
end of thread, other threads:[~2010-06-25 19:33 UTC | newest]
Thread overview: 5+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2010-06-25 16:21 Average of Array? Lee Hachadoorian <lee.hachadoorian@gmail.com>
2010-06-25 16:29 ` Tom Lane <tgl@sss.pgh.pa.us>
2010-06-25 16:29 ` Pavel Stehule <pavel.stehule@gmail.com>
2010-06-25 16:34 ` Lee Hachadoorian <lee.hachadoorian@gmail.com>
2010-06-25 19:33 ` Ireneusz Pluta <ipluta@wp.pl>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox