Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

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.

like image 474
user2999190 Avatar asked Dec 20 '16 12:12

user2999190


1 Answers

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"           
like image 52
David דודו Markovitz Avatar answered Oct 12 '22 23:10

David דודו Markovitz