Re: Computing truncated mean

> On Feb 7, 7:50 am, "Charles Hooper" <hooperc2000_at_yahoo.com> wrote:
> Continuing with another example that returns a different result
> (which right answer do you want?):
> SELECT
> N,
> PERCENT_RANK() OVER (ORDER BY N) RANKING
> FROM
> T1

Charles,

Excellent; using the Oracle analytical functions is a much better approach than what I posted (probably faster too). However I think the desired function is PERCENT_RANK()'s sister function CUME_DIST(), which would make specifying the "clipping" ranges more straightforward.

As an example, consider as values the squares of the first 30 positive integers { 1, 4, ..., 841, 900 }, e.g.,

http://otb.alma.com/otb.fcgi?func=btable&pagesize=200&qid=60&server=orcl&user=HR

and say we want the truncated mean, clipping off the top and bottom 20 percent of rows. For thirty data points, that would mean clipping off the smallest six and largest six values, i.e., ignoring values (N^2 <= 36) and (N^2 >= 625) respectively.

The "ranks" output by CUME_DIST() make this straightforward:

    /* Better implementation of truncated mean using

       CUME_DIST */
    select avg(n) as average_n,

           count(*) as rows_considered
    from (

      select n, cume_dist_n
      from (
        select n,
               cume_dist() over (order by n) AS cume_dist_n
        from (
          /* Squares of first 30 positive integers */
          select rownum * rownum as n
          from all_tables
          where rownum <= 30
        )
        order by n
      )
      where cume_dist_n > 0.20
        and cume_dist_n <= (1 - 0.20)

Note the ">" on the low end and the "<=" on the high end. I think this produces the correct behavior at the boundaries.

Here's an execution of the above:
http://otb.alma.com/otb.fcgi?func=btable&server=orcl&user=HR&qid=61

... and a verification query explicitly setting the "clipping" limits to values I verified by hand:
http://otb.alma.com/otb.fcgi?func=btable&server=orcl&user=HR&qid=62

Hope that helps!

    John Hinsdale Received on Wed Feb 07 2007 - 09:11:42 CST