hiyoko-programingの日記

プログラミングを勉強したてのひよっ子。　　　エンジニア目指して勉強中。

検索したデータの形成

プログラミング・テキスト

データの結合

例えば、ユーザーのフルネームの一覧を取得したいとする。
フルネームは"family_name"と"first_name"から成るので、以下のようなSQLを書くことが出来る。

1
2
 
SELECT family_name, first_name
FROM users

　
すると実行結果は以下のように表示される。

https://tech-master.s3.amazonaws.com/uploads/curriculums//b9c4c88e2c01b27815698c3722af7f6c.png

名前の一覧は取得出来たが、取得したいのはフルネームなのでカラムが分かれているのは気になる。
そこで、"family_name"と"first_name"を併せて1つのカラムに表示させたい。

そんなときに使うのがCONCAT関数。

 CONCAT関数

CONCAT関数は複数の文字列を連結させることが出来る関数。

1
 
CONCAT(文字列1, 文字列2, ……)

 連結させる文字列の中にNULLがあった場合、結果は必ずNULLとなってしまう。

　
実際にSELECT句の中で使って、"family_name"と"first_name"を連結させ、表示してみる。

1
2
 
SELECT CONCAT(family_name, first_name)
FROM users

　
以下のように結果が表示されていれば正常にSQL文が実行出来ている。

https://tech-master.s3.amazonaws.com/uploads/curriculums//1ea9d3faf4502c45773711bc115d4545.png

このようにいくつかのデータを用いて1つのデータを作りたいときはCONCAT関数を使用すると、結果が扱いやすいものとなる。

検索結果のカラム名の変更

CONCAT関数により文字列を連結したが、検索結果のカラム名がSQL文そのままとなっており、少し見にくいです。

https://tech-master.s3.amazonaws.com/uploads/curriculums//2d432928b34707751168dcb4d9baaa6d.png

そこで、検索結果のカラム名を変更する。

 AS句

SELECT句でデータを取得するとき、AS句を併用すると、そのカラムに別名を付けることが出来る。

1
 
SELECT 取得するデータ AS 別名

このときの"別名"が検索結果ではカラム名として表示される。
実際にAS句を使ってどのように表示が変わるかを見てみる。

1
2
 
SELECT CONCAT(family_name, first_name) AS "名前"
FROM users

　
以下のように結果が表示されていれば正常にSQL文が実行出来ている。

https://tech-master.s3.amazonaws.com/uploads/curriculums//d95c16702269463d43922de334cdf3ff.png

　
なお、ASは省略することが出来るので、以下のように書くことも出来る。

1
2
 
SELECT CONCAT(family_name, first_name) "名前"
FROM users

実際に実行してみると、同じくカラム名が変更出来ていることが確認出来る。

重複する行の除外

shiftテーブルには「何日の、何時に、id何番のユーザーがシフトに入ったか」という情報が入っている。

https://tech-master.s3.amazonaws.com/uploads/curriculums//f850488617becaaeb3738ce24f93e458.png

またシフトは1コマ2時間制となっており、つまりレコードが1行存在することは、1コマ分シフトに入っている、ということとなる。

　
ここで、2015年7月1日にシフトに入った人の"user_id"を取得したいという場面を考えてみる。

以下のようなSQL文が書ける。

1
2
3
 
SELECT user_id
FROM shifts
WHERE date = "2015-07-01"

　
しかし、実行結果を見てみると、同じuser_idが複数回表示されてしまっているところがある。

https://tech-master.s3.amazonaws.com/uploads/curriculums//46fcae6c79bd4bb9411d83c0347bce3f.png

これは1日のうち、2コマ分シフトに入っている（レコードが2行ある）人がいるからである。
そこで、user_idの重複をなくした検索結果を表示させてみる。

 DISTINCTキーワード

DISTINCTキーワードを使用すると、指定したカラムの値が重複する行を除外してデータを取得することが出来る。

1
 
SELECT DISTINCT カラム名

　
例えばSELECT DISTINCT user_idと指定すると、以下のように"user_id"カラムが重複している行が除外される。

https://tech-master.s3.amazonaws.com/uploads/curriculums//c8ebbc1ca7ef618ebbb966987b250e07.jpeg

DISTINCTキーワードを使用してSQL文を書きなおしてみる。

1
2
3
 
SELECT DISTINCT user_id
FROM shifts
WHERE date = "2015-07-01"

SELECT句がuser_idからDISTINCT user_idとなったことで、"user_id"が重複する行を除いたSQL文となっている。

　　
以下のように結果が表示されていれば正常にSQL文が実行出来ている。

https://tech-master.s3.amazonaws.com/uploads/curriculums//63989817cd693e88287f8f63c0844375.png

レコードのグループ化

DISTINCTに少し似た、GROUP BY句について。

 GROUP BY句

GROUP BY句を使用すると、指定したカラムが同じ値を持つデータを1つのグループとしてまとめることが出来る。

1
 
GROUP BY カラム名

　
例えばGROUP BY user_idと指定すると、以下のように"user_id"カラムの値が同じレコードが、それぞれグループ化される。

https://tech-master.s3.amazonaws.com/uploads/curriculums//0e426d13704b7062c1002ff6f9ce58e6.jpeg

上の例では、GROUP BY句を使わない左側は5行のデータが表示されるが、GROUP BY句を使用した右側では2行のデータのみが表示されるようになる。

このとき、表示されていないデータもきちんとそのグループに保持されている。

　
実際にSQL文を記述して、GROUP BY句の挙動を確認してみる。

1
2
3
4
 
SELECT user_id
FROM shifts
WHERE date = "2015-07-01"
GROUP BY user_id

このSQL文では、まず"shifts"テーブルから"date"が"2015-07-01"のレコード取得している。
そして"user_id"が同じ値のレコードをグループ化し、そのグループ化された一覧の"user_id"のみを表示する、ということが行われている。

　
以下のように結果が表示されていれば正常にSQL文が実行出来ている。

https://tech-master.s3.amazonaws.com/uploads/curriculums//540f09306696458945b90258ec1f9e32.png

　
実行結果を見てみると、先ほどDISTINCTを使用したときと同じものになっていること。

https://tech-master.s3.amazonaws.com/uploads/curriculums//67301b483082fcb29d554482b026e8ef.jpeg

この結果だけを見ると、DISTINCTを使えば良いのではないか、と思われるかもしれないが、
GROUP BYを使うことには、そのグループ単位で集計した結果を取得することが出来る、という利点がある。

　
グループ化されたデータを用いて、データが何件あるか数えるということをやってみる。

レコードの数を数える

グループ化されたデータは、何件のレコードがグループ化されているかというデータを持っており、それを取得出来る関数がCOUNT関数。

 COUNT関数

COUNT関数はグループ化されたデータに対して使用することが出来る集計関数の一つ。
COUNT関数はカラムを指定して使用することで、そのカラムの値がNULLでないデータの行数を取得することが出来る。

1
 
SELECT COUNT(カラム名)

　
このとき、カラム名の指定でワイルドカードを使用し以下のように記述すると、行の値が全てNULLであるレコードも含め、その行数を取得する。

1
 
SELECT COUNT(*)

　
そしてGROUP BY句を併用している場合は、各グループが何行のレコードを持っているかというデータを取得することが出来る。

https://tech-master.s3.amazonaws.com/uploads/curriculums//4c3a20d7e20899963008e9e3fe667d28.jpeg

　
実際にSQL文を実行してCOUNT関数の挙動を確認してみる。

先ほどは"2015-07-01"に誰がシフトに入ったか、という情報を取得することが出来た。

今度は"2015-07-01"に誰が、何コマずつシフトに入ったか、という情報を取得出来るようにしてみる。

1
2
3
4
 
SELECT user_id, COUNT(*)
FROM shifts
WHERE date = "2015-07-01"
GROUP BY user_id

上記のSQL文は、先ほどGROUP BY句で実行したSQL文の、SELECT句にCOUNT(*)を追加したもの。

今回は特にNULLの行を除きたいといったことはないため、COUNTのカラム指定は*となっている。

　
以下のように結果が表示されていれば正常にSQL文が実行出来ている。

https://tech-master.s3.amazonaws.com/uploads/curriculums//9899ef0cce8653c478dcbb40bc9e13ce.png

"user_id"の隣の列に、1や2という数字が出てきた。
これがその"user_id"の人が何コマシフトに入ったかをカウントした結果である。
　
ですが、カウントしたものを表示している列の名前がCOUNT(*)となってしまっている。
これではこの列の数字が何を表しているのかが分かりにくい。

そのため、このカラムに別名として"コマ数"という名前を付けてあげる。

1
2
3
4
5
6
 
SELECT
    user_id,
    COUNT(*) "コマ数"
FROM shifts
WHERE date = "2015-07-01"
GROUP BY user_id

ついでにSELECT句を要素ごとに改行。
複数の要素を取得したい場合は、要素ごとに改行をしタブキーを押してインデントを付けてあげると、SQL文が可読性の高いものとなるので、ここも意識して書いてみると良い。

　
以下のように結果が表示されていれば正常にSQL文が実行出来ている。

https://tech-master.s3.amazonaws.com/uploads/curriculums//c7e145532865df4c3536501f8e80921c.png

これで実行結果が分かりやすくなった。

　
グループ化されたデータに使用できる集計関数は他にも、平均を求めるAVG、最大値を求めるMAX、最小値を求めるMINといったものが存在する。

複雑なデータの検索

テーブルの結合

COUNT関数を使用してSQL文を書くことで、id何番のユーザーが、何コマシフトに入ったかという情報を得られるようになった。

ですが、idが何番のユーザー、と言われてもそれが誰かは分かりにくい。

　
そのため、検索結果にuser_idではなく、そのユーザーの名前を表示してあげるように変更する。

https://tech-master.s3.amazonaws.com/uploads/curriculums//02297937ba8df01a7c91bb09c88964df.jpeg

いまは"shifts"テーブルのデータを扱って検索をしているが、このテーブルにユーザーの名前の情報はない。

ではユーザーの名前の情報がどこにあるかというと、"users"テーブルなので、どうにかしてそこから情報を持ってくる必要がある、ということが分かる。

　
そこで、"shifts"テーブルから"users"テーブルの情報を取得するために、2つのテーブルを結合させるJOINを使う。

 JOIN

JOINを使用すると、指定したそれぞれのテーブルの、カラムの値が一致するデータを結合することが出来る。

JOIN句はFROM句のあとに記述し、結合の対象となるテーブルを指定する。
そしてそれぞれのテーブルの結合するカラムをONの後に=（イコール）を用いて指定する。

1
2
 
FROM テーブル名1
JOIN テーブル名2 ON テーブル名1.カラム名1 = テーブル名2.カラム名2
実際にデータベースの"shifts"テーブルに"users"テーブルを結合させてみる。

　
すると、SQL文は以下のようになる。

1
2
 
FROM shifts
JOIN users ON shifts.user_id = users.id

このSQL文は、

「"shifts"テーブルに"users"テーブルを結合させる。
　"shifts"テーブルの各行には、その行の"user_id"が"users"テーブルの"id"と一致する行を結合させる。」

という意味になる。

　
例えば"shifts"テーブルの"user_id"が1の行には、"users"テーブルの"id"が1の行を結合させる。
https://tech-master.s3.amazonaws.com/uploads/curriculums//5224394beb7a0720d9ce7e19b868b26c.jpeg

　
また、ONの後にshiftsやusersとテーブル名を再度書くのは冗長である。
ではどうするべきかというと、各テーブルに別名を付け、その名前で扱うのが良い。

　
そのため、上記のSQL文は以下のように書きなおすことが出来る。

1
2
 
FROM shifts s
JOIN users u ON s.user_id = u.id

このSQL文ではFROMやJOINでテーブル名を指定する際、ついでにそのテーブルの別名も指定している。
このとき別名は、"shifts"テーブルならs、"users"テーブルならuのように、各テーブル名の頭文字を取ってあげるのが一般的。

　
実際にSQL文を実行し、テーブルが結合される様子を見てみる。

1
2
3
4
5
6
7
8
 
SELECT
    user_id,
    COUNT(*) "コマ数",
    u.*
FROM shifts s
JOIN users u ON s.user_id = u.id
WHERE date = "2015-07-01"
GROUP BY user_id

先ほどのSQL文に、4行目のu.*と、
6行目のJOIN users u ON s.user_id = u.idが追加されている。

　
6行目のJOIN句は上記のと同じ。

4行目のu.*というのは、"users"テーブルの全てのカラムを取得する、という意味になる。

　
実行結果を見てみると、各行の右側にその行の"user_id"と同じ"id"のユーザーの情報が結合されていることが分かる。

https://tech-master.s3.amazonaws.com/uploads/curriculums//2bef36ca1c5e3106d18f2a86678c49ef.png

　
これで"users"テーブルのデータを結合することが出来た。
あとは実行結果が"名前"と"コマ数"だけになるよう、SQL文を書き直す。

1
2
3
4
5
6
7
 
SELECT
    CONCAT(family_name, first_name) "名前",
    COUNT(*) "コマ数"
FROM shifts s
JOIN users u ON s.user_id = u.id
WHERE date = "2015-07-01"
GROUP BY user_id

先ほどからSELECT句のみを変更した。
名前を表示させるために、この章の一番上で学習したCONCAT関数を使用している。

　
以下のように結果が表示されていれば正常にSQL文が実行出来ている。

https://tech-master.s3.amazonaws.com/uploads/curriculums//6adebbd6f4fbf3e5875d626ee67127ca.png

これで"2015-07-01"に、誰が、何コマシフトに入ったか、という情報を表示することが出来た。

　
今回使用したJOINはINNER JOINと呼ばれているもの。
そしてJOINにはLEFT JOINやRIGHT JOINなど、結合法則の違った他の種類のJOINが存在する。

取得したいデータによってはそちらのJOINを使用したほうが適切という場合もある。

検索結果を用いた検索

今度は"2015-07-01"にシフトに入っていない人の一覧を取得したいとする。

　
シフトに入っていない人は、ユーザーの一覧から、シフトに入っている人を除くことで求められる。
これをSQL文で表現しようとすると、シフトに入っている人を検索し、その検索結果を用いてさらに検索をする必要がある。

そういったときにはサブクエリというものを使用する。

 サブクエリ

サブクエリとは、ある検索結果を使用して別のSQL文を実行する仕組みのこと。

　
実際に"2015-07-01"にシフトに入っていない人を取得するSQL文を作成する工程を見ていく。

　
まずは"2015-07-01"にシフトに入っている人の一覧を取得。
ここはDISTINCTキーワードを使って以下のようなSQL文が書ける。

1
2
3
 
SELECT DISTINCT user_id
FROM shifts
WHERE date = "2015-07-01"

　
このSQL文の実行結果は以下のようになる。

https://tech-master.s3.amazonaws.com/uploads/curriculums//05e4bce77980286c9543bb9d503372c0.png

この赤枠の部分が"2015-07-01"にシフトに入っている人のリスト。

　
では、こういったリストがあるとして、
"users"テーブルから、idがリストに含まれない行のみを取得するSQL文を見てみる。

SQL文はWHERE句で、NOT INを使用して以下のように記述することが出来る。

1
2
3
 
SELECT *
FROM users
WHERE id NOT IN (リスト)

　
あとはこのSQL文のリストという部分に、
先ほどの"2015-07-01"にシフトに入っている人のリストを取得するSQL文を埋め込むだけ。

1
2
3
4
5
6
7
 
SELECT *
FROM users
WHERE id NOT IN (
    SELECT DISTINCT user_id
    FROM shifts
    WHERE date = "2015-07-01"
)

　
以下のように結果が表示されていれば正常にSQL文が実行出来ている。

https://tech-master.s3.amazonaws.com/uploads/curriculums//3e95b3f316b2e32f1dd38d173eeced0e.png

実際に先ほどのリストと見比べてみても、きちんとリストにないidのユーザーのみを取得出来ていることが分かる。

　
サブクエリはWHERE句で使用されることが多く、またイメージがし易いため、今回はWHERE句で使用したが、

サブクエリはSELECT句やFROM句で使うことも出来る。