Re: median value

From: Lisa Kilroy <lkilroy_at_infoseek.com>
Date: 1996/12/13
Message-ID: <32B17F24.76AD_at_infoseek.com>#1/1

On further testing my function won't work if you have multiple records with the same value.

I wrote a pl/sql procedure.

Here it is:

Declare
tran1 number;
tran2 number;
lcount number :=0;
wcount number;
cursor mycur is
select dm_trans from dstats order by dm_trans; begin
open mycur;
delete from medvals
commit;

select count(*) /2 into wcount from dstats; insert into medvals(recline) values (wcount);

if trunc(wcount) = wcount then
for lcount in 1..wcount LOOP

           		fetch mycur into tran1;
        		end loop;
  	fetch mycur into tran2;

insert into medvals (median) values((tran1+tran2)/2); else
insert into medvals (recline) select wcount from dual; for lcount in 1..wcount LOOP

                  fetch mycur into tran1;
end loop;
insert into medvals (median) select tran1 from dual; end if;
close mycur;
end;  

/

Lisa Kilroy wrote:
>
> If you have an even number of records you are
> supposed to avg. those two middle values.
>
> Here's the final function. (except I would like to
> figure out how to make table independent. - Any ideas?)
>
> create or replace
> function median_x (dm_trans IN number)
> return number is
> w_count number;
> medianval dtest.dm_trans%type;
> begin
> select count(*) into w_count from dtest;
> if mod(w_count,2) = 0 then
> select avg(dm_trans) into medianval
> from dtest t where w_count/2 in
> (select count(*) from dtest where dm_trans <=t.dm_trans)
> or (w_count/2)+1 = (select count(*) from dtest
> where dm_trans <=t.dm_trans);
> else
> select dm_trans into medianval
> from dtest t where floor((w_count/2)+1) =
> (select count(*) from dtest where dm_trans <=t.dm_trans);
> end if;
> return (medianval);
> end ;
> /
>
> Ken Denny wrote:
> >
> > Roger Wernersson wrote:
> > >
> > > Lisa Kilroy wrote:
> > > > I need to pull the median value out of a table for a particular field.
> > >
> > > Hello Lisa, no problem!
> > >
> > > SELECT MAX (value)
> > > FROM table
> > > WHERE value <= (
> > > SELECT AVG (value)
> > > FROM table
> > > )
> > >
> > > This might not be the fastest way but it works.
> > >
> > > --
> > > Sport radio: people listening to people watching people having fun
> > > Mailto:roger.wernersson_at_adra.se
> > > BTW: All opinions are mine, all mine, and nobody's but mine.
> >
> > Except that that's not the median. If our table had values 1,2,3,24, and
> > 100 then your solution would yield 24 when the median is really 3.
> > There's no easy way to do it. You have to do a select count(*) from
> > table then find the value where half the count is less than or equal.
> >
> > create or replace
> > function median_x return number is /* assuming value is a number */
> > declare
> > w_count number;
> > medianval table.value%type;
> > begin
> > select count(*) into w_count from table;
> > select value into medianval from table t where floor(w_count/2) =
> > (select count(*) from table where value <=t.value)
> > and rownum=1; /* in case more than one row has the median value */
> > return medianval;
> > end;
> > /
> >
> > Ken Denny
> > Insight Industries, Inc.
> > RTP, NC
> > kdenny_at_interpath.com
Received on Fri Dec 13 1996 - 00:00:00 CET