# 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, …

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 |

… |