Обсуждение: avg() of array values

Hi,

I'm trying to get an avg value of 2 dates (to get to the month that most
part of an interval is in). I found SP's to generate rows from array
values, which I figured I could use with the avg aggregate, but to my
surprise:

*> create or replace function explode_array(in_array anyarray) returns
setof anyelement as
-> $$
$>
$>     select ($1)[s] from generate_series(1,array_upper($1, 1)) as s;
$>
$> $$
-> language sql immutable;
CREATE FUNCTION
*> select * from explode_array(array[1, 3]);
 explode_array
---------------
             1
             3
(2 rows)

*> select avg(*) from explode_array(array[1, 3]);
          avg
------------------------
 1.00000000000000000000
(1 row)

*> select sum(*) from explode_array(array[1, 3]);
 sum
-----
   2
(1 row)


I would have expected an avg of 2.0 and a sum of 4, where am I going wrong?
Or is there a better way to get the avg of 2 dates (median would
suffice, but I don't know the interval length in days before hand - and
thus not the middle point of the interval).

--
Alban Hertroys
alban@magproductions.nl

magproductions b.v.

T: ++31(0)534346874
F: ++31(0)534346876
M:
I: www.magproductions.nl
A: Postbus 416
   7500 AK Enschede

// Integrate Your World //

On Tue, Sep 11, 2007 at 05:50:38PM +0200, Alban Hertroys wrote:
> *> select avg(*) from explode_array(array[1, 3]);
>           avg
> ------------------------
>  1.00000000000000000000
> (1 row)

avg(*) is not valid, same for sum(*) the reaosn you get the answer you
do it because postgres replaces the * with a 1, whic doesn't change the
fact that the query is wrong.

Have a nice day,
--
Martijn van Oosterhout   <kleptog@svana.org>   http://svana.org/kleptog/
> From each according to his ability. To each according to his ability to litigate.

On 9/11/07, Alban Hertroys <alban@magproductions.nl> wrote:
> I would have expected an avg of 2.0 and a sum of 4, where am I going wrong?

This works for me:

select avg(a) from explode_array(array[1, 3]) a;

        avg
--------------------
 2.0000000000000000
(1 row)

Martijn van Oosterhout wrote:
> avg(*) is not valid, same for sum(*)

Doh!

Thanks.

--
Alban Hertroys
alban@magproductions.nl

magproductions b.v.

T: ++31(0)534346874
F: ++31(0)534346876
M:
I: www.magproductions.nl
A: Postbus 416
   7500 AK Enschede

// Integrate Your World //