# 非エンジニアのためのSQL
written by いかずち ＠Ikazuchis_diary
## まえがき
この文書は、プロダクトマネージャ、ゲームプランナ、データ解析職などがデータベースからデータを取得するために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/@Ikazuchi/r1RusloDB
## テーブル
RDBMSでは、データは**テーブル**と呼ばれる表の形をした構造で保存されている。
以下の表は、その例である。
**player**
|id|name|dan|
|-|-|-|
|175|羽生善治|九段|
|207|久保利明|九段|
|235|渡辺明|九段|
|261|中村太地|七段|
|263|佐藤天彦|九段|
|264|豊島将之|八段|
|278|菅井竜也|七段|
|284|高見泰地|七段|
ここで着目すべきは、テーブルや、テーブルの各列（**カラム**）に名前が付いていることである。
この名前を用いてコーディングを行う。
また、テーブルの各行をレコードと呼ぶ。
## select句とfrom句
テーブル名とカラム名を用いて
```sql=
select
id, name
from
player
```
とすることによって、表
|id|name|
|-|-|
|175|羽生善治|
|207|久保利明|
|235|渡辺明|
|261|中村太地|
|263|佐藤天彦|
|264|豊島将之|
|278|菅井竜也|
|284|高見泰地|
が取得できる。
このように、`select`句は取得するカラムを指定し、`from`句は取得するテーブルを指定する。
なお、`select`句に`*`を指定することによって全てのカラムの情報を取得することができる。
### select distinct句
`select`の代わりに`select distinct`を用いると、重複レコードを除いた出力が行われる。
たとえば、
```sql
select
dan
from
player
```
とすると、
|dan|
|-|
|九段|
|九段|
|九段|
|七段|
|九段|
|八段|
|七段|
|七段|
が出力されるのに対し、
```sql
select distinct
dan
from
player
```
とすれば、
|dan|
|-|
|九段|
|七段|
|八段|
が出力される。
複数カラムを指定する場合は、それぞれのカラムの内容がひとつでも異なれば出力される。
## where句
`where`句を用いることで、条件に合うレコードを選択することができる。
```sql=
select
name
from
player
where
dan = '七段'
```
とすると、得られる結果は
|name|
|-|
|中村太地|
|菅井竜也|
|高見泰地|
である。
このとき、文字列を指定する場合は`'hoge'`のようにクオートで括る必要がある。
### where句で使える比較演算子の例
|比較演算子|効果|例|
|-|-|-|
|`> a`|aより大きい|3 > 1|
|`>= a`|a以上|2 >= 2|
|`< a`|aより小さい|1 < 5|
|`<= a`|a以下|3 <= 4|
|`between a and b`|a以上b以下|5 between 1 and 9|
|`= a`|aと等しい|5 = 5|
|`!= a`|aと等しくない|3 != 5|
|`in (a, b, c, ...)`|a, b, c, ...のどれかと等しい|5 in (1, 2, 3, 4, 5)|
|`is null`|nullである|null is null|
|`is not null`|nullでない|5 is not null|
|`like a`|aのような文字列である|"abc" like "%c"|
#### nullとは
nullは、データの欠損を意味する。
テーブル中にnullが格納されている場合もあれば、後述するテーブルの結合によって生まれる場合もある。
#### like 演算子の使い方
like演算子は、文字列を曖昧に比較するときに使う。
比較対象の文字列では、`%`（任意長の任意の文字列）や `_` （任意の1文字）を使って曖昧さを表現する。
たとえば、 `%abc_` と書くと、 `aabcd` や `abgdefabcz` などがヒットする。
### 算術演算子
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のときエラー|
#### 例
```sql
select
5 * 10 as calc
```
|calc|
|-|
|2|
### 比較演算子の結合
比較演算子は、以下の論理演算子を用いて結合することができる。
|論理演算子|効果|
|-|-|
|not a|aでない|
|a and b|aかつb|
|a or b|aまたはb|
#### 例
**player**テーブルで、棋士番号が200より大きい九段の棋士番号と名前を求めるためのクエリは、以下のようになる。
```sql=
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|
このとき、羽生九段が何局勝利しているかを、以下のクエリで調べることができる。
```sql=
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」ではないものを数える）
#### コラム: 条件付き数え上げ
実務を行っていると、特定の条件をつけた上でcountを行いたいことがよくある。
たとえば、七段の人数を数えたい場合は、いっぱんに
```sql=
select
count(*) as cnt
from
player
where
dan = '七段'
```
とする。
しかし、この場合では、 `where` 句で大きくデータを絞り込むことになってしまうため、ひとつのクエリで複数の条件付き数え上げを行いたい場合に困ることになる。その場合は、 `count(null or 条件式)` を用いるとよい。
```sql=
select
count(null or dan = '七段') as seven_dan_count
count(null or dan = '八段') as eight_dan_count
count(null or dan = '九段') as nine_dan_count
from
player
```
これは、 `null or false` は `null` と評価され、 `null or true` は `true` と評価されるのを利用したものである。
## group by句
`group by`句は、あるカラムのそれぞれの値に対して集計関数を適用させたい場合に用いる。
たとえば、棋戦ごとの対局数を求めるには、以下のようなクエリを用いる。
```sql=
select
tournament
, count(*) as cnt
from
match
group by
tournament
```
|tournament|cnt|
|-|-|
|竜王戦|5|
|名人戦|5|
|叡王戦|4|
`group by`句で集計の対象にするカラムは、複数を選択することもできる。
```sql=
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`句は、集計後の値を用いて出力されるレコードを制限したいときに使用される。
たとえば、
```sql=
select
winner
, tournament
, count(*) as cnt
from
match
group by
winner
, tournament
having
cnt > 3
```
の出力は以下である。
|winner|tournament|cnt|
|-|-|-|
|羽生善治|竜王戦|4|
|高見泰地|叡王戦|4|
`having`句では、`select`句において`as`で指定した名前が使えるほか、以下のように集計関数を直接書き込むこともできる。
```sql=
select
winner
, tournament
from
match
group by
winner
, tournament
having
count(*) > 3
```
|winner|tournament|
|-|-|
|羽生善治|竜王戦|
|高見泰地|叡王戦|
## order by句
`order by`句にカラムを指定することによって、出力結果をカラムの内容の昇順（文字列の場合は辞書順）に並べることができる。
```sql=
select
winner
, tournament
, count(*) as cnt
from
match
group by
winner
, tournament
order by
cnt
```
|winner|tournament|cnt|
|-|-|-|
|渡辺明|竜王戦|1|
|羽生善治|名人戦|2|
|佐藤天彦|名人戦|3|
|羽生善治|竜王戦|4|
|高見泰地|叡王戦|4|
また、カラムを指定した後に`desc`をつけることで降順に並べることができる。
```sql=
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`句を用いることで、出力数を制限することができる。
```sql=
select distinct
tournament
from
match
order by
date desc
limit
2
```
|tournament|
|-|
|竜王戦|
|名人戦|
## テーブルの結合
RDBMSでは、2つ以上のテーブルを結合することによって、より多くのデータを取得することができる。
そのための代表的な方法を紹介する。
### inner join
`inner join`は、2つのテーブルに共通する要素だけを残す結合方法である。
以下のようなテーブル群が与えられたとする。
**player**
|id|name|dan|
|-|-|-|
|175|羽生善治|九段|
|207|久保利明|九段|
|222|木村一基|九段|
|235|渡辺明|九段|
|261|中村太地|七段|
|263|佐藤天彦|九段|
|264|豊島将之|八段|
|276|永瀬拓矢|八段|
|278|菅井竜也|七段|
|284|高見泰地|七段|
|307|藤井聡太|七段|
**title**
|id|title|
|-|-|
|1|竜王|
|2|名人|
|3|叡王|
|4|王位|
|5|王座|
|6|棋王|
|7|王将|
|8|棋聖|
**player_title**
|title_id|player_id|
|-|-|
|1|264|
|2|264|
|3|276|
|4|222|
|5|276|
|6|235|
|7|235|
|8|307|
このとき、以下のクエリ
```sql=
select
name
, title_id
from
player as p
inner join player_title as pt
on p.id = pt.player_id
```
によって、次のような出力が得られる。
|name|title_id|
|-|-|
|木村一基|4|
|渡辺明|6|
|渡辺明|7|
|豊島将之|1|
|豊島将之|2|
|永瀬拓矢|3|
|永瀬拓矢|5|
|藤井聡太|8|
このように、テーブルの結合は、`on`以下で指定された条件のもとで複数のテーブルを1つにまとめる操作のことである。
新しく生成されたテーブルのカラム名は`table.column`という形で表され、`from`句で`as`を用いてテーブルに名前をつけることによって`table`の部分の文字列は指定することができる。
カラム名が一意なもの（たとえば、この場合は`id`以外のすべてのカラム名が一意）については、テーブル名の指定を省略し単に`column`とするだけで指定することができる。
また、inner joinは情報損失のある結合である。`player`テーブルにはすべての棋士の情報が記録されているが、`player_title`にはタイトルを持つ棋士の情報しか記録されていない。
この2つのテーブルをinner joinすると、タイトルを持たない棋士の情報は消失してしまう。
#### using句
上の例では`on`を用いて結合条件を指定したが、結合する2つのカラム名が重複している場合には`using`句を用いることができる。
#### 命名規則
既存のテーブル名の名前を`as`で上書きする際は、テーブル名の各単語の頭の文字を使うのが一般的である。
### left join, right join, full join
inner joinによる情報損失を回避するために、`left join`、`right join`、`full join`を用いることができる。
`inner join`のクエリを、`inner join`から`left join`に変更してみる。
```sql=
select
name
, title_id
from
player as p
left join player_title as pt
on p.id = pt.player_id
```
|name|title_id|
|-|-|
|羽生善治|null|
|久保利明|null|
|木村一基|4|
|中村太地|null|
|佐藤天彦|null|
|渡辺明|6|
|渡辺明|7|
|豊島将之|1|
|豊島将之|2|
|永瀬拓矢|3|
|永瀬拓矢|5|
|菅井竜也|null|
|高見泰地|null|
|藤井聡太|8|
すると、このように、すべての棋士の名前が出力される。
title_idが存在しない棋士は、nullが挿入されて帳尻が合わされることになる。
**left** joinでは、左辺のテーブルの情報が損失しないように結合し、**right** joinでは右辺のテーブルの情報が損失しないように結合する。
また、full joinでは両方のテーブルの情報が損失しないように結合する。
#### left joinを使った効果的な例
このleft joinを使った効果的な例として、各棋士の持っているタイトルの数を求めるクエリが考えられる。
```sql=
select
name
, count(title_id) as title_count
from
player as p
inner join player_title as pt
on p.id = pt.player_id
group by
name
```
とすれば、
|name|title_count|
|-|-|
|木村一基|1|
|渡辺明|2|
|豊島将之|2|
|永瀬拓矢|2|
|藤井聡太|1|
となるが、
```sql=
select
name
, count(title_id) as title_count
from
player as p
left join player_title as pt
on p.id = pt.player_id
group by
name
```
|name|title_count|
|-|-|
|羽生善治|0|
|久保利明|0|
|木村一基|1|
|中村太地|0|
|佐藤天彦|0|
|渡辺明|2|
|豊島将之|2|
|永瀬拓矢|2|
|菅井竜也|0|
|高見泰地|0|
|藤井聡太|1|
とすることができ、より正しい情報を取得することができる。
### union (all)
`union (all)`句は、テーブルをそのまま縦に結合する。
たとえば、次のようなテーブルを考える。
**player2**
|id|name|dan|
|-|-|-|
|321|折田翔吾|四段|
|322|服部慎一郎|四段|
|323|谷合廣紀|四段|
このとき、
```sql
select
*
from
player
union all
select
*
from
player2
```
の出力は
|id|name|dan|
|-|-|-|
|175|羽生善治|九段|
|207|久保利明|九段|
|222|木村一基|九段|
|235|渡辺明|九段|
|261|中村太地|七段|
|263|佐藤天彦|九段|
|264|豊島将之|八段|
|276|永瀬拓矢|八段|
|278|菅井竜也|七段|
|284|高見泰地|七段|
|307|藤井聡太|七段|
|321|折田翔吾|四段|
|322|服部慎一郎|四段|
|323|谷合廣紀|四段|
となる。
このとき、`union`句は重複したレコードは追加せず、`union all`は重複したレコードを追加する。
## サブクエリ
以下のように、`from`句の引数にクエリを書くことによって、あるクエリの結果のテーブルを`from句`の入力とすることができる。
この際、作成した中間テーブルには`as`句を用いて名前をつけなければならない。
主に、集計関数で集計した結果をもう一度集計する必要があるときに必要になる。
```sql=
-- あまりよくない例だが…
select
count(name) as titleholder_count
from
(
select
name
, count(title_id) as title_count
from
player as p
inner join player_title as pt
on p.id = pt.player_id
) as a
```
|titleholder_count|
|-|
|5|
※タイトルホルダーの人数を確認したい場合は、以下のクエリのほうがより適切である。
```sql=
select
count(distinct player_id) as titleholder_count
from
player_title
```
<!--### with句《Google BigQuery Standard SQL》
Google BigQuery Standard SQLには、サブクエリを簡単に書くためのサポートとして`with`句が存在する。
これは、サブクエリの出力をある変数に保存するもので、以下のように用いる。
```sql=
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関数を用いる
```sql
-- MySQL
select
cast('123' as signed)
-- -> 123
```
##### 専用の関数を用いる
```sql
-- Standard SQL
select
date('2018-01-02 23:02:01', '+09')
-- -> 2018-01-03
```
Sign in with Wallet
Connect another wallet