CODEWITHSUNDEEP
sqloracle-databasegroup-bylead
user2999190
user2999190

Reputation: 73

Oracle - grouping values along interval

I need to query a table, containing a step id + value. The result will list the intervals, along with their associated value. Intervals here are defined as "succession of contiguous ids of steps, sharing same data value".

I'm having a hard time describing it in words, so please 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 out here, but did not succeed.

Upvotes: 7

Views: 81

Answers (2)

David דודו Markovitz
David דודו Markovitz

Reputation: 44971

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"

Upvotes: 1

Gordon Linoff
Gordon Linoff

Reputation: 1270703

You can do this by generating a sequence of numbers and subtracting from the step. This will be a 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 is not quite clear where the NULLs are coming from. If I speculate 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;

Upvotes: 1

Related Questions