我一直在努力解决SQL一些棘手的问题，我需要从事件的时间间隔推断资产利用率，并刚刚了解的艾伦的区间代数，这似乎是关键，解决这些问题。

I've been struggling to solve a few tricky problems in SQL where I need to infer asset utilisation from event intervals, and have just learned about Allen's Interval Algebra, which seems to be the key to solving these problems.

代数描述13种间隔之间的关系，和下面的图片显示了前七，其余为逆（X前即Y，Y满足X，等等）

The algebra describes 13 kinds of relationships between intervals, and the image below shows the first seven, with the rest being the inverse (i.e. y before x, y meets x, etc)

但我无法找到如何执行相关操作。

But I'm having trouble finding out how to implement the relevant operations.

由于我的样本数据，我怎么能去从以下三种类型的操作在SQL或PLSQL？

Given my sample data, how can I go about getting results from the following three types of operations in SQL or PLSQL?

请看看我的SQLFiddle链接： http://sqlfiddle.com/#!4/cf0cc

Please see my SQLFiddle link: 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

我想查询返回不相交集从上面的数据，所有的重叠区间已经被分成行，使得不存在重叠

I'd like a query to return the disjoint set from the data above, where all overlapping intervals have been broken into rows such that no overlaps exist.

我如何去了解这个SQL？

How do I go about this 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

我如何去减少/压扁的间隔，使得它们分别是：

How do I go about reducing/flattening the intervals, such that they are:

在我的例子，这将是这样的：

For my example, this would look like:

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

另外，我怎么会找到差距？

Also, how would I find the gaps?

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

推荐答案

下面是一个 SQLFiddle演示 首先创建临时表来简化查询，但你可以把这些创作查询到最终的查询和做没有临时表：

Here is a SQLFiddle demo First of all create temp tables to simplify queries though you can put these creation queries into final queries and do it without temp tables:

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;