Oracle - grouping values ​​by interval

Question

I need to query a table containing the step id value. As a result, the intervals will be indicated, as well as their associated value. Intervals here are defined as "a sequence of contiguous step identifiers sharing the same data value."

I find it difficult to describe it in words, so see this:

From this table

Step ! Data ------------ 1 ! A 2 ! A 3 ! A 5 ! A 6 ! B 10 ! A 

I need the following report

  From ! To ! Data ------------------- 1 ! 3 ! A 5 ! 5 ! A 6 ! null ! B 10 ! null ! A 

I thought lead () would help me here, but failed.

Answer 1

 select min (step) as "from" ,nullif (max (step),max(min(step)) over (partition by data)) as "to" ,data from (select step,data ,row_number () over (partition by data order by step) as n from t ) group by data ,step - n order by "from" 

Answer 2

You can do this by creating a sequence of numbers and subtracting from step . This will be constant when the values ​​are sequential:

 select min(step) as from_step, max(step) as to_step, data from (select t.*, row_number() over (partition by data order by step) as seqnum from t ) t group by (step - seqnum), data; 

EDIT:

It's not clear where NULL comes from. If I assume that they are the last values ​​for each value, you can do:

 select min(step) as from_step, (case when max(step) <> max_step then max(step) end) as to_step, data from (select t.*, max(step) over (partition by data) as max_step row_number() over (partition by data order by step) as seqnum from t ) t group by (step - seqnum), data, max_step;