RE: median function

[Ben]

Title: median function

here is a note from the list from awhile aback:

 

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.shtml ,
 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
 11  /

 

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
[EMAIL PROTECTED]

-----Original Message-----
From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]On Behalf Of Adams, Matthew (GECP, MABG, 088130)
Sent: June 10, 2003 12:40 PM
To: Multiple recipients of list ORACLE-L
Subject: 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
where a.row1 in ( select floor(count(*)/2 +.5) from a )
      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
 , 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