データを検索および処理する複雑な非自明なタスクに直面して、ときどきそれを真正面から解決したいことがあります。 また、ソリューションはおそらく遅いか実行不可能であり、知識と経験がそれを実際に解決するのに十分ではないことを理解していますが、急ぐ必要はありません。 DBMSはこのために特別に作成されたものであり、それらを対象としたタスクを他の方法で解決することは価値がないことを理解することが重要です。
挑戦する
人々のグループによる特定の日付の手頃な価格のホテルを検索します。
プロジェクト
プロジェクトが私たちの手に渡ったとき、検索はすでに実装されていました。 彼はゆっくりと、非常にゆっくりと働きました。 そしてすべては、計算と選択がデータベース側ではなく、Webアプリケーション側で実行されたためです：大量のレコードが異なるテーブルから選択され、数字がサイクルで選択および計算され、ページごとにフィルタリング、ソート、表示されました。 非常に非効率的です。 ちなみに、アプリケーションはRuby on Railsで書かれています。
しないでください。
ソースデータ
元のデータスキーマ （例ではsqlfiddle制約に適合するように人工的に簡略化）
場所 -道順、リゾート。 フィールドから-名前のみ。
地区 -エリア。 各方向には複数の領域があります。 フィールド：方向の名前とID。
プロパティ -ホテルは、方向または特定のエリアに関連付けることができます。 フィールド：
- 名前-名前
- dest_type-方向または領域とのポリモーフィック接続のタイプ（「Place」または「District」）
- dest_id-方向または地域との接続のID
- 星-星（0〜5）
- 通貨-通貨コード
Property_arrival_rules-各ホテルのエントリルール。 フィールド：
- 到着日-到着日
- property_id-ホテルID
- ルール-ルールのタイプ（0または1）、タイプに応じて、出発日は異なる方法で計算されます。詳細は以下の決定で
- min_stay-最低宿泊日数
特定の日付のテーブルにエントリがない場合、この日のエントリは不可能です。 なぜこのように保存されるのですか？ エントリールールの種類がすべてです。 これらのタイプの詳細については、以下のソリューションをご覧ください 。
部屋 -ホテルの部屋、より正確には、部屋の種類 たとえば、2部屋の同一の部屋が1つのホテルに複数ある場合があります。 フィールド：ホテルの名前とID。
Room_availabilities-毎晩の空室状況。 フィールド：
- room_id-番号のID
- 日付-日付
- 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-日付
- 価格-価格
夜間の録音不足は、その夜に部屋を購入できないことを意味します。
Currency_rates-為替レート。 フィールド：
- sale_currency-販売通貨コード
- buy_currency-通貨コードを購入する
- 価格-レート、販売通貨の単位数をレートで割ると、購入通貨の単位数が得られます
入力パラメータ
検索フォームのユーザーは以下を選択できます。
- 方向またはエリアは、場所または地区の1つです。 また、これが目的地である場合、検索時には、目的地のホテルだけでなく、すべての地域のホテルも検索する必要があります
- ご希望の到着日
- ご希望の出発日
- 大人3人+子供2人（7歳と9歳）などの人々のグループの構成
- 必要に応じて、1泊の価格、ホテルの星評価、食事の種類でフィルターします
検索結果
検索の結果、目的地、地区別にホテルのリストが表示されます。 各ホテルについて：
- 適切なチェックイン/チェックアウト日
- 最も安い部屋番号、 またはグループ全体を収容できる部屋がない場合は3つの最も安い部屋に適しています
- 結果に表示される各番号の基本通貨でのチェックイン/チェックアウト期間のコスト
ホテルのリストを並べ替える必要があります。最初に適切な数のホテル、次に3つの最も安いホテル、次に空室のないホテルが表示されます。 ホテルのスターダムまたは期間ごとのコストで並べ替えることも可能です。
特定のページ（ページネーション）の限られた数のエントリがデータベースからすでにアプリケーションに到達している必要があることに注意してください。
可能ですか？ はい、2（2！）のSQLクエリで（データスキームを少し変更した後）
解決策
ユーザーが次のパラメーターで検索するとします。
- 方向「Val Thorens」には、さらに2つの領域「Tignes Le Lac」と「Tignes Val Claret」が含まれます
- 希望到着日：2018年1月2日
- 希望の出発日：2018年1月8日（したがって、希望の夜の数は6です）
- 人々のグループの構成：大人3人+子供2人（7および9歳）
- 今日：2017年8月17日
ステップ1.ご希望の最も近い到着日
実際、ホテルの目的地またはエリアごとに、目的のエントリ日付に最も近い日付を持つエントリルールを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から）と、希望するチェックイン/チェックアウト日の差として計算される夜数に基づいて、各ホテルのチェックアウト日を計算する必要があります。
そして、最初の問題が始まりました。 エントリールールは非常にトリッキーでした。 ルールには2つのタイプがあります。
タイプ1。特定の日に、任意の日数だけ呼び出すことができますが、N日以上です。
タイプ2。特定の日に厳密にN日間呼び出すことができます。
タイプ2のルールが検索期間に含まれる場合、期間全体を計算するために、ルールが終了する日に続く次のルール、つまりルールからの到着日+ N日を確認する必要があります。
タイプ2のルールの実際の例。土曜日にホテルに入室できるのは1週間のみです。 1から6日間入力したい場合は、まだ1週間はかかります。 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に置く方法は？
アプリケーション側では、エントリの規則に従って、毎日のすべての可能な到着-終了期間を事前計算し、フィールドを含む別のテーブルに入れることができます。
| 到着日
（到着日） | wanted_departure_date
（希望の出発日） | 出発日
（実際の 計算された 出発日） | property_id
（ホテルID） |
または、レコード数を減らすために、より厳密に、 タイプ2ルールの場合、いくつかの近くの日のチェックイン日と計算されたチェックアウト日はしばしば一致します
| 到着日
（到着日） | wanted_departure_range
（希望する出発期間、 タイプdaterange） | 出発日
（実際の 計算された 出発日） | property_id
（ホテルID） |
それをproperty_arrival_periods-計算されたエントリー期間と呼びましょう。
このテーブルのエントリ数を制限し、計算を無制限にしないようにするには、最大予約期間（30日など）に何らかの制限を追加する必要があります。 1年間の各ホテルのこのような制限により、最悪の場合、11,000エントリまでになります。
したがって、エントリルールを追加/変更/削除する場合、アプリケーションの背景がわかります。
- 日付の計算された期間を削除します：「ルール日から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から）を採用し、期間のコストと1泊あたりの平均価格を計算し、ホテル通貨からいくつかの基本通貨（この場合はEUR）に金額を再計算します。 さらに、「日付による予約」および「到着のN日前の予約」ポリシーの特別な条件を考慮する必要があります。
また、受け取ったポリシーごとに「グループ全体が数に収まる」という記号が必要です。
タスクの場合、ポリシーには数量とともに最大許容年齢を含める必要があります。
たとえば、大人3人と5歳の子供2人が入室できます。
グループはこの数に収まります。
- 大人3人
- 大人3人+子供4歳
- 大人2人+ 10歳の子供（大人1人あたり）
しかし、適合しない：
- 大人4人
- 大人3人+子供7歳
- 大人2人+子供2人9年
そしてそれが問題です。
それだけでなく、最初のゲストの最大数は、奇妙な形式のhstore型のフィールド（条件の記述が難しい）で表されます。マップでは、キーは最大年齢で、値は数であり、大人の場合、キーは一般に「大人」です。
また、一般的にそのような情報をどのように提示して、人々のグループが適合するかどうかを明確にする方法も不明です。
ゲストの最大数を場所の配列（昇順で並べ替え）として想像してみましょう。各場所は最大年齢（大人の場合は18）です。 そして、「大人3人+ 5人の子供2人」の部屋の容量は次のようになります。
[5, 5, 18, 18, 18]
そして、年齢の配列として人々のグループを想像すると、「大人2人+子供2人（5歳と9歳）」のようになります
[5, 9, 18, 18]
その結果、容量の列がポリシーのテーブル（room_price_policies）に追加され、この形式で保存されます。
しかし、疑問はまだ残っています。 SQLで条件（またはクエリ）を記述する方法：[5、9、18、18]を[5、5、18、18、18]に適合させますか？ グループの各ゲストが部屋の場所を探す必要があり、その場所の年齢はゲストの年齢以上でなければならず、1つの場所に1人しかいないことを考慮に入れる必要があることがわかります。 ゲストと部屋内の場所の一種の再帰的除外。
そして、ここでストアドプロシージャが役立ちます。 このタスクの手順は次のとおりです。
手順「グループは数に収まりますか？」
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.適切なホテル
「グループ全体が部屋に収まる」という正の値を持つ最も安価な部屋ポリシーの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 ( (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から）、その他のすべてのホテルを並べ替え、必要に応じてホテルの期間または星評価の価格で並べ替え、さらにページネーション（ 1ページあたり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から）。現在のページにそのようなホテルが2つあり、それらの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
結果
検索を数十倍高速化しますが、これは比較的少量のデータで行われ、時間が経つにつれて、その差はますます感じられます。
そしてもちろん、決定の間に得られたたくさんの有益な経験。