| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Mailing Lists -> Oracle-L -> RE: sql/function to calculate the median
> -----Original Message-----
> From: MacGregor, Ian A. [mailto:ian_at_SLAC.Stanford.EDU]
>
> I had asked the median question over a year ago posting a
> proposed solution and asking for better methods. A few
> people psoted a solution out of Celko's book. However,
> testing that solution showed that it did not always give the
> right answer. Larry Elkins came up with the solution which
> costs least to run. But I have misplaced it.
The archives come to the rescue!
http://www.fatcity.com/
sign in
click on "My Lists"
search Oracle-L for "median" in "All Fields" and "All Dates"
http://www.fatcity.com/ListGuru/message.php?id=28&digestid=16500&messageid=2 07447
From: "Larry Elkins"
Date: Mon, 2 Jul 2001 08:49:17 -0500
Subject: RE: Calculating The Median: Error Discovered in Oracle SQL 101
Code
Ian,
Nice stuff!
Since this subject was brought back up, I thought maybe some would be interested in the following. I've never had a need to calculate a median, but, I knew Celko's SQL for Smarties had a few variations and examples from various people, each with caveats. And then there were differences between what he termed statistical and financial mean, and some other things as well. Anyway, a google search turned up another Celko solution. And this one also brings up the concept of weighted median.
Here is his example,
http://www.intelligententerprise.com/db_area/archives/1999/992004/celko.shtm
l ,
modified by me to use the standard EMP table's SAL column:
SQL> SELECT AVG(DISTINCT x.sal)
2 FROM (SELECT F1.sal
3 FROM emp F1, emp F2 4 GROUP BY F1.empno, F1.sal 5 HAVING SUM(CASE WHEN F2.sal = F1.sal 6 THEN 1 ELSE 0 END) 7 >= ABS(SUM(CASE WHEN F2.sal < F1.sal THEN 1 8 WHEN F2.sal > F1.sal THEN -1 9 ELSE 0 END))) 10 X
AVG(DISTINCTX.SAL)
1550
The link above goes into some detail regarding the logic behind the query and how his query finally reached the form above. I may never need to do a median, but, this subject has been a good opportunity for learning. I've tested the above with even, odd, multiple occurences of SAL, null,s etc. It seems to work, but, everyone have a whack at it if you like.
Regards,
Larry G. Elkins
elkinsl_at_flash.net
-- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Jacques Kilchoer INET: Jacques.Kilchoer_at_quest.com Fat City Network Services -- 858-538-5051 http://www.fatcity.com San Diego, California -- Mailing list and web hosting services --------------------------------------------------------------------- To REMOVE yourself from this mailing list, send an E-Mail message to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).Received on Tue Apr 22 2003 - 21:26:39 CDT
 |
 |