はじめに
RDBについて学んだので、復習を兼ねてポケモンのデータベースを作り、SQLで分析してみました。
RDBとは
Relational Database（リレーショナルデータベース）の略語。
関係データベースと訳され、データを複数の表として管理し、表と表の関係を定義することで、複雑なデータの関連性を扱えるようにしたデータベース管理方式。
目次
1.開発環境とテーブル構成
2.monster_tableで身長・体重の分析
3.中間テーブルとは
4.type_tableとjoinしてタイプごとに分析
5.generation_tableとjoinして世代ごとに分析
6.すべてのテーブルをくっつけて分析する
1.開発環境とテーブル構成
開発環境
- XAMPP
- phpMyAdmin
テーブル構成
「pokemon」データベース内に、以下の4テーブルを用意
- monster_table
| id | name | height | weight |
|---|---|---|---|
| 1 | フシギダネ | 0.7 | 6.9 |
| 2 | ヒトカゲ | 0.6 | 8.5 |
| … | |||
| 26 | ホゲータ | 0.4 | 9.8 |
| 27 | クワッス | 0.5 | 6.1 |
- type_table
| id | type |
|---|---|
| 1 | くさ |
| 2 | ほのお |
| 3 | みず |
| 4 | どく |
| 5 | ひこう |
- generation_table
| id | generation | game |
|---|---|---|
| 1 | 第１世代 | 赤・緑 |
| … | ||
| 9 | 第９世代 | スカーレット・バイオレット |
- join_table（中間テーブル）
| monster_id | type_id | generation_id |
|---|---|---|
| 1 | 1 | 1 |
| ... |
2.monster_tableで身長・体重の分析
まずは、テーブル1つだけで分析してみます。
monster_tableには、歴代のポケモン御三家の名前、身長、体重のデータがあります。この3つの要素でできる分析をしてみます。
御三家とは
「ポケットモンスター」シリーズにて冒険の初めに主人公が受け取るポケモン（通称 御三家）
最初に3体から選ぶ→最新作は9作目→3×9=27体のデータがあります。
私の推しポケモン、「ホゲータ」を中心として分析をします。
身長が低い順に並び替え：ORDER BY
SELECT * FROM monster_table ORDER BY height ASC;
結果 : 歴代御三家で、ホゲータは2番目に背が低い
体重が重い順 上位３件を表示 : LIMIT
SELECT * FROM monster_table ORDER BY weight DESC LIMIT 3;
結果 : 歴代御三家で、ホゲータは3番目に重い
| id | name | height | weight |
|---|---|---|---|
| 10 | ナエトル | 0.4 | 10.2 |
| 14 | ポカブ | 0.5 | 9.9 |
| 26 | ホゲータ | 0.4 | 9.8 |
名前に"ー"を含むポケモンを表示 : WHERE , LIKE
SELECT * FROM monster_table WHERE name LIkE '%ー%';
結果 : 名前に"ー"を含むポケモンは6体
（チコリータ、ツタージャ、モクロー、ニャビー、ヒバニー、ホゲータ）
3.中間テーブルとは
ここからは、複数テーブルをくっつけて（joinして）分析します。
今回は中間テーブル（今回だとjoin_table）を使います。
御三家のタイプは基本「くさ」「ほのお」「みず」の３タイプ。
タイプは原則１体につき１つなのですが、まれに２つのタイプをもつポケモンがいます。御三家でいうと「フシギダネ」が「くさ」「どく」、「モクロー」が「くさ」「ひこう」タイプです。
こういった場合に中間テーブルを使う（と思っています。）
例えば、モクローなら以下のようになります。
| monster_id | type_id | generation_id |
|---|---|---|
| 19 | 1 | 7 |
| 19 | 5 | 7 |
monster_tableのid（19）とgeneration_tableのid（7）は同じですが、type_tableのidが2つ（1:くさ と 5:ひこう）になります。
4.type_tableとjoinしてタイプごとに分析する
まずは、monster_tableとtype_tableを中間テーブルでjoinして、ポケモンのタイプ別に分析します。
ほのおタイプポケモンは全部で何体？：COUNT
SELECT COUNT (*) FROM join_table
JOIN monster_table ON join_table.monster_id = monster_table.id
JOIN type_table ON join_table.type_id = type_table.id
WHERE type = 'ほのお' ;
結果：ほのおタイプはホゲータ含め9体
5.generation_tableとjoinして世代ごとに平均値を算出する
次に、monster_tableとgeneration_tableを中間テーブルでjoinして、ポケモンの世代（シリーズ）毎に分析します。
パルデア御三家の平均体重：AVG
SELECT AVG (weight) FROM join_table
JOIN monster_table ON join_table.monster_id = monster_table.id
JOIN generation_table ON join_table.generation_id = generation_table.id
WHERE generation = '第９世代' ;
結果： 第9世代、パルデア御三家（ニャオハ、ホゲータ、クワッス）の平均体重は6.66667(kg)
第9世代、スカーレット・バイオレットの舞台がパルデア地方なのでパルデア御三家と表記しています。
6.すべてのテーブルをくっつけて分析する
最後に、すべてのテーブルをjoinして分析してみます。
ほのおタイプのポケモンを、重い順に並べる
SELECT * FROM join_table
JOIN monster_table ON join_table.monster_id = monster_table.id
JOIN type_table ON join_table.type_id = type_table.id
JOIN generation_table ON join_table.generation_id = generation_table.id
WHERE type = 'ほのお' ORDER BY weight DESC;
すべてのテーブルをくっつけて、上記のSQL文を実行すると以下のようになります。
結果：ホゲータは歴代御三家（ほのおタイプ）の中で2番目に重い
さいごに
データ量を少なくするためにポケモン御三家に絞って分析しましたが、お気に入りのポケモンのデータを追加して比較するのも面白そうです。今回は試していませんが、ASでカラム名を変えたり、他にもできることはあるかと思うので今後も勉強して試してみたいです。