Встречаясь со сложной нетривиальной задачей по поиску и обработке данных, порой хочется решить ее в лоб. И хотя ты понимаешь, что решение возможно будет медленным или вообще нежизнеспособным, а знаний и опыта не хватает, чтобы решить ее по-настоящему, не нужно спешить. Важно понять, что СУБД были специально созданы для этого, и решать задачи, предназначенные для них, другими способами не стоит.
Задача
Поиск отелей с доступными номерами на конкретные даты некоторой группой людей.
Проект
Когда проект попал к нам в руки, поиск уже был реализован. Он работал медленно, очень медленно. А все потому, что расчеты и выборки велись не на стороне базы данных, а на стороне web-приложения: выбиралась тонна записей из разных таблиц, и в циклах подбирались и рассчитывались номера, фильтровались, сортировались и выводились постранично. Очень неэффективно. А приложение, к слову, написано на Ruby on Rails.
Не надо так.
Исходные данные
Исходная схема данных (в примерах искусственно упрощена, чтобы влезть в ограничения sqlfiddle)
Places — направления, курорты. Из полей — только название.
Districts — районы. Каждое направление может иметь несколько районов. Поля: название и id направления.
Properties — отели, могут быть привязаны к направлению или к конкретному району. Поля:
- name — название
- dest_type — тип полиморфной связи с направлением или районом («Place» или «District»)
- dest_id — id связи с направлением или районом
- stars — звездность (от 0 до 5)
- currency — код валюты
Property_arrival_rules — правила въезда в каждый отель. Поля:
- arrival_date — дата заезда
- property_id — id отеля
- rule — тип правила (0 или 1), в зависимости от типа по разному рассчитывается дата выезда, подробнее в решении ниже
- min_stay — минимальное количество ночей для проживания
Отсутствие записи в таблице на конкретную дату означает, что въезд в этот день невозможен. Зачем хранится так? Все дело в типах правил въезда. Подробнее об этих типах в решении ниже.
Rooms — номера в отелях, точнее типы номеров, т.к. например, 2-х комнатных одинаковых номеров может быть несколько в одном отеле. Поля: название и id отеля.
Room_availabilities — доступность номера на каждую ночь. Поля:
- room_id — id номера
- date — дата
- initial_count — количество доступных номеров
- sales_count — количество уже забронированных номеров
Отсутствие записи на какую-либо ночь означает недоступность номера.
Room_price_policies — политики номеров. Один и тот же номер может иметь различные расценки в зависимости от количества гостей, типа питания и других условий. Поля:
- room_id — id номера
- max_guests — максимальное количество гостей
- meal_type — тип питания, число от 0 до 8, где 0 — без питания, 1 — завтрак, 2 — полупансион и т.д.
- has_special_requirements — наличие специальных условий, булево значение
- before_type — тип специального условия (0 или 1), 0 — политика действует, только если бронирование происходит до определенной даты, 1 — политика действует, если бронирование совершается за N дней до даты заезда
- before_date — дата для before_type 0
- days_before_arrival — количество дней для before_type 1
Room_prices — цены по политикам номеров за каждую ночь в валюте отеля. Поля:
- room_price_policy_id — id политики номера
- price_date — дата
- price — цена
Отсутствие записи за какую-либо ночь означает невозможность приобрести номер в эту ночь.
Currency_rates — курсы обмена валют. Поля:
- sale_currency — код продаваемой валюты
- buy_currency — код покупаемой валюты
- price — курс, число единиц продаваемой валюты, деленное на курс, даст число единиц покупаемой валюты
Входные параметры
Пользователь в форме поиска может выбрать:
- Направление или район — что-то из places или districts. Причем если это направление, то при поиске надо искать не только отели направления, но и отели всех районов направления
- Желаемая дата заезда
- Желаемая дата выезда
- Состав группы людей, например, 3 взрослых + 2 ребенка (7 и 9 лет)
- Опционально, фильтры по цене за ночь, звездности отеля, типу питания
Результаты поиска
Результатом поиска должен стать список отелей по направлению, району. И для каждого отеля:
- Подходящие даты заезда-выезда
- Подходящий по вместимости самый дешевый номер ИЛИ 3 самых дешевых номера если нет номера вмещающего всю группу
- Стоимость за период заезда-выезда в базовой валюте на каждый номер, попавший в результат
Список отелей должен быть отсортирован: сначала идут отели с подходящим номером, затем отели с 3-мя самыми дешевыми, затем отели без доступных номеров. Дополнительно возможна сортировка по звездности отеля или стоимости за период.
При этом надо учитывать, что прийти в приложение из базы должно уже лимитированное число записей для конкретной страницы (пагинация).
Это возможно? Да, в 2 (два!) sql-запроса (после небольшой модификации схемы данных)
Решение
Допустим пользователь ищет по следующим параметрам:
- Направление “Валь Торанс”, в которое входят еще два района “Тинь Ле Лак” и “Тинь Валь Кларе”
- Желаемая дата заезда: 2 января 2018
- Желаемая дата выезда: 8 января 2018 (соответственно количество желаемых ночей — 6)
- Состав группы людей: 3 взрослых + 2 ребенка (7 и 9 лет)
- Сегодня: 17 августа 2017
Шаг 1. Ближайшая дата заезда к желаемой
По сути, надо найти по одному правилу въезда для каждого отеля направления или района с ближайшей датой к желаемой дате въезда. И здесь можно допустить, что ищем ближайшую дату не дальше N дней от желаемой, например, 7 дней. Вот так выглядит такой запрос.
SELECT DISTINCT ON (property_id)
arrival_date,
property_id,
abs('2018-01-02'::date - arrival_date) AS days_diff
FROM property_arrival_rules
INNER JOIN properties ON properties.id = property_arrival_rules.property_id
WHERE '2018-01-02'::date - 7 <= arrival_date AND arrival_date <= '2018-01-02'::date + 7
AND (
(properties.dest_type = 'Place' AND properties.dest_id IN (9))
OR (properties.dest_type = 'District' AND properties.dest_id IN (16, 17))
)
ORDER BY property_id, days_diff
Шаг 2. Подходящая дата выезда
Нам надо рассчитать дату выезда на каждый отель исходя из выбранного правила въезда (из шага 1) и количества ночей, вычисленных как разница между желаемыми датами выезда-заезда.
И тут открылась первая проблема, т.к. правила въезда оказались очень хитрыми. Есть два типа правил:
Тип 1. Можно заехать в определенный день на любое количество дней, но не меньше чем на N дней
Тип 2. Можно заехать в определенный день строго на N дней
И когда в искомый период попадают правила типа 2, то чтобы рассчитать весь период следует просматривать следующее правило, идущее в день окончания правила — дата заезда из правила + N дней.
Реальный пример правила типа 2. В отель можно въезжать только по субботам ровно на неделю. Если я хочу въехать на срок от 1 до 6 дней — мне все равно придется брать на всю неделю. Если же я хочу взять больше чем на 7 дней, например, на 9 дней, то мне придется взять или на 14 дней или ограничить себя сроком меньше — на 7 дней. И так далее…
И получается, что алгоритм расчета даты выезда выглядит следующим образом:
1. берем найденное правило въезда и предполагаемую дату выезда (дата заезда из правила + желаемое количество ночей)
2. проверяем находится ли дата выезда внутри минимального периода правила: от “даты заезда” до “даты заезда + N дней”
2.1. если внутри, т.е. период правила перекрывает желаемые даты — проверяем к какому концу периода ближе
2.1.1. если ближе к началу и это не первое просматриваемое правило, то дата выезда — это дата заезда из правила
2.1.2. иначе датой выезда оказывается “дата заезда + N дней”
2.2. если снаружи, т.е. периода правила может быть недостаточно — проверяем какого типа правило мы смотрим
2.2.1. если типа 1, то предполагаемая дата выезда и будет рассчитанной датой выезда
2.2.2. если типа 2, берем следующее правило на дату: “дата заезда + N дней”
2.2.2.1. если следующее правило существует, то рекурсивно повторяем п.2 уже для этого правила, с учетом того, что это не первое просматриваемое правило
2.2.2.2. если следующее правило не существует, то датой выезда будет “дата заезда + N дней”
И как такое положить на sql?
Можно на стороне приложения заранее рассчитать по правилам въезда все возможные периоды заезда-выезда на каждый день и положить в отдельную таблицу с полями:
| arrival_date (дата заезда) |
wanted_departure_date (желаемая дата выезда) |
departure_date (фактическая рассчитанная дата выезда) |
property_id (id отеля) |
Или даже более плотно, дабы уменьшить количество записей, т.к. для правил типа 2 будут часто совпадать дата заезда и рассчитанная дата выезда для некоторых рядом стоящих дней
| arrival_date (дата заезда) |
wanted_departure_range (желаемый период выезда, тип daterange) |
departure_date (фактическая рассчитанная дата выезда) |
property_id (id отеля) |
И назовем ее property_arrival_periods — рассчитанные периоды въезда.
Для того, чтобы ограничить число записей в этой таблице и сделать расчет не бесконечным, нужно добавить некое ограничение на максимальный срок бронирования, например, 30 дней. При таком ограничении на каждый отель на один год, в худшем случае, будет ~11000 записей, что выглядит вполне неплохо.
Таким образом при добавлении / изменении / удалении правила въезда, мы фоном в приложении:
- удаляем рассчитанные периоды за даты: от “даты правила минус 30 дней” до “даты правила”
- рассчитываем периоды на каждый день от “даты правила минус 30 дней” до “даты правила” на каждый период бронирования: на 1 день, на 2 дня, на 3 дня, …, на 30 дней
И тогда при поиске нам ничего не нужно считать, а только выбрать из этой новой таблицы.
WITH fit_arrival_rules AS (
SELECT DISTINCT ON (property_id)
arrival_date,
property_id,
abs('2018-01-02'::date - arrival_date) AS days_diff
FROM property_arrival_rules
INNER JOIN properties ON properties.id = property_arrival_rules.property_id
WHERE '2017-01-02'::date - 7 <= arrival_date AND arrival_date <= '2018-01-02'::date + 7
AND (
(properties.dest_type = 'Place' AND properties.dest_id IN (9))
OR (properties.dest_type = 'District' AND properties.dest_id IN (16, 17))
)
ORDER BY property_id, days_diff
)
SELECT
property_arrival_periods.arrival_date,
property_arrival_periods.departure_date,
property_arrival_periods.property_id
FROM property_arrival_periods
INNER JOIN fit_arrival_rules
ON property_arrival_periods.property_id = fit_arrival_rules.property_id
AND property_arrival_periods.arrival_date = fit_arrival_rules.arrival_date
WHERE wanted_departure_range @> (property_arrival_periods.arrival_date + 6)
Шаг 3. Доступные номера
Берем все доступные номера, т.е. те, что имеют записи на рассчитанный период въезда-выезда (из шага 2) и одновременно доступны каждую ночь периода.
WITH fit_arrival_rules AS (
SELECT DISTINCT ON (property_id)
arrival_date,
property_id,
abs('2018-01-02'::date - arrival_date) AS days_diff
FROM property_arrival_rules
INNER JOIN properties ON properties.id = property_arrival_rules.property_id
WHERE '2017-01-02'::date - 7 <= arrival_date AND arrival_date <= '2018-01-02'::date + 7
AND (
(properties.dest_type = 'Place' AND properties.dest_id IN (9))
OR (properties.dest_type = 'District' AND properties.dest_id IN (16, 17))
)
ORDER BY property_id, days_diff
),
fit_arrival_dates AS (
SELECT
property_arrival_periods.arrival_date,
property_arrival_periods.departure_date,
property_arrival_periods.property_id
FROM property_arrival_periods
INNER JOIN fit_arrival_rules
ON property_arrival_periods.property_id = fit_arrival_rules.property_id
AND property_arrival_periods.arrival_date = fit_arrival_rules.arrival_date
WHERE wanted_departure_range @> (property_arrival_periods.arrival_date + 6)
)
SELECT room_availabilities.room_id
FROM room_availabilities
INNER JOIN rooms ON rooms.id = room_availabilities.room_id
INNER JOIN fit_arrival_dates ON fit_arrival_dates.property_id = rooms.property_id
WHERE fit_arrival_dates.arrival_date <= date AND date < fit_arrival_dates.departure_date
AND initial_count - sales_count > 0
GROUP BY fit_arrival_dates.arrival_date, fit_arrival_dates.departure_date, room_id
HAVING COUNT(room_availabilities.id) = (fit_arrival_dates.departure_date - fit_arrival_dates.arrival_date)
Шаг 4. Стоимость номеров и “помещается ли группа?”
Берем политики номеров (из шага 3), для которых есть цены на каждый день рассчитанного периода, и вычисляем стоимость за период и среднюю цену за ночь, пересчитывая суммы при этом из валюты отеля в некую базовую валюту (в нашем случае — EUR). Кроме того, необходимо учитывать специальные условия политик “бронирование до даты” и “бронирование за N дней до въезда”.
Также нам понадобится признак “помещается ли вся группа в номер” на каждую полученную политику.
По задаче политика должна содержать максимально допустимые возрасты с количеством.
Например, в номер могут заехать 3 взрослых + 2 ребенка 5 лет.
В такой номер смогут поместиться группы:
- 3 взрослых
- 3 взрослых + ребенок 4 лет
- 2 взрослых + ребенок 10 лет (на место взрослого)
Но не поместятся:
- 4 взрослых
- 3 взрослых + ребенок 7 лет
- 2 взрослых + 2 ребенка 9 лет
И это проблема.
Мало того, что изначально максимальное количество гостей представлено полем типа hstore (к которому условия проблемно будет написать) в странном виде: Map, где ключи — максимальный возраст, а значения — количество, а для взрослых — ключ вообще “adults”.
Так еще и непонятно, как вообще представить такую информацию так, чтобы можно было проверить поместится группа людей или нет.
А давайте представим максимальное количество гостей в виде массива мест (отсортированного по возрастанию), где каждое место — максимальный возраст (18 для взрослого). И тогда вместимость номера “3 взрослых + 2 ребенка 5 лет” будет выглядеть как
[5, 5, 18, 18, 18]
А группу людей представим как массив их возрастов, и тогда “2 взрослых + 2 ребенка (5 и 9 лет)” будут выглядеть как
[5, 9, 18, 18]
В итоге, в таблицу политик (room_price_policies) был добавлен столбец вместимости (capacity) хранящий ее в таком виде.
Но еще остается вопрос. Как на sql написать условие (или запрос): поместится ли [5, 9, 18, 18] в [5, 5, 18, 18, 18]? Получается нам надо для каждого гостя из группы искать место в номере, и возраст места должен быть больше или равен возрасту гостя, и учитывать, что на одно место только один человек. Этакое рекурсивное исключение гостей и мест в номере.
И здесь нам помогут хранимые процедуры. Для нашей задачи процедура выглядит следующим образом.
CREATE OR REPLACE FUNCTION is_room_fit_guests(guests INTEGER[], capacity INTEGER[])
RETURNS BOOLEAN
AS
$$
DECLARE
guest int;
seat int;
seat_index int;
max_array_index CONSTANT int := 2147483647;
BEGIN
guest = guests[1];
IF guest IS NULL
THEN
RETURN TRUE;
END IF;
seat_index := 1;
FOREACH seat IN ARRAY capacity
LOOP
IF guest <= seat
THEN
RETURN is_room_fit_guests(guests[2:max_array_index], capacity[1:seat_index-1] || capacity[seat_index+1:max_array_index]);
END IF;
seat_index := seat_index + 1;
END LOOP;
RETURN FALSE;
END;
$$
LANGUAGE plpgsql;
И пример использования.
И теперь наш запрос выглядит так.
WITH fit_arrival_rules AS (
SELECT DISTINCT ON (property_id)
arrival_date,
property_id,
abs('2018-01-02'::date - arrival_date) AS days_diff
FROM property_arrival_rules
INNER JOIN properties ON properties.id = property_arrival_rules.property_id
WHERE '2017-01-02'::date - 7 <= arrival_date AND arrival_date <= '2018-01-02'::date + 7
AND (
(properties.dest_type = 'Place' AND properties.dest_id IN (9))
OR (properties.dest_type = 'District' AND properties.dest_id IN (16, 17))
)
ORDER BY property_id, days_diff
),
fit_arrival_dates AS (
SELECT
property_arrival_periods.arrival_date,
property_arrival_periods.departure_date,
property_arrival_periods.property_id
FROM property_arrival_periods
INNER JOIN fit_arrival_rules
ON property_arrival_periods.property_id = fit_arrival_rules.property_id
AND property_arrival_periods.arrival_date = fit_arrival_rules.arrival_date
WHERE wanted_departure_range @> (property_arrival_periods.arrival_date + 6)
)
SELECT
rooms.property_id,
fit_arrival_dates.arrival_date,
fit_arrival_dates.departure_date,
room_price_policy_id,
room_price_policies.meal_type,
(
CASE
WHEN room_properties.currency = 'EUR' THEN SUM(room_prices.price)
ELSE (SUM(room_prices.price) / COALESCE(currency_rates.price, 1))::DECIMAL(10,2)
END
) AS total,
(
CASE
WHEN room_properties.currency = 'EUR' THEN AVG(room_prices.price)::DECIMAL(10,2)
ELSE (AVG(room_prices.price) / COALESCE(currency_rates.price, 1))::DECIMAL(10,2)
END
) AS average_night_price,
rooms.id AS room_id,
is_room_fit_guests(ARRAY[7,9,18,18,18], room_price_policies.capacity) AS fit_people
FROM room_prices
INNER JOIN room_price_policies ON room_prices.room_price_policy_id = room_price_policies.id
INNER JOIN rooms ON room_price_policies.room_id = rooms.id
INNER JOIN properties room_properties ON room_properties.id = rooms.property_id
LEFT JOIN currency_rates
ON currency_rates.sale_currency = room_properties.currency
AND currency_rates.buy_currency = 'EUR'
INNER JOIN (
SELECT room_availabilities.room_id
FROM room_availabilities
INNER JOIN rooms ON rooms.id = room_availabilities.room_id
INNER JOIN fit_arrival_dates ON fit_arrival_dates.property_id = rooms.property_id
WHERE fit_arrival_dates.arrival_date <= date AND date < fit_arrival_dates.departure_date
AND initial_count - sales_count > 0
GROUP BY fit_arrival_dates.arrival_date, fit_arrival_dates.departure_date, room_id
HAVING COUNT(room_availabilities.id) = (fit_arrival_dates.departure_date - fit_arrival_dates.arrival_date)
) ra ON ra.room_id = rooms.id
INNER JOIN fit_arrival_dates ON fit_arrival_dates.property_id = rooms.property_id
WHERE fit_arrival_dates.arrival_date <= price_date AND price_date < fit_arrival_dates.departure_date
AND (room_price_policies.has_special_requirements = FALSE
OR (room_price_policies.has_special_requirements = TRUE AND room_price_policies.before_type = 0
AND room_price_policies.before_date IS NOT NULL AND '2017-08-17'::date < room_price_policies.before_date)
OR (room_price_policies.has_special_requirements = TRUE AND room_price_policies.before_type = 1
AND room_price_policies.days_before_arrival IS NOT NULL
AND '2017-08-17'::date < fit_arrival_dates.arrival_date - room_price_policies.days_before_arrival)
)
AND room_price_policies.capacity IS NOT NULL
GROUP BY
rooms.property_id,
fit_arrival_dates.arrival_date,
fit_arrival_dates.departure_date,
room_price_policy_id,
room_price_policies.meal_type,
rooms.id,
room_properties.currency,
currency_rates.price,
room_price_policies.capacity
HAVING COUNT(room_prices.id) = (fit_arrival_dates.departure_date - fit_arrival_dates.arrival_date)
Шаг 5. Подходящие отели
Выбираем отели с данными (из шага 4) по одной самой дешевой политике номера с положительным значением “помещается ли вся группа в номер”.
WITH fit_arrival_rules AS (
SELECT DISTINCT ON (property_id)
arrival_date,
property_id,
abs('2018-01-02'::date - arrival_date) AS days_diff
FROM property_arrival_rules
INNER JOIN properties ON properties.id = property_arrival_rules.property_id
WHERE '2017-01-02'::date - 7 <= arrival_date AND arrival_date <= '2018-01-02'::date + 7
AND (
(properties.dest_type = 'Place' AND properties.dest_id IN (9))
OR (properties.dest_type = 'District' AND properties.dest_id IN (16, 17))
)
ORDER BY property_id, days_diff
),
fit_arrival_dates AS (
SELECT
property_arrival_periods.arrival_date,
property_arrival_periods.departure_date,
property_arrival_periods.property_id
FROM property_arrival_periods
INNER JOIN fit_arrival_rules
ON property_arrival_periods.property_id = fit_arrival_rules.property_id
AND property_arrival_periods.arrival_date = fit_arrival_rules.arrival_date
WHERE wanted_departure_range @> (property_arrival_periods.arrival_date + 6)
),
properties_with_rooms AS (
SELECT
rooms.property_id,
fit_arrival_dates.arrival_date,
fit_arrival_dates.departure_date,
room_price_policy_id,
room_price_policies.meal_type,
(
CASE
WHEN room_properties.currency = 'EUR' THEN SUM(room_prices.price)
ELSE (SUM(room_prices.price) / COALESCE(currency_rates.price, 1))::DECIMAL(10,2)
END
) AS total,
(
CASE
WHEN room_properties.currency = 'EUR' THEN AVG(room_prices.price)::DECIMAL(10,2)
ELSE (AVG(room_prices.price) / COALESCE(currency_rates.price, 1))::DECIMAL(10,2)
END
) AS average_night_price,
rooms.id AS room_id,
is_room_fit_guests(ARRAY[7,9,18,18,18], room_price_policies.capacity) AS fit_people
FROM room_prices
INNER JOIN room_price_policies ON room_prices.room_price_policy_id = room_price_policies.id
INNER JOIN rooms ON room_price_policies.room_id = rooms.id
INNER JOIN properties room_properties ON room_properties.id = rooms.property_id
LEFT JOIN currency_rates
ON currency_rates.sale_currency = room_properties.currency
AND currency_rates.buy_currency = 'EUR'
INNER JOIN (
SELECT room_availabilities.room_id
FROM room_availabilities
INNER JOIN rooms ON rooms.id = room_availabilities.room_id
INNER JOIN fit_arrival_dates ON fit_arrival_dates.property_id = rooms.property_id
WHERE fit_arrival_dates.arrival_date <= date AND date < fit_arrival_dates.departure_date
AND initial_count - sales_count > 0
GROUP BY fit_arrival_dates.arrival_date, fit_arrival_dates.departure_date, room_id
HAVING COUNT(room_availabilities.id) = (fit_arrival_dates.departure_date - fit_arrival_dates.arrival_date)
) ra ON ra.room_id = rooms.id
INNER JOIN fit_arrival_dates ON fit_arrival_dates.property_id = rooms.property_id
WHERE fit_arrival_dates.arrival_date <= price_date AND price_date < fit_arrival_dates.departure_date
AND (room_price_policies.has_special_requirements = FALSE
OR (room_price_policies.has_special_requirements = TRUE AND room_price_policies.before_type = 0
AND room_price_policies.before_date IS NOT NULL AND '2017-08-17'::date < room_price_policies.before_date)
OR (room_price_policies.has_special_requirements = TRUE AND room_price_policies.before_type = 1
AND room_price_policies.days_before_arrival IS NOT NULL
AND '2017-08-17'::date < fit_arrival_dates.arrival_date - room_price_policies.days_before_arrival)
)
AND room_price_policies.capacity IS NOT NULL
GROUP BY
rooms.property_id,
fit_arrival_dates.arrival_date,
fit_arrival_dates.departure_date,
room_price_policy_id,
room_price_policies.meal_type,
rooms.id,
room_properties.currency,
currency_rates.price,
room_price_policies.capacity
HAVING COUNT(room_prices.id) = (fit_arrival_dates.departure_date - fit_arrival_dates.arrival_date)
)
SELECT DISTINCT ON(property_id) *,
1 as all_guests_placed
FROM properties_with_rooms
WHERE fit_people = TRUE
ORDER BY property_id, total
Шаг 6. Неподходящие отели с номерами в наличии
Такие отели, в которых нет номера под всю группу гостей, в качестве вариантов для бронирования нескольких номеров. Выбираем отели из шага 4 с отрицательным значением “помещается ли вся группа в номер”, но не попавшие в результат шага 5
WITH fit_arrival_rules AS (
SELECT DISTINCT ON (property_id)
arrival_date,
property_id,
abs('2018-01-02'::date - arrival_date) AS days_diff
FROM property_arrival_rules
INNER JOIN properties ON properties.id = property_arrival_rules.property_id
WHERE '2017-01-02'::date - 7 <= arrival_date AND arrival_date <= '2018-01-02'::date + 7
AND (
(properties.dest_type = 'Place' AND properties.dest_id IN (9))
OR (properties.dest_type = 'District' AND properties.dest_id IN (16, 17))
)
ORDER BY property_id, days_diff
),
fit_arrival_dates AS (
SELECT
property_arrival_periods.arrival_date,
property_arrival_periods.departure_date,
property_arrival_periods.property_id
FROM property_arrival_periods
INNER JOIN fit_arrival_rules
ON property_arrival_periods.property_id = fit_arrival_rules.property_id
AND property_arrival_periods.arrival_date = fit_arrival_rules.arrival_date
WHERE wanted_departure_range @> (property_arrival_periods.arrival_date + 6)
),
properties_with_rooms AS (
SELECT
rooms.property_id,
fit_arrival_dates.arrival_date,
fit_arrival_dates.departure_date,
room_price_policy_id,
room_price_policies.meal_type,
(
CASE
WHEN room_properties.currency = 'EUR' THEN SUM(room_prices.price)
ELSE (SUM(room_prices.price) / COALESCE(currency_rates.price, 1))::DECIMAL(10,2)
END
) AS total,
(
CASE
WHEN room_properties.currency = 'EUR' THEN AVG(room_prices.price)::DECIMAL(10,2)
ELSE (AVG(room_prices.price) / COALESCE(currency_rates.price, 1))::DECIMAL(10,2)
END
) AS average_night_price,
rooms.id AS room_id,
is_room_fit_guests(ARRAY[7,9,18,18,18], room_price_policies.capacity) AS fit_people
FROM room_prices
INNER JOIN room_price_policies ON room_prices.room_price_policy_id = room_price_policies.id
INNER JOIN rooms ON room_price_policies.room_id = rooms.id
INNER JOIN properties room_properties ON room_properties.id = rooms.property_id
LEFT JOIN currency_rates
ON currency_rates.sale_currency = room_properties.currency
AND currency_rates.buy_currency = 'EUR'
INNER JOIN (
SELECT room_availabilities.room_id
FROM room_availabilities
INNER JOIN rooms ON rooms.id = room_availabilities.room_id
INNER JOIN fit_arrival_dates ON fit_arrival_dates.property_id = rooms.property_id
WHERE fit_arrival_dates.arrival_date <= date AND date < fit_arrival_dates.departure_date
AND initial_count - sales_count > 0
GROUP BY fit_arrival_dates.arrival_date, fit_arrival_dates.departure_date, room_id
HAVING COUNT(room_availabilities.id) = (fit_arrival_dates.departure_date - fit_arrival_dates.arrival_date)
) ra ON ra.room_id = rooms.id
INNER JOIN fit_arrival_dates ON fit_arrival_dates.property_id = rooms.property_id
WHERE fit_arrival_dates.arrival_date <= price_date AND price_date < fit_arrival_dates.departure_date
AND (room_price_policies.has_special_requirements = FALSE
OR (room_price_policies.has_special_requirements = TRUE AND room_price_policies.before_type = 0
AND room_price_policies.before_date IS NOT NULL AND '2017-08-17'::date < room_price_policies.before_date)
OR (room_price_policies.has_special_requirements = TRUE AND room_price_policies.before_type = 1
AND room_price_policies.days_before_arrival IS NOT NULL
AND '2017-08-17'::date < fit_arrival_dates.arrival_date - room_price_policies.days_before_arrival)
)
AND room_price_policies.capacity IS NOT NULL
GROUP BY
rooms.property_id,
fit_arrival_dates.arrival_date,
fit_arrival_dates.departure_date,
room_price_policy_id,
room_price_policies.meal_type,
rooms.id,
room_properties.currency,
currency_rates.price,
room_price_policies.capacity
HAVING COUNT(room_prices.id) = (fit_arrival_dates.departure_date - fit_arrival_dates.arrival_date)
),
properties_with_recommended_room AS (
SELECT DISTINCT ON(property_id) *,
1 as all_guests_placed
FROM properties_with_rooms
WHERE fit_people = TRUE
ORDER BY property_id, total
)
SELECT DISTINCT ON(property_id) *,
0 as all_guests_placed
FROM properties_with_rooms
WHERE property_id NOT IN (SELECT property_id FROM properties_with_recommended_room)
ORDER BY property_id, total
Шаг 7. Все отели направления
И наконец, объединяем результаты, сортируя сначала подходящие отели (из шага 5), затем неподходящие отели с доступными номерами (из шага 6), затем все остальные отели, дополнительно сортируя по стоимости за период или звездности отеля при необходимости, а также добавляя пагинацию (20 отелей на странице)
WITH fit_arrival_rules AS (
SELECT DISTINCT ON (property_id)
arrival_date,
property_id,
abs('2018-01-02'::date - arrival_date) AS days_diff
FROM property_arrival_rules
INNER JOIN properties ON properties.id = property_arrival_rules.property_id
WHERE '2017-01-02'::date - 7 <= arrival_date AND arrival_date <= '2018-01-02'::date + 7
AND (
(properties.dest_type = 'Place' AND properties.dest_id IN (9))
OR (properties.dest_type = 'District' AND properties.dest_id IN (16, 17))
)
ORDER BY property_id, days_diff
),
fit_arrival_dates AS (
SELECT
property_arrival_periods.arrival_date,
property_arrival_periods.departure_date,
property_arrival_periods.property_id
FROM property_arrival_periods
INNER JOIN fit_arrival_rules
ON property_arrival_periods.property_id = fit_arrival_rules.property_id
AND property_arrival_periods.arrival_date = fit_arrival_rules.arrival_date
WHERE wanted_departure_range @> (property_arrival_periods.arrival_date + 6)
),
properties_with_rooms AS (
SELECT
rooms.property_id,
fit_arrival_dates.arrival_date,
fit_arrival_dates.departure_date,
room_price_policy_id,
room_price_policies.meal_type,
(
CASE
WHEN room_properties.currency = 'EUR' THEN SUM(room_prices.price)
ELSE (SUM(room_prices.price) / COALESCE(currency_rates.price, 1))::DECIMAL(10,2)
END
) AS total,
(
CASE
WHEN room_properties.currency = 'EUR' THEN AVG(room_prices.price)::DECIMAL(10,2)
ELSE (AVG(room_prices.price) / COALESCE(currency_rates.price, 1))::DECIMAL(10,2)
END
) AS average_night_price,
rooms.id AS room_id,
is_room_fit_guests(ARRAY[7,9,18,18,18], room_price_policies.capacity) AS fit_people
FROM room_prices
INNER JOIN room_price_policies ON room_prices.room_price_policy_id = room_price_policies.id
INNER JOIN rooms ON room_price_policies.room_id = rooms.id
INNER JOIN properties room_properties ON room_properties.id = rooms.property_id
LEFT JOIN currency_rates
ON currency_rates.sale_currency = room_properties.currency
AND currency_rates.buy_currency = 'EUR'
INNER JOIN (
SELECT room_availabilities.room_id
FROM room_availabilities
INNER JOIN rooms ON rooms.id = room_availabilities.room_id
INNER JOIN fit_arrival_dates ON fit_arrival_dates.property_id = rooms.property_id
WHERE fit_arrival_dates.arrival_date <= date AND date < fit_arrival_dates.departure_date
AND initial_count - sales_count > 0
GROUP BY fit_arrival_dates.arrival_date, fit_arrival_dates.departure_date, room_id
HAVING COUNT(room_availabilities.id) = (fit_arrival_dates.departure_date - fit_arrival_dates.arrival_date)
) ra ON ra.room_id = rooms.id
INNER JOIN fit_arrival_dates ON fit_arrival_dates.property_id = rooms.property_id
WHERE fit_arrival_dates.arrival_date <= price_date AND price_date < fit_arrival_dates.departure_date
AND (room_price_policies.has_special_requirements = FALSE
OR (room_price_policies.has_special_requirements = TRUE AND room_price_policies.before_type = 0
AND room_price_policies.before_date IS NOT NULL AND '2017-08-17'::date < room_price_policies.before_date)
OR (room_price_policies.has_special_requirements = TRUE AND room_price_policies.before_type = 1
AND room_price_policies.days_before_arrival IS NOT NULL
AND '2017-08-17'::date < fit_arrival_dates.arrival_date - room_price_policies.days_before_arrival)
)
AND room_price_policies.capacity IS NOT NULL
GROUP BY
rooms.property_id,
fit_arrival_dates.arrival_date,
fit_arrival_dates.departure_date,
room_price_policy_id,
room_price_policies.meal_type,
rooms.id,
room_properties.currency,
currency_rates.price,
room_price_policies.capacity
HAVING COUNT(room_prices.id) = (fit_arrival_dates.departure_date - fit_arrival_dates.arrival_date)
),
properties_with_recommended_room AS (
SELECT DISTINCT ON(property_id) *,
1 as all_guests_placed
FROM properties_with_rooms
WHERE fit_people = TRUE
ORDER BY property_id, total
),
properties_without_recommended_room AS (
SELECT DISTINCT ON(property_id) *,
0 as all_guests_placed
FROM properties_with_rooms
WHERE property_id NOT IN (SELECT property_id FROM properties_with_recommended_room)
ORDER BY property_id, total
),
properties_with_cheapest_room AS (
SELECT * FROM properties_with_recommended_room
UNION ALL
SELECT * FROM properties_without_recommended_room
)
SELECT properties.*,
(
CASE
WHEN room_id IS NOT NULL THEN 1
ELSE 0
END
) AS room_available,
properties_with_cheapest_room.arrival_date,
properties_with_cheapest_room.departure_date,
properties_with_cheapest_room.room_id,
properties_with_cheapest_room.room_price_policy_id,
properties_with_cheapest_room.total,
properties_with_cheapest_room.average_night_price,
properties_with_cheapest_room.all_guests_placed
FROM properties
LEFT JOIN properties_with_cheapest_room ON properties_with_cheapest_room.property_id = properties.id
WHERE
(
(properties.dest_type = 'Place' AND properties.dest_id IN (9))
OR (properties.dest_type = 'District' AND properties.dest_id IN (16, 17))
)
ORDER BY all_guests_placed DESC NULLS LAST, room_available DESC, total ASC
LIMIT 20 OFFSET 0
Шаг 8. 3 самых дешевых номера
Перед тем как отдать результат пользователю, для неподходящих отелей с доступными номерами отдельным sql-запросом, выбираем 3 самых дешевых номера. Запрос очень похож на поиск самих отелей. Разве что выбираются уникальные номера и только на конкретные отели (из шага 6). Допустим, что на текущей странице два таких отеля, и их id — 1 и 4. Запрос будет таким.
WITH fit_arrival_rules AS (
SELECT DISTINCT ON (property_id)
arrival_date,
property_id,
abs('2018-01-02'::date - arrival_date) AS days_diff
FROM property_arrival_rules
INNER JOIN properties ON properties.id = property_arrival_rules.property_id
WHERE '2017-01-02'::date - 7 <= arrival_date AND arrival_date <= '2018-01-02'::date + 7
AND property_id IN (1, 4)
ORDER BY property_id, days_diff
),
fit_arrival_dates AS (
SELECT
property_arrival_periods.arrival_date,
property_arrival_periods.departure_date,
property_arrival_periods.property_id
FROM property_arrival_periods
INNER JOIN fit_arrival_rules
ON property_arrival_periods.property_id = fit_arrival_rules.property_id
AND property_arrival_periods.arrival_date = fit_arrival_rules.arrival_date
WHERE wanted_departure_range @> (property_arrival_periods.arrival_date + 6)
),
properties_with_available_rooms AS (
SELECT DISTINCT ON (rooms.id)
rooms.property_id,
fit_arrival_dates.arrival_date,
fit_arrival_dates.departure_date,
room_price_policy_id,
room_price_policies.meal_type,
(
CASE
WHEN room_properties.currency = 'EUR' THEN SUM(room_prices.price)
ELSE (SUM(room_prices.price) / COALESCE(currency_rates.price, 1))::DECIMAL(10,2)
END
) AS total,
(
CASE
WHEN room_properties.currency = 'EUR' THEN AVG(room_prices.price)::DECIMAL(10,2)
ELSE (AVG(room_prices.price) / COALESCE(currency_rates.price, 1))::DECIMAL(10,2)
END
) AS average_night_price,
rooms.id AS room_id
FROM room_prices
INNER JOIN room_price_policies ON room_prices.room_price_policy_id = room_price_policies.id
INNER JOIN rooms ON room_price_policies.room_id = rooms.id
INNER JOIN properties room_properties ON room_properties.id = rooms.property_id
LEFT JOIN currency_rates
ON currency_rates.sale_currency = room_properties.currency
AND currency_rates.buy_currency = 'EUR'
INNER JOIN (
SELECT room_availabilities.room_id
FROM room_availabilities
INNER JOIN rooms ON rooms.id = room_availabilities.room_id
INNER JOIN fit_arrival_dates ON fit_arrival_dates.property_id = rooms.property_id
WHERE fit_arrival_dates.arrival_date <= date AND date < fit_arrival_dates.departure_date
AND initial_count - sales_count > 0
GROUP BY fit_arrival_dates.arrival_date, fit_arrival_dates.departure_date, room_id
HAVING COUNT(room_availabilities.id) = (fit_arrival_dates.departure_date - fit_arrival_dates.arrival_date)
) ra ON ra.room_id = rooms.id
INNER JOIN fit_arrival_dates ON fit_arrival_dates.property_id = rooms.property_id
WHERE fit_arrival_dates.arrival_date <= price_date AND price_date < fit_arrival_dates.departure_date
AND (room_price_policies.has_special_requirements = FALSE
OR (room_price_policies.has_special_requirements = TRUE AND room_price_policies.before_type = 0
AND room_price_policies.before_date IS NOT NULL AND '2017-08-17'::date < room_price_policies.before_date)
OR (room_price_policies.has_special_requirements = TRUE AND room_price_policies.before_type = 1
AND room_price_policies.days_before_arrival IS NOT NULL
AND '2017-08-17'::date < fit_arrival_dates.arrival_date - room_price_policies.days_before_arrival)
)
GROUP BY
rooms.property_id,
fit_arrival_dates.arrival_date,
fit_arrival_dates.departure_date,
room_price_policy_id,
room_price_policies.meal_type,
rooms.id,
room_properties.currency,
currency_rates.price
HAVING COUNT(room_prices.id) = (fit_arrival_dates.departure_date - fit_arrival_dates.arrival_date)
)
SELECT
distinct_available_rooms.property_id,
distinct_available_rooms.room_id,
distinct_available_rooms.room_price_policy_id,
distinct_available_rooms.total
FROM properties
JOIN LATERAL (
SELECT * FROM properties_with_available_rooms
WHERE properties.id = properties_with_available_rooms.property_id
ORDER BY total
LIMIT 3
) distinct_available_rooms ON distinct_available_rooms.property_id = properties.id
WHERE properties.id IN (1, 4)
ORDER BY distinct_available_rooms.total
Результат
Ускорение работы поиска в десятки раз и это при относительно небольшом количестве данных, а со временем разница будет ощущаться все больше и больше.
И конечно тонна полезного опыта, полученного в ходе решения.
Автор: rubyruby
