Oracle - grouping values along interval

Question

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.

It seems the data is considered separately for values A and B? Then: Why is the result (5, 5) for that row, but then (6, NULL) and not (6, 6)? — mathguy, Dec 20 '16 at 12:58
Oh... so, for the LAST occurrence of each "Data" value, the interval should be open-ended, but all other intervals are "close-ended"? — mathguy, Dec 20 '16 at 13:05
You got it right, still this is kinda over-specified: "To" values showing identical as "From" instead of NULL is perfectly acceptable — user2999190, Dec 20 '16 at 13:13
If we would also have `A ! 11` would you like the last range to be '10 ! null` or `10 ! 11'? — David דודו Markovitz, Dec 20 '16 at 13:42

Gordon Linoff · Answer 1 · 2016-12-20T13:09:38.947

1

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;

edited Dec 20 '16 at 13:09

answered Dec 20 '16 at 12:55

Gordon Linoff

1,122,135
50
484
624

The OP didn't explain it very well, but it seems the data is partitioned by value (look at steps 5-6). – mathguy Dec 20 '16 at 12:56
@mathguy . . . That is what this solution does. – Gordon Linoff Dec 20 '16 at 12:58
2

@GordonLinoff shoudn't `row_number` be `partition by data` or at least use `data` in `order` claues? – Kacper Dec 20 '16 at 12:59
Wow, that was fast - and efficient – user2999190 Dec 20 '16 at 13:01
Putting `data` in the `row_number()` is definitely an option. It is not required in this case because the steps seem to be a monotonically increasing sequence. – Gordon Linoff Dec 20 '16 at 13:01
No, it doesn't. Imagine 1, 2, 3, 4, 5, 6 with the "Data" values A,A,B,B,A,A. Here 1, 2, 5, 6 will be grouped together. – mathguy Dec 20 '16 at 13:01
Wow, that was fast - and efficient ! I tried your suggestion and it fits the needs pretty good. This does not provide null value for unterminated intervals though, but I can easily cope with that – user2999190 Dec 20 '16 at 13:03
BS. He didn't change the question; a_horse changed a tag. – mathguy Dec 20 '16 at 13:03
Not sure what OP change you're referrring to ? Don't have currently test data for mathguy's point, but if confirmed, yes, this would be an issue for the purpose – user2999190 Dec 20 '16 at 13:06
Wrong results in case the last range has more than 1 element – David דודו Markovitz Dec 20 '16 at 13:32
@DuduMarkovitz . . . The comparison of the max steps should work regardless of how many steps are in the last range. – Gordon Linoff Dec 20 '16 at 13:37
A sepcial warm thanks to @Gordon Linoff: your edited version works like a charm - only I had to add an else statement to the to_step column statement. Cheers to all, this community is awesome ! – user2999190 Dec 20 '16 at 13:38
Am allowed to notify only one at a time, so here my thx to @mathguy too for detecting the excess grouping – user2999190 Dec 20 '16 at 13:39
No, this puts null even for close range – David דודו Markovitz Dec 20 '16 at 13:46
@DuduMarkovitz . . . The last two ranges are open in the OP's example. I'm not sure what a "close range" would be. – Gordon Linoff Dec 20 '16 at 14:03

David דודו Markovitz · Accepted Answer · 2016-12-20T13:26:14.063

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"

edited Dec 20 '16 at 13:26

answered Dec 20 '16 at 13:16

David דודו Markovitz

31,018
5
48
69

The sample result, shouldn't it have `5 ! null ! A` instead of `5 ! 5 ! A`? – David דודו Markovitz Dec 20 '16 at 13:17
NULL would only indicate the last open interval. As you can see, for value A, a new interval is starting at step 10, that's why then interval starting at 5 is ending there too. – user2999190 Dec 20 '16 at 13:24
Please add 'A ! 11` to the result set and then compare the solutions – David דודו Markovitz Dec 20 '16 at 13:49
Thank you so much @Dudu Markovitz, your solution fills exactly my initial need - the null values indeed bring useful information for open-ended intervals. – user2999190 Dec 20 '16 at 13:49

Oracle - grouping values along interval

2 Answers2