From: Tom Lane <tgl@sss.pgh.pa.us

Date: August 19, 2010 10:25:36 AM PDT

To: David Fetter <david@fetter.org

Cc: Kevin Grittner <Kevin.Grittner@wicourts.gov , Robert Haas <robertmhaas@gmail.com , Pavel Stehule <pavel.stehule@gmail.com , Greg Stark <gsstark@mit.edu , PostgreSQL Hackers <pgsql-hackers@postgresql.org

Subject: Re: wip: functions median and percentile

David Fetter <david@fetter.org writes:

On Thu, Aug 19, 2010 at 12:12:12PM -0500, Kevin Grittner wrote:

http://www.merriam-webster.com/dictionary/median

If you do a google search for "median" and poke around, you'll find

many places where this is the only definition mentioned; the others

seem to be rather infrequently used. Why not make the commone usage

convenient?

The reason not to is the same reason that MEDIAN doesn't appear in the

SQL standard, namely that what's common in one field is wrong in

another.

Hmm, do you have any *evidence* that that's why it's not in the standard?

My own take on that is that it's reasonably probable that the SQL

committee might standardize a function by that name someday. What we

need to be worrying about is the prospect that if there are multiple

definitions for the term, they might pick a different one than we did.

A name like "arithmetic_median" seems much less likely to get blindsided

by future standardization.

regards, tom lane

Median is in the standard, you just have to look a little harder, under the section

on inverse distribution functions:

SELECT PERCENTILE_DIST(0.5) WITHIN GROUP (order by x) ...

or

SELECT PERCENTILE_CONT(0.5) WITHIN GROUP (order by x) ...

Depending on whether you want a discrete or continuous median.

Oracle added support for the inverse distribution functions in Oracle 9, which

is perhaps why you can find it in the standard.

Oracle added the "median(x)" aggregate as a synonym for "percentile_cont(0.5) within group (order by x)"

in Oracle 10.

My money would be on this become standardized at some point, especially since it is a

much friendlier syntax.

Regards,

Caleb