Re: Median Function?

From: Thomas Kyte <tkyte_at_oracle.com>
Date: 23 Jan 2003 17:52:58 -0800
Message-ID: <b0q69q01f1@drn.newsguy.com>

In article <H96vxr.3LI_at_campus-news-reading.utoronto.ca>, "Okey says...
>
>Hi all,
>
>Oracle does not seem to have a "Median" function. Am I just missing it, or
>is there some easy way to implement it? I would like to be able to do
>something like:
>
>select barcode, median(s635)
>from raw_data
>group by barcode;
>
>Any suggestions very much appreciated!
>Paul
>
>

9i has it:

scott_at_ORA920.US.ORACLE.COM> SELECT deptno,

       PERCENTILE_CONT(0.5) WITHIN GROUP (ORDER BY sal DESC) med_cont,
       PERCENTILE_DISC(0.5) WITHIN GROUP (ORDER BY sal DESC) med_disc

  FROM emp
 GROUP BY deptno
/
  2 3 4 5 6
DEPTNO MED_CONT MED_DISC
------ ---------- ----------

    10       2450       2450
    20       2975       2975
    30       1375       1500

cont - is continous, it is what most of us think of as median (averages middle two numbers in the event of a even number of observations)

disc - is discrete, it picks a number that actually exists in the set

In 816 and up, this can be done with row_number and count:

scott_at_ORA920.US.ORACLE.COM> select deptno, avg(sal) median_sal   2 from (
  3 select deptno, sal,

  4         count(*) over (partition by deptno) cnt,
  5             row_number() over (partition by deptno order by sal) rn
  6    from emp
  7         ) a

  8 where rn in ( ceil(cnt/2), floor(cnt/2+1) )   9 group by deptno
 10 /

DEPTNO MEDIAN_SAL
------ ----------

    10       2450
    20       2975
    30       1375

scott_at_ORA920.US.ORACLE.COM>

as well.

--
Thomas Kyte (tkyte@oracle.com)             http://asktom.oracle.com/ 
Expert one on one Oracle, programming techniques and solutions for Oracle.
http://www.amazon.com/exec/obidos/ASIN/1861004826/  
Opinions are mine and do not necessarily reflect those of Oracle Corp 

Received on Thu Jan 23 2003 - 19:52:58 CST