Re: Is it possible to have a max function in SQL that exlcudes outliners(values 20x average)

The following will give you the values that are less than 2 times the avg of all numbers. You can AND in other qualifiers on the HAVING clause.

select a.i
from x a, x b
group by a.i
having sum (sign(1-sign(b.i - a.i))) > 2*avg(a.i)

This kind of stuff comes from this book:

http://www.amazon.com/exec/obidos/ASIN/0964981203/qid%3D964102803/103-813947 7-4319863

HTH,
Tom Best

dsad <adads_at_asdad.com> wrote in message
news:aIsd5.32616$Ra6.480043_at_zombie.newscene.com...
>
> but does this exlcude the very large and very small values from the
 average it
> self? if the vlaues are
>
> 3
> 12
> 32
> 1
> 0000023
> 23
> 43453343
>
> will this exlcude the very small and the very large ones?
>
>
> In article <8l4hgo$ptk$1_at_nnrp1.deja.com>, oratune_at_aol.com wrote:
> >In article <lXhd5.29262$Ra6.433828_at_zombie.newscene.com>,
> > dsad_at_hotmail.com (dsad) wrote:
> >> we need to get max of a value for an account over a time period, but
 we want
> >> to exclude any values that are 20x greater or less than average for
 that
> >> account over the period
> >>
> >> any ideas?
> >>
> >
> >You certainly won't want to use 'between' for the value comparisons as
> >that is inclusive of the boundary conditions; you can use 'between' for
> >the date comparisons, though. Let's try this:
> >
> >declare
> > a_val number;
> > mval number;
> > cursor get_av is
> > select avg(value) from mytable;
> > cursor get_maxval (aval in number, s_dt in date, e_dt in date) is
> > select max(value)
> > from mytable
> > where value > aval/20
> > and value < aval*20
> > and proc_dt between s_dt and e_dt;
> >begin
> > open get_av;
> > fetch get_av into a_val;
> > close get_av;
> > open get_maxval(a_val, sysdate-1, sysdate);
> > fetch get_maxval into mval;
> > close get_maxval;
> > ....
> >end;
> >/
> >
> >After the proper max is computed you can do with it what you like; I've
> >read the other offerings and I believe this to be more along the lines
> >of what you are looking for since this variant excludes any values >=
> >20*(avg value) and any values <= (avg value/20).
> >
Received on Thu Jul 20 2000 - 00:00:00 CDT