Re: [pgsql-hackers] Daily digest v1.11023 (17 messages)

From: Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us<mailto:tgl(at)sss(dot)pgh(dot)pa(dot)us>>
Date: August 19, 2010 10:25:36 AM PDT
To: David Fetter <david(at)fetter(dot)org<mailto:david(at)fetter(dot)org>>
Cc: Kevin Grittner <Kevin(dot)Grittner(at)wicourts(dot)gov<mailto:Kevin(dot)Grittner(at)wicourts(dot)gov>>, Robert Haas <robertmhaas(at)gmail(dot)com<mailto:robertmhaas(at)gmail(dot)com>>, Pavel Stehule <pavel(dot)stehule(at)gmail(dot)com<mailto:pavel(dot)stehule(at)gmail(dot)com>>, Greg Stark <gsstark(at)mit(dot)edu<mailto:gsstark(at)mit(dot)edu>>, PostgreSQL Hackers <pgsql-hackers(at)postgresql(dot)org<mailto:pgsql-hackers(at)postgresql(dot)org>>
Subject: Re: wip: functions median and percentile

David Fetter <david(at)fetter(dot)org<mailto:david(at)fetter(dot)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