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

Фон/Приложение

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

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

Схема

CREATE TABLE IF NOT EXISTS `property` (
    `id` bigint(20) NOT NULL AUTO_INCREMENT,
    `name` varchar(60) COLLATE utf8_unicode_ci DEFAULT NULL,
    PRIMARY KEY (`id`)
) ENGINE=InnoDB  DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

CREATE TABLE IF NOT EXISTS `property_booking` (
    `id` bigint(20) NOT NULL AUTO_INCREMENT,
    `property_id` bigint(20) DEFAULT NULL,
    `name` varchar(60) COLLATE utf8_unicode_ci DEFAULT NULL,
    `date_start` date DEFAULT NULL,
    `date_end` date DEFAULT NULL,
    PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

Примеры данных

INSERT INTO `property` (`name`) 
VALUES ('Property 1'), ('Property 2'), ('Property 3');

INSERT INTO `property_booking` (`property_id`,`name`,`date_start`,`date_end`) 
VALUES (1, 'Steve', '2011-03-01', '2011-03-08'), 
(2, 'Bob', '2011-03-13', '2011-03-20'), 
(3, 'Jim', '2011-03-16', '2011-03-23');

Пример сценария

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

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

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

Текущий метод

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

SELECT p.`id`, p.`name` 
FROM `property` p 
WHERE (NOT (EXISTS (SELECT p2.`name` FROM `property_booking` p2 WHERE (p2.`property_id` = p.`id` AND '2011-03-10' < DATE_SUB(p2.`date_end`, INTERVAL 1 DAY) AND '2011-03-17' > DATE_ADD(p2.`date_start`, INTERVAL 1 DAY))))) 
OR (NOT (EXISTS (SELECT p3.`name` FROM `property_booking` p3 WHERE (p3.`property_id` = p.`id` AND '2011-03-11' < DATE_SUB(p3.`date_end`, INTERVAL 1 DAY) AND '2011-03-18' > DATE_ADD(p3.`date_start`, INTERVAL 1 DAY))))) 
OR (NOT (EXISTS (SELECT p4.`name` FROM `property_booking` p4 WHERE (p4.`property_id` = p.`id` AND '2011-03-09' < DATE_SUB(p4.`date_end`, INTERVAL 1 DAY) AND '2011-03-16' > DATE_ADD(p4.`date_start`, INTERVAL 1 DAY))))) 
OR (NOT (EXISTS (SELECT p5.`name` FROM `property_booking` p5 WHERE (p5.`property_id` = p.`id` AND '2011-03-12' < DATE_SUB(p5.`date_end`, INTERVAL 1 DAY) AND '2011-03-19' > DATE_ADD(p5.`date_start`, INTERVAL 1 DAY)))))
OR (NOT (EXISTS (SELECT p6.`name` FROM `property_booking` p6 WHERE (p6.`property_id` = p.`id` AND '2011-03-08' < DATE_SUB(p6.`date_end`, INTERVAL 1 DAY) AND '2011-03-15' > DATE_ADD(p6.`date_start`, INTERVAL 1 DAY)))));

В образце набора данных он достаточно быстр, но на гораздо более крупных наборах данных он будет очень медленным, тем более, когда вы создадите полную +/- 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 должен охватывать его более эффективно, чем несколько подзапросов.