Detecting “Holes” in a Series
Question:
Consider the following table
While seq is supposed to be a continous series of figures, this is not the case. How to detect the range of “holes” within the data?create table t ( seq int );
For example, assume t contain the following:
t
——-
1
2
6
8
The values indicating 3-5 and 7 should be returned.
The funny part in this question is that you need to return valuesoutside the data. But those data can be derived by the data easily
Answer:
select
case when sum(hole_start_pre) = 0 then
null
else
sum(hole_start_pre)
end hole_start,
case when sum(hole_end_pre) = 0 then
null
else
sum(hole_end_pre)
end hole_end
from
(
select seq+1 hole_start_pre, 0 hole_end_pre,
seq ordering, 1 is_start, rownum+1 gap_segment
from t
where seq + 1 not in (
select seq
from t
)
union
select 0, seq - 1,
seq ordering, 0 is_start, rownum
from t
where seq - 1 not in (
select seq
from t
)
)
group by gap_segment
order by sum(hole_start_pre)
/
This seems overly complicated… but I am glad that it works
(0)