エンジニアが非エンジニア向けにデータ分析用SQLを教えるための教材を作ってみた

# まえがき

これは Supership Advent Calendar 2018 14日目です。
弊社のアドベントカレンダー、ジャンルが多岐に渡っていて楽しいので、ぜひ他の記事もご覧ください。

# はじめに

* 先週1週間分のアクセスユーザ数ちょうだい
* 昨日一番アクセスあったURLちょうだい
* ほげほげな条件のアクセスログちょうだい

上記のようなエンジニアと非エンジニア間のやり取りで時間を消費するのは、仕方ないとはいえお互いの作業が止まってしまいます。
そのため、非エンジニアでも簡単なSQLを書けるようになってもらおうということで入門用の教材を作ってみました。


## 本記事で取り扱うこと

* データ分析で使用することを目的とした、基本的なSQLの書き方

## 本記事では取り扱わないこと

* 厳密なSQLの説明
    * 厳密性よりも使えるようになることや障壁を取り除くことを優先
* テクニカルなSQLの書き方
    * 慣れてからでよいです
    * 難しいSQLが書きたくなった時点で調べて書く力がついてそうなので
* アプリケーション向けのSQLの話
    * テーブル設計の話など
* データベース自体の説明
    * 他のドキュメントを当たってもらえると幸いです
* テーブルの作り方、インポートの仕方
    * すでにデータが用意してある状態を想定

## おことわり

本記事はBigQueryのStandard SQLを使うことを前提としています。
また、データセットはBigQueryのPublic Datasetを使用します。
そのため、テーブルの作成などは不要です。
GCPプロジェクトやBigQueryやIAMの設定は公式マニュアルを参照しあらかじめ済ませておいてください。

なお、Standard / Legacy SQLまわりの問題を回避するため、新UIを使うことをお勧めします（2018-12-14現在はBeta版です）。
https://console.cloud.google.com/bigquery

説明の中で、下記のPublic Datasetを使用します。
新UIだとPublic Datasetはデフォルトでは見えないので、データセットを追加しておくとよいかもしれません（追加しなくてもクエリ対象にはできるようです）。

https://console.cloud.google.com/marketplace/details/social-security-administration/us-names

---

以上前置き

以下教材

---

# SQL入門

## SQLの概要

SQLはデータベースからデータを取得するときに使う言語です。
ざっくりいうと下記のような構造になっています。

```sql
/* 結果として何が欲しいか */
SELECT
  year,
  SUM(number) AS total

/* どこからデータを持ってくるか */
FROM
  `bigquery-public-data.usa_names.usa_1910_current`

/* どういう条件のデータが欲しいか */
WHERE
  year >= 2000

/* データのグループをどうするか */
GROUP BY
  year

/* データの並び順をどうするか */
ORDER BY
  year DESC
```

上記のSQLは実際にBigQueryコンソール上で実行することができます。
下記の図を参考に実際にBigQueryコンソールに入力してみてください。
結果が得られれば成功です。

<img width="512" alt="BigQueryコンソール操作イメージ" src="https://qiita-image-store.s3.amazonaws.com/0/124692/5627eb75-78ac-88ad-c2ed-66e345ae66c7.png">

最初はこのSQLが何を行なっているのか全くわからないかもしれません。
ですが、本記事の内容を全て終えた後なら、きっと理解できるようになっていますので安心してください。

では、各項目について順を追って確認していきます。

## SELECT : 結果として何が欲しいか

### 概要

`SELECT`句には`結果として何が欲しいか`を書きます。
例えば、下記のように使います。

```sql
SELECT
  1 AS num,
  'a' AS str
```

これは固定値の`1`と`"a"`を取得するというSQLです。
とても退屈なこのSQLですが、`SELECT`句の基本が詰まっています。

* 結果の列は複数指定することができます
    * 列は`,`で区切ります
    * 最後の列の後ろには`,`は不要です
* 列には名前をつけることができます
    * `値 AS 名前`もしくは`列 AS 名前`という構文です
    * 結果を見やすく/扱いやすくするために使います
* 列には値を記述することができます
    * 固定値を得ることができます

このセクションでは演習はありません。
早速次の節に進みましょう。

## FROM : どこからデータを持ってくるか

### 概要

`FROM`句には`どこからデータを持ってくるか`を書きます。
例えば、下記のように使います。

```sql
SELECT
  state,
  name
FROM
  `bigquery-public-data.usa_names.usa_1910_current`
LIMIT
  30
```

これは`bigquery-public-data.usa_names.usa_1910_current`テーブルの`state`列と`name`列の値を`30レコード`取得するというSQLです。
このSQLは`テーブルに入っている値をぼんやりと眺める`際に使う典型的な構文です。
どのようなデータが入っているかがわかっていないとデータ分析も何もないので、この作業は想像より大切です。

上記のSQLでは`FROM`句に`bigquery-public-data.usa_names.usa_1910_current`を指定することでデータのありかを指定しています。
Excelに無理やり例えると、シート名を指定しているようなイメージです。

### 追加情報1

前節で学んだ`SELECT`句にも追加の情報があります。
`SELECT 〜 FROM`の組み合わせでよく使うものばかりのため、セットで覚えておくと捗ります。

* テーブルの列名を指定することで、特定の列の値のみを抜き出すことができます
    * 上記SQL例では`state`,`name`列を指定しています
* 列に名前をつける`AS 名前`構文は省略可能です
    * その場合、結果にはテーブルの列名がそのまま使用されます

### 追加情報2

例で使用したテーブルにはレコードが500万行以上あり、全てのデータを取得するとサイズが大きすぎて、とても確認などしきれません。
特に、今回のような`テーブルに入っている値をぼんやりと眺める`際には一部のデータだけ確認できればよいですよね。
そういう際に`レコードの取得件数の最大値を指定する`構文の`LIMIT`句を併用します。

* `LIMIT 数値`の構文で使用します
    * レコードの総数が`LIMIT`句で指定した数値より多い場合は指定した数だけレコードを取得します
    * レコードの総数が`LIMIT`句で指定した数値よりも少ない場合は全レコードを取得します
* `LIMIT`句はSQLの最後に書きます

上記のSQL例では、取得件数を30件にしぼっています。

### 演習

演習では`bigquery-public-data.usa_names.usa_1910_current`テーブルに対して色々な操作を行ってみます。
なお、2018-12-14時点でのテーブル定義は下記の通りです。

| フィールド名 | 型 | 説明 |
|:--|:--|:--|
| state | 文字列 | 二桁の州コード |
| gender | 文字列 | 性別(M=男 or F=女)|
| year | 整数 | 4桁誕生年 |
| name | 文字列 | 名前 |
| number | 整数 | 名前の出現数 |

演習で書くSQLは、答えと完全に一致していなくても大筋が合っていれば大丈夫です。

**(1) 性別と誕生年を取得する**

<details><summary>(1)の答え</summary><div>

```sql
SELECT
  gender,
  name
FROM
  `bigquery-public-data.usa_names.usa_1910_current`
```

**チェックポイント**

* `SELECT`句に`gender`列と`name`列を指定できているか

</div></details>

**(2) 年と名前と出現数を50件取得する**

<details><summary>(2)の答え</summary><div>

```sql
SELECT
  year,
  name,
  number
FROM
  `bigquery-public-data.usa_names.usa_1910_current`
LIMIT
  50
```

**チェックポイント**

* `SELECT`句に`year`列、`name`列、`number`列を指定できているか
* `LIMIT 50`を指定できているか

</div></details>

この節で学んだ`SELECT 〜 FROM`構文は全てのSQLの基礎になるのでしっかりマスターして次の節に進みましょう。

## WHERE : どういう条件のデータが欲しいか

### 概要

`WHERE`句には`どういう条件のデータが欲しいか`を書きます。
例えば、下記のように使います。

```sql
SELECT DISTINCT
  name
FROM
  `bigquery-public-data.usa_names.usa_1910_current`
WHERE
  gender = 'M'
```

これは`bigquery-public-data.usa_names.usa_1910_current`テーブルの`gender`列の値が`M`であるレコードの`name`列の値を重複を削除して取得するというSQLです。
`男性の名前の一覧が欲しい`をSQLで表現するとこのようになります。

`WHERE`句は`WHERE 条件`の構文で使用し、指定した条件を満たすレコードのみを取得するために使用します。
代表的な条件には下記のものがあります。

| 条件 | 意味 | 使いどき |
|:--|:--|:--|
| `A < B`, `A <= B`, `A >= B`, `A > B` | それぞれ数学の`小なり`、`小なりイコール`、`大なりイコール`、`大なり`と同じ | 数値の大小比較 |
| `A = B`, `A <> B` | それぞれ数学の`イコール`、`ノットイコール`と同じ| 数値、文字列の一致 |
| `A IS NULL` `A IS NOT NULL` | それぞれ`Aの値が空`、`Aの値が空でない` | 値があるものだけ/ないものだけ取得したいとき |
| `A LIKE パターン`、 `A NOT LIKE パターン` | それぞれ`Aがパターンを満たすとき`、`Aがパターンを満たさないとき`| 柔軟な文字列絞り込みをしたいとき |
| `条件1 AND 条件2` | 条件1と条件2の両方を満たすとき、全体として条件を満たす | 2つ以上の条件を組み合わせるとき |
| `条件1 OR 条件2` | 条件1と条件2のどちらか（両方でもよい）を満たすとき、全体として条件を満たす | 2つ以上の条件を組み合わせるとき |
| `NOT 条件` | 条件の満たす/満たさないを反転する | 複雑な条件を記述するとき |

完全なものは`論理演算子`や`比較演算子`のドキュメントを参照してください。
https://cloud.google.com/bigquery/docs/reference/standard-sql/functions-and-operators?hl=ja#logical-operators
https://cloud.google.com/bigquery/docs/reference/standard-sql/functions-and-operators?hl=ja#comparison-operators
上記ドキュメントはBigQueryのマニュアルです。
BigQuery特有のものもありますので、他のデータベースを使用している場合は、ドキュメントを当たるようにしてください。

### 追加情報

前々節で学んだ`SELECT`句にさらに追加の情報があります。
覚えておくと役に立つので一緒に覚えておきましょう。

* `DISTINCT`を指定することで同じ値のレコードを重複とみなして削除できます
    * `SELECT DISTINCT フィールド名`の構文で使用します
    * フィールドを複数指定した場合、全てのフィールドの値が一致するレコードを同じ値とします
    * 値のバリエーションを見たいときに使用します

### 演習

演習では、引き続き`bigquery-public-data.usa_names.usa_1910_current`テーブルに対して色々な操作を行ってみます。
テーブル定義は`FROM`句の演習を参照してください。
演習で書くSQLは、答えと完全に一致していなくても大筋が合っていれば大丈夫です。

**(1) 誕生年が2010以降の州名と名前を取得する**

<details><summary>(1)の答え</summary><div>

```sql
SELECT
  state,
  name
FROM
  `bigquery-public-data.usa_names.usa_1910_current`
WHERE
  year >= 2010
```

**チェックポイント**

* `SELECT`句に`state`列と`name`列を指定できているか
* `WHERE`句に`year`列の絞り込みを記述できているか
* `WHERE`句の条件に`>=`（大なりイコール）が使用できているか

</div></details>

**(2) Aで始まる名前の一覧を重複削除して抽出**

ヒント:`Aで始まる`というパターンの書き方は`'A%'`

<details><summary>(2)の答え</summary><div>

```sql
SELECT DISTINCT
  name
FROM
  `bigquery-public-data.usa_names.usa_1910_current`
WHERE
  name LIKE 'A%'
```

**チェックポイント**

* `SELECT`句に`name`列を指定できているか
* `DISTINCT`を指定し、重複削除ができているか
* `WHERE`句に`name`列の絞り込みを記述できているか
* `WHERE`句の絞り込みに`LIKE`を使用できているか

</div></details>

`WHERE`句を使いこなせるとかなり複雑なデータ抽出が可能となるため、すでに一定のSQL力がついたと言えます。
さらにSQL力を高めるため次の節に進みましょう。

## GROUP BY : データのグループをどうするか

### 概要

`GROUP BY`句には`データのグループをどうするか`を書きます。
例えば、下記のように使います。

```sql
SELECT
  year,
  COUNT(*) AS cnt
FROM
  `bigquery-public-data.usa_names.usa_1910_current`
GROUP BY
  year
```

これは`bigquery-public-data.usa_names.usa_1910_current`テーブルの`year`列の値ごとのレコードの数を取得するというSQLです。
`各年のレコード数が欲しい`をSQLで書くと表現するとこのようになります。
この例のような`SELECT 〜 FROM 〜 GROUP BY`の組み合わせはKPIを取得する際の典型的な構文の一つなので、マスターしておきましょう。

`GROUP BY`句を使うことで`SELECT`句に通常の列と`集計関数`の列を書くことができるようになります。
代表的な集計関数は下記のものがあります。

|関数|使いどき|
|:--|:--|:--|
| `COUNT(*)` | レコード数を数えたいとき |
| `COUNT(DISTINCT 列名)` | ある列の値のバリエーション数を数えたいとき（=重複削除して数える） |
| `SUM(列名)` | ある列の合計値を求めたいとき |
| `MAX(列名)` | ある列の最大値を求めたいとき |
| `MIN(列名)` | ある列の最小値を求めたいとき |
| `AVG(列名)` | ある列の平均値を求めたいとき |

完全なものは`集計関数`のドキュメントを参照してください。
https://cloud.google.com/bigquery/docs/reference/standard-sql/functions-and-operators?hl=ja#aggregate-functions
上記ドキュメントはBigQueryのマニュアルです。
BigQuery特有のものもありますので、他のデータベースを使用している場合は、ドキュメントを当たるようにしてください。

また、`GROUP BY`句を使用する際には下記の注意点があります。

* `SELECT`句に集計関数と通常の列を併用する場合、列は全て`GROUP BY`句に列挙する必要があります
    * 例のSQLだと、`SELECT`句に`year`列を指定しているため、`GROUP BY`句に`year`列を指定する必要があります
    * 指定を忘れるとSQL文法エラーになります

### 演習

演習では、引き続き`bigquery-public-data.usa_names.usa_1910_current`テーブルに対して色々な操作を行ってみます。
テーブル定義は`FROM`句の演習を参照してください。
演習で書くSQLは、答えと完全に一致していなくても大筋が合っていれば大丈夫です。

**(1) 男女別の合計人数を抽出**

<details><summary>(1)の答え</summary><div>

```sql
SELECT
  gender,
  SUM(number) AS total
FROM
  `bigquery-public-data.usa_names.usa_1910_current`
GROUP BY
  gender
```

**チェックポイント**

* `SELECT`句に`gender`列と`SUM(number)`を指定できているか
* `GROUP BY`句に`gender`列を指定できているか

</div></details>


**(2) 2010年の州ごとのレコード数を抽出**

ヒント:各句の記述の順序に気をつけましょう

<details><summary>(2)の答え</summary><div>

```sql
SELECT
  state,
  COUNT(*) AS total
FROM
  `bigquery-public-data.usa_names.usa_1910_current`
WHERE
  year = 2010
GROUP BY
  state
```

**チェックポイント**

* `SELECT`句に`state`列と`COUNT(*)`を指定できているか
* `WHERE`句に`year`列の絞り込みを記述できているか
* `GROUP BY`句に`state`列を指定できているか

</div></details>

`GROUP BY`句は分析SQLの山場です。
ここまでの内容が理解できればKPIは自分で算出できるようになっています。
もう一息頑張りましょう。

## ORDER BY : データの並び順をどうするか

### 概要

`ORDER BY`句には`データの並び順をどうするか`を書きます。
例えば、下記のように使います。

```sql
SELECT
  name,
  number
FROM
  `bigquery-public-data.usa_names.usa_1910_current`
ORDER BY
  number DESC
```

これは`bigquery-public-data.usa_names.usa_1910_current`テーブルの`name`列と`number`列の値を`number`列の降順（大きい順）に並べて取得するというSQLです。
`数の多い名前から順番に欲しい`をSQLで書くとこのようになります。

`ORDER BY`句は下記のように使います

|記法|意味|
|:--|:--|
| `ORDER BY 列名` | 指定した列の昇順（小さい順）に並べる |
| `ORDER BY 列名 ASC` | 指定した列の昇順（小さい順）に並べる |
| `ORDER BY 列名 DESC` | 指定した列の降順（大きい順）に並べる |

また、複数の列を下記のように`,`区切りで指定することができます。

`ORDER BY 列名1 (ASC|DESC), 列名2 (ASC|DESC)`

この場合、下記のような動きをします。

* 1つ目の列の値で並び替え
    * ここで順序が決まる場合は以降の処理はスキップ
* 1つ目の列の値が同じ場合は2つ目の列の値で並び替え
    * 以降、指定した全ての列に対して同様の処理をします

なお、値が同じだった列の並び方は決まっていません。
実行するたびに並び方が変わり得るので、場合によっては異なる結果が得られます。

### 演習

演習では、引き続き`bigquery-public-data.usa_names.usa_1910_current`テーブルに対して色々な操作を行ってみます。
テーブル定義は`FROM`句の演習を参照してください。
演習で書くSQLは、答えと完全に一致していなくても大筋が合っていれば大丈夫です。

**(1) 2005年の出現数の多い名前上位10件を取得する**

<details><summary>(1)の答え</summary><div>

```sql
SELECT
  name,
  number
FROM
  `bigquery-public-data.usa_names.usa_1910_current`
WHERE
  year = 2005
ORDER BY
  number DESC
LIMIT
  10
```

**チェックポイント**

* `SELECT`句に`name`列と`number`列を指定できているか
* `WHERE`句に`year`列の絞り込みを記述できているか
* `ORDER BY`句に`number`列を指定できているか
* `ORDER BY`句に`DESC`を指定できているか
* `LIMIT`句を指定できているか


</div></details>

**(2) 男女ごとの合計人数を昇順に取得する**

ヒント:集計関数に名前をつけると`ORDER BY`句に指定することができます

<details><summary>(2)の答え</summary><div>

```sql
SELECT
  gender,
  SUM(number) AS total
FROM
  `bigquery-public-data.usa_names.usa_1910_current`
GROUP BY
  gender
ORDER BY
  total ASC
```

**チェックポイント**

* `SELECT`句に`gender`列と`SUM(number)`を指定できているか
* `GROUP BY`句に`gender`列を指定できているか
* `ORDER BY`句に`SUM(number)`に対してつけた名前を指定できているか
* `ORDER BY`句に`ASC`を指定できているか（ASCは省略していてもよい）

</div></details>

最後の演習はこれまでの集大成ですので難しいかもしれませんが、頑張って取り組んでみましょう。

## まとめ

ここまで学んできたことを踏まえて、冒頭のSQLを再度確認してみましょう。

```sql
/* 結果として何が欲しいか */
SELECT
  year,
  SUM(number) AS total

/* どこからデータを持ってくるか */
FROM
  `bigquery-public-data.usa_names.usa_1910_current`

/* どういう条件のデータが欲しいか */
WHERE
  year >= 2000

/* データのグループをどうするか */
GROUP BY
  year

/* データの並び順をどうするか */
ORDER BY
  year DESC
```

どういう意味のSQLかはわかりますか？
わかるならこの文章の内容は完璧です。
わからないなら、何がわからないのかを理解し、その節に戻って復習しましょう。

<details><summary>冒頭のSQLの意味の答え</summary><div>

`bigquery-public-data.usa_names.usa_1910_current`テーブルの`year`列の値が2000以上のものについて、`year`列の値が同じレコードの`number`列の合計値を求めて、`year`列の値が大きいものから順に並べるSQLです。

`2000年以降の出生年ごとの合計人数を新しいものから順に並べる`をSQLで表現したものです。

**チェックポイント**

* 各句の意味を捕らえられているか

</div></details>

## さらにレベルアップするための情報

本記事で取り上げなかったものの、一段階上のデータ分析SQLを書くために必要となってくるものについて軽く列挙しておきます。
本記事で取り扱ったことでは実現できない集計をしたくなった際に、調べる糸口としてください。

* JOIN
    * 複数のテーブルを組み合わせる際に使います
* サブクエリ
    * あるSQLの実行結果を`FROM`句のデータ取得対象とする際に使います
    * 入れ子のSQLを書くイメージです
* WITH
    * サブクエリの結果に対して一時的な名前をつける際に使います
    * 名前をつけたクエリはテーブル名のように`FROM`句に指定します
* HAVING
    * グループに対する条件で絞り込みをしたい際に使います
    * 使うときはほぼ`GROUP BY`句とセットで使います
    * `WHERE`句も絞り込みのための構文ですが、使いどきが異なります
* CASE
    * 列に対して、条件によって場合分けをしたい場合に使います
* WINDOW関数
    * `GROUP BY`句よりも柔軟なグルーピングを伴う集計をしたい際に使います
    * 難しい概念なので慣れてから触れることをお勧めします
* 各種データ変換関数
    * データベースごとの違いが大きい領域です
    * BigQueryならこちらのドキュメントを参照 => https://cloud.google.com/bigquery/docs/reference/standard-sql/functions-and-operators


# 終わりに

本記事で学んだSQLを使えば単純なKPIなら自分で作成できます。
あとはCSVでデータをダウンロードしてExcelやGoogle Spreadsheetでグラフを描くもよし、Re:dashやDataStudioなどのツールを使ってグラフ化、ダッシュボード化するもよしです。
ここまでできたら色々と通用すると思うので、ぜひ挑戦してみてください。

# あとがき

本記事の内容への意見や指摘、もっといい記事見つけた/作ったなどの情報はどんどんコメント欄に書き込んでいただけると幸いです。
SQL力の底上げのために、ぜひこの記事を踏み台にしてやってください。