Re: computing median

I think the following is a counterexample for the suggestion.

Take the three numbers 1,1,10. The median is 1. However, placing these numbers in a table called testmedian and doing an ntile on them gives the following:

SQL> select a, ntile(2) over (order by a) q from testmedian;

         A Q
---------- ----------

         1          1
         1          1
        10          2

The max of the first ntile is 1 and the min of the second is 10. Their average is 5.5 which is not the median.

Frank Hubeny

Jonathan Lewis wrote:

> I think you could do this with the analytic NTILE()
> function. Take the NTILE(2) of the data, and the
> median ought to be the average of the max() of the
> 1st ntile and the min() of the second ntile.
>
> --
> Jonathan Lewis
> Yet another Oracle-related web site: http://www.jlcomp.demon.co.uk
>
> Practical Oracle 8i: Building Efficient Databases
> Publishers: Addison-Wesley
>
> Reviews at: http://www.jlcomp.demon.co.uk/book_rev.html
>
> Scott Watson wrote in message ...
> >I don't believe there is a function that does that you need. The only
> >solution is to write one yourself in PL?SQL.
> >
> >eg exec :median := my_stats.median(tablename, column_name);
> >
> >inside the function you would have to calculate the number of rows in the
> >column and then if it is odd take the middle value or else use the two
> >values around the center and add them together and divide by 2.
> >
> >Scott.
Received on Mon Apr 23 2001 - 01:28:57 CDT