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