题目:
来自ITPUB论坛讨论 比如一个表, create table test (from number(10), to number(10)); 数据是: from to 1 10 11 20 代数意义就是,第一条记录的数轴的范围是[1,10],第二条是[11,20] 显然是不重叠的; 假如在第3条记录是: from to 13 21 那么 [13,21]就和第2条[11,20]存在存在重叠部分; (重叠举例:[1,3]和[2,4]重叠(交叉重叠);[1,7]和[3,6]重叠(包含重叠) ) 现在就想写一个存储过程判断test的数据是否有重叠现象,最好能知道到底哪两条重叠; 我想到的一个最简单的方法就是展开; 比如吧 from to 13 21 变成 13 14 .. 21 所有的都展开后,然后 group by 展开的数字 having count(1) >1 ,就能发现重叠的地方; 但是这样感觉太费效率; 假如 有: from to 10万 800万 那么展开的记录太多了; 不知道有什么好的方法; |
解决方案:
1.
overlap隐含函数就可以,一般的方法就是[a,b] [c,d]如果重叠满足(a-d)*(b-c)<=0;
2.
一次找到全部重叠的,我下面的没有加id,构造了个rownum代替id,因为a和b重叠,b必然和a重叠,所以剔重了一下
SQL> select * from overlap_t; S E ---------- ---------- 1 4 2 3 1 5 8 9 10 11 8.5 8.7 20 25 27 28 30 21 21 26 10 rows selected SQL> with t as( 2 select a.rn rn1,a.s s1,a.e e1,b.rn rn2,b.s s2,b.e e2 from ( 3 select rownum rn,s,e from overlap_t) a,(select rownum rn,s,e from overlap_t) b 4 where a.s<=b.e and a.e>=b.s and a.rn <> b.rn 5 ) 6 select t.* 7 from t where t.rn1=(select least(t.rn1,t1.rn1) 8 from t t1 where t.rn1 =t1.rn2 and t.s1= t1.s2 and t.e1=t1.e2 and t.s2=t1.s1 and t.e2=t1.e1); RN1 S1 E1 RN2 S2 E2 ---------- ---------- ---------- ---------- ---------- ---------- 1 1 4 2 2 3 1 1 4 3 1 5 2 2 3 3 1 5 4 8 9 6 8.5 8.7 7 20 25 10 21 26 |
3.最佳解决方法:
where (a-d)*(b-c)<=0 换成: where (a<=d and b>=c) or (a>=d and b<=c) 也许能用上索引; 还要,即使这样用不上索引,但是 (a-d)*(b-c)的乘法运算可能是个很大的值,搞不好溢出;而且乘法运算效率应该不如 后面的做法。 |