Есть интервалы - нужны "дырки"

Oracle 8i SE (т.е. без аналитики, pipeline ...)
Результат нужен в SQL.
Есть интервалы - нужны "дырки".
Тестовые данные:

CREATE TABLE tmp(
  Id NUMBER,
  d1 DATE,
  d2 DATE)
/

INSERT INTO tmp(id, d1, d2)
  VALUES(1, TO_DATE('01.01.2000', 'DD.MM.YYYY'), TO_DATE('01.01.2001', 'DD.MM.YYYY'))
/

INSERT INTO tmp(id, d1, d2)
  VALUES(2, TO_DATE('01.01.2000', 'DD.MM.YYYY'), TO_DATE('01.01.2002', 'DD.MM.YYYY'))
/
INSERT INTO tmp(id, d1, d2)
  VALUES(2, TO_DATE('01.01.2000', 'DD.MM.YYYY'), TO_DATE('01.01.2002', 'DD.MM.YYYY'))
/
INSERT INTO tmp(id, d1, d2)
  VALUES(2, TO_DATE('01.01.2001', 'DD.MM.YYYY'), TO_DATE('01.01.2004', 'DD.MM.YYYY'))
/

INSERT INTO tmp(id, d1, d2)
  VALUES(3, TO_DATE('01.01.2000', 'DD.MM.YYYY'), TO_DATE('01.01.2002', 'DD.MM.YYYY'))
/
INSERT INTO tmp(id, d1, d2)
  VALUES(3, TO_DATE('01.01.2004', 'DD.MM.YYYY'), TO_DATE('01.01.2005', 'DD.MM.YYYY'))
/
INSERT INTO tmp(id, d1, d2)
  VALUES(3, TO_DATE('01.01.2005', 'DD.MM.YYYY'), TO_DATE('01.01.2007', 'DD.MM.YYYY'))
/

INSERT INTO tmp(id, d1, d2)
  VALUES(4, TO_DATE('01.01.2000', 'DD.MM.YYYY'), TO_DATE('01.01.2007', 'DD.MM.YYYY'))
/
INSERT INTO tmp(id, d1, d2)
  VALUES(4, TO_DATE('01.01.2002', 'DD.MM.YYYY'), TO_DATE('01.01.2004', 'DD.MM.YYYY'))
/
INSERT INTO tmp(id, d1, d2)
  VALUES(4, TO_DATE('01.01.2003', 'DD.MM.YYYY'), TO_DATE('01.01.2007', 'DD.MM.YYYY'))
/

COMMIT
/

т.е. нужен результат:

ID         D1         D2         
-- ---------- ---------- 
 1            01.01.2000 
 1 01.01.2001 
 2            01.01.2000
 2 01.01.2004 
 3            01.01.2000
 3 01.01.2002 01.01.2004
 3 01.01.2007 
 4            01.01.2000
 4 01.01.2007 

Апропо , в 8i аналитика присутствует.

Andrey.L

Есть интервалы - нужны "дырки".

С интервалами вашими вроде бы понятно.
Каждый интервал описывается строкой из tmp. (Так)
Дайте определение "дырок", пожалуйста.

dmidek

Апропо , в 8i аналитика присутствует.

SE - Standard Edition - не работает!!!

SQL*Plus

Дайте определение "дырок", пожалуйста.

Все интервалы, которые не пересекают данные из таблицы.

Andrey.L

SQL*Plus Дайте определение "дырок", пожалуйста. Все интервалы, которые не пересекают данные из таблицы.

SQL> select * from tmp order by id, d1, d2;

        ID   D1           D2
----------   ----------   ----------
         1   01.01.2000   01.01.2001
         2   01.01.2000   01.01.2002
         2   01.01.2000   01.01.2002
         2   01.01.2001   01.01.2004
         3   01.01.2000   01.01.2002
         3   01.01.2004   01.01.2005
         3   01.01.2005   01.01.2007
         4   01.01.2000   01.01.2007
         4   01.01.2002   01.01.2004
         4   01.01.2003   01.01.2007

10 rows selected.

Приведите примеры и объясните почему именно и как именно "не пересекают данные из таблицы".

SQL*Plus

Приведите примеры и объясните почему именно и как именно "не пересекают данные из таблицы".

+1
Единственная строка, которая я понимаю - это

3 01.01.2002 01.01.2004

На мой взгляд это единственная строка, которая и является "дыркой".
Все остальные "полудырки" :-) я совершенно не понимаю ...

dmidek

SQL*Plus Приведите примеры и объясните почему именно и как именно "не пересекают данные из таблицы". +1 Единственная строка, которая я понимаю - это 3 01.01.2002 01.01.2004 На мой взгляд это единственная строка, которая и является "дыркой". Все остальные "полудырки" :-) я совершенно не понимаю ...

Мне кажется, что в процессе разъяснения всего этого так, чтобы мы поняли,
автор вопроса сам решит свою задачу... :-)

в моем понимании автор хочет следующее

((-∞,+∞)/(A1,B1))∩((-∞,+∞)/(A2,B2))∩...∩((-∞,+∞)/(An,Bn))
где 1..n номера интервалов принадлежащих одному ID

возможно неправильно понял что есть дырка
но (доработать граничные случаи)

SQL> select * from tmp;

       ID D1         D2
--------- ---------- ----------
        1 01.01.2000 01.01.2001
        2 01.01.2000 01.01.2002
        2 01.01.2000 01.01.2002
        2 01.01.2001 01.01.2004
        3 01.01.2000 01.01.2002
        3 01.01.2004 01.01.2005
        3 01.01.2005 01.01.2007
        4 01.01.2000 01.01.2007
        4 01.01.2002 01.01.2004
        4 01.01.2003 01.01.2007
 дырка
       10 01.02.2007 20.02.2007

select * from
(select
  dd d_from
,nvl((select min(dd) from
 ( select d1 dd from tmp
   union
   select d2 dd from tmp
 ) t2 where t2.dd>t1.dd),dd)  d_to from (
select d1 dd from tmp
union
select d2 dd from tmp) t1
) tt
where
tt.d_from<>tt.d_to
and  not exists
(select 1 from tmp t where tt.d_from<t.d2 and tt.d_to>t.d1)
/
SQL> /

D_FROM     D_TO
---------- ----------
01.01.2007 01.02.2007

.....
stax

решение, конечно, так себе
думаю можно ретушнуть...
но все же)

select distinct id, lag(max) over (partition by id order by min, max) d1, 
           lead(min) over (partition by id order by min, max) d2 from (select id, min(d1) min, max(d2) max--, lag(min(d1)) over (partition by id order by null), lead(max(d2)) over (partition by id order by null)
  from (select t.*, sum(prisn) over(partition by id order by d1, d2) s
          from (select tmp.*,
                       case when 
                        lag(d2) over(partition by id order by d1, d2) >= d1 
                         then 0
                         else 1
                       end prisn
                  from tmp) t) tt
 group by id, s), 
 (select 1 x from dual union all select 2 from dual)
 order by 1,3,2