Запрос перекрытия SQL

#sql #sqlite

Вопрос:

Допустим, у меня есть таблица с двумя столбцами X1 и X2, где X1 всегда меньше X2. Каждая строка составляет некоторый диапазон (X1, X2). Возможно, что диапазоны X1 / X2 нескольких строк перекрываются, создавая некоторый больший диапазон (X1n, X2m).

Есть ли способ выполнить запрос с использованием стандартного SQL, чтобы найти все эти диапазоны?

Например, таблица может выглядеть следующим образом:

 |x1         |x2       |
-----------------------
|1          |3        |
|2          |4        |
|3          |5        |
|7          |9        |
|8          |27       |
|100        |105      |
-----------------------

Ожидаемый результат будет:

 ((1, 5), (7, 27), (100, 105))

Я ценю любую помощь, которая указывает нам правильное направление.

Я использую sqlite 3.11.0 .

1. Какую версию SQLite вы используете? Это возможно с помощью оконных функций и намного сложнее без них.

2. Можете ли вы включить несколько примеров строк (в формате, который легко импортировать в таблицу, не в виде изображения или чего-либо еще) и желаемые результаты на основе этих данных?

3. @GordonLinoff, я использую sqlite3, я добавил эту информацию к вопросу.

4. @PhillipMartin . , , Ваша версия слишком старая, чтобы быть действительно полезной, увы. Требуется версия 3.26 или новее.

5. @GordonLinoff, я не женат на этой версии. Это просто с версией 3.26 или новее?

Ответ №1:

Попробуйте это:

 SELECT 
t1.x1  AS Start,
MIN(t2.x2) AS End
FROM test t1 
INNER JOIN test t2 ON t1.x1 <= t2.x2
      AND NOT EXISTS(SELECT * FROM test t3 
                     WHERE t2.x2 >= t3.x1 AND t2.x2 < t3.x2) 
WHERE NOT EXISTS(SELECT * FROM test t4 
                 WHERE t1.x1 > t4.x1 AND t1.x1 <= t4.x2) 
GROUP BY t1.x1

Демонстрация здесь

1. Я должен добавить, что x1n и x2m не обязательно известны, они просто представляют то, что гипотетически может быть минимальным и максимальным в диапазоне, содержащем несколько диапазонов x1 / x2.