LoginSignup
20
14

Delete article

Deleted articles cannot be recovered.

Draft of this article would be also deleted.

Are you sure you want to delete this article?

More than 5 years have passed since last update.

Day 14

Supership株式会社 Advent Calendar 2018

@frtn

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

Last updated at Posted at 2018-12-13

まえがき

これは 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はデフォルトでは見えないので、データセットを追加しておくとよいかもしれません（追加しなくてもクエリ対象にはできるようです）。

以上前置き

以下教材

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コンソールに入力してみてください。
結果が得られれば成功です。

BigQueryコンソール操作イメージ

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

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

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

概要

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

SELECT
  1 AS num,
  'a' AS str

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

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

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

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

概要

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

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) 性別と誕生年を取得する

(1)の答え
SELECT
  gender,
  name
FROM
  `bigquery-public-data.usa_names.usa_1910_current`

チェックポイント

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

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

(2)の答え
SELECT
  year,
  name,
  number
FROM
  `bigquery-public-data.usa_names.usa_1910_current`
LIMIT
  50

チェックポイント

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

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

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

概要

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

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以降の州名と名前を取得する

(1)の答え
SELECT
  state,
  name
FROM
  `bigquery-public-data.usa_names.usa_1910_current`
WHERE
  year >= 2010

チェックポイント

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

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

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

(2)の答え
SELECT DISTINCT
  name
FROM
  `bigquery-public-data.usa_names.usa_1910_current`
WHERE
  name LIKE 'A%'

チェックポイント

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

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

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

概要

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

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) 男女別の合計人数を抽出

(1)の答え
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列を指定できているか

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

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

(2)の答え
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列を指定できているか

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

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

概要

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

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件を取得する

(1)の答え
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句を指定できているか

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

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

(2)の答え
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は省略していてもよい）

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

まとめ

ここまで学んできたことを踏まえて、冒頭の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かはわかりますか？
わかるならこの文章の内容は完璧です。
わからないなら、何がわからないのかを理解し、その節に戻って復習しましょう。

冒頭のSQLの意味の答え

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

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

チェックポイント

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

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

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

  • JOIN
    • 複数のテーブルを組み合わせる際に使います
  • サブクエリ
    • あるSQLの実行結果をFROM句のデータ取得対象とする際に使います
    • 入れ子のSQLを書くイメージです
  • WITH
    • サブクエリの結果に対して一時的な名前をつける際に使います
    • 名前をつけたクエリはテーブル名のようにFROM句に指定します
  • HAVING
    • グループに対する条件で絞り込みをしたい際に使います
    • 使うときはほぼGROUP BY句とセットで使います
    • WHERE句も絞り込みのための構文ですが、使いどきが異なります
  • CASE
    • 列に対して、条件によって場合分けをしたい場合に使います
  • WINDOW関数
    • GROUP BY句よりも柔軟なグルーピングを伴う集計をしたい際に使います
    • 難しい概念なので慣れてから触れることをお勧めします
  • 各種データ変換関数

終わりに

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

あとがき

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

20
14
1

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
20
14

Delete article

Deleted articles cannot be recovered.

Draft of this article would be also deleted.

Are you sure you want to delete this article?