SQL> select PERCENTILE_CONT(0.5)
2 within group (ORDER BY sal DESC) sal
3 from emp;
SAL
1550
hth
connor
- Ben <[EMAIL PROTECTED]> wrote: > median
functionhere 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.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
> 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-----
> Adams, Matthew (GECP, MABG, 088130)
> Sent: June 10, 2003 12:40 PM
> To: Multiple recipients of list ORACLE-L
>
>
> 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
>
Connor McDonald
web:
http://www.oracledba.co.uk
web:
http://www.oaktable.net
email: [EMAIL PROTECTED]
"GIVE a man a fish and he will eat for a day. But TEACH him how to fish, and...he will
sit in a boat and drink beer all day"
Yahoo! Plus - For a better Internet experience
http://uk.promotions.yahoo.com/yplus/yoffer.html
--
Please see the official ORACLE-L FAQ: http://www.orafaq.net
--
Author: =?iso-8859-1?q?Connor=20McDonald?=
INET: [EMAIL PROTECTED]
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: [EMAIL PROTECTED] (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 Wed Jun 11 2003 - 21:15:43 CDT
