Алленовские интервальные операции Алгебра в SQL

Я пытаюсь решить несколько сложных проблем в SQL, где мне нужно вывести использование активов из интервалов событий и только что узнали о Allen Interval Алгебра, которая, по-видимому, является ключом к решению этих проблем.

Алгебра описывает 13 видов отношений между интервалами, а на изображении ниже показаны первые семь, а остальное - обратное (т.е. y до x, y соответствует x и т.д.)

enter image description here

Но у меня возникли проблемы с поиском способов выполнения соответствующих операций.

Учитывая мои данные примера, как я могу получить результаты следующих трех типов операций в SQL или PLSQL?

  • разъединять
  • Сокращение
  • Найти пробелы

См. мою ссылку SQLFiddle: http://sqlfiddle.com/#!4/cf0cc

Исходные данные

   start end width
[1]     1  12    12
[2]     8  13     6
[3]    14  19     6
[4]    15  29    15
[5]    19  24     6
[6]    34  35     2
[7]    40  46     7

enter image description here

Операция 1 - Disconnined Result

Мне бы хотелось, чтобы запрос возвращал disjoint set из приведенных выше данных, где все перекрывающиеся интервалы были разбиты на строки таким образом, что не существует перекрытия.

Как мне обойти этот SQL?

     start end width
[1]      1   7     7
[2]      8  12     5
[3]     13  13     1
[4]     14  14     1
[5]     15  18     4
[6]     19  19     1
[7]     20  24     5
[8]     25  29     5
[9]     34  35     2
[10]    40  46     7

enter image description hereenter image description here

Операция 2 - Уменьшенный результат

Как я могу уменьшить/сплющить интервалы, такие как:

  • не пусто (т.е. имеют ненулевую ширину);
  • не перекрывается;
  • заказывается слева направо;
  • даже не соседствует (т.е. должен быть пустой пробел между двумя последовательными диапазонами)

В моем примере это будет выглядеть так:

    start end width
[1]     1  29    29
[2]    34  35     2
[3]    40  46     7

enter image description hereenter image description here

Операция 3 - Результат разрыва

Также, как бы я нашел пробелы?

   start end width
[1]    30  33     4
[2]    36  39     4

enter image description hereenter image description here

Ответы

Ответ 1

Вот демон SQLFiddle Прежде всего создайте временные таблицы для упрощения запросов, хотя вы можете поместить эти запросы создания в окончательные запросы и сделать это без временных таблиц:

create table t as select * from
(
select null s ,"start"-1 as e  from data
union all
select "start" s,null e  from data
union all
select "end"+1 s ,null e  from data
union all
select null s ,"end" e  from data
) d where exists (select "start" 
                  from data where d.s between data."start" and data."end"
                               or d.e between data."start" and data."end"
                                );
--Operation 1 - Disjoined Result   
create table t1 as select s,e,e-s+1 width from
(
select distinct s,(select min(e) from t where t.e>=t1.s) e from t t1
) t2 where t2.s is not null and t2.e is not null;

--Operation 2 - Reduced Result
create table t2 as 
select s,e,e-s+1 width from
(
select s,(select min(d2.e) from t1 d2 where d2.s>=d.s and not exists
          (select s from t1 where t1.s=d2.e+1) ) e
from
t1 d where not exists(select s from t1 where t1.e=d.s-1) 
) t2;

--Temp table for Operation 3 - Gaps
create table t3 as 
select null s, s-1 e from t2
union all
select e+1 s, null e from t2;

Теперь вот запросы:

--Operation 1 - Disjoined Result
select * from t1 order by s;

--Operation 2 - Reduced Result


select * from t2 order by s;

--Operation 3 - Gaps

select s,e,e-s+1 width 
from
(
select s,(select min(e) from t3 where t3.e>=d.s) e from t3 d
) t4 where s is not null and e is not null
order by s;