Re: User defined function for median

From: Thomas Kyte <tkyte_at_us.oracle.com>
Date: Sat, 22 Jan 2000 12:33:54 -0500
Message-ID: <68qj8s4tav9qn7k8ncb07mlssunck7rc2i@4ax.com>

A copy of this was sent to "Dale Sanders" <dsanders_at_uswest.net> (if that email address didn't require changing) On Sat, 22 Jan 2000 08:36:21 -0700, you wrote:

>I can't seem to find a function in Oracle for calculating the median on a
>column.
>
>Is anyone aware of a libary, somewhere on the web, of user defined functions
>that I might contain a median function?
>
>We can write one ourselves, but I would rather reuse an existing one.
>
>Thanks...
>

You can do it in SQL as well as writing your own plsql function to do it.

Here is one method of getting a median in sql:

create or replace view number_data as select user_id datum from t
/

SELECT AVG(DISTINCT DATUM)
  FROM (SELECT CP1.DATUM

           FROM NUMBER_DATA CP1, NUMBER_DATA CP2
          GROUP BY CP1.DATUM
         HAVING SUM(DECODE(CP1.DATUM, CP2.DATUM, 1, 0)) >=
                         ABS(SUM(SIGN(CP1.DATUM - CP2.DATUM))))

/

that should work in all releases. An Oracle8i, release 8.1 query that would do it (maybe faster) as well would be:

create table t as select user_id from all_users;

select avg( user_id )
  from ( select user_id, rownum r

           from ( select user_id
                    from t
                   order by user_id )
       ),
       ( select count(*) cnt
           from T
       )

 where ( cnt/2 = trunc(cnt/2) AND ( r = cnt/2 or r = cnt/2+1 ) )

    or ( cnt/2<> trunc(cnt/2) and ( r = ceil(cnt/2) ) ) /

--
See http://osi.oracle.com/~tkyte/ for my columns 'Digging-in to Oracle8i'... Current article is "Part I of V, Autonomous Transactions" updated June 21'st  

Thomas Kyte                   tkyte_at_us.oracle.com
Oracle Service Industries     Reston, VA   USA

Opinions are mine and do not necessarily reflect those of Oracle Corporation Received on Sat Jan 22 2000 - 11:33:54 CST