Встречаясь со сложной нетривиальной задачей по поиску и обработке данных, порой хочется решить ее в лоб. И хотя ты понимаешь, что решение возможно будет медленным или вообще нежизнеспособным, а знаний и опыта не хватает, чтобы решить ее по-настоящему, не нужно спешить. Важно понять, что СУБД были специально созданы для этого, и решать задачи, предназначенные для них, другими способами не стоит.
Задача
Поиск отелей с доступными номерами на конкретные даты некоторой группой людей.
Проект
Когда проект попал к нам в руки, поиск уже был реализован. Он работал медленно, очень медленно. А все потому, что расчеты и выборки велись не на стороне базы данных, а на стороне 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. Запрос будет таким.
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
Результат
Ускорение работы поиска в десятки раз и это при относительно небольшом количестве данных, а со временем разница будет ощущаться все больше и больше.
И конечно тонна полезного опыта, полученного в ходе решения.
Комментарии (45)
ZurgInq
22.09.2017 13:02+4Написать правильный sql запрос — это отдельное искусство, далеко не все программисты (особенно на ruby on rails) способны это сделать. Да и сам фрэймворк и любые ORM не располагают к тому, что бы сочинять сложные sql запросы.
manefesto
22.09.2017 13:16+1Кроме того что надо написать правильно запрос, надо еще и провести некий анализ БД
«наложение» индексов может значительно повысить эффективность
stranger_shaman
22.09.2017 14:37WHERE '2017-01-02'::date - 7 <= arrival_date AND arrival_date <= '2018-01-02'::date + 7
Весьма странное место. Зачем нужно проверять даты за год назад?rubyruby Автор
22.09.2017 14:40+1Да, опечатка. Должно быть
WHERE '2018-01-02'::date - 7 <= arrival_date AND arrival_date <= '2018-01-02'::date + 7
Поправили, спасибо.
franzose
23.09.2017 02:36+1Можно сократить до
WHERE arrival_date BETWEEN '2018-01-02'::date - 8 AND '2018-01-02'::date + 8.Rupper
23.09.2017 08:38-1Ненадо так сокращать, ибо between работает по разному Иногда как интервал иногда как отрезок. Это я про разные базы. И эта экономия в три кнопки потом вльется в труднонаходимый косяк.
SirEdvin
22.09.2017 15:13-1О да, отличное решение. Давайте грузить базу одним длинным запросом с кучей join'ов, ведь такое решение отлично масштабируется и вообще не испытывает никаких проблем (спойлер: нет).
Когда у вас мало пользователей, то да, вообще никаких проблем. А они возникнут ровно тогда, когда их количество увеличится, и скажем, у вас будут постоянные локи на таблицы. Что вы будете делать тогда с запросом, который будет из-за этого адово тупить?
Я так же напомню, что в postgres даже нет грязного чтения, только repetable read.manefesto
22.09.2017 15:16+1На самом деле автор хотел донести то что он убрал нагрузку с бэкенда и сложил на откуп БД.
С одной стороны на одном и том же железе получился прирост производительности, с другой стороны его решение не оптимально и требует доработкиSirEdvin
22.09.2017 15:21Проблема в том, что база данных и так почти всегда bottleneck. Не понятно, зачем еще больше выносить на нее нагрузку.
manefesto
22.09.2017 15:26Не вся логика должна быть заложена в коде, в крупных продуктах очень много сделано через хранимые процедуры, тем более раз мы оперируем с данными из бд, то сомневаюсь что какой-то язык будет работать быстрее чем нативный SQL
Тот же самый birt-viewer все отчеты строит в БД, потому что он оперирует полученными данными для отображенияy90a19
22.09.2017 17:41-3хранимые процедуры это даже хуже огромных запросов.
И вы серьезно думаете, что например Java медленнее чем pgSQL?manefesto
22.09.2017 17:48+4Вы путаете мягкое с тёплым
Во-первых, sql выполняет именно то для чего он создан, запросы к бд
Во-вторых, приложению сначала надо получит данные из бд, а потом уже с ними выполнять некие действия
В-третьих, мы все знаем что Java очень любит память
Для текущего решения делать выборку средствами БД самое разумное. Но никто не отменял оптимизацию запросов и индексирования таблиц
Rupper
23.09.2017 08:48+1Я думаю, что для перечисленных вариантов с джойнами база выполнит запрос с меньшим числом операций чем вы это проделаете в своей программе на яве. Либо, вы умеете делать Все оптимизации которые делаются в базе и пишите их каждый раз когда надо вытащить данные. Но даже если так, запрос будет быстрее потому что
— при выполнении можно зачитать данные только из индекса (например для выполнения exists
— не произойдет конфликта согласованности данных только если вы не используете уровень изоляции serializable что дорого
— у базы есть статистика о кардинальности связей и она может применя разные
алгоримы для соединения таблиц
— база кеширует данные таблиц и индексов в памяти
Это просто 4 фактора что на вскидку в голову пришли.
В итоге чтении данных потребуется меньше операций чтения с диска (что на самом деле является единственной проблемой при чтении как в задаче с поиском) причем меньше не в константу раз а лучше.
win32nipuh
23.09.2017 18:45+1«хранимые процедуры это даже хуже огромных запросов» — вот так просто, как шашкой рубанул. Хуже огромных запросов только кривые руки девелоперов. А процедуры и функции — это то, что надо.
GlukKazan
22.09.2017 16:16+2А зачем ему грязное чтение? Он версионник. Конечно, его вакуум — отдельная и больная тема, но с чего бы ему «адово тупить» на локах, когда пишущие не лочат читающих?
SirEdvin
22.09.2017 16:26-2Официальная документация с вами не согласна. Локи от update отлично конфликтуют с локами для select.
GlukKazan
22.09.2017 18:16+1Локов нет, но есть нюансы. Например, в Oracle есть знаменитый «Snapshot too old». Ну и select (в Oracle точно, возможно и в PostgreSQL, этот момент не помню), в некоторых случаях может менять данные, но это всё экзотика. В первом приближении, можно считать, что в версионниках читающие не блокируют пишущих и наоборот (для того версионники и придуманы). Причём, в PostgreSQL версионное чтение экстремально дешёвое (дешевле чем в Oracle с его UNDO), поскольку версии не восстанавливаются, а просто читаются с диска и из кэша. Но за это приходится расплачиваться вакуумом.
Rupper
23.09.2017 08:56Подозреваю вы под словами селект может менять данные понимаете его реализацию select for update. При последовательном чтении строк действительно происходит пометка блока с зачитанными строками чтобы можно было реализовать select for update skip locked. Но такой паттерн это реализация очередей как правило и там не требуется апдейтов обычно, и проблем нет. Это про оракл.
А поясните, что значит версии не восстанавливаются и почему чтение радикально дешевле чем в оракле? В оракле есть три уровня изоляции и в пг вроде тоже read commited read dirty и serializable
Третий реально требует поиска блока нужной версии в анду, а read committed позволяет просто найти оригинальную версию блока в анду причем его адрес в самом блоке и записан так что там небольшие потери. А как постгрес действует?
darthunix
23.09.2017 16:16думаю, под «select в некоторых случаях может менять данные» подразумевается история в pg про обновление хинт битов при первом чтении вставленной строки. суть в том, что pg версионник, то есть под капотом в его таблицах лежат незакомиченные и удаленные строки, которые периодически чистит автовакуум. при выборе, какие строки убить, автовакуум ориентируется на хинт биты (они нужны еще для кучи разных вещей, не только для этого). когда вы начали транзакцию, но еще не закоммитили ее, строка все равно появляется таблице, а в хитн битах у нее пусто. как только транзакция коммитится, информация об этом замечательном факте попадает в clog. к сожалению, понять в момент коммита, в каких строках таблиц транзакция успела поменять данные проблемно, да и часть страничек может быть вытеснена из буферного кеша (а повторно считывать их дорого). поэтому в строках из закомиченной транзакции хинт биты остаются пустыми. а вот как только мы запросим через select одну из таких строк, pg проставит им хинт биты, что вызовет запись на диск при чтении данных. ну а если таких строк было много, то первое чтение может породить серьезную нагрузку на диск за счет вытеснение грязных страничек их общих буферов pg. это порой ставит в тупик не наступавших на эти грабли людей.
GlukKazan
24.09.2017 16:07+1Нет, select… for update, это вообще не совсем select, а DML в чистом виде. В том что касается Oracle, я говорил о ситуации, когда честный select просматривает блоки с устаревшими блокировками ранее закрытых транзакций и очищает их, что приводит к записи на диск. Про PostgreSQL вам подробно ответили выше. От себя лишь добавлю, что версионное чтение в Oracle может быть весьма накладным, поскольку включает в себя собственно чтение текущего состояния блока, а затем чтение UNDO, для приведения состояния этого блока на требуемый момент в прошлом. Если UNDO не хватает — получаем ORA-01555. В отличии от Oracle, PostgreSQL хранит исторические данные там же где и текущие и ему, грубо говоря, нет разницы, читать то или другое.
rubyruby Автор
22.09.2017 16:22А давайте вы напишете более-менее реалистичный сценарий? Здесь описан поиск и нет никаких UPDATE и уж тем более нет EXCLUSIVE блокировок. Данные только читаются. Адово тупить будет только разве что плохо написанный код.
Scf
22.09.2017 15:14Я бы поднял Solr/ElasticSearch c денормализованными данными, оптимизированными под нужный вид поиска.
Быть может, это было бы больше кода, связанного с обновлением поискового индекса, но решение точно получилось бы проще и масштабировалось бы лучше.rubyruby Автор
22.09.2017 16:47Вроде ничего решение, правда оно кажется очень трудоемким по реализации обновления данных, а также объем данных будет очень и очень большим. Кроме того, мы неоднократно встречались с ситуацией, когда какая-то нода отстает в кластере или же индекс совсем развалился. Обычно непросто понять, что произошло. Тут это еще как-то мониторить отдельно нужно. С другой стороны, по нашему опыту PostgreSQL великолепно держит нагрузку. Наше решение позволило «малой кровью» точечно переписать проблемную часть проекта.
Scf
22.09.2017 17:36Проблема вашего решения в наличии связи между бизнес-требованиями и структурой базы. Это проблемно, т.к.
- при изменении требований возможно придется делать несовместимую миграцию базы (долго и даунтайм)
- может появиться рассинхрон между данными и производными полями. У поискового индекса такой проблемы нет, т.к. его можно быстро перестроить.
- народ может повадиться делать селекты не по данным, а по производным полям, добавленным для поиска. Это значительно увеличит хрупкость системы.
Трудоемкость — это довод, если умеете лепить хитрые запросы и не умеете в индекс. Но на вашем месте я бы как минимум вынес производные поля как минимум в отдельные таблицы.
rubyruby Автор
22.09.2017 18:42У нас производных полей почти нет. Мы вынесли в отдельную таблицу периоды… Схема БД была изменена крайне незначительно.
amaksr
22.09.2017 16:40-2Запрос, конечно, выглядит монструозно. Саппорт (или следующий разработчик) вас за него помянет добрым словом, когда придется вносить какие-то изменения или искать баги.
Почему было не написать логику в Stored Procedure? Ведь тогда этого монитра можно было бы побить на куски, которые человек был бы способен понять.rubyruby Автор
22.09.2017 17:13Может запрос и выглядит устрашающим, но только если его рассматривать целиком.
На деле он строится из кусков, это всего лишь несколько CTE следующие друг за другом. И в приложении каждое CTE строится отдельно, и более того, они переиспользуются в других подобных запросах.
И если рассматривать запрос отдельно, по шагам, как в статье, то ничего страшного в нем нет.
symbix
22.09.2017 21:44И чем же хранимая процедура упростит внесение изменений или поиск багов? На куски можно побить и в коде приложения с ровно таким же успехом.
Shvonder
22.09.2017 21:07А зачем в таблице Properties поля dest_type и dest_id? Почему не сделать place_id и district_id? Или только district_id.
rubyruby Автор
23.09.2017 10:17Это так называемая полиморфная связь. Сейчас property можно привязать либо к place, либо к district. Представьте, что появляется третья (четвертая, пятая, ...) сущность к которой можно будет привязать property. При такой связи в структуру таблицы properties не придется вносить изменений.
bigtrot
25.09.2017 13:17Если боретесь за оптимизацию, то конечно надо всю логику переносить в базу данных. При этом, если стоит выбор между хранимыми процедурами и SQL, то однозначно SQL. SQL будет работать быстрее при условии, если вы используете все возможности SQL. Когда боретесь за производительность надо читать планы запросов и понимать где узкие места и что делать в той или иной ситуации. В приведенных запросах без планов тяжело сказать все ли вы выжали, но на вскидку можно сказать что использование DISTINCT или DISTINC ON это уже не эффективно. Есть паттерны как обходиться без DISTINCT. Оптимизированный запрос не значит доступен в понимании другими разработчиками, тем более если он не является программистом БД.
ZOXEXIVO
По вашему, вы сделали так, как нужно?
rubyruby Автор
Да, поиск работает, и гораздо быстрее чем изначально.
youlose
А как этот ад поддерживать-то и дорабатывать теперь?
youlose
Минуса-то аргументируйте…
Мой аргумент — это то чтобы дополнить такой запрос на 2 экрана, надо потратить от получаса времени, чтобы разобраться как он и почему работает и как его исправить. И это надо будет делать КАЖДЫЙ раз при встрече с таким запросом. И протестировать его и отладить по частям неудобно. И план запроса по такой бандуре сложен и непонятен.
rubyruby Автор
Запрос конструируется.
youlose
Хорошо, тогда другое дело.