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
Views: Raw Message | Whole Thread | Download mbox | Resend email
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

Browse pgsql-novice by date

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