Как сделать эффективный запрос по диапазону дат?

Question

[Денис Ильясов]

Привет всем! Назревает проблема, необходимо сделать возможность фильтрации по диапазону дат с полным совпадением по датам. Грубо говоря есть сервис бронирования гостиниц, пользователь задает от 1 апреля по 10 апреля, в то же время владелец гостиницы заранее задал в какие даты у него доступно бронирование номера. Необходимо отфильтровать номера по полному совпадению дат. Даты решено было хранить в отдельной таблице со столбцом date, в котором хранится конкретный день.
Не приходит хорошей мысли как эффективнее сделать запрос что бы не нужно было перебирать по каждому дню. Мне кажется это будет занимать очень много времени при большом количестве номеров. Или кроме этого решения больше нет выхода? Спасибо за внимание!

Answer 1

[Алексей Черемисин]

Так вроде бы проблем нет.
Отельер в таблице указывает даты, когда номер может быть забронирован.
типа

outdate                  indate                room
20-01-2015           22-01-2015            125

Делаем просто в два этапа.
1) выбираем все номера за свободный промежуток

select * from freerooms where outdate between 20-01-2015 and 23-01-2015 and indate between 20-01-2015 and 23-01-2015

2) Получив список (он будет небольшим, даже для гостиницы в 10000 номеров), программно проходим по массиву номеров и дополнительно подыскиваем по требуемому времени.
Это все конечно приблизительно, но испытать можно за 20 минут.

Comments

[Олег]

там скорее всего один столбец с датой, для вашего примера буде три записи.

[Алексей Черемисин]

0LLEGator: Ну... а кто мешает второй столбец замутить? Да если и один, перебрать их программно гораздо легче, чем городить супер-пупер-сиквел-запросы, тем более, что не будет же у них 100 посетителей/секунда... А если будет, то здесь уже и кеширование понадобится и всё равно рабоатть быстрее будет по простой выборке (с кешированием конечно).

[Олег]

Алексей Черемисин: Ну на сколько я понимаю, эффективнее всё-таки городить запросы, чем перебирать данные программно, ибо БД заточена под выборку и обработку данных, тем более запрос-то не супер-пупер.

[Алексей Черемисин]

0LLEGator: Ну, не всегда так. Бывает, что чтобы отсортировать сотню записей (из нескольких миллионов) нужно так за ипачить запрос, что он не попадет в индексы, просрёт все кеши и будет по данным шуршать пол часа. Раньше выходили из этого положения созданием временных таблиц, но нонче это не особо модно, так как запросов таких может быть иной раз тышу в секунду. Так что я предпочитаю выдернуть поболее, в переделах разумного, и фильтрануть программно, когда это позволительно.
Да и не всегда можно запрос сделать, увы...

[Алексей Черемисин]

0LLEGator: Более того, база данных одна, а приложений может быть несколько, да на разных серверах. Так что чаще бывает просто выдернуть данные, их закешировать и обрабатывать. Но конечно же все зависит от данных, и прочих разных условий.

[Олег]

Алексей Черемисин: ну если у вас ничего не тормозит, то всё делаете правильно. Но по сути вопрос в поиске золотой середины.
А один запрос бывает не то что сложно написать - тормозить будет. Сделал процедурку, раздеил запросик и вуаля, зашуршало.

[Алексей Черемисин]

Олег: Ух... С высоты моего скромного опыта, хранимки и тригерры - зло неимоверное! Непереносимое, неконтролируемое, глючное, абсолютно не расширяемое, пережиток динозавров. По каждому пункту могу целую статью написать с примерами, почему так никогда, никогда не нужно делать.
Как пример - бизнес-система, программисты добавили немного данных в новые таблицы, привязали через вторичные ключи, залили новые данные по клиентам, а дурацкий триггер с этой вот самой хранимкой, ранее несколькими днями добавленный одним нехорошим человеком, взял и списал с нескольких тысяч клиентов малую денюжку. Откат базы данных и накат транзакций за услуги занял без малого тройку дней с кофем, матючком и куртизанками. Триггер просто вылидировал данные, но блокировал при этом отдельные поля и таблицы, а хранимка по откату пыталась вадидировать счет.
И это при том, что все было 10 раз проверено, оттестировано, отлажено и прочая прочая...
Не делайте так никогда. Тогда данные всегда будут правильные и пушистые, транзакции быстры и точны.

[Алексей Черемисин]

Олег: И да, иногда легче еще один сервер поставить с приложением, чем переносить базу на новый.. Ибо иногда это деньги и немалые за лицензии... Так что грузить лишний раз сервер баз данных не нужно, пусть себе работает, а резервный - отдыхает.

Answer 2

[aol-nnov]

а вот интервалы есть в постгресе. можно подумать в эту сторону..

Answer 3

[Олег]

Из таблицы со свободными датами номеров выбираете все записи, у которых дата попадает в введённый пользователем диапазон и проверяем, что количество записей для каждого номера равно диапазону.

Таблица FreeNumbers:
FreeDate - Дата, когда номер свободен
NumberNo - номер номера

SELECT count(NumberNo), datediff ('dd', @BeginDate, @EndDate), NumberNo
From FreeNumbers
WHERE FreeDate between @BeginDate and @EndDate
group by NumberNo, datediff ('dd', @BeginDate, @EndDate)

Код на MS SQL'е, звиняйте.
Спасибо, nApoBo3, за ценное замечание

Comments

[nApoBo3]

Вы не учли разрывы.

[Олег]

Ну тогда нужно добавить вычисляемый столбец, который бы считал количество найденных дней count(room), наример (в MS SQL count(*) НЕ игнорирует значения null) и сравнивать с количеством дней в диапазоне, ну и, конечно, группировать по номеру комнаты.

[nApoBo3]

Зачем такие сложности? Лучше из таблицы занятых выбрать по не вхождению.

select roomid from roomsatus where roomid not in (select roomid from roomstatus where datefrom <= date and dateto >= date)

[Олег]

nApoBo3: Осталось только найти в условии (вопросе) таблицу занятых.А сложности - для их решения, размять часть мозга, ответственную за SQL.

[nApoBo3]

Олег: Ну так и таблица свободных в вопросе не фигурирует. Есть только "владелец задал", каким именно образом не описано. ИМХО логично хранить вообще все даты, плюс столбец статуса, поскольку недоступность номера может быть по целому ряду причин. Так же можно хранить номер "документа", на основании которого проставлен статус( это может быть бронь ).

Answer 4

[nApoBo3]

Вы отталкиваетесь от одной гостиницы или это агрегатор.
Если от одной, то проблемы имхо вообще нет, даже если сделать всё очень плохо.
Допустим в гостинице 400 номеров, в году 365 дней, в таблице в храним Id номера, дату, статус номера. Это примерно 8 байт на запись( для postgresql), около мегабайта в год.
Данные прошлых периодов можно сразу переносить в архив.
Запрос: даты где номер не среди номеров для которых есть занято в датах с, по.