Re: Computing truncated mean

From: John K. Hinsdale <hin_at_alma.com>
Date: 6 Feb 2007 14:45:18 -0800
Message-ID: <1170801918.142872.27950@k78g2000cwa.googlegroups.com>

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!
-- John Hinsdale Received on Tue Feb 06 2007 - 16:45:18 CST