Re: Median Function

It works with this sample table.

But when I tried it on my own table. It took several hours to run the query and return 0 rows. Any idea?

Is it easy to create a function similar to AVG, SUM, etc.?

Thanks,
Xiaobin

In article <89r5vc$a6a$1_at_nnrp1.deja.com>,   Thomas J. Kyte <tkyte_at_us.oracle.com> wrote:
> In article <20000304010047.03253.00000149_at_ng-fl1.aol.com>,
> hrrascal_at_aol.com (HRrascal) wrote:
> > Can anyone help me with a fubction that will return the median of a
> group of
> > numbers? Thanks for any help.
> >
> > E Parbuoni
> > eparbuon_at_cvm.fda.gov
> >
>
> Here are 2 examples. the first query works in all releases (7.1 and
> up). the second (little more efficient) works in 8.1.5 and up only.
>
> create table t as select user_id from all_users
> where rownum < 11;
>
> 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))))
> /
>
> 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) ) )
> /
>
> --
> Thomas Kyte tkyte_at_us.oracle.com
> Oracle Service Industries
> http://osi.oracle.com/~tkyte/index.html
> --
> Opinions are mine and do not necessarily reflect those of Oracle Corp
>
> Sent via Deja.com http://www.deja.com/
> Before you buy.
>

Sent via Deja.com http://www.deja.com/
Before you buy. Received on Wed Mar 08 2000 - 21:23:43 CET