Oracle and statistical calculations using groups

From: Ray Chow <aa813_at_hwcn.org>
Date: 1997/03/25
Message-ID: <AeJOz4lbRYKG092yn@hwcn.org>#1/1

I am trying to calculate some statistics on an Oracle table, including median, deciles, and quartiles. In February, Solomon.Yakobson_at_entex.com posted a method for calculating the median of a table, and I have adapted this to calculate the other statistics, all of which depend on sorting the specified column in ascending order.

e.g. to calculate the median, decile-1, quartile-1, quartile-3, and decile-9 of column x I can do this:

create index i_tmp$x on tmp(x);
select sum(decode(rownum,half_count,decode(remainder,0,x,0),

           half_count+1,decode(remainder,0,x,2*x),0))/2 median,
       sum(decode(rownum,d1_pos,x,0)) d1,
       sum(decode(rownum,q1_pos,x,0)) q1,
       sum(decode(rownum,q3_pos,x,0)) q3,
       sum(decode(rownum,d9_pos,x,0)) d9
from tmp,
      (select trunc(count(*)/2) half_count,
              mod(count(*),2) remainder,
              round(9*(count(*)+1)/10) d1_pos,
              round(3*(count(*)+1)/4) q1_pos,
              round((count(*)+1)/4) q3_pos,
              round((count(*)+1)/10) d9_pos,
              min(x) min_value
      from tmp where x is not null)

where x >= min_value;

This works well if I want the statistics for the entire table. My problem is that these data are associated with groups (e.g. company or geographic region), and I need to calculate these statistics for each group. The problem is that the subquery operates on the entire table, not on the subgroups. I suppose I could use a PL/SQL cursor FOR loop to go through all the different values in the group, but that's tedious. Is there a better way?

Thanks.

-- 
ray chow / canada / aa813_at_hwcn.org
<URL:http://www.hwcn.org/~aa813/>

Received on Tue Mar 25 1997 - 00:00:00 CST