こんにちは。
今回は電脳少女プログラミング2088-壊レタ君を再構築-のA：巨大コーポの最上階に取り組みました。
そのなかで、レイミから「 CTE を使用して改善してください。」とのフィードバックがありました。
実際にCTEを使用してみました。その内容を投稿させたいただきます。
問題は以下のサイト
問題
あなたは軍の機密情報へアクセスできる端末を見つけました。そこから重要なデータだけを得ようとし>ています。
log (ログ), memory (記憶), battle (戦闘), category (カテゴリ), person (人物) のテーブルが以下の図のように与えられます
person (人物) のうち重要度が 5 の人物の person.deleted_at と同じ battle (戦闘) の battle.created_at (戦闘.作成日) から memory (記憶) と battle (戦闘) と person (人物) の memory.id (記憶.ID), memory.talk (記憶.会話内容), person.name (人物.人物名), battle.created_at (戦闘.作成日) を出力してください。
考え方と解答
個人的な考えとして結合が多そうなテーブルを軸としたいなぁと。。。
（この考えが正しいとは限らない）
そこでlogテーブルを軸として考えました。
イメージとして
- logテーブルとmemoryテーブルを記憶のIDとIDで結合
- logテーブルにbattleテーブルとpersonテーブルを人物のIDとIDで結合したものを人物のIDで結合
- categoryテーブルは必要なさそうなので結合しない
この考えと条件踏まえかつ条件に合うように作成したSQLが以下の通り
SELECT memory.id,memory.talk,data.name,data.created_at
FROM log
INNER JOIN memory
ON log.memory_id = memory.id
INNER JOIN (SELECT battle.person_id as person_id, person.name as name,
battle.created_at as created_at
FROM battle
INNER JOIN person
ON battle.person_id = person.id
WHERE person.importance >= 5
AND battle.created_at = person.deleted_at)data
ON log.person_id = data.person_id
このソースでも良いと思うが、レイミのフィードバックは次のようでした。
タイトルでも書いていますが、CTE？？？？？？？？？？？？？？？？？？？
仕事でSQLを使用していますが、初めて聞いた言葉でした。
CTEとは
謎すぎるので検索してみました。
メインのSQLの問い合わせを実行するために補助的に使う、一時テーブルを（WITH句を使って）定義するものです。
…というと、派生テーブル（FROM句のサブクエリ）と何が違うの？となりますが、（非再帰のCTEはWITH句を使うか使わないか等の書式の違いを除いて、派生テーブルとほぼ同じようなものですので）実質的な違いは「再帰的に書ける」ところです。
参照先 https://qiita.com/hmatsu47/items/01211556089b19913d05
つまりサブクエリをメインとSQL文の外に書き共通化しようとのことです。英語的にも共通テーブルということのようです。何度も同じ結合したテーブルを使用した時にとても便利だと思いました！
CTE : Common Table Expressions
参照先 https://qiita.com/hmatsu47/items/01211556089b19913d05
使い方は以下の通り、WITH句を使用します。
MySQLWITH cte1 AS (SELECT a, b FROM table1), cte2 AS (SELECT c, d FROM table2) SELECT b, d FROM cte1 JOIN cte2 WHERE cte1.a = cte2.c;
CTEを使用して解答を作成してみた
CTEを使用して書いたSQLは以下の通りです。
WITH
btl AS
(SELECT battle.person_id as person_id,
person.name as name,
battle.created_at as created_at
FROM battle
INNER JOIN person
ON battle.person_id = person.id
WHERE person.importance = 5
AND battle.created_at = person.deleted_at),
memo AS
(SELECT log.person_id as person_id,
memory.id,memory.talk as talk
FROM log
INNER JOIN memory
ON log.memory_id = memory.id)
SELECT memo.id,
memo.talk,
btl.name,
btl.created_at
FROM memo
INNER JOIN btl
ON memo.person_id = btl.person_id
メインのSQLはとてもシンプルとなりました。
共通テーブルは以下の２つ
btlテーブル：battleテーブルとpersonテーブルを人物IDとIDで結合し、問題文の条件に合うようにWHERE句に記載したテーブル
memoテーブル：logテーブルとmemoryテーブルを記憶のIDとIDで結合したテーブいる
２つの共通テーブルを人物IDで結合しました。
CTEを使ってみた感想
今回は初めてCTEを使用したSQLを作成してみました。正直なところ今回に関してはあんまりメリットがないというか。CTEを使用しない方がシンプルと思われる方も多いかもしれません。
ただ、知識として蓄え使えるときに活用したいと思いました。
SQLも奥が深いですね！！
電脳少女プログラミング2088-壊レタ君を再構築-のSQLの問題について
今回、A~DランクのSQLの問題がありすべて挑戦しました。高ランクでも基礎を押さえていれば解ける問題となっております。SQLに苦手意識がある人でもゲーム感覚でできるのでオススメです。
是非、チャレンジしてスキルアップしてくださいね！