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

Здравствуйте. Имеется вот такая база данных: http://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 запрос для моего случая) ?

Ответ

Спасибо за интересный вопрос.
Я попробовал реализовать алгоритм на MS SQL, и проигнорировал в большинстве мест фильтрацию по типу номера, надеюсь, у вас получится транслировать его в код для MySQL и добавить где надо условия на тип - должно быть несложно.
Основной смысл алгоритма - сбрасываем номера у всех броней, чье начало больше текущей даты, после чего заново присваиваем им номера по одному, распределяя их как можно плотнее к уже занятым датам.
declare @start date = '2016-12-12'; -- Новая дата заезда declare @end date = '2016-12-20'; -- Новая дата выезда declare @today date = '2016-12-11';
update main set numbernomer = null where datestart > @today;
declare @mainnumber int;
while exists (select * from main where numbernomer is null) begin
select top(1) @mainnumber = id from main where numbernomer is null order by datestart asc, datediff(day, datestart, dateend) desc;
update m0 set numbernomer = m1.nomer from main m0 cross apply (SELECT top 1 a.nomer FROM allnomer a LEFT JOIN main m ON a.nomer = m.numbernomer AND DATEDIFF(day, m.datestart, m0.dateend) * DATEDIFF(day, m.dateend, m0.datestart) <= 0 left join (select m2.numbernomer, max(m2.dateend) as dateend from main m2 group by m2.numbernomer) as m2 on a.nomer = m2.numbernomer WHERE a.type = 'lux' AND m.numbernomer IS NULL order by datediff(day, m2.dateend, m0.datestart), a.nomer ) as m1 where m0.id = @mainnumber;
end;
SELECT a.nomer FROM allnomer a LEFT JOIN main m ON a.nomer = m.numbernomer AND DATEDIFF(day, m.datestart, @end) * DATEDIFF(day, m.dateend, @start) <= 0 WHERE a.type = 'lux' AND m.numbernomer IS NULL;