Cила PostgreSQL22.09.2017 14:48

1c0b91da958d4c49b0d09fc96619ef40.jpg


Встречаясь со сложной нетривиальной задачей по поиску и обработке данных, порой хочется решить ее в лоб. И хотя ты понимаешь, что решение возможно будет медленным или вообще нежизнеспособным, а знаний и опыта не хватает, чтобы решить ее по-настоящему, не нужно спешить. Важно понять, что СУБД были специально созданы для этого, и решать задачи, предназначенные для них, другими способами не стоит.

Задача


Поиск отелей с доступными номерами на конкретные даты некоторой группой людей.

Проект


Когда проект попал к нам в руки, поиск уже был реализован. Он работал медленно, очень медленно. А все потому, что расчеты и выборки велись не на стороне базы данных, а на стороне 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 '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
)

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 '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
),
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 '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
),
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 '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
),
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 '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
),
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 '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
),
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. Запрос будет таким.

3 дешевых номера
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 '2018-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

Результат


Ускорение работы поиска в десятки раз и это при относительно небольшом количестве данных, а со временем разница будет ощущаться все больше и больше.
И конечно тонна полезного опыта, полученного в ходе решения.

© Habrahabr.ru