идентификатор группы

дано

with xxx as (
    select 1 as id, 0 as be, 10 as en from dual 
        union all
    select 2 as id, 11 as be, 35 as en from dual 
        union all
    select 3 as id, 36 as be, 50 as en from dual 
        union all
    select 4 as id, 51 as be, null as en from dual 
        union all
    select 5 as id, 0 as be, 45 as en from dual 
        union all
    select 6 as id, 46 as be, 10 as en from dual 
        union all    
    select 7 as id, 0 as be, null as en from dual                        
)
select * from xxx;

подскажите как получить

1	1	0	10
2	1	11	35
3	1	36	50
4	1	51	
5	2	0	45
6	2	46	10
7	3	0	

те идентификатор группы, в которой есть полное множество от 0 до бесконечности, представленной null.

Предполагается, что входные данные корректны и пропусков между интервалами в группе нет.

with xxx as (
    select 1 as id, 1 as group_id, 0 as be, 10 as en from dual 
        union all
    select 2 as id, 1 as group_id, 11 as be, 35 as en from dual 
        union all
    select 3 as id, 1 as group_id, 36 as be, 50 as en from dual 
        union all
    select 4 as id, 1 as group_id, 51 as be, null as en from dual 
        union all
    select 5 as id, 2 as group_id, 0 as be, 45 as en from dual 
        union all
    select 6 as id, 2 as group_id, 46 as be, null as en from dual 
        union all    
    select 7 as id, 3 as group_id, 0 as be, null as en from dual                        
)
select * from xxx;

конечно же

STFF strat_of_group

ну в моём случае start_of_group это be = 0

with xxx as (
    select 1 as id,  0 as be, 10 as en from dual 
        union all
    select 2 as id,  11 as be, 35 as en from dual 
        union all
    select 3 as id,  36 as be, 50 as en from dual 
        union all
    select 4 as id,  51 as be, null as en from dual 
        union all
    select 5 as id,  0 as be, 45 as en from dual 
        union all
    select 6 as id,  46 as be, null as en from dual 
        union all   
    select 7 as id,  0 as be, null as en from dual                        
)
    select id, be, en, connect_by_root id as s_o_g from xxx
    connect by prior en + 1  = be 
    start with be = 0;

ничего лучше в голову не пришло, хотя это не совсем честное решение, если у групп вдруг будет точка пересечения, то облом

Hawker_1
ничего лучше в голову не пришло

UTFG

а что не так у меня? если нет точек пересечения, то всё правильно, если же есть, то группа так и так однозначно не определяется

нужны будут доп условия.

Hawker_1,

  1  with xxx as (
  2      select 1 as id,  0 as be, 10 as en from dual
  3          union all
  4      select 2 as id,  11 as be, 35 as en from dual
  5          union all
  6      select 3 as id,  36 as be, 50 as en from dual
  7          union all
  8      select 4 as id,  51 as be, null as en from dual
  9          union all
 10      select 5 as id,  0 as be, 45 as en from dual
 11          union all
 12      select 6 as id,  46 as be, null as en from dual
 13          union all
 14      select 7 as id,  0 as be, null as en from dual
 15  )
 16  select id,sum(decode(be,0,1)) over (order by id) gr, be,en from xxx
 17* order by 1
SQL> /

        ID         GR         BE         EN
---------- ---------- ---------- ----------
         1          1          0         10
         2          1         11         35
         3          1         36         50
         4          1         51
         5          2          0         45
         6          2         46
         7          3          0

7 rows selected.

.....
stax

Hawker_1
а что не так у меня? если нет точек пересечения, то всё правильно, если же есть, то группа так и так однозначно не определяется

Мелкоскопом по гвоздям.

+

case be - 1 when lag(en) over (order by id) then 0 else 1 end as start_of_group

stax..,

справедливости ради твой запрос базируется на правильности установки id, а мой - нет