はじめに
今回は、こちらのプログラミングゲームの解答コードを投稿しよう！の公式イベントの記事になります。
具体的には、paizaの新作プログラミングゲーム「電脳少女プログラミング2088 ─壊レタ君を再構築─」の「巨大コーポの最上階」というSQLの問題の解答例とその解説を紹介していきたいと思います。
こちらはpaizaのランクA相当とのことです。
問題 (2025年1月25日時点)
あなたは軍の機密情報へアクセスできる端末を見つけました。
そこから重要なデータだけを得ようとしています。
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 (戦闘.作成日) を
出力してください。
解答例
私は以下のSQLを作成し、提出しました。
これでOKでした！
-- Let's チャレンジ！！
SELECT
memory.id
, memory.talk
, person.name
, battle.created_at
FROM person
INNER JOIN battle
ON person.id = battle.person_id
AND person.deleted_at = battle.created_at
INNER JOIN log
ON person.id = log.person_id
INNER JOIN memory
ON log.memory_id = memory.id
WHERE person.importance = 5;
解説
ここから私がどのように考えたか解説していきます。
1.出力対象のテーブルを確認する
問題文の最後に
memory (記憶) と battle (戦闘) と person (人物) の (中略)を出力してください。
とあります。
つまり、memory/battle/personテーブルを利用することが伺えます。
RDBでは、テーブル間を関連づける設定をする列(外部キー)があります。
今回は、問題にあるテーブルの図から、
personテーブルのidとbattleテーブルのperson_idが関連づけられている
ということがすぐにわかるかと思います。
しかし、memoryテーブルはpersonにもbattleにも外部キーの設定がありません。
さあ、困った...
...
.....
と、焦る必要はありません！
よく図を確認すると、
person(もしくはbattle)とmemoryの間にlogというテーブルがあり、
logを経由すると、personとmemoryが関連づけられていることがわかります。
つまり、
今回は、memory/battle/personテーブルに、関連付けのためにlogテーブルを加えた4テーブルを参照すれば良いことがわかります。
2.personテーブルのSELECT文の作成
さて、もう一度問題文に戻ってみると、
person (人物) のうち重要度が 5 の人物(略)
person.deleted_at と同じ(略)
とpersonテーブルについて触れた記述が多いので、**とりあえずpersonテーブルのSELECT文を書いておきましょう。
SELECT *
FROM person
;
これは流石にSQLを触ったことがあれば誰でも書けるかと思います。
3.personテーブルに関する条件を加えてみる
次に条件を見ていきます。
2.personテーブルのSELECT文の作成でも触れましたが、問題文の中に
person (人物) のうち重要度が 5 の人物
とあります。
また、personテーブルにはimportanceという「重要度」を指すint型のカラムが存在することが、テーブルの図からわかります。
これを先ほどのSQLに反映していきます。
SELECT *
FROM person
+ WHERE person.importance = 5
;
4.必要なテーブルのJOIN
問題文にはさらにbattleテーブルに関する条件が記載されていますが、一旦1.出力対象のテーブルを確認するで判明した必要なテーブルをJOINしていきます。
今回は、Nullの値に関する記述等がないので、基本的にNullの情報を除去できるINNER JOINを利用して良いかと思います。
再掲になりますが、以下の線で示したように
- [
personテーブルのid]と[battleテーブルのperson_id] - [
person(もしくはbattle)テーブルのid]と[logテーブルのperson_id] - [
personテーブルのid]と[logテーブルのperson_id] - [
logテーブルのmemory_id]と[memoryテーブルのid]
が、外部キーの組み合わせとして考えられます。
一気にお見せすると、おおっ
っと圧倒される方もいるかもしれないので、まずは、personテーブルとbattleテーブルを結合してみましょう。
SELECT *
FROM person
+ INNER JOIN battle
+ ON person.id = battle.person_id
WHERE person.importance = 5
;
このようになります。
-
INNER JOIN+JOINしたいテーブル名 -
ON句にJOINする条件
以上でテーブルを結合することが可能です。
さらに他のテーブルを結合すると以下の通りです。
SELECT *
FROM person
INNER JOIN battle
ON person.id = battle.person_id
+ INNER JOIN log
+ ON person.id = log.person_id -- person.idはbattle.person_idでも可
+ INNER JOIN memory
+ ON log.memory_id = memory.id
WHERE person.importance = 5
;
5.battleテーブルの条件の追加
問題文にはさらにbattleテーブルに関する条件が記載されています。
person.deleted_at と同じ battle (戦闘) の battle.created_at (戦闘.作成日) から
person.deleted_atと同じbattle.created_atということなので、これらをイコールで繋いで条件として追加します。
SELECT *
FROM person
INNER JOIN battle
ON person.id = battle.person_id
+ AND person.deleted_at = battle.created_at
INNER JOIN log
ON person.id = log.person_id -- person.idはbattle.person_idでも可
INNER JOIN memory
ON log.memory_id = memory.id
WHERE person.importance = 5
+ -- AND person.deleted_at = battle.created_at (こちらでもOK)
;
6.SELECTで出力したい項目だけにする
このままでは、結合したテーブルのすべてのカラムが出力されてしまいます。
一方で、問題文には、
memory (記憶) と battle (戦闘) と person (人物) の memory.id (記憶.ID), memory.talk (記憶.会話内容), person.name (人物.人物名), battle.created_at (戦闘.作成日) を出力してください。
とあります。これに沿って、
- memory.id
- memory.talk
- person.name
- battle.created_at
を*と置き換えます。
- SELECT *
+ SELECT memory.id, memory.talk, person.name, battle.created_at
FROM person
INNER JOIN battle
ON person.id = battle.person_id
AND person.deleted_at = battle.created_at
INNER JOIN log
ON person.id = log.person_id
INNER JOIN memory
ON log.memory_id = memory.id
WHERE person.importance = 5
;
完成です！！！
終わりに
SQL初心者の方にとっては、複数のテーブルをJOINして条件を追加していく流れは少し複雑に感じられるかもしれません。
しかし、問題を分解しながら進めることで確実に理解が深まります。
また、外部キーの関係や問題文の条件を正確に読み取ることが、正しいクエリを作成するポイントだと実感いただけたのではないでしょうか。
この問題は、SQLの基礎から応用まで幅広く学べる内容が詰まっていますので、ぜひこの記事を参考に他の問題にも挑戦してみてください！
プログラミングスキルの向上に役立つはずです。
他の問題にも取り組んで、そちらも記事にしていこうと思うので、興味があればぜひそちらもみてもらえたらと思います！