Re: Computing truncated mean

From: Walt <walt_askier_at_SHOESyahoo.com>
Date: Wed, 07 Feb 2007 10:32:50 -0500
Message-ID: <12sjs92pkk6d735@corp.supernews.com>

John K. Hinsdale wrote:
> On Feb 6, 4:40 pm, Walt <walt_askier_at_SHOESyahoo.com> wrote:

>> A truncated mean or a trimmed average is the average after throwing away
>> the top and bottom n percent.
>>
>> I seem to recall a built-in function in Oracle that did this fairly
>> simply, but I can't seem to locate it in the docs.  I could cook up my
>> own in PL-SQL, but if there's something already out there I'd prefer to
>> use it. Anybody know a simple way to do it?

>
> Walt,
> Don't know of a built-in. You can do it in SQL though; no need
> to resort to PL/SQL. Below is an example on the EMPLOYEES
> table of demo HR schema, computing avarage salary, but
> ignoring top the 10% and bottom 10% of rows. Season
> to taste. Note it "scans" EMPLOYEES twice, once to
> count it and once to enumerate/sort it.
>
> /************* Cut here ******************/
> select avg(salary)as avarage_salary,
> count(*) as rows_considered,
> max(total_count) - count(*) as rows_ignored,
> max(total_count) as total_rows
> from ( select OQ.*, rownum / OQ.total_count as row_frac
> from /* Table, ORDERED by salary */
> ( select E.*, C.total_count
> from employees E,
> ( select count(*) as total_count from employees )
> C
> /* Don't count NULL rows */
> where E.salary is not null
> order by E.salary
> ) OQ
> )
> /* Ignore top and bottom deciles */
> where row_frac >= 0.10
> and row_frac <= 0.90
> /************* Cut here ******************/
>
>
> Here is the above query "in action" on a live database:
> http://otb.alma.com/otb.fcgi?func=btable&server=orcl&user=HR&qid=59
>
>
> Hope this helps!

Thanks. This looks pretty good, although in the dim recesses of my memory I seem to recall a function call something like "avg_trim(salary,.10,.90)" that just did this. Maybe I'm misremembering - it would have been seven years ago, probably on Oracle 7.

BTW, I think you want to include a non-null clause in your total_count query, otherwise you're including null rows in the denominator when you calculate row_frac.

//Walt Received on Wed Feb 07 2007 - 09:32:50 CST