我一直在努力解决sql中的一些棘手问题,我需要从事件间隔推断资产利用率,刚刚学到了大约
Allen’s Interval Algebra,这似乎是解决这些问题的关键.
代数描述了间隔之间的13种关系,下面的图像显示了前七个,其余的是逆(即x在x,y满足x之前)
但是,我无法找出如何实施相关的操作.
给出我的示例数据,我如何从sql或PLsql中的以下三种操作中获取结果？
> Disjoin
>减少
>查找差距
请参阅我的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
操作1 – 取消结果
我想要一个查询从上面的数据返回不相交集合,其中所有重叠的间隔已被分解成行,使得不存在重叠.
我该怎么做这个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
操作2 – 减少结果
我如何减少/平坦化间隔,使它们是：
>不为空(即它们具有非空宽度);
>不重叠
>从左到右订购;
>不是相邻的(即在2个连续的范围之间必须有一个非空的间隙)
对于我的例子,这样就像：
start end width [1] 1 29 29 [2] 34 35 2 [3] 40 46 7
操作3 – 间隙结果
此外,我如何找到差距？
start end width [1] 30 33 4 [2] 36 39 4
解决方法
这是一个
SQLFiddle demo
首先创建临时表以简化查询,但您可以将这些创建查询放入最终查询中,并且不使用临时表：
首先创建临时表以简化查询,但您可以将这些创建查询放入最终查询中,并且不使用临时表：
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-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-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;
