MySQL: сопоставление записей, имеющих x последовательных дат, доступных между двумя датами

У меня есть база данных MySQL, содержащая таблицу арендуемых свойств и таблицу заказов для этих свойств. Существует также функция поиска для нахождения доступных свойств между двумя предоставленными датами. При поиске пользователь может ввести дату начала, количество дней, которое они хотят оставить, и гибкость даты до +/- 7 дней. Бронирование может начаться в тот же день, когда заканчивается другое бронирование (вечеринка 1 уходит утром, вечеринка 2 приходит вечером).

У меня с трудом реализуется функция гибкости.

Пользователь выбирает, что они хотят начать свое пребывание в 2011-03-10, они хотят остаться на 7 дней, и они имеют гибкость +/- 2 дня. Я собрал изображение, которое отображает данные и параметры ниже. (Красный: Бронирование 1, Зеленый: Бронирование 2, Полосы: Бронирование 3, Синий: Диапазон дат (2011-03-10, + 7 дней и +/- 2 дня))

Свойство 1 (возможны заказы на протяжении всего диапазона дат)
Собственность 3 (Возможно бронирование начиная с 2011-03-08 или 2011-03-09)

Мой текущий запрос проверяет перекрытие для всех диапазонов дат 7 дней в пределах всего диапазона доступных для поиска значений, например:

В образце набора данных он достаточно быстр, но на гораздо более крупных наборах данных он будет очень медленным, тем более, когда вы создадите полную +/- 7-дневную гибкость.

Есть ли у кого-нибудь какие-либо предложения относительно того, как этот запрос может быть лучше написан?

Ответы

Ответ 1

Хорошо, здесь сложный ответ на сложный вопрос...

SELECT * FROM property AS p
LEFT JOIN  
(
  SELECT property_id, DATEDIFF(MAX(date_end),20110308) AS startblock, 
      DATEDIFF(20110319,MIN(date_start))-1 AS endblock
  FROM property_booking AS pb
  WHERE date_start < 20110319 || date_end >= 20110308 
  GROUP BY property_id
  HAVING LEAST(startblock,endblock) > 4
) AS p2 ON p.id = p2.property_id 
WHERE p2.property_id IS NULL;

Подзапрос выбирает все свойства, которые не подходят. LEFT JOIN с IS NULL в основном выдает исключение (отрицание на неприемлемые свойства)

20110308 - желаемая дата начала -2 дня (поскольку +/- 2-дневная гибкость)
20110319 - желаемая дата окончания +2 дня.
Число 4 в HAVING LEAST(startblock,endblock) > 4 в два раза ваш +/- номер (2 * 2)

Мне потребовалось некоторое время, чтобы разобраться (но ваш вопрос был интересным, и у меня было время на моей руке)

Я тестировал его с помощью кросс-кейсов, и он работал для всех тестовых случаев, которые я на него набросал...). Логика, стоящая за ней, немного странная, но хорошая старая ручка и бумага помогли мне разобраться с ней!

Изменить

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

Проблема заключается в том, что вам нужно искать информацию, которая не "присутствует" в БД, и восстанавливать ее из данных, которые у вас есть. Ознакомьтесь с моим комментарием по вашему вопросу, чтобы увидеть лучший способ справиться с этой проблемой.

Ответ 2

Я думаю, что это то, что вы ищете:

   SELECT MAX( IF( (    b.date_start < '2011-03-08' + INTERVAL 7 DAY
                    AND b.date_end > '2011-03-08'), 1, 0)) AS is_booked,
          p.id,
          p.name
     FROM property p
LEFT JOIN property_booking b ON p.id = b.property_id
 GROUP BY p.id
   HAVING is_booked < 1

Если вы хотите включить освобождение, разверните агрегат MAX(), чтобы включить параметры:

   SELECT MAX( IF(    (    b.date_start < '2011-03-08' + INTERVAL 7 DAY
                       AND b.date_end > '2011-03-08')
                  AND (    b.date_start < '2011-03-08' + INTERVAL 7 DAY + INTERVAL 1 DAY
                       AND b.date_end > '2011-03-08' + INTERVAL 1 DAY)
                  AND (    b.date_start < '2011-03-08' + INTERVAL 7 DAY + INTERVAL 2 DAY
                       AND b.date_end > '2011-03-08' + INTERVAL 2 DAY), 1, 0)
             ) AS is_booked,
          p.id,
          p.name
     FROM property p
LEFT JOIN property_booking b ON p.id = b.property_id
 GROUP BY p.id
   HAVING is_booked < 1

Если я правильно понимаю ваш вопрос, этот запрос GROUP BY должен охватывать его более эффективно, чем несколько подзапросов.