Intereting Posts
Как мне сначала возвращать строки с определенным значением? Обновить столбцы с нулевыми значениями Необходимо вычислить долготу широты из базы данных почтового кода, когда местоположение имеет несколько кодов Странное поведение внешнего ключа в пустых таблицах в SQLite 3 Увеличение на один день в SQL Developer Запрос двойного соединения HQL с отношением один к другому Существуют ли многострочные разделители комментариев в SQL, которые являются агностиками поставщиков? Как запросить подсчет конкретных побед команды и найти победителя серии T-SQL, чтобы обрезать дату и время до ближайшей даты? существует ли ограничение длины для group_concat или по другой причине, почему он не будет работать в текстовом поле почему отображается «дополнительные символы после команды», показанная для командной строки sed? Как извлечь имя базы данных из строки подключения независимо от СУБД? Оптимизировать групповой максимальный запрос Объединить выбранные столбцы нескольких строк в одну строку MYSQL INSERT или UPDATE IF

Эффективное объединение диапазонов интервалов в SQL

Предположим, что у меня есть две таблицы следующим образом (данные, взятые из этого сообщения SO ):

Таблица d1 :

  x start end a 1 3 b 5 11 c 19 22 d 30 39 e 7 25 

Таблица d2 :

  x pos a 2 a 3 b 3 b 12 c 20 d 52 e 10 

Первая строка в обеих таблицах – это заголовки столбцов. Я хотел бы извлечь все строки в d2 где столбец x совпадает с d1 и pos1 попадает в (включая граничные значения) start и end столбцы d1 . То есть, мне бы хотелось получить результат:

  x pos start end a 2 1 3 a 3 1 3 c 20 19 22 e 10 7 25 

То, как я это делал до сих пор, это:

 SELECT * FROM d1 JOIN d2 USING (x) WHERE pos BETWEEN start AND end 

Но мне не ясно, является ли эта операция такой эффективной, как она может быть (т. Е. Оптимизирована внутри). Например, вычисление всего соединения сначала не является масштабируемым подходом IMHO (с точки зрения скорости и памяти).

Существуют ли другие эффективные оптимизации запросов (например, использование интервальных деревьев ) или другие алгоритмы, которые могут эффективно обрабатывать диапазоны (опять же, с точки зрения скорости и памяти) в SQL, которые я могу использовать? Не имеет значения, использует ли он SQLite, PostgreSQL, mySQL и т. Д.

Каков наиболее эффективный способ выполнения этой операции в SQL?

Большое спасибо.

    Не уверен, как все это работает внутри, но в зависимости от ситуации я бы советовал играть со столом, который «выкатывает» все значения из d1, а затем присоединяется к этому. Таким образом, механизм запроса может точно определить правильную запись «точно», вместо того чтобы найти комбинацию границ, которые соответствуют ожидаемому значению.

    например

     x value a 1 a 2 a 3 b 5 b 6 b 7 b 8 b 9 b 10 b 11 c 19 etc.. 

    с учетом индекса в столбце значений (**), это должно быть довольно немного быстрее, чем присоединение к началу и концу МЕСЯЦА на исходной таблице D1 IMHO.

    Конечно, каждый раз, когда вы вносите изменения в d1, вам нужно также отрегулировать развернутую таблицу (триггер?). Если это случается часто, вы потратите больше времени на обновление развернутой таблицы, чем вы получили в первую очередь! Кроме того, это может занять довольно много (дискового) пространства, если некоторые из интервалов действительно большие; а также, это предполагает, что нам не нужно искать нецелые числа (например, что, если мы ищем значение 3.14?)

    (Вы можете рассмотреть эксперимент с уникальным на (значение, x) здесь …)