Cила PostgreSQL

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

Задача

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

Проект

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

Результат

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

И конечно тонна полезного опыта, полученного в ходе решения.