From: Tom Lane <t...@sss.pgh.pa.us<mailto:t...@sss.pgh.pa.us>> Date: August 19, 2010 10:25:36 AM PDT To: David Fetter <da...@fetter.org<mailto:da...@fetter.org>> Cc: Kevin Grittner <kevin.gritt...@wicourts.gov<mailto:kevin.gritt...@wicourts.gov>>, Robert Haas <robertmh...@gmail.com<mailto:robertmh...@gmail.com>>, Pavel Stehule <pavel.steh...@gmail.com<mailto:pavel.steh...@gmail.com>>, Greg Stark <gsst...@mit.edu<mailto:gsst...@mit.edu>>, PostgreSQL Hackers <pgsql-hackers@postgresql.org<mailto:pgsql-hackers@postgresql.org>> Subject: Re: wip: functions median and percentile
David Fetter <da...@fetter.org<mailto:da...@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
