And in 8i, prior to 9i's PERCENTILE_CONT, one option for doing it in a
single pass is to use NTILE to split in "halves" and use the lower and upper
boundary values to average when even number of rows, or the upper value of
the lower half when odd number of rows. Of course, 9i's percentile_cont, as
demonstrated by Connor, is much easier. Anyway, here's one 8i way (8.1.6 and
beyond) of doing it:
SQL> SELECT CASE WHEN MOD(CNT,2) = 0 Then
2 (P1+P2)/2 -- When even number of rows (mod=0) then average
the boundary values
3 ELSE
4 P1 -- Else when odd number of rows take the max value
from lower half
5 END CASE
6 FROM (SELECT MIN(CNT) CNT, -- Use MAX/MIN, whatever, or do a GROUP BY
7 MAX(CASE WHEN BUCKET = 1 THEN SAL END) P1, -- Max value
of lower half
8 MIN(CASE WHEN BUCKET = 2 THEN SAL END) P2 -- Min value
of upper half
9 FROM (SELECT SAL,
10 NTILE(2) OVER (ORDER BY sal) Bucket, -- Split into
two buckets
11 Count(*) OVER () CNT -- Get the
count
12 FROM EMP)
13 GROUP BY CNT)
14 /
CASE
----------
1550
Yeah, I like 9i's PERCENTILE_CONT a lot better ;-)
Regards,
Larry G. Elkins
[EMAIL PROTECTED]
> -----Original Message-----
> From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] Behalf Of
> Connor McDonald
> Sent: Wednesday, June 11, 2003 9:49 PM
> To: Multiple recipients of list ORACLE-L
> Subject: RE: median function
>
>
> 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).
>
--
Please see the official ORACLE-L FAQ: http://www.orafaq.net
--
Author: Larry Elkins
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).
