SQL Local Minima and Maxima

Question

I have this data:

row_id  type    value
1       a       1
2       a       2
3       a       3
4       a       5        --note that type a, value 4 is missing
5       a       6
6       a       7
7       b       1
8       b       2
9       b       3
10      b       4
11      b       5        --note that type b is missing no values from 1 to 5
12      c       1
13      c       3        --note that type c, value 2 is missing

I want to find the minimum and maximum values for each consecutive "run" within each type. That is, I want to return

row_id  type    group_num   min_value   max_value
1       a       1           1           3
2       a       2           5           7
3       b       1           1           5
4       c       1           1           1
5       c       2           3           3

I am a fairly experienced SQL user, but I've never solved this problem. Obviously I know how to get the overall minimum and maximum for each type, using GROUP, MIN, and MAX, but I'm really at a loss for these local minima and maxima. I haven't found anything on other questions that answers my question.

I'm using PLSQL Developer with Oracle 11g. Thanks!

Answer 1

This is a gaps-and-islands problem. You can use an analytic function effect/trick to finds the chains of contiguous values for each type:

select type,
  min(value) as min_value,
  max(value) as max_value
from (
  select type, value,
    dense_rank() over (partition by type order by value)
      - dense_rank() over (partition by null order by value) as chain
  from your_table
)
group by type, chain
order by type, min(value);

The inner query uses the difference between the ranking of the values within the type and within the entire result set to create the 'chain' number. The outer query just uses that for the grouping.

SQL Fiddle including the result of the inner query.

Answer 2

This is one way to achieve the result you require:

with step_1 as (
    select w.type,
           w.value,
           w.value - row_number() over (partition by w.type order by w.row_id) as grp
    from   window_test w
), step_2 as (
    select x.type,
           x.value,
           dense_rank() over (partition by x.type order by x.grp) as grp
    from   step_1 x
)
select rank() over (order by y.type, y.grp) as row_id,
       y.type, 
       y.grp as group_num,
       min(y.value) as min_val,
       max(y.value) as max_val
from   step_2 y
group by y.type, y.grp
order by 1;