Skip site navigation (1) Skip section navigation (2)

Re: Median/Quantile Aggregate

From: David Orme <d(dot)orme(at)imperial(dot)ac(dot)uk>
To: pgsql-novice(at)postgresql(dot)org
Subject: Re: Median/Quantile Aggregate
Date: 2005-05-17 16:59:36
Message-ID: 717A5003-3A45-4314-AEDE-0306B5C24806@ic.ac.uk (view raw or flat)
Thread:
Lists: pgsql-novice
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.
>
>


In response to

pgsql-novice by date

Next:From: postgresql.orgDate: 2005-05-17 17:10:51
Subject: Re: problems with postgresql.msi (installing 8.0.2)
Previous:From: operationsengineer1Date: 2005-05-17 16:46:44
Subject: Re: concepts?

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group