1.WITH句とは？

SQLクエリ内に一時的な名前付きサブクエリを作成する方法です。WITH句を使用すると、サブクエリを一度だけ定義し、その後、同じクエリ内で何度も参照できます。

WITH句は、複雑なクエリを分割し、読みやすく、メンテナンスしやすいものにすることができます。また、WITH句を使用すると、クエリのパフォーマンスも向上する場合があります。

2.今回のモチベーション

今回のモチベーションは、
ある宿泊施設に訪れた人に対して、他の宿泊施設をおすすめする
そんな感じにしております

そのため、最終的には
HotelIDとそれ以外のhotelID（小地域か大地域かは所属している小地域による）を与えることにします。

2.使い方

下が実際に、WITH句を用いたサブクエリの書き方になってます。
以前に、CASE句を使ったクエリの書き方を紹介しているので、よかったらみて見てください。

WITH small_area_mst AS (
SELECT
small_area_name,
--20件以上あれば join_area_idをsmall_area_nameとして設定
--20件未満であれば　join_area_idをbig_area_nameとして設定
-- -1は、自ホテルを引いている

CASE WHEN COUNT(hotel_id)-1 >=20
THEN small_area_name ELSE big_area_name END AS join_area_id

FROM hotel.hotel
GROUP BY big_area_name , small_area_name
)

こんな感じで、あたかもテーブルがあるかのようにFROM句を使えるようになります。

SELECT *
FROM small_area_mst

WITH句はコロンで複数作成することが可能です。

WITH small_area_mst AS (
SELECT
small_area_name,
--20件以上あれば join_area_idをsmall_area_nameとして設定
--20件未満であれば　join_area_idをbig_area_nameとして設定
-- -1は、自ホテルを引いている

CASE WHEN COUNT(hotel_id)-1 >=20
THEN small_area_name ELSE big_area_name END AS join_area_id

FROM hotel.hotel
GROUP BY big_area_name , small_area_name
),

--どのホテルがどこのareaに含まれているかをまとめている。
recommend_hotel_mst AS(
SELECT
big_area_name AS join_area_id,
hotel_id AS rec_hotel_id
FROM hotel.hotel
--unionでテーブル同士を連結UNION
UNION ALL

SELECT
small_area_name AS join_area_id,
hotel_id AS rec_hotel_id

FROM hotel.hotel
)

UNION_ALL を使うことで、複数のテーブルを結合することができます。
詳しい説明はこちらがわかりやすいかと思います。

3.WITH句の真骨頂

WITH句は以下のように使うことができます。

--どのホテルがどこのareaに含まれているかをまとめている。

--目的、small_area_nameが20以上の場合はsmall_area_nameを推薦して
-- それ以外の場合は、big_area_nameとして付与する。

SELECT 
hotels.hotel_id,
r_hotel_mst.rec_hotel_id
--レコメンド元のhotel_tbを読み込み
FROM hotel.hotel hotels

INNER JOIN 
small_area_mst s_area_mst
ON hotels.small_area_name = s_area_mst.small_area_name

--対象エリアのレコメンド候補を結合する
INNER JOIN recommend_hotel_mst r_hotel_mst
ON s_area_mst.join_area_id = r_hotel_mst.join_area_id

--レコメンド候補から自分ホテルを除く
AND hotels.hotel_id != r_hotel_mst.rec_hotel_id

INNER JOINで元のテーブルとWITH句で作成したテーブルを順番にJOINしております。

作成したデータセットがこちらになります。

４.まとめ

複雑なクエリはWITH句を使って解決しましょう。

とは言いつつ、自分もスラスラとコーディングできるようになりたいなぁ

それではまた。