RE: median function

From: Ben <poelsb_at_post.queensu.ca>
Date: Tue, 10 Jun 2003 09:56:16 -0700
Message-ID: <F001.005AE5B1.20030610091602@fatcity.com>

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, <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:

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

THEN 1 ELSE 0 END)
7 >= ABS(SUM(CASE WHEN
F2.sal < F1.sal THEN 1

WHEN F2.sal > F1.sal THEN -1

ELSE 0 END))) 10
X 11 /

<FONT face=Arial color=#0000ff

size=2>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 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 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 <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