Group by calculating the non-breaking periods from two (from and to) values

Question

Which Oracle SQL-technique is possible to use for concatenate this rows:

id | from | to
--------------
 a |   20 | 25
 a |   26 | 30
 a |   32 | 40
 b |    1 |  5
 b |    7 | 10
 b |   11 | 20

for such result:

 a |   20 | 30
 a |   32 | 40
 b |    1 |  5
 b |    7 | 20

? Assumed that from and to is start and end of an integer period, and necessary to choose an non-breaking periods for id's

Just looking for right direction and example, can this be done using group by or connect by or something else?

DB is Oracle Database 11g Enterprise Edition Release 11.1.0.6.0 - 64bit Production

Lajos Veres · Accepted Answer · 2013-10-08T07:51:57.443

1

Maybe something similar:

select
    field_id,
    min(field_from),
    max(field_to)
from (
    select
        field_from,
        field_to,
        field_id,
        sum(willSum) over(partition by field_id order by field_from) as GID
    from (
        select
            field_from,
            field_to,
            field_id,
            case when field_from
                    = Lag(field_to) over(partition by field_id order by field_from)
                 then 0 else 1 end as willSum + 1
        from
            rj_mytest
        )
    )
group by
    field_id,
    GID
order by
    field_id,
    min(field_from);

There are a few similar example: https://forums.oracle.com/thread/969005

edited Oct 08 '13 at 07:51

answered Oct 07 '13 at 17:49

Lajos Veres

13,595
7
43
56

You've forgotten `partition by field_id` at line #10 – Egor Skriptunoff Oct 07 '13 at 18:12
@Lajos Veres, you are missing a `+ 1` in the case statement. – Noel Oct 08 '13 at 04:04
@Lajos Veres, appears that this method doesn't depend on the data type, I have no problem to replace the `field_from`, `field_to` from `number` to `date` type, thanks – jurijcz Oct 08 '13 at 08:35

score 1 · Answer 2 · answered Oct 07 '13 at 18:03

select id, max("from") as "from", max("to") as "to"
from (
  select 
    nvl(prv.id, nxt.id) id,
    nxt."from", 
    prv."to", 
    nvl2(nxt."to", 
      row_number() over (partition by nxt.id order by nxt."from"), 
      row_number() over (partition by prv.id order by prv."to")) rn
  from t prv full join t nxt on
    prv.id = nxt.id and prv."to" + 1 = nxt."from"
  where nxt."to" + prv."to" is null 
)
group by id, rn

fiddle

score 1 · Answer 3 · answered Oct 07 '13 at 18:13

Another approach, which uses model clause to assign a group number to a consecutive pairs of values:

select id1
     , min(from1) as from1
     , max(to1)   as to1
  from (select *
          from t1
          model
          partition by (id1)
          dimension by (row_number() over(partition by id1 order by from1) as rn)
          measures(from1, to1, 1 as grp)
          rules(
              grp[rn] = case
                          when from1[cv()] = nvl(to1[cv()-1] + 1, from1[cv()]) 
                          then nvl(grp[cv() - 1], 1)
                          else grp[cv() - 1] + 1
                        end 
               )
)
group by id1
       , grp
order by id1
       , from1

Result:

ID1      FROM1        TO1
--- ---------- ----------
a           20         30 
a           32         40 
b            1          5 
b            7         20

SQLFiddle Demo

and if I tested rightly, if remove just only `+ 1` from line 12, then will be able to handle same end/start of ranges? (a, 20, 25), (b, 25, 30), (a, 30, 40) — jurijcz, Oct 08 '13 at 06:15

Group by calculating the non-breaking periods from two (from and to) values

3 Answers3