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句を使って解決しましょう。
とは言いつつ、自分もスラスラとコーディングできるようになりたいなぁ
それではまた。