Re: SQL Question -- Stratifying Data

Ken Moore wrote:
>
> I was wondering what the best method is to allow a user to create a
> query which returns stratified data from continuous numeric data. For
> example, say I have a table with a value for age. I want to write a
> select statement which returns four age categories from all the possible
> age values in the table. As these categories will change frequently, I
> don't want to create and store an age strata column in the table.
>
> I know that the DECODE function will allow me to categorize interval
> data, but I need a method to do the same for data which may be contained
> within a continuous range.
>
> Thanks for your help

Here is an example :

create a table with the ranges :

create table value_range (
range_id varchar2(10),
range varchar2(10),
val_min number,
val_max number);

insert into value_range values ('strat1','0-10','0','10');
insert into value_range values ('strat1','11-20','11','20');
insert into value_range values ('strat1','21-30','21','30');
insert into value_range values ('strat1','31-999','31','999');

create table value_data (
col1 varchar2(10),
val number);

insert into value_data values ('A','5');
insert into value_data values ('B','10);
insert into value_data values ('C','12');
insert into value_data values ('D','21');
insert into value_data values ('E','50');

select range,count(*) from value_range,value_data  where val between val_min and val_max
   and range_id = 'strat1'
 group by range;

The principle is to create a cartesian product and select only the rows where the value falls into the range.

In this way you can define different categories (strat1,strat2,...). Or you can modify the val_min and val_max of strat1.

Be carefull that within one category, the different ranges do not overlap and cover all possible the values.

If you have real values instead of integers, you also have to be very carefull about the val_min and val_max. (to avoid that a value falls between the val_max of 1 range and the val_min of the next range.

Leo

Leo Put
Vlaamse UitgeversMaatschappij Belgium
E-mail : lp2014_at_vum.be Received on Thu Jul 31 1997 - 00:00:00 CDT