#php #mysql #sql
Здравствуйте. Имеется вот такая база данных: 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
запрос для моего случая) ?
Ответы
Ответ 1
Спасибо за интересный вопрос. Я попробовал реализовать алгоритм на 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;Ответ 2
Оптимизирующий запрос из разряда магии переменных. Рассчитан на более правильную структуру базы данных, в которой, в таблице main нет поля namenomer так как оно дублируется из таблицы allnomer, чем нарушает вторую нормальную форму. На вашей структуре БД запрос работает и его можно еще немного сократить, за счет того, что тип номера будет браться прямо из main (но я этого не рекомендую, а рекомендую нормализовать структуру БД). set @today:=date('2016-12-11'); 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 Тест выбирающего подзапроса на sqlfiddle.com (В первой колонке результата новый номер, который будет установлен записи резервов, после него все поля записи резерва, включая старый номер. В результате нет записей с nomer=302, т.к. в результате оптимизации этот номер оказывается свободным с 12 до 20 и на него не назначен ни один резерв). Принцип работы: Самый глубокий подзапрос получает список всех номеров с последней датой активного на сегодня (переменная @today) резерва. Если в данный момент номер свободен, то последней занятой датой считается вчерашний день. К каждому номеру приклеиваются все существующие резервы с более поздними датами со всех номеров данного типа. Но по факту их данные в работе не используются, они нужны только для порождения заведомо достаточного для эмуляции рекурсии количества записей. Полученные записи сортируются в обратном порядке по дате окончания текущего резерва. Т.е. первым обрабатываемым номером окажется 304, т.к. его текущий резерв оканчивается 13 числа. Для каждой записи пытаемся найти такую запись резерва, которая в запросе ранее еще не встречалась (ее нет в переменной @used) и у которой дата начала наиболее близка к текущей обрабатываемой дате. Если запись найдена (@cid НЕ NULL), то в качестве следующей рабочей даты берем ее дату окончания. Таким образом в следующей записи будет найдена одна запись с началом наиболее близким к текущей. Когда обработка текущего номера заканчивается (новый номер не равен старому @cnum) рабочая дата сбрасывается в дату освобождения нового номера и мы ищем следующую цепочку резервов. P.S. Хранимой процедурой конечно можно сделать по проще и более понятно, с сохранением общего принципа. Но мне интересно делать единым запросом, особенно в такой ситуации, когда это кажется невозможным.Ответ 3
Структуры и наполнение - из инит-поста на sqlfiddle. CREATE PROCEDURE pack() BEGIN DECLARE done INT DEFAULT FALSE; DECLARE d_start DATE; DECLARE d_end DATE; DECLARE num varchar(7); DECLARE cur CURSOR FOR SELECT datestart, dateend FROM main ORDER BY 1, 2 DESC; DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE; CREATE TEMPORARY TABLE appts(nomer varchar (7), free_from DATE) ENGINE = Memory; INSERT INTO appts(nomer, free_from) SELECT nomer, 0 FROM allnomer; CREATE TEMPORARY TABLE shedule(nomer varchar (7), datestart DATE, dateend DATE) ENGINE = Memory; OPEN cur; read_loop: LOOP FETCH cur INTO d_start, d_end; IF done THEN LEAVE read_loop; END IF; SELECT a.nomer INTO num FROM appts a WHERE a.free_from < d_start ORDER BY 1 LIMIT 1; INSERT INTO shedule SELECT num, d_start, d_end; UPDATE appts a SET a.free_from = d_end WHERE a.nomer = num; END LOOP read_loop; CLOSE cur; SELECT nomer, datestart, dateend FROM shedule ORDER BY 1, 2; DROP TEMPORARY TABLE shedule; DROP TEMPORARY TABLE appts; END;
Комментариев нет:
Отправить комментарий