# 非エンジニアのためのSQL

written by いかずちくん

## まえがき

この文書は、プロダクトマネージャ、ゲームプランナ、データ解析職などがデータベースからデータを取得するためにSQLを用いることを想定した参考資料である。
本来、RDBMS（Relational DataBase Management System;関係データベース管理システム）でSQL言語を用いて提供されている機能は多岐にわたるが、ここではデータを取得するselect句にのみ着目することする。
これにより、非エンジニア職が自らSQLを叩いて必要なデータを取得できるようになることを期待する。

## もくじ

[TOC]

## SQLと方言

SQLの処理系はRDBMSごとに異なるものが存在し、一言に「SQL」といっても処理系によって使用できる文法や関数が微妙に異なる。
SQLの方言として、以下のようなものがある。

- MySQL
- sqlite
- Oracle SQL
- MS SQL
- Google BigQuery Legacy SQL
- Google BigQuery Standard SQL

本資料では主にMySQLに準拠し、場合によってはGBQ Standard SQLについての記述も行う。

## 演習問題について

こちらを参照のこと。
https://hackmd.io/ERNchL0oQu6UrMaWuHf0Ug

## テーブル

RDBMSでは、データは**テーブル**と呼ばれる表の形をした構造で保存されている。

以下の表は、その例である。

**player**
|id|name|dan|
|-|-|-|
|175|羽生善治|九段|
|207|久保利明|九段|
|235|渡辺明|九段|
|261|中村太地|七段|
|263|佐藤天彦|九段|
|264|豊島将之|八段|
|278|菅井竜也|七段|
|284|高見泰地|七段|

ここで着目すべきは、テーブルや、テーブルの各列（**カラム**）に名前が付いていることである。
この名前を用いてコーディングを行う。
また、テーブルの各行をレコードと呼ぶ。

## select句とfrom句

テーブル名とカラム名を用いて

select
  id, name
from
  player

とすることによって、表

|id|name|
|-|-|
|175|羽生善治|
|207|久保利明|
|235|渡辺明|
|261|中村太地|
|263|佐藤天彦|
|264|豊島将之|
|278|菅井竜也|
|284|高見泰地|

が取得できる。
このように、`select`句は取得するカラムを指定し、`from`句は取得するテーブルを指定する。
なお、`select`句に`*`を指定することによって全てのカラムの情報を取得することができる。

### select distinct句

`select`の代わりに`select distinct`を用いると、重複レコードを除いた出力が行われる。
たとえば、

select distinct
  dan
from
  player

とすれば、
|dan|
|-|
|九段|
|七段|
|八段|
が出力される。
複数カラムを指定する場合は、それぞれのカラムの内容がひとつでも異なれば出力される。

## where句

`where`句を用いることで、条件に合うレコードを選択することができる。

select
  name
from
  player
where
  dan = '七段'

とすると、得られる結果は

|name|
|-|
|中村太地|
|菅井竜也|
|高見泰地|

である。
このとき、文字列を指定する場合は`'hoge'`のようにクオートで括る必要がある。

### where句で使える比較演算子の例

|比較演算子|効果|
|-|-|
|`> a`|aより大きい|
|`>= a`|a以上|
|`< a`|aより小さい|
|`<= a`|a以下|
|`between a and b`|a以上b以下|
|`= a`|aと等しい|
|`!= a`|aと等しくない|
|`in (a, b, c, ...)`|a, b, c, ...のどれかと等しい|
|`is null`|nullである|
|`is not null`|nullでない|

### 算術演算子

SQLにおいても、多くのプログラミング言語と同様に、以下のような算術演算子を用いて計算を行うことができる。

|算術演算子|効果|
|-|-|
|a + b|aとbの和|
|a - b|aとbの差|
|a * b|aとbの積|
|a / b|aとbの商。b=0のときエラー|
|a % b|aをbで割った余り。b=0のときエラー|

### 比較演算子の結合

比較演算子は、以下の論理演算子を用いて結合することができる。

|論理演算子|効果|
|-|-|
|not a|aでない|
|a and b|aかつb|
|a or b|aまたはb|

#### 例

**player**テーブルで、棋士番号が200より大きい九段の棋士番号と名前を求めるためのクエリは、以下のようになる。

select
  id
  , name
from
  player
where
  id > 200
  and dan = '九段'

である。

## 集計関数

集計関数は、`select`句でカラムを指定する際に用い、カラムの内容の集計に用いる。

次のテーブルを考える。

**match**
|black|white|tournament|winner|date|
|-|-|-|-|-|
|羽生善治|渡辺明|竜王戦|羽生善治|2017-10-20|
|渡辺明|羽生善治|竜王戦|羽生善治|2017-10-28|
|羽生善治|渡辺明|竜王戦|渡辺明|2017-11-04|
|渡辺明|羽生善治|竜王戦|羽生善治|2017-11-23|
|羽生善治|渡辺明|竜王戦|羽生善治|2017-12-04|
|羽生善治|佐藤天彦|名人戦|羽生善治|2018-04-11|
|佐藤天彦|羽生善治|名人戦|佐藤天彦|2018-04-19|
|羽生善治|佐藤天彦|名人戦|羽生善治|2018-05-08|
|佐藤天彦|羽生善治|名人戦|佐藤天彦|2018-05-19|
|羽生善治|佐藤天彦|名人戦|佐藤天彦|2018-05-29|
|金井恒太|高見泰地|叡王戦|高見泰地|2018-04-14|
|高見泰地|金井恒太|叡王戦|高見泰地|2018-04-28|
|金井恒太|高見泰地|叡王戦|高見泰地|2018-05-12|
|高見泰地|金井恒太|叡王戦|高見泰地|2018-05-26|

このとき、羽生竜王が何局勝利しているかを、以下のクエリで調べることができる。

select
  count(*) as cnt
from
  match
where
  winner = '羽生善治'

の出力は

|cnt|
|-|
|6|

となる。
`select`句中に集計関数を用いるものと用いないものを混在させるとエラーが出るので、気をつける。

### 集計関数の例

|集計関数|効果|
|-|-|
|count(col)|nullでないデータ数を数える|
|count(distinct col)|nullでないデータを重複なく数える|
|sum(col)|合計を求める|
|max(col)|最大値を求める|
|min(col)|最小値を求める|
|avg(col)|算術平均を求める|

（`count`に`*`を指定すると、「全てのカラムがnull」ではないものを数える）

## group by句

`group by`句は、あるカラムのそれぞれの値に対して集計関数を適用させたい場合に用いる。
たとえば、棋戦ごとの対局数を求めるには、以下のようなクエリを用いる。

select
  tournament
  , count(*) as cnt
from
  match
group by
  tournament

|tournament|cnt|
|-|-|
|竜王戦|5|
|名人戦|5|
|叡王戦|4|

`group by`句で集計の対象にするカラムは、複数を選択することもできる。

select
  winner
  , tournament
  , count(*) as cnt
from
  match
group by
  winner
  , tournament

|winner|tournament|cnt|
|-|-|-|
|羽生善治|竜王戦|4|
|渡辺明|竜王戦|1|
|羽生善治|名人戦|2|
|佐藤天彦|名人戦|3|
|高見泰地|叡王戦|4|

`group by`で指定していないカラムを集計関数なしで`select`に書くとエラーが発生する。

## having句

`having`句は、集計後の値を用いて出力されるレコードを制限したいときに使用される。

たとえば、

select
  winner
  , tournament
  , count(*) as cnt
from
  match
group by
  winner
  , tournament
having
  cnt > 3

の出力は以下である。

|winner|tournament|cnt|
|-|-|-|
|羽生善治|竜王戦|4|
|高見泰地|叡王戦|4|

`having`句では、`select`句において`as`で指定した名前が使えるほか、以下のように集計関数を直接書き込むこともできる。

select
  winner
  , tournament
from
  match
group by
  winner
  , tournament
having
  count(*) > 3

|winner|tournament|
|-|-|
|羽生善治|竜王戦|
|高見泰地|叡王戦|

## order by句

`order by`句にカラムを指定することによって、出力結果をカラムの内容の昇順（文字列の場合は辞書順）に並べることができる。

select
  winner
  , tournament
  , count(*) as cnt
from
  match
group by
  winner
  , tournament
order by
  cnt

|winner|tournament|cnt|
|-|-|-|
|渡辺明|竜王戦|1|
|羽生善治|名人戦|2|
|佐藤天彦|名人戦|3|
|羽生善治|竜王戦|4|
|高見泰地|叡王戦|4|

また、カラムを指定した後に`desc`をつけることで降順に並べることができる。

select
  winner
  , tournament
  , count(*) as cnt
from
  match
group by
  winner
  , tournament
order by
  cnt desc

|winner|tournament|cnt|
|-|-|-|
|羽生善治|竜王戦|4|
|高見泰地|叡王戦|4|
|佐藤天彦|名人戦|3|
|羽生善治|名人戦|2|
|渡辺明|竜王戦|1|

## limit句

`limit`句を用いることで、出力数を制限することができる。

select distinct
  tournament
from
  match
order by
  date desc
limit
  2

|tournament|
|-|
|竜王戦|
|名人戦|

## テーブルの結合

RDBMSでは、2つ以上のテーブルを結合することによって、より多くのデータを取得することができる。
そのための代表的な方法を紹介する。

### inner join

`inner join`は、2つのテーブルに共通する要素だけを残す結合方法である。
以下のようなテーブル群が与えられたとする。

**member**
|member_id|member_name|mail|
|-|-|-|
|1|Takami|[e-mail]|
|2|Sakurauchi|[e-mail]|
|3|Matsuura|[e-mail]|
|4|Kurosawa|[e-mail]|
|5|Watanabe|[e-mail]|
|6|Tsushima|[e-mail]|
|7|Kunikida|[e-mail]|
|8|Ohara|[e-mail]|
|9|Kurosawa|[e-mail]|
|10|Shiitake|[e-mail]|

**unit**
|unit_id|unit_name|
|-|-|
|1|CYaRoN!|
|2|AZALEA|
|3|Guilty Kiss|

**unit_member_assignment**
|unit_id|member_id|
|-|-|
|1|1|
|1|5|
|1|9|
|2|3|
|2|4|
|2|7|
|3|2|
|3|6|
|3|8|

このとき、以下のクエリ

select
  *
from
  unit as u
  inner join unit_member_assignment as uma
    on u.unit_id = uma.unit_id

によって、次のような出力が得られる。

|u.unit_id|u.member_name|uma.unit_id|uma.member_id|
|-|-|-|-|
|1|CYaRoN!|1|1|
|1|CYaRoN!|1|5|
|1|CYaRoN!|1|9|
|2|AZALEA|2|3|
|2|AZALEA|2|4|
|2|AZALEA|2|7|
|3|Guilty Kiss|3|2|
|3|Guilty Kiss|3|6|
|3|Guilty Kiss|3|8|

このように、テーブルの結合は、`on`以下で指定された条件のもとで複数のテーブルを1つにまとめる操作のことである。
新しく生成されたテーブルのカラム名は`table.column`という形で表され、`from`句で`as`を用いてテーブルに名前をつけることによって`table`の部分の文字列は指定することができる。
カラム名が一意なもの（たとえば、この場合は`member_name`と`member_id`が一意）については、テーブル名の指定を省略し単に`column`とするだけで指定することができる。

#### using句

上の例では`on`を用いて結合条件を指定したが、結合する2つのカラム名が重複している場合には`using`句を用いることができる。

select
  *
from
  unit
  inner join unit_member_assignment
    using(unit_id)

|unit_id|member_name|member_id|
|-|-|-|
|1|CYaRoN!|1|
|1|CYaRoN!|5|
|1|CYaRoN!|9|
|2|AZALEA|3|
|2|AZALEA|4|
|2|AZALEA|7|
|3|Guilty Kiss|2|
|3|Guilty Kiss|6|
|3|Guilty Kiss|8|

`using`句を使う場合は`on`とは異なり、同じ名前のカラムが発生しない。
例の場合は、名前が重複したカラムが発生しなくなるため、テーブル名を省略して直感的にカラムを扱うことができる。

#### inner joinのinnerについて

`inner join`の`inner`は、次のような`join`を行ったときに効果を持つ。

select
  *
from
  member as m
  inner join unit_member_assignment as uma
    on m.id = uma.member_id  

|id|name|mail|unit_id|member_id|
|-|-|-|-|-|
|1|Takami|[e-mail]|1|1|
|2|Sakurauchi|[e-mail]|3|2|
|3|Matsuura|[e-mail]|2|3|
|4|Kurosawa|[e-mail]|2|4|
|5|Watanabe|[e-mail]|1|5|
|6|Tsushima|[e-mail]|3|6|
|7|Kunikida|[e-mail]|2|7|
|8|Ohara|[e-mail]|3|8|
|9|Kurosawa|[e-mail]|1|9|

**`member`テーブルに存在するはずの`id`が10のレコードが削除されている。**
これは、`unit_member_assignment`テーブルに`member_id`が10のレコードが存在しないためである。
`inner join`は、**`on`で指定したカラムに同じものが存在するレコード以外は削除される**、情報損失のある結合である。

### left join, right join, full join

以上のように、`inner join`を用いると、情報が損失してしまい意図しない状況が発生する場合がある。
たとえば、新たに用意したフォロー関係を表すテーブルを用いて、「フォロワーの数を一覧にして取得しよう」と思って以下のクエリを書いたとする。

**followings**
|following_id|followed_id|
|-|-|
|1|2|
|1|3|
|1|4|
|2|1|
|2|3|
|2|5|
|3|1|
|3|2|

select
  m.member_id
  , count(distinct f.following_id) as follower_num
from
  member as m
  inner join following as f
    on m.member_id = f.followed_id
where
  m.member_id in (1, 2, 5, 6)

|member_id|follower_num|
|-|-|
|1|2|
|2|2|
|5|1|

このとき、フォロワーが存在しないキャラクターは`following`テーブルの`followed_id`カラムに`member_id`が存在しないため、出力されるテーブルから抹消されてしまう。

これを回避するために、`left join`、`right join`、`full join`が必要になる。
`inner join`の2つめの例のクエリを、`inner join`から`left join`に変更してみる。

select
  *
from
  member as m
  left join unit_member_assignment as uma
    on m.id = uma.member_id  

|id|name|mail|unit_id|member_id|
|-|-|-|-|-|
|1|Takami|[e-mail]|1|1|
|2|Sakurauchi|[e-mail]|3|2|
|3|Matsuura|[e-mail]|2|3|
|4|Kurosawa|[e-mail]|2|4|
|5|Watanabe|[e-mail]|1|5|
|6|Tsushima|[e-mail]|3|6|
|7|Kunikida|[e-mail]|2|7|
|8|Ohara|[e-mail]|3|8|
|9|Kurosawa|[e-mail]|1|9|
|10|Shiitake|[e-mail]|null|null|

出力は上の表のようになり、`inner join`では削除されていたShiitakeの情報が残る。
このとき、`unit_id`と`member_id`は対応するものが存在しないので、`null`が挿入される。

このように、`left join`は左側に（=先に）指定したテーブルの要素を全て残し、都合が悪い部分を`null`で埋めるような動作が行われる。
同様に、`right join`は右側に（=後に）指定したテーブルの要素を全て残し、`full join`は両方のテーブルの要素を全て残し都合の悪い部分を`null`で埋める動作が行われる。

これをうまく使うことで、フォロワー数の例でも、

select
  m.member_id
  , count(distinct f.following_id) as follower_num
from
  member as m
  left join following as f
    on m.member_id = f.followed_id
where
  m.member_id in (1, 2, 5, 6)

として
|member_id|follower_num|
|-|-|
|1|2|
|2|2|
|5|1|
|6|0|

を出力させることができる。

### union (all)

`union (all)`句は、テーブルをそのまま縦に結合する。
たとえば、次のようなテーブルを考える。

**unit2**
|id|name|
|-|-|
|4|Printemps|
|5|BiBi|
|6|lily white|

このとき、

select
  *
from
  unit
  union all
    unit2

の出力は

|id|name|
|-|-|
|1|CYaRoN!|
|2|AZALEA|
|3|Guilty Kiss|
|4|Printemps|
|5|BiBi|
|6|lily white|

となる。
このとき、`union`句は重複したレコードは追加せず、`union all`は重複したレコードを追加する。

## サブクエリ

以下のように、`from`句の引数にクエリを書くことによって、あるクエリの結果のテーブルを`from句`の入力とすることができる。
この際、作成した中間テーブルには`as`句を用いて名前をつけなければならない。
主に、集計関数で集計した結果をもう一度集計する必要があるときに必要になる。

-- あまりよくない例だが…
select
  a.name
from
  (
  select
    u.id
    , u.name
    , count(distinct uma.member_id) as cnt
  from
    unit as u
    inner join unit_member_assignment as uma
      on u.id = uma.unit_id
  ) as a
where
  cnt > 2

|name|
|-|
|CYaRoN!|
|AZALEA|
|Guilty Kiss|

### with句《Google BigQuery Standard SQL》

Google BigQuery Standard SQLには、サブクエリを簡単に書くためのサポートとして`with`句が存在する。
これは、サブクエリの出力をある変数に保存するもので、以下のように用いる。

with a as (
  select
    u.id
    , u.name
    , count(distinct uma.member_id) as cnt
  from
    unit as u
    inner join unit_member_assignment as uma
      on u.id = uma.unit_id
)

select
  a.name
from
  a
where
  cnt > 2

## 付録

### データ型

SQLにも、多くのプログラミング言語と同様に、データ型が存在する。
データ型は、各カラムの内容がどのようなデータであるかを表すものである。
代表的なものを紹介する。

|MySQL|GBQ Standard SQL|データの特徴|例|
|-|-|-|-|
|signed|int64|$-2^{63} \le n \le 2^{63}-1$たる整数$n$|1|
|double|float64|浮動小数点数（大まかな精度での実数の表現）|1.0|
|timestamp|timestamp|時刻|2017-01-01 00:00:00.000 UTC|
|date|date|日付|2017-01-01|
|varcharなど|string|文字列|'123'|

#### データ型の変換

データ型の変換方法は、SQLの方言によって大きく異なる。
おもな方法として、以下のようなものがある。

##### cast関数を用いる

-- MySQL
select
  cast('123' as signed)
-- -> 123

##### 専用の関数を用いる

-- Standard SQL
select
  date('2018-01-02 23:02:01', '+09')
-- -> 2018-01-03