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.orme@imperial.ac.uk
>>
>>
>>
>> ---------------------------(end of
>> broadcast)---------------------------
>> TIP 3: if posting/reading through Usenet, please send an appropriate
>>     subscribe-nomail command to majordomo@postgresql.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.
>
>