Как дефрагментировать даты в базе данных?

Question

[del993788]

Здравствуйте. Имеется вот такая база данных: sqlfiddle.com/#!9/9554b . Первая таблица отвечает за отображение номеров в гостинице, которые существуют, вторая - за существующие брони в гостинице. Я попытался её визуализировать:

За такой порядок расстановки/распределения номеров отвечает sql код. Он берёт новые даты заезда и выезда, сравнивает их с таблицей main и проверяет свободный номер на эти даты. Первый попавшийся свободный номер он присвоит новой броне. Всё логично. От сюда и следует такой "хаотичное" распределение номеров. Вот, собственно, сам select запрос на выдачу номера.

SET @start = '2016-12-12'; -- Новая дата заезда
SET @end = '2016-12-20'; -- Новая дата выезда
SELECT a.nomer
FROM allnomer a
LEFT JOIN main m
  ON a.nomer = m.numbernomer
  AND DATEDIFF(m.datestart, @end) * DATEDIFF(m.dateend, @start) <= 0
WHERE a.type = 'lux' AND m.numbernomer IS NULL
LIMIT 1

Но есть у меня одна проблема. По сути, 12, 13, 14... 20 числа в гостинице жить никто не будет. Это свободные дни. Они образовались из-за распределения дат моим скриптом. Но если 11 числа придёт бронь на даты с 12 по 20, то скрипт её отвергнет (вернёт null), ибо даты заезда/выезда пересекаются. И это вполне ожидаемо, но хотелось бы решить эту проблему. Если перераспределить брони, то всё встанет на свои места, и номер с 12 по 20 число откроется.

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

Answer 1

[Макс]

IMHO вся Ваша проблема в дырках - остающемся свободном дне. добавте условие в первый скрипт, чтобы выбирался не первый попавшийся, а номер, у которого дата выезда совпадает с "датой нового заезда -1". Хотя реально в гостинице вроде же для этого и есть время вселения и время выселений - т.е. дата въезда и выезда вообще должны совпадать.

Comments

[del993788]

а по факту они совпадают. в 304 номере человек покинет гостиницу 14 числа до обеда, после обеда вселится другой. На клиенте всё так и отображается. Просто, чтобы себе жизнь не усложнять, я отделил день заезда и выезда. А по поводу вашего предложения... Мне кажется, что найдётся такой случай, где, вроде, даты стоят рядом, а окна всё равно образовываются. Я даже уже предполагаю, как... Здесь нужна именно дефрагментация.

[Макс]

qqignatqq: попробуйте сами для себя описать алгоритм такой дефрагментации. И окажется, что вы двигаете гостей так, чтобы небыло пустых дней. Что равно тому, чтобы сразу выбирать номер, который только освободился. Правда в таком случае есть риск, что только 1 номер и будет использоваться какое-то время

[del993788]

Вот. Вот та ситуация, в которой есть пробелы. Я пронумеровал очерёдность бронирования номера. 4ая бронь не знает о существовании 5ой, так как 5ой еще нет.
Здесь поможет только перераспределение

[Макс]

qqignatqq: ок, теперь понял. в таком случае при каждом добавлении брони необходимо пересортировывать бронирование.
1. бронь, у которой дата начала наступил - не трогаем.
2. из остальных броней выбираем наиболее длинную, ищем к ней номер с минимальным разрывом в датах.
3. повторяем шаг 2 столько раз, сколько было броней.

в таком случае после пересортировки при добавлении брони 4 бронь 4 встала бы в первую строку, бронь 2 - в вторую. При добавлении 5-ой бронь 2 перепрыгнет на строку 3. .