Consider the following table:
T_ID | T_START | T_END -----+---------+------ 1 | 0.25 | 0.5 2 | 0.8 | 1 3 | 0.4 | 0.6 4 | 0.2 | 0.3 5 | 0.7 | 0.8
T_ID is unique. Each row represents a continuous range of numbers and is a subset of 0 to 1.
T_START is less than
I need to identify any ranges not included between 0 and 1. Note that some ranges do overlap. The exclusivity of the endpoints is not relevant for my use case; I only need to identify what the endpoints of the gaps are. (As such, single point gaps are not a consideration.)
For this specific data set, I'd expect the result to be
GAP_START | GAP_END ----------+-------- 0 | 0.2 0.6 | 0.7
The actual data set is large and will be aggregated over some other data (hundreds of thousands of rows, with possibly 100 row per aggregate group), so performance is important. (Answers that are not performant but may be improved are welcome, though.)
I considered trying to first determine what the covered ranges are and then trying to reverse that, but I haven't been able to figure out how to even compute the covered ranges. A simple
GROUP BY is insufficient, since we have chains of overlapping ranges that would be merged into a single range, even though not all ranges overlap each other. I am of the mind that a recursive query might be helpful, but I haven't been able to figure out the logic for it yet.
I've created a SQLFiddle with this example dataset.
I am, sadly (and unproductively), not at liberty to modify the underlying representation.