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

Median/Quantile Aggregate

From: David Orme <d(dot)orme(at)imperial(dot)ac(dot)uk>
To: pgsql-novice(at)postgresql(dot)org
Subject: Median/Quantile Aggregate
Date: 2005-05-17 15:46:36
Message-ID: 50FA52F0-CB4B-4642-BA9B-F894EB4E0055@ic.ac.uk (view raw or flat)
Thread:
Lists: pgsql-novice
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



Responses

pgsql-novice by date

Next:From: Tom LaneDate: 2005-05-17 15:46:42
Subject: Re: BAD SU operator at Postgresql startup (during boot)
Previous:From: Hal ShearerDate: 2005-05-17 14:53:28
Subject: BAD SU operator at Postgresql startup (during boot)

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