SQL: просмотр представления отсутствующих диапазонов

Учитывая таблицу строк, каждая из которых представляет собой числовой диапазон.

CREATE TABLE ranges (
    start INTEGER,
    end INTEGER
)

Как я мог создать представление, представляющее «дыры» в диапазонах? (Игнорирование границ от -infinity до + бесконечности).

Например, если таблица имела данные:

(1,3)
(4,5)
(8,10)
(16,20)

В результате я бы хотел:

(6,7)
(11,15)

Я использую python и sqlite. В настоящее время я думаю, что использование процедурного подхода в функции sqlite или python может быть самым ясным и эффективным подходом.

Я видел подход для поиска отсутствующих дат, которые полагаются на временную таблицу для всех возможных дат, но этот метод невозможен для целых диапазонов, поскольку они могут быть очень большими.

Всего 1 ответ


Мне нравится делать это как:

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;

Обратите внимание, что это не предполагает совпадений. Если возможны совпадения, проблема может быть сложнее решить.


Есть идеи?

10000