Re: Re: [BUGS] BUG #4916: wish: more statistical functions (median, percentiles etc)

On Sun, Jul 12, 2009 at 5:53 PM, David Fetter<david(at)fetter(dot)org> wrote:
>> [mode would also be useful, as an explicit function, though we can get it
>> easily enough using count(1) order by count desc].
>
> You can get that with windowing functions, too. :)
>
>> According to google, this has been a wish since at least year 2000
>> for various people, but doesn't seem to be implemented.
>
> Patches are welcome :)

Trouble in writing such aggregate, would be that it has to keep full
set, in order to sort it, and choose n/2 element (in case set contains
odd number of elements), or ([n/2]+[(n/2)+1])/2 otherwise.

I usually, if in need to calculate it , I usually do it like that (but
that's pretty slow on large sets):

pg84(at)atlantic:~$ psql
psql (8.4beta2)
Type "help" for help.

pg84=# create table foo(a int not null);
CREATE TABLE
pg84=# insert into foo(a) select random()*666 from generate_series(1,666);
INSERT 0 666
pg84=# select a from foo order by a limit 1 offset (select count(*)/2 from foo);
a
-----
321
(1 row)

(yeah, I know it is lame).

So , I think in order to create such patch, the aggregate would have
to secretly create some temporary table, to store the set first...

--
GJ