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

Search Discussions

Related Discussions

Discussion Navigation
viewthread | post
Discussion Overview
grouppgsql-hackers @
categoriespostgresql
postedAug 19, '10 at 9:28p
activeAug 19, '10 at 9:28p
posts1
users1
websitepostgresql.org...
irc#postgresql

1 user in discussion

Caleb Welton: 1 post

People

Translate

site design / logo © 2022 Grokbase