LoginSignup
15
15

More than 5 years have passed since last update.

@g_ryotaro

SQL文(読み出し)のまとめ、データと戯れる

Last updated at Posted at 2015-05-23

SQLの一問一答方式をやります。もちろんまとめとしても使える...かな?

(記述ミスを修正)sounisi5011さん編集リクエストありがとうございました!!

背景

新卒研修でデータベースについて学びました。学生時代に講義でデータベースを聴講したことがあったのですが、勉強しようと思った時にモチベーションが上がらず結局ほとんどさわらずに現在に至ったのです...
モチベーションが上がらなかった理由は2つ

  • 用途がわからなかった
  • データベースのデータがつまらない(田中さん、鈴木さん、A商事...)

そうなんです。僕は別に田中さんの給料なんて知りたくありません。もちろん鈴木さんがどの部署に配属しているかも興味がありません。A商事がいくら売上があるかも興味がないのです。(田中さん、鈴木さんスイマセン)
しかし、研修を通じてSQLは中々面白い!!ということがわかったので、練習するなら一問一答しながら気になるデータを分析してみようと思いました。
ジャンプキャラでやろうと思ったのですが、クロールしづらかったので、ポケモンですw。
のんびりやってたらかなり長くなってしまった...。
(今回使用したデータは一番下に置いているので、興味があればつかってみてください)

ゴール

この投稿を読み終わった時、読者が上司から〜なデータ出しといてと言われた時にSQLを使ってパッとできるようになる。
学生がデータベースの講義で〜なSQLを書きなさいという問いに対して余裕の解答ができる。
ことがゴールです。さっそくやっていきます。

やったこと

ポケモンのデータから条件をいじって色々しらべてみました。

ORDER BYとか
LIKE、GROUP BY、IN、LIMIT
INNER JOIN等を使いました。
上手く説明はできないです。

入っているデータはどんなの?

ポケモン図鑑とポケモンDBからクロールしてきたデータを元にER図を作成し、テーブルとデータを用意しました。
結果ER図は以下のようになります。

Database (1).png

説明するとテーブルは4つ、モンスター、ソフト、タイプ、関連実体(モンスタータイプ)です。
関連実体を作成する理由は、多対多をさけるためです。
モンスターが属性を2つ以上持つ場合があるのでモンスターとタイプのテーブルは元々多対多、
RDBSではいわゆる多対多を避けるために間に、仮のテーブルを作ります。それが関連実体
あと基本的に多となる方に相手方のidを入れるようにします。

じゃあ実際にそれぞれのテーブルにどんなデータが入っているか一部見てみます。

monster

id name software_id weight(kg) height(m)
1 フシギダネ 1 6.900 0.700

software

id name release_date
1 レッド・グリーン 1996-02-27

type

id name
5 草
8 どく

monster_type

id monster_id type_id
1 1 5
1 1 8

この場合はフシギダネは草・毒タイプをもつということです。

では早速やってみようと思います

SQLとSQLを実行した結果を並べます。

モンスターの身長と体重のデータを出す(列の抽出)

まずは単純なSELECT文の使い方です。
1つのテーブル内のデータをそのまま抜き出せる場合は以下のようになります。

sample.sql
SELECT
    name, height, weight
FROM
    monster;

フシギダネ 0.700 6.900
フシギソウ 1.000 13.000
フシギバナ 2.000 100.000
...

SELECTの下に書いた列名が結果として返ってきます。
これでモンスターテーブルから名前、身長、体重の列が返ってきます。
しかし、このままだとコイツできないやつだな...と思われるかもしれません。
身長を私達の馴染みやすい単位(cm)に変換しましょう!

sample.sql
SELECT
    name, height*100, weight
FROM
    monster;

フシギダネ 70.000 6.900
フシギソウ 100.000 13.000
フシギバナ 200.000 100.000
...

SQLでは算術式が使えます。
ていうか単位も書きましょう!

sample.sql
SELECT
    name, CONCAT(height*100, " (cm)"), CONCAT(weight, " (kg)")
FROM
    monster;

フシギダネ 70.000 (cm) 6.900 (kg)
フシギソウ 100.000 (cm) 13.000 (kg)
フシギバナ 200.000 (cm) 100.000 (kg)
...

様々な便利な関数があります。あと単位をつけるのは今回だけです笑

ちなみにすべての列を抽出する場合は

sample.sql
SELECT
    *
FROM
    monster;

120kgより重いモンスターを抽出する(行を抽出)

条件が絡んできます
ちなみに120kgは僕の全盛期の体重です♪

sample.sql
SELECT
    name, weight
FROM
    monster
WHERE
    weight > 120;

ウインディ 155.000
カイリキー 130.000
...

論理演算子のAND、OR、NOTを使えば、さらに複雑な条件で検索できます。
今はidが小さい順に並んでいるので、体重が重い順でソートしてみます(ORDER BY)

sample.sql
SELECT
    name, weight
FROM
    monster
WHERE
    weight > 120
ORDER BY
    weight
DESC;

グラードン 950.000
ギラティナ 750.000
ディアルガ 683.000
...

なるほどグラードンが一番重く950kgということがわかりました。
ホンダのN BOXの車重と同じ重さですね!!

DESCはdescending(降順)
ASCはascending(昇順)です
しかし、昇順とか降順といわれてもパッとわからない。
だからAはアルファベットの最初だから小さい方から並べるんだな的なノリで覚えることにします。

100kg〜30kgの範囲にいるモンスターの抽出を行う場合
条件式を組み合わせずに

sample.sql
SELECT
    name, weight
FROM
    monster
WHERE
    weight
BETWEEN
    30 AND 100;

バタフリー 32.000
ピジョン 30.000
ピジョット 39.500
...

BETWEEN a AND bで行うことができます。
バタフリーって32kgもあったんか
昆虫で30kg超えとか恐怖でしかないですね(^^♪

あれってなんだっけ..ゴロ.が入るのはわかってるんだけど

最近ボクも増えたもの忘れに対応する、パターン検索です。
名前にゴロが入っているモンスターを抜き出します。

sample.sql
SELECT
    name
FROM
    monster
WHERE
    name
LIKE
    '%ゴロ%';

ゴローン
ゴローニャ
ミズゴロウ
ダンゴロ
ゴロンダ

そうそうゴローニャ
っていう感じに使えるLIKE。%は0字以上の単語が入ることを示します。1字以上は_を使います。
NOT LIKEとかを使えば、除外キーワードとしても使えます

モンスター名とソフト名を表示する

モンスター名とソフト名は別のテーブルに存在するので、結合という作業が必要です。

sample.sql
SELECT
    m.name, s.name
FROM
    monster m
INNER JOIN
    software s
ON
    m.software_id = s.id;

フシギダネ レッド・グリーン
...
チコリータ ゴールド・シルバー
...

mやsはmonster、softwareを省略したものです。nameという属性はモンスターテーブルとソフトテーブルどちらにも存在しますが、前にテーブル名をつけることで区別します。
ON以下はmのソフトウェアidとsのidは同じ意味だよと教えています。それによって結合できるんですね〜。

結合には2種類あり、内部結合と外部結合があります。今回のデータの例だと結合の手法によって差が出ないので、結合には内部結合を使います。

ソフト毎にモンスターの重量の平均値と身長の平均値を表示して

キーワードはソフト毎、平均値です。

sample.sql
SELECT
    s.name, AVG(m.weight), AVG(s.height)
FROM
    monster m
INNER JOIN
    software s
ON
    m.software_id = s.id
GROUP BY
    m.software_id;

レッド・グリーン 45.9 1.19
ゴールド・シルバー 49.1 1.1
ルビー・サファイア 67.0 1.2
ダイヤモンド・パール 76.8 1.1
ブラック・ホワイト 52.4 1.0
X・Y 50.5 1.0

ソフト毎という条件からGROUP BY句を使います。
AVGは関数ですが、CONCATとは少し毛色が異なり引数に集合をとるので扱いには気をつけなければいけません。最後に関数をまとめます。

それぞれのソフトで一番身長が高いモンスターは何?

キーワードは
それぞれのソフト、一番身長が高い
そして最後にモンスターの名前を聞いているということです。

sample.sql
SELECT
    m.name, s.name, m.height
FROM
    monster m
INNER JOIN
    software s
ON
    m.software_id = s.id
WHERE
    m.height
IN
(
SELECT
    MAX(m.height)
FROM
    monster m
INNER JOIN
    software s
ON
    m.software_id = s.id
GROUP BY
    m.software_id
);

イワーク レッド・グリーン 8.800
ハガネール ゴールド・シルバー 9.200
ホエルオー ルビー・サファイア 14.500
ディアルガ ダイヤモンド・パール 5.400
ジャローダ ブラック・ホワイト 3.300
イベルタル X・Y 5.800

SELECT文2つあるやん!!
となりそうですが焦らないで聞いて下さい
IN句は列を与えるとその条件と等しくなるものを返してくれます。列を返すSELECT文も中に入れることが出来ます。
別々に考えると

  • 各ソフトで最大の身長は?
  • その身長をもつモンスターとソフトは?

という二つの問いに分解したものです。

ちなみに以下のSQLは解答として 間違っています

sample.sql
SELECT
    m.name, s.name, MAX(m.height)
FROM
    monster m
INNER JOIN
    software s
ON
    m.software_id = s.id
GROUP BY
    m.software_id;

フシギダネ レッド・グリーン 8.800
チコリータ ゴールド・シルバー 9.200
キモリ ルビー・サファイア 14.500
ナエトル ダイヤモンド・パール 5.400
ビクティニ ブラック・ホワイト 3.300
ハリマロン X・Y 5.800

データは返ってくるのですが、モンスターの名前が変です
各ソフトで名前がはじめに来るモンスターが返ってきてしまいます。
エラーにならないから余計間違えやすいですね

タイプ毎の平均体重は?

まずはモンスターのタイプを抽出します。

sample.sql
SELECT
    m.name, t.name
FROM
    monster m
INNER JOIN
    monster_type mt
ON
    m.id = mt.monster_id
INNER JOIN
    type t
ON
    t.id = mt.type_id;

タイプとモンスターの間には関連実体が入るので、それを結合した後にタイプを結合します。

フシギダネ くさ
フシギダネ どく
...

それぞれのタイプで平均体重を算出します。GROUP BYです。

sample.sql
SELECT
    t.name, AVG(m.weight)
FROM
    monster m
INNER JOIN
    monster_type mt
ON
    m.id = mt.monster_id
INNER JOIN
    type t
ON
    t.id = mt.type_id
GROUP BY
    t.id;
ノーマル 42.6422680
ほのお 60.3072727
水 52.6940171
でんき 41.7500000
くさ 35.4011905
こおり 111.2333333
かくとう 65.5454545
どく 30.5288136
じめん 104.7050000
ひこう 43.1377778
エスパー 50.0493151
むし 29.3257576
いわ 103.6296296
ゴースト 58.8176471
ドラゴン 144.0526316
あく 49.3818182
はがね 133.0365854
フェアリー 19.7848485

やっぱりドラゴンタイプの平均が高いんですね!
想像通りです。

モンスターの中でBMIが高いものを上から5匹

単なる興味です。
BMIはBody Mass Indexの略で体重(kg)/[身長(m)*身長(m)]で算出されます。
LIMITを使えば上から何番目までを指定することができます。

sample.sql
SELECT
    m.name, m.weight, m.height, m.weight/(m.height*m.height)
FROM
    monster m
ORDER BY
    m.weight/(m.height*m.height) DESC
LIMIT
    5;

ココドラ 60.000 0.400 375.0000000
アイアント 33.000 0.300 366.6666667
パールル 52.500 0.400 328.1250000
コータス 80.400 0.500 321.6000000
サボネア 51.300 0.400 320.6250000

一番右がBMIの値なのですが、375(kg/(m*m))ってすごいですねw
人だと重度の肥満はBMI40以上なのですが、9倍以上ですね!

まとめ

SQL文を使って様々な状態のテーブルからデータの読み出しを行いました。
少し複雑な条件での読み出しも行いました。

データの抽出を行う際に必要なのはテーブル同士の関連を理解すること、何を条件にするかをハッキリさせることが大切だとわかりました。
あと、想定していたデータを取り出せているかも必ず確認することが重要です。
エラーにならないけどデータを返す場合があるので...

データベースのデータ

git clone https://github.com/Islands5/dummydata/blob/master/test.sql

でテーブルの作成とINSERTを行うことが出来ます。

環境

  • vagrant(1.6.5)
  • virtualbox(4.3.26)
  • mysql(5.6.24)

不等号

符等号 意味
= 左辺が右辺と等しい
<> Notイコール
< 左辺が右辺より小さい
<= 左辺が右辺以下
> 左辺が右辺より大きい
>= 左辺が右辺以上

関数の紹介

単一行関数
CONCAT 文字列結合
NOW() 現在日付
LAST_DAY 月末取得
DATE_FORMAT 日付を指定したフォーマットで整形

グループ関数
MIN 最小値を取得
MAX 最大値を取得
SUM 合計値を取得
AVG 平均値を取得
COUNT 件数を取得
COUNT(DISTINCT 列名) 重複をのぞく件数

これでも一部です。
エクセル並に関数あります(http://db.yulib.com/)

15
15
0

Register as a new user and use Qiita more conveniently

  1. You get articles that match your needs
  2. You can efficiently read back useful information
  3. You can use dark theme
What you can do with signing up
Sign upLogin
15
15