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

drkrol

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

За такой порядок расстановки/распределения номеров отвечает sql код. Он берёт новые даты заезда и выезда, сравнивает их с таблицей main и проверяет свободный номер на эти даты. Первый попавшийся свободный номер он присвоит новой броне. Всё логично. От сюда и следует такой "хаотичное" распределение номеров. Вот, собственно, сам select запрос на выдачу номера.
PHP:
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 число откроется.
f941d22a93934529b946e9f907c25bcd.jpg

Я не трогал еще действующие брони, я перемещал только будущие.
Хотелось бы узнать, на сколько реальна эта затея с перераспределением/дефрагментацией, и как её воплотить в жизнь (может быть уже было что-то подобное, либо есть такой sql запрос для моего случая) ?
 
При заполнении брони, перед LIMIT 1 добавь:
PHP:
ORDER BY MIN(DATEDIFF(m.dateend, @start))
Хотя лимит там скорее всего и не пригодится после такого финта...

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

Если способ сработает:
1. старт транзакции + лок таблицы
2. Выбор бушующих бронек (по классу номера) + отсортировать по datestart и в массиве запомнить
3. Удаление бушующих бронек
4. insert по алгоритму из массива.
5. завершение транзакции
 
Последнее редактирование:
При заполнении брони, перед LIMIT 1 добавь:
эм. Я поменял limit на order by mi... иии... ничего не изменилось. Теперь на запрос datestart = 12, dateend = 14, мне возвращается null. Было false.
 
эм. Я поменял limit на order by mi... иии... ничего не изменилось. Теперь на запрос datestart = 12, dateend = 14, мне возвращается null. Было false.
Этот запрос будет срабатывать для новых - выдавая номера таким образом чтобы зазор между броньками был минимален.
Тестировал удалив все будущие брони. На древней мускульке, будет ли он на 5,5+ работать еще надо проверять.
LIMIT лучше всё же оставить.

Писать алгоритм дефрага уже готовых бронек так чтобы правильно - довольно муторно
В простейшем, но плохеньком варианте, есть описание алгоритма в моём предыдущем посте и состоит из 5 пунктов.
 
Последнее редактирование:
Этот запрос будет срабатывать для новых - выдавая номера таким образом чтобы зазор между броньками был минимален.
Жалко, но это не решает мою проблему. Там есть один случай, когда даже с минимальным расстоянием между датами всё равно образовываются такие пробелы.
чтоб отпала у тебя эта идея
:D
Вот решение проблемы:
PHP:
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
 
Назад
Сверху