Re: Median/Quantile Aggregate

Using PL/R, two examples... One for median and inter-quartile range

CREATE OR REPLACE FUNCTION r_median(_float8) RETURNS float AS '
median(arg1)
' LANGUAGE 'plr';

CREATE AGGREGATE median (
sfunc = plr_array_accum,
basetype = float8,
stype = _float8,
finalfunc = r_median
);

CREATE OR REPLACE FUNCTION r_iqr(_float8) RETURNS float AS '
IQR(arg1)
' LANGUAGE 'plr';

CREATE AGGREGATE iqr (
sfunc = plr_array_accum,
basetype = float8,
stype = _float8,
finalfunc = r_iqr
);

> Hi,
>
> I know people have asked about this before but I can't find a working
> solution on the web - I've found code for specific instances of
> calculating medians but I need a general aggregate function for
> calculating medians, or more generally any given quantile.
>
> The kind of thing I need to do is to be able to extract the median
> value from a table of 4 million rows, aggregating across more than
> 50,000 grouping values - the sort of thing that is really easy to do
> for averaging:
>
> SELECT grid_id, avg(rs) FROM behr_grid GROUP BY grid_id;
>
> From what I've seen on the web, I know this isn't trivial or
> necessarily fast but at the moment I'm reading the data out into
> something that calculates medians and then matching it back in and
> this doesn't feel particularly elegant!
>
> Any suggestions gratefully received...
>
> Thanks,
> David
>
>
>
> ---------------------------------------
> Dr. David Orme
>
> Department of Biological Sciences
> Imperial College London
> Silwood Park Campus
> Ascot, Berkshire SL5 7PY UK.
>
> Tel: +44 (0)20 759 42358
> Fax: +44 (0)20 759 42339
> e-mail: d(dot)orme(at)imperial(dot)ac(dot)uk
>
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 3: if posting/reading through Usenet, please send an appropriate
> subscribe-nomail command to majordomo(at)postgresql(dot)org so that your
> message can get through to the mailing list cleanly

--
This message has been scanned for viruses and
dangerous content by MailScanner, and is
believed to be clean.