- PVSM.RU - https://www.pvsm.ru -
Встречаясь со сложной нетривиальной задачей по поиску и обработке данных, порой хочется решить ее в лоб. И хотя ты понимаешь, что решение возможно будет медленным или вообще нежизнеспособным, а знаний и опыта не хватает, чтобы решить ее по-настоящему, не нужно спешить. Важно понять, что СУБД были специально созданы для этого, и решать задачи, предназначенные для них, другими способами не стоит.
Поиск отелей с доступными номерами на конкретные даты некоторой группой людей.
Когда проект попал к нам в руки, поиск уже был реализован. Он работал медленно, очень медленно. А все потому, что расчеты и выборки велись не на стороне базы данных, а на стороне web-приложения: выбиралась тонна записей из разных таблиц, и в циклах подбирались и рассчитывались номера, фильтровались, сортировались и выводились постранично. Очень неэффективно. А приложение, к слову, написано на Ruby on Rails.
Не надо так.
Исходная схема данных [1] (в примерах искусственно упрощена, чтобы влезть в ограничения sqlfiddle)
Отсутствие записи в таблице на конкретную дату означает, что въезд в этот день невозможен. Зачем хранится так? Все дело в типах правил въезда. Подробнее об этих типах в решении ниже [7].
Отсутствие записи на какую-либо ночь означает недоступность номера.
Отсутствие записи за какую-либо ночь означает невозможность приобрести номер в эту ночь.
Пользователь в форме поиска может выбрать:
Результатом поиска должен стать список отелей по направлению, району. И для каждого отеля:
Список отелей должен быть отсортирован: сначала идут отели с подходящим номером, затем отели с 3-мя самыми дешевыми, затем отели без доступных номеров. Дополнительно возможна сортировка по звездности отеля или стоимости за период.
При этом надо учитывать, что прийти в приложение из базы должно уже лимитированное число записей для конкретной страницы (пагинация).
Это возможно? Да, в 2 (два!) sql-запроса (после небольшой модификации схемы данных)
Допустим пользователь ищет по следующим параметрам:
По сути, надо найти по одному правилу въезда для каждого отеля направления или района с ближайшей датой к желаемой дате въезда. И здесь можно допустить, что ищем ближайшую дату не дальше N дней от желаемой, например, 7 дней. Вот так выглядит такой запрос [13].
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
Нам надо рассчитать дату выезда на каждый отель исходя из выбранного правила въезда (из шага 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 [14] — рассчитанные периоды въезда.
Для того, чтобы ограничить число записей в этой таблице и сделать расчет не бесконечным, нужно добавить некое ограничение на максимальный срок бронирования, например, 30 дней. При таком ограничении на каждый отель на один год, в худшем случае, будет ~11000 записей, что выглядит вполне неплохо.
Таким образом при добавлении / изменении / удалении правила въезда, мы фоном в приложении:
И тогда при поиске нам ничего не нужно считать, а только выбрать из этой новой таблицы [15].
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)
Берем все доступные номера [16], т.е. те, что имеют записи на рассчитанный период въезда-выезда (из шага 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)
Берем политики номеров (из шага 3), для которых есть цены на каждый день рассчитанного периода, и вычисляем стоимость за период и среднюю цену за ночь, пересчитывая суммы при этом из валюты отеля в некую базовую валюту (в нашем случае — EUR). Кроме того, необходимо учитывать специальные условия политик “бронирование до даты” и “бронирование за N дней до въезда”.
Также нам понадобится признак “помещается ли вся группа в номер” на каждую полученную политику.
По задаче политика должна содержать максимально допустимые возрасты с количеством.
Например, в номер могут заехать 3 взрослых + 2 ребенка 5 лет.
В такой номер смогут поместиться группы:
Но не поместятся:
И это проблема.
Мало того, что изначально максимальное количество гостей представлено полем типа hstore (к которому условия проблемно будет написать) в странном виде: Map, где ключи — максимальный возраст, а значения — количество, а для взрослых — ключ вообще “adults”.
Так еще и непонятно, как вообще представить такую информацию так, чтобы можно было проверить поместится группа людей или нет.
А давайте представим максимальное количество гостей в виде массива мест (отсортированного по возрастанию), где каждое место — максимальный возраст (18 для взрослого). И тогда вместимость номера “3 взрослых + 2 ребенка 5 лет” будет выглядеть как
[5, 5, 18, 18, 18]
А группу людей представим как массив их возрастов, и тогда “2 взрослых + 2 ребенка (5 и 9 лет)” будут выглядеть как
[5, 9, 18, 18]
В итоге, в таблицу политик (room_price_policies) [17] был добавлен столбец вместимости (capacity) хранящий ее в таком виде.
Но еще остается вопрос. Как на sql написать условие (или запрос): поместится ли [5, 9, 18, 18] в [5, 5, 18, 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;
И пример [19] использования.
И теперь наш запрос выглядит так [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)
)
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)
Выбираем отели [21] с данными (из шага 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
Такие отели, в которых нет номера под всю группу гостей, в качестве вариантов для бронирования нескольких номеров. Выбираем отели [22] из шага 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
И наконец, объединяем результаты, [23] сортируя сначала подходящие отели (из шага 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
Перед тем как отдать результат пользователю, для неподходящих отелей с доступными номерами отдельным sql-запросом, выбираем 3 самых дешевых номера. Запрос очень похож на поиск самих отелей. Разве что выбираются уникальные номера и только на конкретные отели (из шага 6). Допустим, что на текущей странице два таких отеля, и их id — 1 и 4. Запрос будет таким [24].
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
Источник [25]
Сайт-источник PVSM.RU: https://www.pvsm.ru
Путь до страницы источника: https://www.pvsm.ru/postgresql/264234
Ссылки в тексте:
[1] Исходная схема данных: http://sqlfiddle.com/#!17/18237
[2] Places: http://sqlfiddle.com/#!17/18237/1/0
[3] Districts: http://sqlfiddle.com/#!17/18237/2
[4] Properties: http://sqlfiddle.com/#!17/18237/3/0
[5] полиморфной связи: http://guides.rubyonrails.org/association_basics.html#polymorphic-associations
[6] Property_arrival_rules: http://sqlfiddle.com/#!17/18237/4/0
[7] в решении ниже: #departure_date
[8] Rooms: http://sqlfiddle.com/#!17/18237/5/0
[9] Room_availabilities: http://sqlfiddle.com/#!17/18237/6/0
[10] Room_price_policies: http://sqlfiddle.com/#!17/18237/7/0
[11] Room_prices: http://sqlfiddle.com/#!17/18237/8/0
[12] Currency_rates: http://sqlfiddle.com/#!17/18237/9/0
[13] Вот так выглядит такой запрос: http://sqlfiddle.com/#!17/18237/19/0
[14] property_arrival_periods: http://sqlfiddle.com/#!17/30c3f/9/0
[15] выбрать из этой новой таблицы: http://sqlfiddle.com/#!17/30c3f/2/0
[16] Берем все доступные номера: http://sqlfiddle.com/#!17/30c3f/3/0
[17] таблицу политик (room_price_policies): http://sqlfiddle.com/#!17/30c3f/10/0
[18] хранимые процедуры: https://www.postgresql.org/docs/9.6/static/plpgsql.html
[19] пример: http://sqlfiddle.com/#!17/30c3f/4/0
[20] выглядит так: http://sqlfiddle.com/#!17/6dd2c/1/0
[21] Выбираем отели: http://sqlfiddle.com/#!17/6dd2c/3/0
[22] Выбираем отели: http://sqlfiddle.com/#!17/6dd2c/4/0
[23] объединяем результаты,: http://sqlfiddle.com/#!17/6dd2c/6/0
[24] будет таким: http://sqlfiddle.com/#!17/6dd2c/8/0
[25] Источник: https://habrahabr.ru/post/338406/?utm_source=habrahabr&utm_medium=rss&utm_campaign=best
Нажмите здесь для печати.