Given a table of rows each representing a numerical range.
CREATE TABLE ranges ( start INTEGER, end INTEGER )
How could I create a view that represents the “holes” in the ranges? (Ignoring the bounds from -infinity to +infinity).
For example if the table had the data:
(1,3) (4,5) (8,10) (16,20)
The result I’d like is:
I’m using python and sqlite. I’m currently thinking that a using a procedural approach in a sqlite function or python might be the most clear and performant approach.
I’ve seen an approach for finding missing dates that rely on a temp table for all the possible dates, but this method isn’t feasible for integer ranges as they could very large.
I like to do this as:
select (start + 1) as missing_start, (next_start - 1) as missing_ne from (select t.*, (select min(t2.start) from t t2 where t2.start > t.start ) as next_start from t ) t where next_start > end + 1;
Note that this assumes no overlaps. If overlaps are possible the problem can be more difficult to solve.