Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
Home -> Community -> Mailing Lists -> Oracle-L -> RE: median function
here
is a note from the list from awhile aback:
<FONT face=Arial color=#0000ff
size=2>
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, <A
href="http://www.intelligententerprise.com/db_area/archives/1999/992004/celko.shtml">http://www.intelligententerprise.com/db_area/archives/1999/992004/celko.shtml
, modified by me to use the standard EMP table's SAL column:
<FONT face=Arial color=#0000ff
size=2>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
8
9
size=2>AVG(DISTINCTX.SAL) ------------------
<FONT face=Arial color=#0000ff
size=2>Regards,
Larry
G. Elkins [EMAIL PROTECTED]
<FONT face=Tahoma
size=2>-----Original Message-----From: [EMAIL PROTECTED]
[mailto:[EMAIL PROTECTED]On Behalf Of Adams, Matthew (GECP, MABG,
088130)Sent: June 10, 2003 12:40 PMTo: Multiple recipients
of list ORACLE-LSubject: median function
I'm attempting to write a query to calculate the median
of a column of numbers.
The first solution I came across was
Select avg(col1) MEDIAN from ( select
rownum row1, col1 from a where col1 in (select col1 from a )) a <FONT
size=2>where a.row1 in ( select floor(count(*)/2 +.5) from a ) <FONT
size=2> or a.row1 in ( select ceil(count(*)/2+.5)
from a )
This does too many FT scans (4) of table a, so I tried to
write a simpler version using the analytical
functions.
I have gotten as far as
SELECT col1 FROM
( SELECT col1
, row_number() OVER (ORDER BY col1) AS r <FONT
size=2> , CEIL(COUNT(col1) OVER () /2) m FROM
a ) WHERE r =
m
However, this only works for an odd number of values.
IIRC, if an even number of values is present, the median
is defined as the average of the two middle-most
numbers.
Before I spend much more time on this, has anybody already
written one ?
---- Matt Adams - GE Appliances -
[EMAIL PROTECTED] If carpenters built buildings the
way programmers write programs, the first woodpecker to
come along would destroy civilization. - author
unknown
Received on Tue Jun 10 2003 - 11:56:16 CDT