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

[drkrol]

Здравствуйте. Имеется вот такая база данных: Для просмотра ссылки Войди или Зарегистрируйся . Первая таблица отвечает за отображение номеров в гостинице, которые существуют, вторая - за существующие брони в гостинице. Я попытался её визуализировать:

За такой порядок расстановки/распределения номеров отвечает 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 запрос для моего случая) ?

 

[latteo]

При заполнении брони, перед LIMIT 1 добавь:

ORDER BY MIN(DATEDIFF(m.dateend, @start))

Хотя лимит там скорее всего и не пригодится после такого финта...

Это должно помочь избежать дефрагментации.

Если способ сработает:
1. старт транзакции + лок таблицы
2. Выбор бушующих бронек (по классу номера) + отсортировать по datestart и в массиве запомнить
3. Удаление бушующих бронек
4. insert по алгоритму из массива.
5. завершение транзакции

 

[drkrol]

При заполнении брони, перед LIMIT 1 добавь:

эм. Я поменял limit на order by mi... иии... ничего не изменилось. Теперь на запрос datestart = 12, dateend = 14, мне возвращается null. Было false.

 

[latteo]

эм. Я поменял limit на order by mi... иии... ничего не изменилось. Теперь на запрос datestart = 12, dateend = 14, мне возвращается null. Было false.

Этот запрос будет срабатывать для новых - выдавая номера таким образом чтобы зазор между броньками был минимален.
Тестировал удалив все будущие брони. На древней мускульке, будет ли он на 5,5+ работать еще надо проверять.
LIMIT лучше всё же оставить.

Писать алгоритм дефрага уже готовых бронек так чтобы правильно - довольно муторно
В простейшем, но плохеньком варианте, есть описание алгоритма в моём предыдущем посте и состоит из 5 пунктов.

 

[drkrol]

Этот запрос будет срабатывать для новых - выдавая номера таким образом чтобы зазор между броньками был минимален.

Жалко, но это не решает мою проблему. Там есть один случай, когда даже с минимальным расстоянием между датами всё равно образовываются такие пробелы.

чтоб отпала у тебя эта идея

Вот решение проблемы:

set @today:=NOW();
update main M
  join (
   select cid, nomer
     from (
       select A.nomer,
              @cstart:=if(@cnum=A.nomer,@cstart,A.start),
              @cnum:=A.nomer,
              @cid:=(select M.id
                       from main M,allnomer MT
                      where MT.nomer=M.numbernomer and MT.type='lux'
                        and M.datestart>@cstart
                        and find_in_set(M.id,@used)=0
                      order by datestart
                      limit 1
                    ) cid,
              @cstart:=(select dateend from main where id=@cid) dend,
              @used:=coalesce(concat(@used,',',@cid),@used)
         from (
          select A.nomer, A.start
            from (
              select A.nomer,
                     (select coalesce(min(dateend),@today-interval 1 day)
                        from main M
                       where datestart<=@today and M.numbernomer=A.nomer) start
                from allnomer A where type='lux'
            ) A,
            main M, allnomer MT
           where MT.nomer=M.numbernomer and MT.type='lux' and M.datestart>A.start
           order by A.start desc, A.nomer
         ) A,
         (select @cid:=0,@cnum:=0,@cstart:=NULL,@used:='') Y
     ) X where cid is not null
  ) U
  on M.id=U.cid
set M.numbernomer=U.nomer