Re: Median/Quantile Aggregate

Hi Sean and Adam,

I certainly have considered PL/R but I was initially hoping for
something that meant I didn't have to recompile R with the shared
library enabled on my Mac! From the looks of Adam's messages on the
PL/R helplist, that doesn't look like too much of a struggle.

Since R is already my first choice for statistical analysis (and
indeed, the external software I was using for medians in the first
place!) this seems like a good way forward.

Tom - thanks for the plpgsql suggestion. I'll try and get that
working too, for the practise!

Thanks to all,
David

On 17 May 2005, at 17:34, Adam Witney wrote:

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