oracle 求差集的方法

来源：互联网发布：国家统计局2017年数据编辑：程序博客网时间：2024/05/18 01:30

//一个表有一个ID字段，是主键并且是递增的，现在需要一条SQL把这个表没有的ID查出来//例如ID的记录如下：ID124579//我们SQL需要把3，6，8这三个值查出来//这是一个典型的求集合的差集问题：with ta as( select 1 id from dual union all select 2 from dual union all select 4 from dual union all select 5 from dual union all select 7 from dual union all select 9 from dual)select level idfrom dualconnect by level<=(select max(id)-min(id)+1 from ta)minusselect id from ta/ ID---------- 3 6 8//对于此问题，这里ta中数据很小量的，如果ta的最后一个元素为1000001呢?//那么怎么样的查询才算高效呢？//求集合的差集还有其他方法吗？//下面我们来看看类似情况(求连续递增的数中没有出现的数)的差集怎么的：create table t asselect 1 id from dual union allselect 2 from dual union allselect 3 from dual union allselect 4 from dual union allselect 456 from dual union allselect 10145 from dual union allselect 1044653 from dual///方法一：使用minusselect count(*) from ( select level id from dual connect by level<=(select max(id)-min(id)+1 from t) minus select id from t)/ COUNT(*)---------- 1044646Executed in 1.547 seconds//方法二：使用not existsselect count(*)from ( select * from ( select level id from dual connect by level<=(select max(id)-min(id)+1 from t)) a where not exists( select 1 from t b where a.id=b.id))/ COUNT(*)---------- 1044646Executed in 2.157 seconds//方法三:使用not inselect count(*)from ( select * from ( select level id from dual connect by level<=(select max(id)-min(id)+1 from t)) a where a.id not in( select b.id from t b where a.id=b.id))/ COUNT(*)---------- 1044646Executed in 8.39 seconds//从这里看出，在处理大量数据时，相比于exists,not in(in)的效率是相当低的//所以建议在应用中要尽量使用exists，少用in//因为in要进行元素匹配，对比，而exists只需要判断存在性即可//方法四：使用lag()分析函数select count(*)from ( with temp as( select s,e from ( select lag(id) over(order by id)+1 s,id-1 e from t) where e - s >= 0) select a.s + b.rn -1 h from temp a, ( select rownum rn from (select max(e-s+1) gap from temp) connect by rownum <= gap) b where a.s + b.rn-1 <= a.e order by 1)/ COUNT(*)---------- 1044646Executed in 10.313 secondsSQL> set time on;7:11:37 SQL> / COUNT(*)---------- 1044646Executed in 10.156 seconds7:11:50 SQL> //此方法效率最低，因为我们这里用到了lag()分析函数,max(),min(),以及with临时表，//在一个查询中，使用的函数越多，就会增加oracle的负担，//所以oracle engine在查理此查询时，也需要额外的开销(时间和资源)

原帖:

http://topic.csdn.net/u/20110514/20/a46a060b-bb96-4ab5-83fd-11711ee203bd.html?seed=1011733181&r=73313442#r_73313442

oracle lag()分析函数:

http://blog.csdn.net/BOBO12082119/archive/2011/03/30/6289648.aspx