Generating table of intervals (1,10),(11,20),...,(101,200),...,(1001,2000),...

27/04/19 10:36
It's often useful to present statistical results by interval representing frequencies but not necessarily evenly spaced,
example with (task, person) associations,
how many persons with only 1 task, with 2 to 10, with 11 to 20, … with 101 to 200, …


with ranges(lvl, strt, step) as (
    select 2 as lvl, 0 as strt, 10*power(10,trunc((1-2)/9,0)) as step from dual
    union all
    select lvl+1, strt + step, 10*power(10,trunc((lvl-2)/9,0)) from ranges
    where lvl <= 20
)
select 1 as f, 1 as t, 'Only once' as l, 0 as s from dual
union
select
    case when f=0 then 2 else f+1 end,
    t,

   case when f=0 then 2 else f+1 end || ' - ' || t as l, lvl as s
from (
     select lvl-1 as lvl, strt as f, (lead(strt,1,9999999999) over(order by lvl)) as t
    from ranges
);
F
T
L
S
1
1
Only once
0
2
10
2 - 10
1
11
20
11 - 20
2
…




You can play with the depth of the recursion by changing the "where lvl <= 20" condition and change the intervals by changing the "10*power" (e.g. using 100*power", you will get 2-100,101-200, … intervals).
Using this table in a JOIN query with your data source statistics will give you the results presenting the frequencies.

F
T
L
S
1
1
Only once
0
2
100
2 - 10
1
101
200
101 - 200
2
…