Slide 1
Slide 1 text
データハンドリングのためのSQL
〜SQLを用いた集計・分析〜
新卒社員研修
株式会社ブレインパッド
2019年版
Slide 2
Slide 2 text
Analytics Innovation Company ©BrainPad Inc. 1
目次
0. はじめに
0-1. 付属資料
0-2. 本研修の目的
0-3. SQL研修の流れ
1. データベースとSQL
1-1. データベースとは
1-2. SQLとは
1-3. 実業務におけるデータベース
2. SQLの基礎①
2-1. 4つの基本機能CRUD
2-2. WHERE(条件検索)
2-3. GROUP BY(集約), HAVING(絞込)
2-4. SQLの実行順序
3. SQLの基礎②
3-1. 条件式 CASE, COALESCE
3-2. データ型と型変換CAST
3-3. 文字列関数 SUBSTRING
3-4. 日付関数 CURRENT_DATE,
DATE_DIFF
4. SQLの応用①
4-1. サブクエリ(副問い合わせ）
4-2. テーブルの結合 JOIN
4-3. 一時テーブル
5. SQLの応用②
5-1. ウィンドウ関数
5-2. EXISTS（存在検査）
5-3. 縦持ち・横持ち
Slide 3
Slide 3 text
Analytics Innovation Company ©BrainPad Inc. 2
0. はじめに
• 0-1. 付属資料
• 0-2. 本研修の目的
• 0-3. SQL研修の流れ
Slide 4
Slide 4 text
Analytics Innovation Company ©BrainPad Inc. 3
0-1. 付属資料
本資料には以下の付属資料があります。併せてご参照ください。
※実際の研修では社内環境のRedshiftを使用していましたが、本資料は各人のローカル環境で手を動
かしながら学べるように、MySQL8.0に合わせて一部内容を変更しています。
本資料内容を実行するためのSQLローカル環境構築
方法をまとめた資料を同じくSpeaker Deckに公開し
ています。
https://speakerdeck.com/brainpadpr/sql-local-
environment-construction
資料中の
・環境構築
・DBデータ＆解答コード
類題 に対する解答コードをGitHubにて
本資料で使用するデータや、
公開しています。
https://github.com/BrainPad/SQLForBeginners2019
Slide 5
Slide 5 text
Analytics Innovation Company ©BrainPad Inc. 4
0-2. 本研修の目的
• 本研修の目的
分析業務に必須となる基本的なデータ加工技術を身につける
Slide 6
Slide 6 text
Analytics Innovation Company ©BrainPad Inc. 5
0-3. SQL研修の流れ
1.例題
最初に、学ぶSQLを写経して実行
2.解説
実行したSQLの詳細を解説
3.類題
類似したSQLを自分で書いて理解
各章の中でこの
サイクルを繰り返
します
Slide 7
Slide 7 text
Analytics Innovation Company ©BrainPad Inc. 6
1. データベースとSQL
1-1. データベースとは
① 利用例：家計簿アプリ
② テーブル構造
③ テーブル定義
④ 代表的なデータ型
⑤ ER図 – エンティティ
⑥ ER図 – 主キー、外部キー
⑦ RDB/DWHとDBMS
1-2. SQLとは
① クエリとSQL
② SQLでできることの例
1-3. 実業務におけるデータベース
Slide 8
Slide 8 text
Analytics Innovation Company ©BrainPad Inc. 7
ユーザ情報
1-1. データベースとは① 利用例：家計簿アプリ
id … … … …
1
2
…
レシート情報
id … … … …
1
2
…
レシートアイテム情報
id … … … …
1
2
…
ログイン情報
（ユーザ名など）
読込
入力
レシート
データ登録
蓄積されたデータを活用
DB
Slide 9
Slide 9 text
Analytics Innovation Company ©BrainPad Inc. 8
user
1-1. データベースとは② テーブル構造
user
id … … … …
1
2
…
user
id … … … …
1
2
…
user
id … … … …
1
2
…
user
id … … … …
1
2
…
① テーブル：表 ② テーブル名：表の名前
③ レコード｜行：1行分のデータ ④ フィールド｜列：1列分のデータ
Slide 10
Slide 10 text
Analytics Innovation Company ©BrainPad Inc. 9
• テーブルで定義するもの
• テーブル名
• フィールド名 ※カラム名と呼ぶこともある
• データ型
1-1. データベースとは③ テーブル構造
user
id name … … …
1 佐藤
2 田中
…
…
user
id name … … …
1 佐藤
2 田中
…
…
例1
フィールド名： id
データ型： 整数型
例2
フィールド名： name
データ型： 文字列型
テーブル名：user
Slide 11
Slide 11 text
Analytics Innovation Company ©BrainPad Inc. 10
1-1. データベースとは④ 代表的なデータ型
• データ型はデータ加工や集計操作で失敗しやすい
• エラーが出たときに確認するポイント
• 「データ型の定義がきちんと行われているか」
• 「関数の引数に正しいデータ型を用いているか」
数値型
int
tinyint
bigint
1 符号付き整数
float
real
0.5 浮動小数点数値
文字列型 char(n)
‘apple’
固定長の文字列(n:バイト数)
varchar(n) 可変長の文字列(n:バイト数)
日付型 date 2018-04-01 日付
timestamp 2018-04-01 01:00:00 日付時刻
論理値型 boolean true(真)かfalse(偽)のみ 論理値
Slide 12
Slide 12 text
Analytics Innovation Company ©BrainPad Inc. 11
1-1. データベースとは④ 代表的なデータ型
• データ型にまつわる失敗例1（エラーになる）
文字列 ’aaa’ を数値に変換しようとする
• データ型にまつわる失敗例2（エラーにはならない）
文字列で格納された ’1’ と ’2’ を足したつもり
‘1’ + ‘2’ 結果 ‘12’ （文字列が連結）
整数値で格納された1と2を足した
1 + 2 結果 3（整数値）
• このように、エラーでなくとも、望む結果にならないことがあるので、データ型をしっか
り確認する必要がある
Slide 13
Slide 13 text
Analytics Innovation Company ©BrainPad Inc. 12
1-1. データベースとは⑤ ER図 – エンティティ
user
id name gender …
1
2
…
receipt receipt_item
テ
ー
ブ
ル
id
name
gender
…
user
id
user_id (FK)
shop_name
…
receipt
id
user_id(FK)
receipt_id(FK)
…
receipt_item
• テーブルに含まれるデータ項目をまとめたものをエンティティ(
Entity)という
エ
ン
テ
ィ
テ
ィ
（
物
理
モ
デ
ル
）
id user
_id
shop
_name
…
1
2
…
id user
_id
receipt
_id
…
1
2
…
ユーザ情報 レシート情報 レシートアイテム情報
Slide 14
Slide 14 text
Analytics Innovation Company ©BrainPad Inc. 13
1-1. データベースとは⑥ ER図 – 主キー・外部キー
id
name
gender
…
user
id
user_id (FK)
shop_name
…
receipt
id
user_id (FK)
receipt_id (FK)
…
receipt_item
id
name
gender
…
user
id
user_id (FK)
shop_name
…
receipt
id
user_id (FK)
receipt_id (FK)
…
receipt_item
• レコードを一意に特定できるデータ項目を主キー(PK)という
※下図の各 “id” は同じカラム名がついているが意味が異なるので注意
• 他テーブルとの関連付けに用いるデータ項目を外部キー(FK)という
• エンティティ間の関係をリレーション(
Relation)という
Slide 15
Slide 15 text
Analytics Innovation Company ©BrainPad Inc. 14
user
id (PK) BIGINT
auth_type INTEGER
device_id CHAR(32)
hashed_external_id VARCHAR(64)
hashed_password VARCHAR(64)
gender INTEGER
birthday DATE
location VARCHAR(64)
state_code CHAR(8)
last_login DATE
create_date TIMESTAMP
receipt
id (PK) BIGINT
user_id (FK) BIGINT
shop_name VARCHAR(464)
paid_at TIMESTAMP
read_at TIMESTAMP
update_at TIMESTAMP
client_id VARCHAR(32)
receipt_item
id (PK) BIGINT
user_id (FK) BIGINT
receipt_id (FK) BIGINT
name VARCHAR(200)
price FLOAT8
expense (FK) INTEGER
expense_parent (FK) INTEGER
image_file_name VARCHAR(64)
type_expense
id (PK) BIGINT
main_heading VARCHAR(64)
sub_heading VARCHAR(64)
1-1. データベースとは⑦ ER図 – 例（物理モデル）
ER図で、テーブル定義とテーブル間
の関係を把握することができる
Slide 16
Slide 16 text
Analytics Innovation Company ©BrainPad Inc. 15
user
id (PK) BIGINT
auth_type INTEGER
device_id CHAR(32)
hashed_external_id VARCHAR(64)
hashed_password VARCHAR(64)
gender INTEGER
birthday DATE
location VARCHAR(64)
state_code CHAR(8)
last_login DATE
create_date TIMESTAMP
receipt
id (PK) BIGINT
user_id (FK) BIGINT
shop_name VARCHAR(464)
paid_at TIMESTAMP
read_at TIMESTAMP
update_at TIMESTAMP
client_id VARCHAR(32)
receipt_item
id (PK) BIGINT
user_id (FK) BIGINT
receipt_id (FK) BIGINT
name VARCHAR(200)
price FLOAT8
expense (FK) INTEGER
expense_parent (FK) INTEGER
image_file_name VARCHAR(64)
type_expense
id (PK) BIGINT
main_heading VARCHAR(64)
sub_heading VARCHAR(64)
1-1. データベースとは⑦ ER図 – 例（物理モデル）
公開しているサンプルデータでは一部
のカラムのみのデータになっています
Slide 17
Slide 17 text
Analytics Innovation Company ©BrainPad Inc. 16
1-1. データベースとは⑦ RDB/DWHとDBMS
• リレーショナルデータベース(RDB)
• データを複数のテーブル（表）として管理し、テーブル間の関係を定義することで複雑な
データの関連性を扱えるようにした管理方式
• データウェアハウス(DWH)
• 列指向データベースで大規模なデータ集計や分析に用いられる
• DBMS
• データベースマネジメントシステムの略
• データベースの構築、運用、管理を行うソフトウェア
Slide 18
Slide 18 text
Analytics Innovation Company ©BrainPad Inc. 17
1-2. SQLとは① クエリとSQL
• データベースに対する問い合わせ、命令をクエリという
• データベースに命令する際に用いられる言語をSQLという
• 由来はStructured Query Language
人間 & データベースの場合
言語：日本語 言語：SQL
質問「△△教えて」
返事「○○だよ」
人間 ＆ 人間の場合
問い合わせ
応答
DB
クエリ：SELECT * FROM …
○○, △△…
問い合わせ
応答
Slide 19
Slide 19 text
Analytics Innovation Company ©BrainPad Inc. 18
1-2. SQLとは② SQLにできることの例
操作の例 対応する句や関数の例
必要な列を選択する SELECT
レコードを検索する、フィルタをかける WHERE
重複したレコードを省く DISTINCT
レコードを並べ替える ORDER BY
列の結合 JOIN
四則演算、集合演算、数学演算、日付演算 UNION, ABS, DATEDIFF
値の追加、削除、更新 INSERT, UPDATE, DELETE
集計（集計関数の利用） COUNT, SUM, MAX
テーブル、ユーザ作成、権限の付与 CREATE, GRANT
Slide 20
Slide 20 text
Analytics Innovation Company ©BrainPad Inc. 19
1-3. 実業務におけるデータベース
• 受託分析ではデータ分析を開始するに当たって、最初にお客様からデータを受領する
1.データ 3.バリュー
2.受託分析会社
データ受領
お客様 お客様
データ分析
データ加工
• 分析処理
• 検索、フィルタリング、ソート、集計、結合など
• 管理
• 一元管理、統一的なアクセス
• 簡単に削除、変更されない
• 同時アクセスを可能、破壊されない、整合性
• アクセス権限の管理 など
データ基盤：RDB
操作：SQL
実現
Slide 21
Slide 21 text
Analytics Innovation Company ©BrainPad Inc. 20
2. SQLの基礎①
2-1. 4つの基本機能CRUD
2-2. WHERE(条件検索)
2-3. GROUP BY(グループ化), HAVING(絞り込み)
2-4. SQLの実行順序
Slide 22
Slide 22 text
Analytics Innovation Company ©BrainPad Inc. 21
2-1. 4つの基本機能CRUD
2-1-1. 4つの基本機能CRUDとSQL
2-1-2. SELECT① 特定フィールドの抽出
• コメントの書き方
• LIMIT
2-1-3. SELECT② COUNT, DISTINCT, AS
• 予約語
2-1-4. SELECT③ ORDER BY, SELECT *
2-1-5. コーディング規約(簡易版)
Slide 23
Slide 23 text
Analytics Innovation Company ©BrainPad Inc. 22
2-1-1. 4つの基本機能CRUDとSQL
• ほとんど全てのコンピュータが持つ4つの基本機能を、それぞれの頭文字をとって
CRUDという
• Create 生成
• Read 読取
• Update 更新
• Delete 削除
• 標準SQLとの対応
• Create 生成 → INSERT （データ挿入）
• Read 読取 → SELECT （データ取得）
• Update 更新 → UPDATE （データ更新）
• Delete 削除 → DELETE （データ削除）
• 本研修ではSELECTに関するSQLを中心に扱う
基本4機能のSQL
Slide 24
Slide 24 text
Analytics Innovation Company ©BrainPad Inc. 23
2-1-2. SELECT① 特定フィールドの抽出
receiptテーブルからid, user_id, paid_atの3つのフィールドに対して3件分の
データを取得する
/*
receiptテーブルからid, user_id, paid_atの3つの
フィールドに対して3件分のデータを取得する
*/
SELECT
id
,user_id
,paid_at
FROM
receipt
LIMIT
3;
例題
実行結果 レコード数 3
SQL
写経の際の注意点
• 大文字, 小文字を使い分ける
• インデントを下げる
• カンマを書く位置を統一する
※その他のコーディング規約は後述
Slide 25
Slide 25 text
Analytics Innovation Company ©BrainPad Inc. 24
2-1-2. SELECT① 特定フィールドの抽出
SELECTのイメージ
解説
receipt テーブル
id user_id … paid_at …
1
2
3
4
…
/*
receiptテーブルからid, user_id,
paid_atの3つのフィールドに対して
3件分のデータを取得する
*/
SELECT
id
,user_id
,paid_at
FROM
receipt
LIMIT
3;
id user_id paid_at
1
2
3
3件のみ
LIMIT 3
特定のフィールドのみ抽出
実行結果
SQL
Slide 26
Slide 26 text
Analytics Innovation Company ©BrainPad Inc. 25
2-1-2. SELECT① 特定フィールドの抽出
/*
receiptテーブルからid, user_id,
paid_atの3つのフィールドに対して
3件分のデータを取得する
*/
SELECT
id
,user_id
,paid_at
FROM
receipt
LIMIT
3;
解説
SELECT
• 「どの項目（列）のデータを取得するか」を指定
FROM
• 「どのテーブルから検索するか」を指定
コメント（SQLの動作に関与しないメモ）の書き方
• /* （コメント）*/ 複数行
• -- （コメント） 一行
LIMIT句
• 取得するデータの件数を制限
セミコロン ;
• SQLの最後に必ずセミコロンをつける ;
SQL
Slide 27
Slide 27 text
Analytics Innovation Company ©BrainPad Inc. 26
2-1-2. SELECT① 特定フィールドの抽出
receipt_itemテーブルから次のフィールドに対して2件分データを取得する
抽出フィールド：id, user_id, receipt_id, price
/*
receipt_itemテーブルから次のフィールドに対して2件分データを取得する
抽出フィールド：id, user_id, receipt_id, price
*/
類題
実行結果 レコード数 2
SQL
Slide 28
Slide 28 text
Analytics Innovation Company ©BrainPad Inc. 27
2-1-3. SELECT② COUNT, DISTINCT, AS
receiptテーブルのuser_idフィールドに対して、「重複を許した場合の件数」と「重複を除いた
場合の件数」をそれぞれ取得する
/*
receipt テーブルのuser_idフィールドに対して、
「重複を許した場合の件数」
「重複を除いた場合の件数」をそれぞれ取得する
*/
SELECT
COUNT(user_id) AS `user`
,COUNT(DISTINCT user_id) AS distinct_user
FROM
receipt;
例題
実行結果 レコード数 1
SQL
バッククォート
Slide 29
Slide 29 text
Analytics Innovation Company ©BrainPad Inc. 28
2-1-3. SELECT② COUNT, DISTINCT, AS
/*
receipt テーブルのuser_idフィールドに
対して、
「重複を許した場合の件数」
「重複を除いた場合の件数」
をそれぞれ取得する
*/
SELECT
COUNT(user_id)
AS `user`
,COUNT(DISTINCT user_id)
AS distinct_user
FROM
receipt;
解説
COUNT
• レコード（行）の件数を求める
DISTINCT
• 重複行を除外する
AS
• 列名やテーブル名に別名をつける
• ［列名］AS ［別名］
• ［テーブル名］ AS ［別名］
予約語
• SELECT, FROM, USERなどはSQLの機能として特別な意
味をもつため、そのままでは列名として使用できない
• 列名として使用するときは `（バッククォート）で囲む
※文字列は ‘ (シングルクォーテーション)で囲む
SQL
バッククォート
（注）Redshiftの場合は ” (ダブルクォーテーション)
Slide 30
Slide 30 text
Analytics Innovation Company ©BrainPad Inc. 29
2-1-3. SELECT② COUNT, DISTINCT, AS
receipt_itemテーブルのreceipt_idフィールドに対して「重複を許した場合の件数」と「重複
を除いた場合の件数」をそれぞれ取得し、列名を別名に変更する
※フィールド名は自分で適当なものをつける
/*
receipt_itemテーブルのreceipt_idフィールドに対して
「重複を許した場合の件数」
「重複を除いた場合の件数」をそれぞれ取得し、列名を別名に変更する
*/
類題
実行結果 レコード数 1
SQL
Slide 31
Slide 31 text
Analytics Innovation Company ©BrainPad Inc. 30
2-1-4. SELECT③ ORDER BY, SELECT *
userテーブルの全てのフィールドを5件分、データ取得する。
ただし、last_loginについて昇順に並び替えて表示する。
/*
user テーブルの全てのフィールドを5件分、データ取得する
idについて昇順に並び替えて表示する
*/
SELECT
*
FROM
`user`
ORDER BY
last_login
LIMIT
5;
例題
実行結果 レコード数 5
SQL
Slide 32
Slide 32 text
Analytics Innovation Company ©BrainPad Inc. 31
2-1-4. SELECT③ ORDER BY, SELECT *
/*
user_master テーブルの全てのフィール
ドを5件分、データ取得する
last_loginについて昇順に並び替えて表
示する
*/
SELECT
*
FROM
`user`
ORDER BY
last_login
LIMIT
5;
解説
*（アスタリスク）
• 全てのフィールドを選択する
ORDER BY
• 指定したフィールドで検索結果を並び替える
• ORDER BY ［フィールド名］ ASC 昇順
• ORDER BY ［フィールド名］ DESC 降順
• ASC/DESCを無指定の場合はASCがデフォルト
【参考】複数列で並び替えたい場合
• ［フィールド1］ ASC, ［フィールド2］ DESC…のようにカ
ンマで区切って指定する
• 上記の場合、「フィールド1で昇順に並び替え、フィールド1が同
じ値の場合にはフィールド2で降順で並び替える」
SQL
Slide 33
Slide 33 text
Analytics Innovation Company ©BrainPad Inc. 32
2-1-4. SELECT③ ORDER BY, SELECT *
receiptテーブルの全てのフィールドを5件分、データ取得する
ただし、 user_idの昇順、read_atの降順に並び替える
/*
receiptテーブルの全てのフィールドに5件分、データ取得する
ただし、 user_idの昇順、read_atの降順に並び替える
*/
類題
実行結果 レコード数 5
SQL
昇順 降順
Slide 34
Slide 34 text
Analytics Innovation Company ©BrainPad Inc. 33
2-1-5. コーディング規約
1. 大文字と小文字を使い分ける
大文字：予約語 SELECTなど
小文字：予約語以外 列名など
2. インデントを下げる
インデント幅は揃える
3. カンマを書く位置を統一する
最初に書くか、最後に書くか
4. 1行の長さが長くなりすぎないようにする
5. わかりやすいフィールド名、テーブル名をつける
何を意味しているかすぐわかるように
その他、より詳細なコーディング規約については各自調べてみてください。
SELECT
col_1
,col_2
,SUM(col_3) AS sum_col_3
FROM
table
ORDER BY
col_1
,col_2
,SUM(col_3) AS sum_col_3
;
SQL
Slide 35
Slide 35 text
Analytics Innovation Company ©BrainPad Inc. 34
2-2. WHERE(条件検索)
2-2-1. WHERE① 比較条件
• <, >, <=, >=, =, <>など
2-2-2. WHERE② 論理条件
• AND, OR, NOT
2-2-3. WHERE③ パターンマッチングLIKE
• パターン文字%, _(アンダースコア)
2-2-4. WHERE④ 範囲条件BETWEEN
2-2-5. WHERE⑤ IN条件
Slide 36
Slide 36 text
Analytics Innovation Company ©BrainPad Inc. 35
2-2-1. WHERE① 比較条件
userテーブルで、性別が1（男性）のid, gender, birthdayの3つのフィールドについて5件
分データ取得する。
/*
userテーブルで性別が1（男性）のid, gender, birthdayの
3つのフィールドについて5件分データ取得する
*/
SELECT
id
,gender
,birthday
FROM
`user`
WHERE
gender = 1
LIMIT
5;
例題
SQL
実行結果 レコード数 5
Slide 37
Slide 37 text
Analytics Innovation Company ©BrainPad Inc. 36
2-2-1. WHERE① 比較条件
/*
user_master テーブルで性別が1（男
性）のid, gender, birthdayの3つの
フィールドについて5件分データ取得する
*/
SELECT
id
,gender
,birthday
FROM
`user`
WHERE
gender = 1
LIMIT
5
;
解説
比較演算子
• 2つの値の間の論理的な関係を指定
• 日付の場合
例 「誕生日が1995年1月1日以降」
WHERE birthday >= ‘1995-01-01’
SQL
比較演算子 意味
>= 以上
<= 以下
> より大きい
< より小さい
= 等しい
<> , != 等しくない
Slide 38
Slide 38 text
Analytics Innovation Company ©BrainPad Inc. 37
2-2-1. WHERE① 比較条件
userテーブルで、誕生日が2006年1月1日以降の人のbirthday, genderの2つのフィール
ドについて5件分データ取得し、誕生日の昇順に並び替えて表示する。
/*
userテーブルで、誕生日が2006年1月1日以降の人のbirthday, genderの
2つのフィールドについて5件分データ取得し、誕生日の昇順に並び替えて表示する。
*/
類題
実行結果 レコード数 5
SQL
2006年1月1日以降
昇順
Slide 39
Slide 39 text
Analytics Innovation Company ©BrainPad Inc. 38
2-2-2. WHERE② 論理条件
userテーブルで、性別が1（男性）かつstate_codeが’13’（東京）である人の総数を取
得する
/*
userテーブルで、性別が1（男性）かつstate_codeが’13’（東京）である人の総数を取得する。
*/
SELECT
COUNT(*)
FROM
`user`
WHERE
gender = 1
AND state_code = ’13’
;
例題
実行結果 レコード数 1
SQL
バッククォート
シングルクォーテーション（コピペ時は注意！）
Slide 40
Slide 40 text
Analytics Innovation Company ©BrainPad Inc. 39
2-2-2. WHERE② 論理条件
/*
userテーブルで、性別が1（男性）かつ
state_codeが’13’（東京）である人の
総数を取得する。
*/
SELECT
COUNT(*)
FROM
`user`
WHERE
gender = 1
AND state_code = ’13’
;
解説
論理演算子
• A AND B （AかつB）
• 2つの条件式の両方が真の場合だけ、真となる
• A OR B（AまたはB）
• 2つの条件式のどちらかが真ならば、真となる
AND, ORの優先順位
• AND → ORの順に処理される
例 A OR B AND C
① B AND Cが処理される（＝結果）
② A OR （結果）が処理される（＝最終結果）
※(A OR B) AND C のように括弧で囲むとORが優先される
SQL
バッククォート
シングルクォーテーション
（コピペ時は注意！）
Slide 41
Slide 41 text
Analytics Innovation Company ©BrainPad Inc. 40
2-2-2. WHERE② 論理条件
userテーブルで、「性別が2（女性）」かつ「state_codeが’13’（東京）または’27’（大
阪）」である人の総数を取得する
/*
userテーブルで、「性別が2（女性）」かつ「state_codeが’13’（東京）または’27’（大阪）」
である人の総数を取得する
*/
類題
実行結果 レコード数 1
SQL
Slide 42
Slide 42 text
Analytics Innovation Company ©BrainPad Inc. 41
2-2-3. WHERE③ パターンマッチングLIKE
receiptテーブルで、shop_nameに文字列「コンビニ」を含むデータについて、全てのフィールド
を5件分取得する。
/*
receiptテーブルで、shop_nameに「コンビニ」を含むデータについて、全てのフィールドを5件分取得する。
*/
SELECT
*
FROM
receipt
WHERE
shop_name LIKE ‘%コンビニ%’
LIMIT
5;
例題
SQL
実行結果 レコード数 5
「コンビニ」を含む
シングルクォーテーション（コピペ時は注意！）
Slide 43
Slide 43 text
Analytics Innovation Company ©BrainPad Inc. 42
2-2-3. WHERE③ パターンマッチングLIKE
/*
receiptテーブルで、shop_nameに「コン
ビニ」を含むデータについて、全てのフィール
ドを5件分取得する。
*/
SELECT
*
FROM
receipt
WHERE
shop_name
LIKE ‘%コンビニ%’
LIMIT
5;
解説
LIKE
• 文字列があるパターンに合致しているかをチェック
• 部分一致の検索が簡単に行える
%を用いたパターン例
%や_を含む文字列をLIKEで探したいとき
• ESCAPE句を用いる（詳細は各自調べてください）
SQL
パターン文字 意味
% 任意の0文字以上の文字列
_（アンダースコア） 任意の1文字
%○○% 「○○」を含む文字列
%○○ 「○○」で終わる文字列
○○% 「○○」で始まる文字列 シングルクォーテーション
（コピペ時は注意！）
Slide 44
Slide 44 text
Analytics Innovation Company ©BrainPad Inc. 43
2-2-3. WHERE③ パターンマッチングLIKE
receiptテーブルで、shop_nameが文字列「B」で終わるデータについて、全てのフィールドを5
件分取得する
/*
receiptテーブルで、shop_nameが文字列「B」で終わるデータについて、
全てのフィールドを5件分取得する
*/
類題
実行結果 レコード数 5
SQL
「B」で終わる
Slide 45
Slide 45 text
Analytics Innovation Company ©BrainPad Inc. 44
2-2-4. WHERE④ 範囲条件BETWEEN
userテーブルで、last_loginが2010年1月1日から2015年12月31日までであるデータにつ
いて、id, gender, last_loginを取得する。ただし、last_loginの昇順に並び替える。
/*
userテーブルで、last_loginが2010年1月1日から2015年12月31日までであるデータについて、
id, gender, last_loginのフィールドを取得する。ただし、last_loginの昇順に並び替える。
*/
SELECT
id
,gender
,last_login
FROM
`user`
WHERE
last_login BETWEEN ‘2010-01-01’ AND ‘2015-12-31’
ORDER BY
last_login;
例題
実行結果 レコード数416
SQL
Slide 46
Slide 46 text
Analytics Innovation Company ©BrainPad Inc. 45
2-2-4. WHERE④ 範囲条件BETWEEN
/*
userテーブルで、last_loginが2010年1
月1日から2015年12月31日までである
データについて、
id, gender, last_loginのフィールドを取
得する。ただし、last_loginの昇順に並び
替える。
*/
SELECT
id
,gender
,last_login
FROM
`user`
WHERE
last_login
BETWEEN ‘2010-01-01’
AND ‘2015-12-31’
ORDER BY
last_login;
解説
BETWEEN
• BETWEEN 値1 AND 値2
• ある範囲内に値が収まっているか判定する
• 指定した範囲の境界は含まれるので注意
例 BETWEEN 100 AND 3000
→ 100以上 3000以下
BETWEENと比較演算子>=, <=
• 例 「priceが100以上、3000以下」
WHERE price BETWEEN 100 AND 3000
WHERE price >= 100 AND price <= 3000
SQL
Slide 47
Slide 47 text
Analytics Innovation Company ©BrainPad Inc. 46
2-2-4. WHERE④ 範囲条件BETWEEN
receiptテーブルで、paid_atが2010年1月1日から2010年12月31日までであるデータにつ
いて、id, user_id, paid_atを5件分取得する。ただし、paid_atの昇順に並び替える。
/*
receiptテーブルで、paid_atが2010年1月1日から2010年12月31日までであるデータについて、
id, user_id, paid_atを5件分取得する。ただし、paid_atの昇順に並び替える。
*/
類題
SQL
実行結果 レコード数5
Slide 48
Slide 48 text
Analytics Innovation Company ©BrainPad Inc. 47
2-2-5. WHERE⑤ IN条件
userテーブルでstate_codeが’13’（東京）または’27’（大阪）である行をカウントする
/*
userテーブルでstate_codeが’13’（東京）または’27’（大阪）である行をカウントする
*/
SELECT
COUNT(*)
FROM
`user`
WHERE
state_code IN (‘13’, ’27’)
;
例題
実行結果 レコード数 1
SQL
Slide 49
Slide 49 text
Analytics Innovation Company ©BrainPad Inc. 48
2-2-5. WHERE⑤ IN条件
/*
userテーブルでstate_codeが’13’（東
京）または’27’（大阪）である行をカウ
ントする
*/
SELECT
COUNT(*)
FROM
`user`
WHERE
state_code IN ( ‘13’, ’27’)
;
解説
IN
• IN (値1, 値2, 値3, …)
• ある値が列挙した値のどれかと等しいかを判定
NOT IN
• NOT IN (値1, 値2, 値3, …)
• ある値が列挙した値のどれとも一致しないことを判定
【参考】ANY / ALL演算子（詳細は各自調べてください）
• 比較演算子 ANY (値1, 値2, 値3, …)
• 値リストとそれぞれ比較して、いずれかが真なら真
• 比較演算子 ALL (値1, 値2, 値3, …）
• 値リストとそれぞれ比較して、すべて真なら真
SQL
Slide 50
Slide 50 text
Analytics Innovation Company ©BrainPad Inc. 49
2-2-5. WHERE⑤ IN条件
receipt_itemテーブルで、nameが文字列「まぐろ」「りんご」であるデータについて、id,
user_id, receipt_id, nameを5件取得する。ただし、idの昇順に並べる。
/*
receipt_itemテーブルで、nameが文字列「まぐろ」「りんご」であるデータについて、
id, user_id, receipt_id, nameを5件取得する。ただし、idの昇順に並べる。
*/
類題
実行結果 レコード数 5
SQL
Slide 51
Slide 51 text
Analytics Innovation Company ©BrainPad Inc. 50
2-3. GROUP BY
2-3-1. GROUP BY① 集計関数
• COUNT, SUM, AVG, MAX, MINなど
2-3-2. GROUP BY② HAVING 集計結果に条件指定
• WHEREとHAVINGの違い
• ROUND
Slide 52
Slide 52 text
Analytics Innovation Company ©BrainPad Inc. 51
2-3-1. GROUP BY① 集計関数
receipt_itemテーブルで、receipt_id毎にグループ化したレコードに対して、
レコード数と、金額priceの合計値・平均値・最大値を求める。ただし、receipt_idが’100’
と’1000’のデータを対象とし、レコード数の昇順に並び替えて表示する。
/*
receipt_itemテーブルで、receipt_id毎にグループ化した
レコードに対して、レコード数と、金額priceの合計値・
平均値・最大値を求める。
ただし、receipt_idが’100’と’1000’のデータを対象とし、レコード数
の昇順に並び替えて表示する*/
SELECT
receipt_id
,COUNT(*) AS `count`
,SUM(price) AS sum_price
,AVG(price) AS avg_price
,MAX(price) AS max_price
FROM
receipt_item
WHERE
receipt_id IN (100, 1000)
GROUP BY
receipt_id
ORDER BY
`count`
;
例題
実行結果 レコード数 2
SQL
Slide 53
Slide 53 text
Analytics Innovation Company ©BrainPad Inc. 52
2-3-1. GROUP BY① 集計関数
GROUP BYの集計イメージ
解説
receipt_item テーブル
… receipt_id … price …
1 100
1 200
2 300
2 400
2 500
…
…
/*
receipt_id毎にグループ化した
レコードに対して、レコード数と、金額
priceの合計値・平均値・最大値を求め
る
*/
SELECT
receipt_id
,COUNT(*) AS `count`
,SUM(price) AS sum_price
,AVG(price) AS avg_price
,MAX(price) AS max_price
FROM
receipt_item
WHERE
receipt_id IN (100, 1000)
GROUP BY
receipt_id
ORDER BY
`count`;
receipt_id count sum_price avg_price max_price
1 2 300 150 200
2 3 1200 400 500
…
receipt_id毎で集計
実行結果
SQL
Slide 54
Slide 54 text
Analytics Innovation Company ©BrainPad Inc. 53
2-3-1. GROUP BY① 集計関数 解説
集計関数
グループ集計においてSELECTで指定できるもの
1. GROUP BYで指定されている基準列
2. 集計関数の集計対象
SQL
集計関数 意味
COUNT レコード数
SUM 合計値
AVG 平均値
MAX 最大値
MIN 最小値
VARIANCE 分散
STDDEV 標準偏差
/*
receipt_id毎にグループ化した
レコードに対して、レコード数と、金額
priceの合計値・平均値・最大値を求め
る
*/
SELECT
receipt_id
,COUNT(*) AS `count`
,SUM(price) AS sum_price
,AVG(price) AS avg_price
,MAX(price) AS max_price
FROM
receipt_item
WHERE
receipt_id IN (100, 1000)
GROUP BY
receipt_id
ORDER BY
`count`;
Slide 55
Slide 55 text
Analytics Innovation Company ©BrainPad Inc. 54
2-3-1. GROUP BY① 集計関数
receipt_itemテーブルで、receipt_id毎にグループ化したレコードの金額priceの最小値を
求め、5件分表示する。ただし、receipt_idの昇順に並べる。
/*
receipt_itemテーブルで、receipt_id毎にグループ化したレコードの金額priceの
最小値を求め、5件分表示する。ただし、receipt_idの昇順に並べる。
*/
類題
実行結果 レコード数 5
SQL
Slide 56
Slide 56 text
Analytics Innovation Company ©BrainPad Inc. 55
2-3-2. GROUP BY② HAVING 集計結果に条件指定
receipt_itemテーブルで、receipt_id毎にグループ化し、金額priceの平均値を求め、その
平均値が500であるものを5件取得する。ただし、receipt_idの昇順に並べる。
/*
receipt_itemテーブルで、receipt_id毎にグループ化し、金額
priceの平均値を求め、その平均値が500であるものを5件取得する。
*/
SELECT
receipt_id
,ROUND(AVG(price), 0) AS avg_price
FROM
receipt_item
WHERE
price >= 0
GROUP BY
receipt_id
HAVING
AVG(price) = 500
ORDER BY
receipt_id
LIMIT
5;
例題
実行結果 レコード数 5
SQL
Slide 57
Slide 57 text
Analytics Innovation Company ©BrainPad Inc. 56
2-3-2. GROUP BY② HAVING 集計結果に条件指定
HAVINGのイメージ
• HAVINGは集計結果に対して抽出条件を指定する
解説
receipt_item テーブル
… receipt_id … price …
1 300
1 700
2 3000
2 400
2 500
/*
receipt_itemテーブルで、receipt_id毎に
グループ化し、金額priceの平均値を求め、そ
の平均値が500であるものを5件取得する。
*/
SELECT
receipt_id
,ROUND(AVG(price), 0)
AS avg_price
FROM
receipt_item
WHERE
price >= 0
GROUP BY
receipt_id
HAVING
AVG(price) = 500
ORDER BY
receipt_id
LIMIT 5;
receipt_id avg_price
1 500
2 1300
①集計
GROUP BY
SQL
receipt_id avg_price
1 500
②抽出
HAVING
500である
ものを抽出
実行結果
Slide 58
Slide 58 text
Analytics Innovation Company ©BrainPad Inc. 57
2-3-2. GROUP BY② HAVING 集計結果に条件指定
/*
receipt_itemテーブルで、receipt_id毎に
グループ化し、金額priceの平均値を求め、そ
の平均値が500であるものを5件取得する。
*/
SELECT
receipt_id
,ROUND(AVG(price), 0)
AS avg_price
FROM
receipt_item
WHERE
price >= 0
GROUP BY
receipt_id
HAVING
AVG(price) = 500
ORDER BY
receipt_id
LIMIT 5;
解説
ROUND
• ROUND(値, 桁数)
• 指定した桁数に数値を丸める（四捨五入）
• 似た関数にFLOOR（切り捨て）,CEILING（切り上げ）も
あるので詳細は各自調べてください
WHEREとHAVINGの違い
• WHERE
• もとの表に対する絞り込み条件
• グループ化する前の検索条件
• HAVING
• 集計結果に対する絞り込み条件
• グループ化した後の抽出条件
SQL
例 12.15を丸める(四捨五入) 結果
ROUND(12.15, 1) 小数第1位までの数に丸める 12.2
ROUND(12.15, 0) 最も近い整数に丸める 12
ROUND(12.15, -1) 一の位を丸める 10
Slide 59
Slide 59 text
Analytics Innovation Company ©BrainPad Inc. 58
2-3-2. GROUP BY② HAVING 集計結果に条件指定
/*
receipt_itemテーブルで、receipt_id毎に
グループ化し、金額priceの平均値を求め、そ
の平均値が500であるものを5件取得する。
*/
SELECT
receipt_id
,ROUND(AVG(price), 0)
AS avg_price
FROM
receipt_item
WHERE
price >= 0
GROUP BY
receipt_id
HAVING
AVG(price) = 500
ORDER BY
receipt_id
LIMIT 5;
解説
WHEREとHAVINGを含む集計基本構文
SELECT
グループ化の基準列名…, 集計関数
FROM
テーブル名
WHERE
もとの表に対する絞り込み条件
GROUP BY
グループ化の基準列名
HAVING
集計結果に対する絞り込み条件
;
SQL
①検索
②集計
③抽出
Slide 60
Slide 60 text
Analytics Innovation Company ©BrainPad Inc. 59
2-3-2. GROUP BY② HAVING 集計結果に条件指定
receipt_itemテーブルで、receipt_id毎にグループ化し、レコードの金額priceに対して最大
値を求め、最大値が1000以上のものを5件取得する。ただし、receipt_idの昇順に並び替
えて表示する。
/*
receipt_itemテーブルで、receipt_id毎にグループ化し、レコードの金額priceに対して最大値を求め、最大値が1000以上のものを5件取
得する。ただし、receipt_idの昇順に並び替えて表示する。
*/
類題
実行結果 レコード数 5
SQL
Slide 61
Slide 61 text
Analytics Innovation Company ©BrainPad Inc. 60
2-4. SQLの実行順序
実行順序
1. FROMで処理対象テーブルを選択
2. WHEREによる絞り込み
3. GROUP BYによるグループ化
4. HAVINGによる絞り込み
5. SELECT
6. ORDER BYによるソート
7. LIMITによる絞り込み
• 実行順序を理解することは、SQLのパフォー
マンス向上や、複雑なクエリを作成する上で
重要となる
SELECT ⑤
receipt_id
,ROUND(AVG(price), 0) AS avg_price
FROM ①
receipt_item
WHERE ②
price >= 0
GROUP BY ③
receipt_id
HAVING ④
AVG(price) = 500
ORDER BY ⑥
receipt_id
LIMIT 5; ⑦
SQL
Slide 62
Slide 62 text
Analytics Innovation Company ©BrainPad Inc. 61
3. SQLの基礎②
3-1. 条件式
3-2. データ型と型変換
3-3. 文字列関数
3-4. 日付関数
Slide 63
Slide 63 text
Analytics Innovation Company ©BrainPad Inc. 62
3-1. 条件式
3-1-1. 条件式① CASE
3-1-2. 条件式② COALESCE
IS NULL, IS NOT NULL
Slide 64
Slide 64 text
Analytics Innovation Company ©BrainPad Inc. 63
3-1-1. 条件式① CASE
receipt_itemテーブルで、priceを次の条件に従ってカテゴリを分ける。ただし、idの昇順に並べる。
① 0円〜800円：low_price ② 800円〜1500円：middle_price ③ 1500円以上：high_price
/*
receipt_itemテーブルで、priceを次の条件に従ってカテゴリを分ける。
① 0円〜800円：low_price
② 800円〜1500円：middle_price
③ 1500円以上： high_price
*/
SELECT
id
,price
,CASE
WHEN price <= 800 THEN ‘low’
WHEN price <= 1500 THEN ‘middle’
ELSE ’high’
END AS price_level
FROM
receipt_item
WHERE
price >= 0
ORDER BY
id
LIMIT
10;
例題
実行結果 レコード数 10
SQL
Slide 65
Slide 65 text
Analytics Innovation Company ©BrainPad Inc. 64
3-1-1. 条件式① CASE
CASE
• 条件分岐で値を変換する
• 基本構文
CASE WHEN 条件1 THEN 返す値
WHEN 条件2 THEN 返す値…
ELSE 上記に合致しないときに返す値
END （AS 別名）
• フラグ（条件該当の印）とCASE
例 「state_codeが’13’（東京）のとき1, 他は0」
CASE WHEN state_code = ‘13’ THEN 1
ELSE 0
END
解説
/*
receipt_itemテーブルで、priceを次の条件に
従ってカテゴリを分ける。
① 0円〜800円：low_price
② 800円〜1500円：middle_price
③ 1500円以上：high_price
*/
SELECT
id
,price
,CASE
WHEN price <= 800
THEN ‘low’
WHEN price <= 1500
THEN ‘middle’
ELSE ’high’
END AS price_level
FROM
receipt_item
（以下省略）
SQL
Slide 66
Slide 66 text
Analytics Innovation Company ©BrainPad Inc. 65
3-1-1. 条件式① CASE
userテーブルでstate_codeが’27’（大阪）であるユーザーの総数を求める。
（state_codeが’27’であるとき1、他は0のフラグを作成して合計する）
/*
userテーブルでstate_codeが’27’（大阪）であるユーザーの総数を求める。
（state_codeが’27’であるとき1、他は0のフラグを作成して合計する）
*/
類題
実行結果 レコード数 1
SQL
Slide 67
Slide 67 text
Analytics Innovation Company ©BrainPad Inc. 66
3-1-1. 条件式① CASE
userテーブルでstate_codeが’27’（大阪）であるユーザーの総数を求める。
【別解】COUNT, DISTINCT, CASEを組み合わせた方法
/*
userテーブルでstate_codeが’27’（大阪）であるユーザーの総数を求める。
【別解】COUNT, DISTINCT, CASEを組み合わせた方法
*/
SELECT
COUNT(DISTINCT CASE WHEN state_code = ‘27’ THEN id ELSE NULL END) AS `sum`
FROM
`user`
;
補足
実行結果 レコード数 1
SQL
処理①
state_code=’27’：id
それ以外：NULL
処理②
重複を除外してidの総数を集計
※NULLはカウントされない
※今回はidの重複はないため省略
しても良い
NULLとは
何も格納されていない、未定義
0や空白文字とも異なる
Slide 68
Slide 68 text
Analytics Innovation Company ©BrainPad Inc. 67
3-1-2. 条件式② COALESCE
NULLをもつフィールド`null`と、NULLを文字列‘不明’に置換したフィールド`co_null`を1件取得する。（テー
ブルは不要なので、FROM句も不要）
/*
NULLをもつフィールド`null`と、NULLを文字列‘不明’に置換したフィール
ド`co_null`を取得する。
*/
SELECT
NULL AS `null`
,COALESCE(NULL, ‘不明’) AS co_null
;
例題
実行結果 レコード数14
SQL
Slide 69
Slide 69 text
Analytics Innovation Company ©BrainPad Inc. 68
3-1-2. 条件式② COALESCE
NULLとは
• 何も格納されていない、未定義
• 0や空白文字とも異なる
NULLの判定
• IS NULL / IS NOT NULL
• 比較演算子=ではNULLの判定はできないので注意！
COALESCE（コアレス）
• 引数のうち、最初に現れたNULLでない引数を返す
• COALESCE(value, 0)
• value列の値がNULLのとき、代わりに0を入れる
解説
/*
NULLをもつフィールド`null`と、NULLを文字列
‘不明’に置換したフィールド`co_null`を取得する。
*/
SELECT
NULL AS `null`
,COALESCE(NULL, ‘不明’) AS co_null
;
SQL
COALESCEの例 返り値
COALESCE(NULL, 1, 2) 1
COALESCE(NULL, 1, NULL) 1
COALESCE(NULL, NULL, 2) 2
NULLを‘不明’に置換
Slide 70
Slide 70 text
Analytics Innovation Company ©BrainPad Inc. 69
3-1-2. 条件式② COALESCE
NULLをもつフィールド`null`と、NULLを整数0に置換したフィールド`co_null`を1件取得する。（テーブルは
不要なので、FROM句も不要）
/*
NULLをもつフィールド`null`と、NULLを整数0に置換したフィールド`co_null`を取得する。
*/
類題
実行結果 レコード数 1
SQL
NULLを0に置換
Slide 71
Slide 71 text
Analytics Innovation Company ©BrainPad Inc. 70
3-2. データ型と型変換
3-2-1. 代表的なデータ型
数値型
文字列型
日付型
3-2-2. 型変換 CAST
timestamp型 → date型
varchar型 → date型
Slide 72
Slide 72 text
Analytics Innovation Company ©BrainPad Inc. 71
3-2-1. 代表的なデータ型
• データ型はデータ加工や集計操作で失敗しやすい
• エラーが出たときに確認するポイント
• 「データ型の定義がきちんと行われているか」
• 「関数の引数に正しいデータ型を用いているか」
数値型
int
tinyint
bigint
符号付き整数
float
real
浮動小数点数値
文字列型
char(n) 固定長の文字列（nはバイト数）
varchar(n) 可変長の文字列（nはバイト数）
日付型
date 日付
timestamp 日付時刻
論理値型 boolean 真（True）か偽（False）
Slide 73
Slide 73 text
Analytics Innovation Company ©BrainPad Inc. 72
3-2-2. 型変換 CAST
receiptテーブルで、paid_atをtimestamp型→date型に変換(cast_paid_at)する。
・抽出フィールド：id, paid_at, cast_paid_at
・idの昇順で5件分表示
/*
receiptテーブルで、paid_atをtimestamp型→date型に変換(cast_paid_at)する。
・抽出フィールド：id, paid_at, cast_paid_at
・idの昇順で5件分表示
*/
SELECT
id
,paid_at
,CAST(paid_at AS DATE) AS cast_paid_at
FROM
receipt
ORDER BY
id
LIMIT
5;
例題
実行結果 レコード数5
SQL
Slide 74
Slide 74 text
Analytics Innovation Company ©BrainPad Inc. 73
3-2-2. 型変換 CAST
CAST
• データ型を変換する
• CAST(フィールド名 AS 変換後のデータ型)
文字列varchar型 → date型の変換
SELECT
‘20180402’ AS v_date
,CAST(‘20180402’ AS DATE) AS d_date ;
解説
/*
receiptテーブルで、paid_atをtimestamp型
→date型に変換(cast_paid_at)する。
・抽出フィールド：id, paid_at, cast_paid_at
・idの昇順で5件分表示
*/
SELECT
id
,paid_at
,CAST(paid_at AS DATE)
AS cast_paid_at
FROM
receipt
ORDER BY
id
LIMIT
5;
SQL
実行結果 レコード数1
varchar型 date型
Slide 75
Slide 75 text
Analytics Innovation Company ©BrainPad Inc. 74
3-2-2. 型変換 CAST
userテーブルで、create_dateをtimestamp型→ date型に変換する。
・抽出フィールド：id, create_date, 型変換後のcreate_date
・ idの昇順で5件分表示
/*
userテーブルで、create_dateをtimestamp型→date型に変換する。
・抽出フィールド：id, create_date, 型変換後のcreate_date
・idの昇順で5件分表示
*/
類題
実行結果 レコード数 5
SQL
Slide 76
Slide 76 text
Analytics Innovation Company ©BrainPad Inc. 75
3-3. 文字列関数
3-3-1. 文字列関数① SUBSTRING
3-2-2. 文字列関数② REPLACE, LENGTH
Slide 77
Slide 77 text
Analytics Innovation Company ©BrainPad Inc. 76
3-3-1. 文字列関数① SUBSTRING
receiptテーブルで、paid_atから西暦部分を取り出す(paid_yearとする)。
・抽出フィールド：id, paid_at, paid_year
・idの昇順で5件分表示
/*
receiptテーブルで、paid_atから西暦部分を取り出す(paid_yearとする)。
・抽出フィールド：id, paid_at, paid_year
・idの昇順で5件分表示
*/
SELECT
id
,paid_at
,SUBSTRING(paid_at, 1, 4) AS paid_year
FROM
receipt
ORDER BY
id
LIMIT
5;
例題
実行結果 レコード数5
SQL
文字列
Slide 78
Slide 78 text
Analytics Innovation Company ©BrainPad Inc. 77
3-3-1. 文字列関数① SUBSTRING
SUBSTRING
• 文字列の一部分だけを取り出す
• SUBSTRING(文字列, 開始位置, 抽出文字数)
例 文字列’20180402’の5文字目から2文字分を抽出
SELECT
SUBSTRING(‘20180402’, 5, 2) AS month;
※DBMS製品によって、文字数指定かバイト数指定か異なる
解説
/*
receiptテーブルで、paid_atから西暦部分を取り
出す(paid_yearとする)。
・抽出フィールド：id, paid_at, paid_year
・idの昇順で5件分表示
*/
SELECT
id
,paid_at
,SUBSTRING(paid_at, 1, 4)
AS paid_year
FROM
receipt
ORDER BY
id
LIMIT
5;
SQL
実行結果 レコード数1
’20180402’
5文字目
2文字分
Slide 79
Slide 79 text
Analytics Innovation Company ©BrainPad Inc. 78
3-3-1. 文字列関数① SUBSTRING
userテーブルで、last_loginからを月を取り出す(last_login_monthとする)。
・抽出フィールド：id, last_login, last_login_month
・idの昇順で5件分表示
/*
userテーブルで、last_loginからを月を取り出す(last_login_monthとする)。
・抽出フィールド：id, last_login, last_login_month
・idの昇順で5件分表示
*/
類題
実行結果 レコード数 5
SQL
Slide 80
Slide 80 text
Analytics Innovation Company ©BrainPad Inc. 79
3-3-2. 文字列関数② LENGTH, REPLACE
receiptテーブルでshop_nameの文字数を取得する(length_shop_nameとする)。
また、shop_nameの ’スーパー’ を ’デパート’ に置換する(replace_shop_nameとする)。
・抽出フィールド：id, shop_name, length_shop_name, replace_shop_name
・idの昇順で5件分表示
/*
receiptテーブルでshop_nameの文字数を取得する(length_shop_nameとする)。
また、shop_nameの ’スーパー’ を ’デパート’ に置換する(replace_shop_nameとする)。
・抽出フィールド：id, shop_name, length_shop_name, replace_shop_name
・idの昇順で5件分表示
*/
SELECT
id
,shop_name
,LENGTH(shop_name) AS length_shop_name
,REPLACE(shop_name, ‘スーパー’, ‘デパート’ ) AS replace_shop_name
FROM
receipt
ORDER BY
id
LIMIT
5;
例題
実行結果 レコード数5
SQL
Slide 81
Slide 81 text
Analytics Innovation Company ©BrainPad Inc. 80
3-3-2. 文字列関数② LENGTH, REPLACE
LENGTH
• 文字列の長さを取得する
• LENGTH(文字列)
REPLACE
• 文字列の一部を別の文字列に置換する
• REPLACE(文字列, 置換前の文字列, 置換後の文字列)
• 例 shop_nameの ’スーパー’ を ’デパート’ に置換
REPLACE(shop_name, ‘スーパー’, ‘デパート’)
※DBMS製品によって、結果が文字数かバイト数か異なる
解説
/*
receiptテーブルでshop_nameの文字数を取得する
(length_shop_nameとする)。
また、shop_nameの ’スーパー’ を ’デパート’ に置換
する(replace_shop_nameとする)。
・抽出フィールド：id, shop_name,
length_shop_name, replace_shop_name
・idの昇順で5件分表示
*/
SELECT
id
,shop_name
,LENGTH(shop_name)
AS length_shop_name
,REPLACE(shop_name,
‘スーパー’, ‘デパート’ )
AS replace_shop_name
FROM
receipt
ORDER BY
id
LIMIT
5;
SQL
置換
Slide 82
Slide 82 text
Analytics Innovation Company ©BrainPad Inc. 81
3-3-2. 文字列関数② LENGTH, REPLACE
receipt_itemテーブルでnameの文字数を取得する(length_nameとする)。
また、nameの ’スナック’ を ’ポテトチップス’ に置換する(replace_nameとする)。
・抽出フィールド：id, name, length_name, replace_name
・idの昇順で5件分表示
/*
receipt_itemテーブルでnameの文字数を取得する(length_nameとする)。
また、nameの ’スナック’ を ’ポテトチップス’ に置換する(replace_nameとする)。
抽出フィールド：id, name, length_name, replace_name ※idの昇順で5件分表示
*/
類題
実行結果 レコード数 5
SQL
Slide 83
Slide 83 text
Analytics Innovation Company ©BrainPad Inc. 82
3-4. 日付関数
3-4-1. 日付関数
CURRENT_DATE
DATEDIFF
3-4-2. 誕生日から年齢を求める
Slide 84
Slide 84 text
Analytics Innovation Company ©BrainPad Inc. 83
3-4-1. 日付関数 CURRENT_DATE, DATEDIFF
receiptテーブルで、現在の日付(current_dateとする)、
及びpaid_atの日付から2018年04月02日までの日数を計算する(date_diffとする)。
・抽出フィールド：id, paid_at, current_date, date_diff
・idの昇順で5件分表示
/*
receiptテーブルで、現在の日付(current_dateとする)、
及びpaid_atの日付から2018年04月02日までの日数を計算する(date_diffとする)。
・抽出フィールド：id, paid_at, current_date, date_diff
・idの昇順で5件分表示
*/
SELECT
id
,CAST(paid_at AS DATE)
,CURRENT_DATE AS `current_date`
,DATEDIFF(CAST(’2018-04-02’ AS DATE), paid_at) AS date_diff
FROM
receipt
ORDER BY
id
LIMIT
5;
例題
SQL
実行結果 レコード数5
実行日が格納される
Slide 85
Slide 85 text
Analytics Innovation Company ©BrainPad Inc. 84
3-4-1. 日付関数 CURRENT_DATE, DATEDIFF
CURRENT_DATE
• 現在の日付を取得する
DATEDIFF
• 2つの日付または時刻式の日付部分の差を返す
• DATEDIFF(日付①, 日付②)
• 日付①-日付②を返す
• RedshiftのDATEDIFFと少し異なります
解説
/*
receiptテーブルで、現在の日付(current_date
とする)、
及びpaid_atの日付から2018年04月02日まで
の日数を計算する(date_diffとする)。
・抽出フィールド：id, paid_at, current_date,
date_diff
・idの昇順で5件分表示
*/
SELECT
id
,CAST(paid_at AS DATE)
,CURRENT_DATE
AS `current_date`
,DATEDIFF(paid_at,
CAST(’2018-04-02’ AS DATE))
AS date_diff
FROM
receipt
ORDER BY
id
LIMIT
5;
SQL
Slide 86
Slide 86 text
Analytics Innovation Company ©BrainPad Inc. 85
3-4-1. 日付関数 CURRENT_DATE, DATEDIFF
userテーブルで、birthdayの日付から現在の日付までの日数を計算する(date_diffとする)。
・抽出フィールド：id, birthday, current_date, date_diff
・idの昇順で5件分表示
/*
userテーブルで、birthdayの日付から現在の日付までの日数を計算する(date_diffとする)。
・抽出フィールド：id, birthday, current_date, date_diff
・idの昇順で5件分表示
*/
類題
実行結果 レコード数 5
SQL
実行日によって結果が異なります
Slide 87
Slide 87 text
Analytics Innovation Company ©BrainPad Inc. 86
3-4-2. 誕生日から年齢を求める
userテーブルのbirthdayから、現在の日付におけるユーザーの年齢を求める。
・抽出フィールド：id, current_date, birthday, age
・idの昇順で5件分表示
/*
userテーブルのbirthdayから、現在の日付におけるユーザーの年齢を求め
る。
・抽出フィールド：id, current_date, birthday, age
・idの昇順で5件分表示
*/
SELECT
id
,CURRENT_DATE AS `current_date`
,birthday
,TRUNCATE((CAST(REPLACE(CURRENT_DATE, ‘-’, ‘’)
AS UNSIGNED)
- CAST(REPLACE(birthday, ‘-’, ‘’)
AS UNSIGNED)
) / 10000, 0) AS `age`
FROM
`user`
ORDER BY
id
LIMIT
5;
例題
実行結果 レコード数5
SQL
Slide 88
Slide 88 text
Analytics Innovation Company ©BrainPad Inc. 87
3-4-2. 誕生日から年齢を求める
誕生日から年齢を求める手順
1. REPLACE(日付, ‘-’, ‘’)
現在日付と誕生日について、yyyy-mm-dd形式のハイフン’-’
を空文字’’に置換し、yyyymmdd形式に直す
2. CAST(yyyymmdd AS UNSIGNED)
整数型(INTEGER)に変換
3. 差をとって10000で割り、小数部分切り捨て
切り捨てはTRUNCATE(数値 , 0)
例. 現在日付2018-04-24, 誕生日1995-06-21
(20180424 – 19950621) / 10000
=22.98 → 22歳
※この方法でなぜ年齢を求めることができるのかについては、各自
調べてください（年齢を求めるAGE関数がサポートされているDB製
品もあります）
解説
/*
userテーブルのbirthdayから、現在の日付におけ
るユーザーの年齢を求める。
・抽出フィールド：id, current_date, birthday,
age
・idの昇順で5件分表示
*/
SELECT
id
,CURRENT_DATE
AS `current_date`
,birthday
,TRUNCATE(
(CAST(REPLACE(CURRENT_DATE,
‘-’, ‘’) AS UNSIGNED)
- CAST(REPLACE(birthday, ‘-’,
‘’)AS UNSIGNED))
/ 10000, 0) AS `age`
…以下省略
SQL
Slide 89
Slide 89 text
Analytics Innovation Company ©BrainPad Inc. 88
3-4-2. 誕生日から年齢を求める
userテーブルのbirthdayから、最終ログインの日付におけるユーザーの年齢を求める。
・抽出フィールド：id, last_login, birthday, age_at_last_login
・idの昇順で5件分表示
/*
userテーブルのbirthdayから、最終ログインの日付におけるユーザーの年齢を求める。
・抽出フィールド：id, last_login, birthday, age_at_last_login
・idの昇順で5件分表示
*/
類題
実行結果 レコード数 5
SQL
Slide 90
Slide 90 text
Analytics Innovation Company ©BrainPad Inc. 89
4. SQLの応用
4-1. サブクエリ（副問い合わせ）
4-2. テーブルの結合
4-3. 一時テーブルの作成
Slide 91
Slide 91 text
Analytics Innovation Company ©BrainPad Inc. 90
4-1. サブクエリ
4-1-1. サブクエリとは
4-1-2. パターン① 単一の値を返す
4-1-3. パターン② 複数の値を返す
4-1-4. パターン③ 表形式の値を返す
4-1-5. WITH句 サブクエリの代用
Slide 92
Slide 92 text
Analytics Innovation Company ©BrainPad Inc. 91
4-1-1. サブクエリとは
例 「price(価格)が最も高いデータのname(品目)を知りたい」
これまでに学んだ知識で実現すると、例えば以下のようになる
手順1
priceの最大値を求め、結果をメモ
SELECT MAX(price) FROM receipt_item;
結果 2000
手順2
手順1の結果2000を条件に検索
SELECT name FROM receipt_item
WHERE price = 2000
;
結果 B
receipt_item
id user_id price name
1 1 100 A
2 1 2000 B
3 2 300 C
4 2 1500 D
5 3 800 E
receipt_item
id user_id price name
1 1 100 A
2 1 2000 B
3 2 300 C
4 2 1500 D
5 3 800 E
代入
Slide 93
Slide 93 text
Analytics Innovation Company ©BrainPad Inc. 92
4-1-1. サブクエリとは
結果を代入するのではなく、クエリそのものを条件に代入する
クエリが入れ子（ネスト構造）になる
手順1
priceの最大値を求め、結果をメモ
SELECT MAX(price) FROM receipt_item;
手順2
手順1の結果2000を条件に検索
SELECT name FROM receipt_item
WHERE price =
;
結果 B
receipt_item
id user_id price name
1 1 100 A
2 1 2000 B
3 2 300 C
4 2 1500 D
5 3 800 E
receipt_item
id user_id price name
1 1 100 A
2 1 2000 B
3 2 300 C
4 2 1500 D
5 3 800 E
括弧で囲み代入
（;は不要）
Slide 94
Slide 94 text
Analytics Innovation Company ©BrainPad Inc. 93
4-1-1. サブクエリとは
前ページのクエリを整理すると、以下のようになる
SELECT
name
FROM
receipt_item
WHERE
price = (SELECT MAX(price) FROM receipt_item)
;
他のSQLの一部分として登場するSELECT文のことを、サブクエリや副問い合わせと
いう。
receipt_item
id user_id price name
1 1 100 A
2 1 2000 B
3 2 300 C
4 2 1500 D
5 3 800 E
Slide 95
Slide 95 text
Analytics Innovation Company ©BrainPad Inc. 94
4-1-1. サブクエリとは
サブクエリの3つのパターン
パターン① 単一の値（1行1列）を返す
パターン② 複数の値（n行1列）を返す
パターン③ 表形式の値（n行m列）を返す
SELECT… (SELECT… ) SELECT… 2000
SELECT… (SELECT… ) SELECT…
SELECT… (SELECT… ) SELECT…
10
20
30
A 1 10
B 2 20
C 3 30
単一の値
複数の値
表の値
n行
1列
n行
m列
1列
1行
Slide 96
Slide 96 text
Analytics Innovation Company ©BrainPad Inc. 95
4-1-2. サブクエリ パターン① 単一の値を返す
receipt_itemテーブルでpriceが最も低いデータのid, user_id, priceを
サブクエリを用いて取得する。
/*
receipt_itemテーブルでpriceが最も低いデータのid, user_id, priceを
サブクエリを用いて取得する。
*/
SELECT
id
,user_id
,price
FROM
receipt_item
WHERE
price = (SELECT MIN(price) FROM receipt_item)
ORDER BY
id
LIMIT 1 ;
例題
SQL
実行結果 レコード数 1
Slide 97
Slide 97 text
Analytics Innovation Company ©BrainPad Inc. 96
4-1-2. サブクエリ パターン① 単一の値を返す
単一行サブクエリ
• 検索結果が1行1列の1つの値になるサブクエリ
• WHEREの他にSELECTの選択列リストにも記述できる
例 user_id, priceとpriceの全体平均値を並べて表示
SELECT
user_id
,price
,(SELECT AVG(price) FROM receipt_item)
AS avg_price
FROM
receipt_item
ORDER BY
user_id
LIMIT
5
;
解説
/*
receipt_itemテーブルでpriceが最も低いデータ
のid, user_id, priceをサブクエリを用いて取得す
る。
*/
SELECT
id
,user_id
,price
FROM
receipt_item
WHERE
price = (SELECT MIN(price)
FROM receipt_item)
ORDER BY
id
LIMIT 1 ;
SQL
実行結果 レコード数 5
Slide 98
Slide 98 text
Analytics Innovation Company ©BrainPad Inc. 97
4-1-2. サブクエリ パターン① 単一の値を返す
receipt_itemテーブルで、user_id, priceとpriceの標準偏差を並べて表示する。
ただし、user_idの昇順で5件表示とし、標準偏差はSTDDEV_POP()を使用せよ。
/*
receipt_itemテーブルで、user_id, priceとpriceの標準偏差を並べて表示する。
ただし、user_idの昇順で5件表示とし、標準偏差はSTDDEV_POP()を使用せよ。
*/
類題
実行結果 レコード数 5
SQL
Slide 99
Slide 99 text
Analytics Innovation Company ©BrainPad Inc. 98
4-1-3. サブクエリ パターン② 複数の値を返す
receipt_itemテーブルとuserテーブルを用いて、’ビールA’ を購入したことの
ある人をstate_code(都道府県)別にカウントする。
/*
receipt_itemテーブルとuserテーブルを用いて、’ビールA’ を購入し
たことのある人を都道府県別にカウントする。
*/
SELECT
state_code
,COUNT(id)
FROM
`user`
WHERE
id IN (SELECT DISTINCT user_id
FROM receipt_item
WHERE name = ‘ビールA’)
GROUP BY
state_code
ORDER BY
state_code;
例題
実行結果
レコード数47
SQL
id
user_id (FK)
…
receipt_item
id
state_code
…
user ER図
Slide 100
Slide 100 text
Analytics Innovation Company ©BrainPad Inc. 99
4-1-3. サブクエリ パターン② 複数の値を返す
複数行サブクエリ
• 検索結果がn行1列の複数の値となるサブクエリ
… WHERE id IN (SELECT DISTINCT user_id
FROM receipt_item
WHERE name = ‘ビールA’)
… WHERE id IN
複数の値と比較するするときはIN演算子, ANY/ALL演算子を用い
る。比較演算子=,>などは使えないので注意！
（復習）
• IN演算子：列挙した値のいずれかに一致するか判定
• ANY演算子：それぞれと比較して、いずれかが真なら真
• ALL演算子：それぞれと比較して、全て真なら真
※redshiftでは一般的なALLはサポートしておらず、<> ALL (= NOT IN)しか使えないので注意
解説
/*
receipt_itemテーブルとuserテーブルを用いて、’
アイス’ を購入したことのある人を都道府県別にカ
ウントする。
*/
SELECT
state_code
,COUNT(id)
FROM
`user`
WHERE
id IN (SELECT DISTINCT user_id
FROM receipt_item
WHERE name = ‘ビールA’)
…
（以下省略）
SQL
user_id
10
20
…
Slide 101
Slide 101 text
Analytics Innovation Company ©BrainPad Inc. 100
4-1-3. サブクエリ パターン② 複数の値を返す
receipt_itemテーブルとuserテーブルを用いて、name(商品名)が’カップラーメンA‘を買った
ことのある人を性別にカウントする。
/*
receipt_itemテーブルとuserテーブルを用いて、name(商品名)が’カップラーメンA‘を
買ったことのある人を性別にカウントする。
*/
類題
実行結果 レコード数 2
SQL
id
user_id (FK)
name
…
receipt_item
id
gender
…
user ER図
Slide 102
Slide 102 text
Analytics Innovation Company ©BrainPad Inc. 101
4-1-4. サブクエリ パターン③ 表形式の値を返す
receipt_itemテーブルを用いて、1回の買い物の合計金額の平均値を求める。
/*
receipt_itemテーブルを用いて、1回の買い物の合計金額の平均
値を求める。
*/
SELECT
AVG(sum_price)
FROM
(SELECT receipt_id
,SUM(price) AS sum_price
FROM receipt_item
GROUP BY receipt_id) AS tbl
;
例題
実行結果
レコード数1
SQL
id
user_id (FK)
price
…
receipt_item
Slide 103
Slide 103 text
Analytics Innovation Company ©BrainPad Inc. 102
4-1-4. サブクエリ パターン③ 表形式の値を返す
表形式の結果となるサブクエリ
• 検索結果がn行m列の表となるサブクエリ
… FROM
(SELECT receipt_id
,SUM(price) AS sum_price
FROM receipt_item
GROUP BY receipt_id) AS tbl
… FROM
• この表のsum_priceの平均値を求めている
解説
/*
receipt_itemテーブルとuserテーブルを用いて、1
回の買い物の合計金額の平均値を求める。
*/
SELECT
AVG(sum_price)
FROM
(SELECT receipt_id
,SUM(price) AS sum_price
FROM receipt_item
GROUP BY receipt_id) AS tbl
;
SQL
receipt_id sum_price
1 409
2 198
…
…
Slide 104
Slide 104 text
Analytics Innovation Company ©BrainPad Inc. 103
4-1-4. サブクエリ パターン③ 表形式の値を返す
receipt_itemテーブルを用いて、1ユーザーあたりの累計合計金額の平均値を
求める。
/*
receipt_itemテーブルを用いて、1ユーザーあたりの累計合計金額の平均値を求める。
ヒント ① サブクエリでuser_idでグループ化し、合計金額を求める ② サブクエリの結果をもとに平均値を求める
*/
類題
SQL
実行結果
レコード数1
id
user_id (FK)
price
…
receipt_item
Slide 105
Slide 105 text
Analytics Innovation Company ©BrainPad Inc. 104
4-1-5. WITH句 サブクエリの代用
4-1-4.例題と同じく、receipt_itemテーブルを用いて、1回の買い物の合計金額の平均値
を求める。ただし、サブクエリの代わりにWITH句を利用する。
/*
receipt_itemテーブルを用いて、1回の買い物の合計金額の平均
値を求める。ただし、サブクエリの代わりにWITH句を利用する。
*/
WITH tbl AS (
SELECT receipt_id
,SUM(price) AS sum_price
FROM receipt_item
GROUP BY receipt_id
)
SELECT
AVG(sum_price)
FROM
tbl;
例題
実行結果
レコード数1
SQL
id
user_id (FK)
price
…
receipt_item
Slide 106
Slide 106 text
Analytics Innovation Company ©BrainPad Inc. 105
4-1-5. WITH句 サブクエリの代用
WITH句（WITH問い合わせ）
• サブクエリに当たる部分をもとのSQLと切り離す
• サブクエリが入れ子になるのに対して、WITH句の場合は構造
がわかりやすい（可読性が高い）
• WITH句はカンマで区切ることで複数続けて書くこともできる
例：WITH tblA AS(…), tblB AS(…) SELECT ・・・;
解説
/*
receipt_itemテーブルを用いて、1回の買い物の
合計金額の平均値を求める。
ただし、サブクエリの代わりにWITH句を利用する。
*/
WITH tbl AS (
SELECT receipt_id
,SUM(price) AS sum_price
FROM receipt_item
GROUP BY receipt_id
)
SELECT
AVG(sum_price)
FROM
tbl
;
SQL
WITH [呼び出し名] AS (
SELECT
…
FROM
…
)
SELECT
…
FROM
…
;
SELECT
…
FROM(
SELECT
…
FROM
…
) AS [呼び出し名]
;
WITH句で呼び出す場合 サブクエリで呼び出す場合
Slide 107
Slide 107 text
Analytics Innovation Company ©BrainPad Inc. 106
4-1-5. WITH句 サブクエリの代用
receiptテーブルを用いて、「1ユーザーあたりのレシート登録数」の平均値を求める。
/*
receiptテーブルを用いて、「1ユーザーあたりのレシート登録数」の平均値を求める。
• ヒント①先ずはWITH句を使って、ユーザーIDごとのレシート登録数を集計する。
• ユーザーID,レシート登録数の２列からなる表を作成する
• ヒント②ヒント①で作成した表から、レシート登録数の平均を計算する。
*/
類題
SQL
実行結果
レコード数1
id
user_id (FK)
price
…
receipt_item
Slide 108
Slide 108 text
Analytics Innovation Company ©BrainPad Inc. 107
4-2. テーブルの結合
4-1-1. テーブルの結合とは
4-2-2. INNER JOIN
内部結合
4-2-2. LEFT JOIN
外部結合
Slide 109
Slide 109 text
Analytics Innovation Company ©BrainPad Inc. 108
4-2-1. テーブルの結合とは
• 複数のテーブルを、共通項目(キー)を用いて1つにまとめることを結合という
• 結合に用いる共通項目はER図から調べる
user(右表)
id state_code
1 13
2 14
3 27
4 45
receipt(左表)
id user_id
1 1
2 2
3 3
4 4
結合した結果
receipt_id user_id state_code
1 1 13
2 2 14
3 3 27
4 4 45
id
gender
…
user
id
user_id (FK)
shop_name
…
receipt
id
user_id (FK)
receipt_id (FK)
…
receipt_item
Slide 110
Slide 110 text
Analytics Innovation Company ©BrainPad Inc. 109
4-2-2. INNER JOIN(内部結合)
receiptテーブル（左表）とuserテーブル（右表）を、userテーブルのidとreceiptテーブルのuser_id
を用いて内部結合する。
・抽出フィールド：receipt テーブルid, shop_name ／ userテーブル id, state_code
/*
receiptテーブルとuserテーブルを、userテーブルのidとreceiptテーブルの
user_idを用いて内部結合する。
・抽出フィールド：receipt テーブルid, shop_name ／ userテーブル id,
state_code
*/
SELECT
r.id AS receipt_id
,u.id AS user_id
,r.shop_name
,u.state_code
FROM
receipt AS r
INNER JOIN
`user` AS u
ON r.user_id = u.id
ORDER BY
r.id
LIMIT
5;
例題
実行結果 レコード数5
SQL
id
user_id (FK)
shop_name
…
receipt
id
state_code
…
user ER図
Slide 111
Slide 111 text
Analytics Innovation Company ©BrainPad Inc. 110
4-2-2. INNER JOIN(内部結合)
INNER JOIN
• 内部結合
共通項目をキーとし、一致するレコードのみ取り出す
• 基本構文
FROM テーブルA（左表）
INNER JOIN テーブルB（右表）
ON 結合条件
.(ドット)の意味
• 日本語の助詞「〜の」と解釈するとわかりやすい
• 例1. u.id：「u(user)テーブルのidフィールド」
• 例2. u.*：「u(user)テーブルの全てのフィールド」
r.idとu.idの区別
• 元のテーブルでは同じ”id”というフィールド名が付いているが、意
味が異なるのでそれぞれ別名をつける
• r.id AS receipt_id 「レシートを一意に決めるid」
• u.id AS user_id 「ユーザーを一意に決めるid」
解説
/*
receiptテーブルとuserテーブルを、userテーブル
のidとreceiptテーブルのuser_idを用いて内部結
合する。
抽出フィールド：receipt テーブルid,
shop_name ／ userテーブル id,
state_code
*/
SELECT
r.id AS receipt_id
,u.id AS user_id
,r.shop_name
,u.state_code
FROM
receipt AS r
INNER JOIN
`user` AS u
ON r.user_id = u.id
…
（以下省略）
SQL
Slide 112
Slide 112 text
Analytics Innovation Company ©BrainPad Inc. 111
4-2-2. INNER JOIN(内部結合)
INNER JOINのイメージ
解説
user（右表）
id state_code
1 13
2 14
3 27
receipt（左表）
id user_id shop_name
1 1 A
2 2 B
3 2 C
4 3 C
5 4 D
内部結合した結果
receipt_id user_id shop_name state_code
1 1 A 13
2 2 B 14
3 2 C 14
4 3 C 27
結合の相手がいない行は消える(こ
の例では左表のuser_id = 4の行
が消えている)
• 内部結合はデータ件数の不一致が生じてしまう場合があるため、注意が必要
• 結合相手がいない場合にもデータを残す結合は外部結合で行う（後述）
Slide 113
Slide 113 text
Analytics Innovation Company ©BrainPad Inc. 112
4-2-2. INNER JOIN(内部結合)
receipt_itemテーブル（左表）とuserテーブル（右表）を、userテーブルのidとreceipt_itemテーブルの
user_idを用いて内部結合する。
・抽出フィールド：receipt_item テーブルid, name ／ userテーブル id, gender
・receipt_itemテーブルのidの昇順で5件分表示。
/*
receipt_itemテーブル（左表）とuserテーブル（右表）を、
userテーブルのidとreceipt_itemテーブルのuser_idを用いて内部結合する。
・抽出フィールド：receipt_item テーブルid, name ／ userテーブル id, gender
・receipt_itemテーブルのidの昇順で5件分表示
*/
類題
実行結果 レコード数 5
SQL
id
user_id (FK)
receipt_id
…
receipt_item
id
gender
…
user ER図
Slide 114
Slide 114 text
Analytics Innovation Company ©BrainPad Inc. 113
4-2-3. LEFT JOIN(外部結合)
receiptテーブル(左表)とuserテーブル(右表)を、userテーブルのidとreceiptテーブルのuser_idを用いて外部
結合する。
・抽出フィールド：receipt テーブルid, shop_name ／ userテーブル id, state_code
・receiptテーブルのid >= 60を対象とし、昇順で5件表示する。
/*
receiptテーブル(左表)とuserテーブル(右表)を、userテーブルのidとreceipt
テーブルのuser_idを用いて外部結合する。
・抽出フィールド：receipt テーブルid, shop_name ／ userテーブル id,
state_code
*/
SELECT
r.id AS receipt_id
,u.id AS user_id
,r.shop_name
,u.state_code
FROM
receipt AS r
LEFT JOIN
`user` AS u
ON r.user_id = u.id
WHERE
r.id >= 60
ORDER BY
r.id
LIMIT
5;
例題
実行結果 レコード数5
SQL
id
user_id (FK)
shop_name
…
receipt
id
state_code
…
user ER図
Slide 115
Slide 115 text
Analytics Innovation Company ©BrainPad Inc. 114
4-2-3. LEFT JOIN(外部結合)
LEFT JOIN
• 左外部結合
対応するレコードがない場合でもレコードが削除されない結合
• 基本構文
FROM テーブルA(左表)
LEFT JOIN テーブルB(右表)
ON 結合条件
対応するレコードがない場合
• 全てNULLになる
解説
/*
receiptテーブルとuserテーブルを、userテーブル
のidとreceiptテーブルのuser_idを用いて内部結
合する。
抽出フィールド：receipt テーブルid,
shop_name ／ userテーブル id,
state_code
*/
SELECT
r.id AS receipt_id
,u.id AS user_id
,r.shop_name
,u.state_code
FROM
receipt AS r
LEFT JOIN
`user` AS u
ON r.user_id = u.id
…
（以下省略）
SQL
Slide 116
Slide 116 text
Analytics Innovation Company ©BrainPad Inc. 115
4-2-3. LEFT JOIN(外部結合)
LEFT JOINのイメージ
解説
user(右表)
id state_code
1 13
2 14
3 27
receipt(左表)
id user_id shop_name
1 1 A
2 2 B
3 2 C
4 3 C
5 4 D
外部結合した結果
receipt_id user_id shop_name state_code
1 1 A 13
2 2 B 14
3 2 C 14
4 3 C 27
5 4 D (NULL)
結合の相手がいない行も削除され
ず、存在しないデータは全てNULL
になる
• 分析用のデータマートを作成する際は、件数の不整合が生じないよう、一般的にLEFT
JOINを用いる
Slide 117
Slide 117 text
Analytics Innovation Company ©BrainPad Inc. 116
4-2-3. LEFT JOIN(外部結合)
receipt_itemテーブル(左表)とuserテーブル(右表)を、userテーブルのidと
receipt_itemテーブルのuser_idを用いて外部結合する。
・抽出フィールド：receipt_item テーブルid, name ／ userテーブル id, gender
・receipt_itemテーブルのid >= 135を対象とし、昇順で5件分表示する。
/*
receipt_itemテーブル(左表)とuserテーブル(右表)を、
userテーブルのidとreceipt_itemテーブルのuser_idを用いて外部結合する。
・抽出フィールド：receipt_item テーブルid, name ／ userテーブル id, gender
・receipt_itemテーブルのid >=135を対象とし、昇順で5件分表示
*/
類題
実行結果 レコード数 5
SQL
id
user_id (FK)
receipt_id
…
receipt_item
id
gender
…
user ER図
Slide 118
Slide 118 text
Analytics Innovation Company ©BrainPad Inc. 117
4-3. 一時テーブル
4-3-1. 一時テーブルの作成
Slide 119
Slide 119 text
Analytics Innovation Company ©BrainPad Inc. 118
4-3-1. 一時テーブルの作成
userテーブルからlast_loginが2013年3月のものを抽出し、一時テーブルtmp_user_201303として保存せ
よ。また、確認のため作成した一時テーブルtmp_user_201303からデータを5件取得せよ。
/*
userテーブルからlast_loginが2013年3月のものを抽出し、一時テーブル
#user_201303として保存せよ。また、確認のため作成した一時テーブルから
データを5件取得せよ。
*/
CREATE TEMPORARY TABLE tmp_user_201303
SELECT
*
FROM
`user`
WHERE
last_login BETWEEN ‘2013-03-01’
AND ‘2013-03-31’
;
一時テーブル作成後、確認のため以下のクエリでデータを取得
SELECT
*
FROM
tmp_user_201303
ORDER BY
id
LIMIT
5;
例題
SQL
一時テーブル作成
一時テーブルからデータ取得結果
Slide 120
Slide 120 text
Analytics Innovation Company ©BrainPad Inc. 119
4-3-1. 一時テーブルの作成
CREATE TABLE 〈テーブル名〉 (); テーブルの作成
GRANT ALL ON 〈テーブル名〉 TO PUBLIC; 作成したテーブルに権限を付ける
1. テーブルを作る
2. テーブルを消す
DROP TABLE 〈テーブル名〉 ; テーブルの削除
DROP TABLE IF EXISTS 〈テーブル名〉 ; テーブルが存在している場合のみ削除
3.一時テーブルを作る
CREATE TEMPORARY TABLE 〈テーブル名〉 () ; セッション中に一時的に作成
テーブル作成・削除に用いるSQL
解説
Slide 121
Slide 121 text
Analytics Innovation Company ©BrainPad Inc. 120
4-3-1. 一時テーブルの作成
userテーブルからcreate_dateが2013年2月のものを抽出し、一時テーブルuser_201302として保存せよ。
また、確認のため作成した一時テーブルuser_201302からデータを5件取得せよ。
/*
userテーブルからcreate_dateが2013年2月のものを抽出し、一時テーブルuser_201302として保存せよ。
また、確認のため作成した一時テーブルuser_201302からデータを5件取得せよ。
*/
類題
SQL
一時テーブル作成結果
一時テーブルからデータ取得結果
Slide 122
Slide 122 text
Analytics Innovation Company ©BrainPad Inc. 121
5. SQLの応用②
5-1. ウィンドウ関数
5-2. EXISTS（存在検査）
5-3. 縦持ち・横持ち
Slide 123
Slide 123 text
Analytics Innovation Company ©BrainPad Inc. 122
5-1. ウィンドウ関数
5-1-1. ウィンドウ関数とは
5-1-2. 集計関数群
5-1-3. ランク付け関数群
5-1-4. ラグ・リード関数群
5-1-5. 参考
Slide 124
Slide 124 text
Analytics Innovation Company ©BrainPad Inc. 123
5-1-1. ウィンドウ関数とは
ウィンドウ関数
• より効率的に分析用のクエリを作成することができる
• SQLの中でも比較的新しい機能
ウィンドウ関数の種類
カテゴリ 使用例 関数例
集計関数群 累積集計を計算する SUM, COUNT, AVG, MIN, MAX, MEDIAN など
ランク付け関数群
ウィンドウ内のランキングを計算す
る ROW_NUMBER, DENSE_RANK, RANK など
ラグ・リード関数群 前後のレコードを取得する LAG, LEAD, FIRST_VALUE, LAST_VALUE,
NTH_VALUE など
レポート関数群 ウィンドウ内の構成比を計算する RATIO_TO_REPORT, CUME_DIST, NTILE,
PERCENTILE_DISC など
線形回帰関数群 統計量を計算する STDDEV_SAMP, VAR_SAMP など
Slide 125
Slide 125 text
Analytics Innovation Company ©BrainPad Inc. 124
5-1-2. ウィンドウ関数① 集計関数群
receipt_itemテーブルで、user_idで区切ったパーティション毎に
SUM, AVG, MIN, MAXで集計を行う。
・user_idが5以下のデータを対象とする。
/*
receipt_itemテーブルで、user_idで区切ったパーティション毎にSUM, AVG,
MIN, MAXで集計を行う。
・user_idが5以下のデータを対象とする。
*/
SELECT
user_id
,price
,SUM(price) OVER(PARTITION BY user_id) AS `sum`
,AVG(price) OVER(PARTITION BY user_id) AS `avg`
,MIN(price) OVER(PARTITION BY user_id) AS `min`
,MAX(price) OVER(PARTITION BY user_id) AS `max`
FROM
receipt_item
WHERE
user_id <=5
ORDER BY
user_id
;
例題
実行結果 レコード数4058
SQL
… …
Slide 126
Slide 126 text
Analytics Innovation Company ©BrainPad Inc. 125
5-1-2. ウィンドウ関数① 集計関数群
ウィンドウ
• 基本構文
関数 OVER(PARTITION BY フィールド ORDER BY フィールド)
例. SUM() OVER(PARTITION BY user_id)
user_id毎で分割して、その集合内で合計する
• ウィンドウ関数は、結果の行を集計するグループ関数とは異なり、
テーブルのすべての行が保持される
（詳細は次のページを参照）
解説
/*
receipt_itemテーブルで、user_id区切ったパー
ティション毎にSUM, AVG, MIN, MAXで集計を
行う。
・user_idが5以下のデータを対象とする。
*/
SELECT
user_id
,price
,SUM(price)
OVER(PARTITION BY user_id)
,AVG(price)
OVER(PARTITION BY user_id)
,MIN(price)
OVER(PARTITION BY user_id)
…（省略）…
FROM
receipt_item
…
（以下省略）
SQL
… …
Slide 127
Slide 127 text
Analytics Innovation Company ©BrainPad Inc. 126
5-1-2. ウィンドウ関数① 集計関数群
ウィンドウ関数の集計関数SUM
SELECT
user_id
,price
,SUM(price)
OVER(PARTITION BY user_id)
FROM
receipt_item
ORDER BY
user_id;
解説
GROUP BYで用いる集計関数SUM
SELECT
user_id
,SUM(price)
FROM
receipt_item
GROUP BY
user_id
ORDER BY
user_id;
実行結果の一部
実行結果の一部
元のレコードが
保持されたまま
user_id毎に
レコードが集約される
集約の基準列以外は集計関
数しか選択できない（price
は選択できない）
以前学んだGROUP BYでの集計と、ウィンドウ関数での集計結果の違いを知る
… …
Slide 128
Slide 128 text
Analytics Innovation Company ©BrainPad Inc. 127
5-1-2. ウィンドウ関数① 集計関数群
receiptテーブルで、user_idで区切ったパーティション毎にCOUNTで集計を行う。
・user_idが3以下のデータを対象とする。
/*
receiptテーブルで、user_idで区切ったパーティション毎にCOUNTで集計を行う。
・user_idが3以下のデータを対象とする。
*/
類題
実行結果 レコード数 82
SQL
Slide 129
Slide 129 text
Analytics Innovation Company ©BrainPad Inc. 128
5-1-3. ウィンドウ関数② ランク付け関数群
receiptテーブルで、user_idで区切ったパーティション毎にpaid_atの昇順で
連番を振る。
・user_idが5以下、paid_atが’2010-01-31’以前のデータを対象とする。
/*
receiptテーブルで、user_idで区切ったパーティション毎にpaid_atの昇順で
連番を振る。
・user_idが5以下のデータを対象とする。
*/
SELECT
user_id
,CAST(paid_at AS DATE)
,ROW_NUMBER() OVER(PARTITION BY user_id
ORDER BY CAST(paid_at AS DATE)) AS `row`
,DENSE_RANK() OVER(PARTITION BY user_id
ORDER BY CAST(paid_at AS DATE)) AS `dense`
,RANK() OVER(PARTITION BY user_id
ORDER BY CAST(paid_at AS DATE)) AS `rank`
FROM
receipt
WHERE
user_id <=5
AND paid_at <= '2010-01-31'
ORDER BY
user_id, paid_at
;
例題
実行結果 レコード数18
SQL
Slide 130
Slide 130 text
Analytics Innovation Company ©BrainPad Inc. 129
5-1-3. ウィンドウ関数② ランク付け関数群
ランク付け関数
例 XX() OVER(PARTITION BY id ORDER BY year)
解説
/*
receiptテーブルで、user_idで区切ったパーティション毎
にpaid_atの昇順で連番を振る。
・user_idが5以下のデータを対象とする。
*/
SELECT
user_id
,CAST(paid_at AS DATE)
,ROW_NUMBER()
OVER(PARTITION BY user_id
ORDER BY CAST(paid_at AS DATE))
…（省略）…
FROM
receipt
WHERE
user_id <=5
AND paid_at <= '2010-01-31'
ORDER BY
user_id, paid_at
;
SQL
ランク付け関数 処理内容
ROW_NUMBER ORDER BYで重複しても気にせずに連番を振る
DENSE_RANK ORDER BYで重複したレコードには同じ番号を振り、
以降は連番が続く
RANK ORDER BYで重複したレコードには同じ番号を振る
が、以降の連番は重複した分番号がとぶ
id year rn
1 2016 1
1 2017 2
2 2017 1
2 2017 2
2 2018 3
id year dr
1 2016 1
1 2017 2
2 2017 1
2 2017 1
2 2018 2
id year r
1 2016 1
1 2017 2
2 2017 1
2 2017 1
2 2018 3
ROW_NUMBER DENSE_RANK RANK
Slide 131
Slide 131 text
Analytics Innovation Company ©BrainPad Inc. 130
5-1-3. ウィンドウ関数② ランク付け関数群
ROW_NUMBERの使い道
• グループ毎に最大値（最小値）を持つレコードを抽出する
• 例 user_id毎にpriceの最大値を求める（※user_idが5以下のデータを対象）
SELECT
user_id
,price AS max_price
,name
FROM
(SELECT
*
,ROW_NUMBER() OVER(PARTITION BY user_id
ORDER BY price DESC) AS seq
FROM
receipt_item) AS ri
WHERE
seq = 1 AND user_id <= 5
ORDER BY
user_id
;
解説
実行結果 レコード数5
サブクエリ（次ページで解説）
Slide 132
Slide 132 text
Analytics Innovation Company ©BrainPad Inc. 131
5-1-3. ウィンドウ関数② ランク付け関数群
サブクエリ部分
解説
(SELECT
*
,ROW_NUMBER() OVER(PARTITION BY user_id ORDER BY price DESC) AS seq
FROM
receipt_item) AS ri
※都合により一部のカラムのみ掲載
サブクエリによって得られたテーブル ri
最終結果
WHERE
seq = 1
各グループの一
番上を取得
priceの降順に連番を振る
…
Slide 133
Slide 133 text
Analytics Innovation Company ©BrainPad Inc. 132
5-1-3. ウィンドウ関数② ランク付け関数群
receipt_itemテーブルで、receipt_id毎にpriceの最大値を求める。
・receipt_idが10以下のデータを対象とする。
/*
receipt_itemテーブルで、receipt_id毎にpriceの最大値を求める。
・receipt_idが10以下のデータを対象とする。
*/
類題
実行結果 レコード数 10
SQL
（参考） サブクエリ部分の取得例
Slide 134
Slide 134 text
Analytics Innovation Company ©BrainPad Inc. 133
5-1-4. ウィンドウ関数③ ラグ・リード関数群
receipt_itemテーブルで、user_idで区切ったパーティション毎にidの昇順に並び替え、priceについて1行前の
値をlag1_price、2行前の値をlag2_priceとして取得する。
・抽出フィールド：user_id, id, price, lag1_price, lag2_price
・user_idが3以下のデータを対象とする。
/*
receipt_itemテーブルで、user_idで区切ったパーティション毎にidの昇順に
並び替え、priceについて1行前の値をlag1_price、2行前の値を
lag2_priceとして取得する。
・抽出フィールド：user_id, id, price, lag1_price, lag2_price
・user_idが3以下のデータを対象とする。
*/
SELECT
user_id
,id
,price
,LAG(price, 1) OVER(PARTITION BY user_id
ORDER BY id) AS lag1_price
,LAG(price, 2) OVER(PARTITION BY user_id
ORDER BY id) AS lag2_price
FROM
receipt_item
WHERE
user_id <= 3
ORDER BY
user_id, id
;
例題
実行結果
SQL
…
Slide 135
Slide 135 text
Analytics Innovation Company ©BrainPad Inc. 134
5-1-4. ウィンドウ関数③ ラグ・リード関数群
ラグ・リード関数群
解説
/*
receipt_itemテーブルで、user_idで区切ったパーティション
毎にidの昇順に並び替え、priceについて1行前の値を
lag1_price、2行前の値をlag2_priceとして取得する。
・抽出フィールド：user_id, id, price, lag1_price,
lag2_price
・user_idが3以下のデータを対象とする。
*/
SELECT
user_id
,id
,price
,LAG(price, 1) OVER(PARTITION BY user_id
ORDER BY id) AS lag1_price
,LAG(price, 2) OVER(PARTITION BY user_id
ORDER BY id) AS lag2_price
FROM
receipt_item
WHERE
user_id <= 3
ORDER BY
user_id, id
;
SQL
ラグ・リード関数 処理内容
LAG(フィールド名, n)
ORDER BYの順でn行前のレコードの値
を取得
LEAD(フィールド名, n)
ORDER BYの順でn行後のレコードの値
を取得
FIRST_VALUE(フィールド名)
ORDER BYの順で最初のレコードの値を
取得
LAST_VALUE(フィールド名)
ORDER BYの順で最後のレコードの値を
取得
id val lag1_val lead1_val first_value last_value
1 10 (NULL) 20 10 20
1 20 10 (NULL) 10 20
2 100 (NULL) 200 100 300
2 200 100 300 100 300
2 300 200 (NULL) 100 300
例 XX() OVER(PARTITION BY id ORDER BY value)
Slide 136
Slide 136 text
Analytics Innovation Company ©BrainPad Inc. 135
5-1-4. ウィンドウ関数③ ラグ・リード関数群
receipt_itemテーブルで、user_idで区切ったパーティション毎にidの昇順に並び替え、priceについて
1行後の値をlead1_price、2行後の値をlead2_priceとして取得する。
・抽出フィールド：user_id, id, price, lead1_price, lead2_price
・user_idが3以下のデータを対象とする。
/*
receipt_itemテーブルで、user_idで区切ったパーティション毎にidの昇順に並び替え、priceについて1行後の値をlead1_price、2行後の
値をlead2_priceとして取得する。
・抽出フィールド：user_id, id, price, lead1_price, lead2_price
・user_idが3以下のデータを対象とする。
*/
類題
SQL
実行結果 レコード数 25
…
Slide 137
Slide 137 text
Analytics Innovation Company ©BrainPad Inc. 136
5-1-5. ウィンドウ関数④ 参考
• ウィンドウ関数全体の基本構文（※関数によって細かな差があるので注意）
関数(集計フィールド名)
OVER(PARTITION BY フィールド名
ORDER BY フィールド名
ROWS 集計対象行の範囲)
PARTITION BY
• パーティションを分ける。「○○別で●●順に集計」の○○
• 省略するとテーブル全体について集計
ORDER BY
• 各パーティション内の行の順序。「○○別で●●順に集計」の●●
ROWS
• 集計処理する対象行を定義する（詳細は次のページで説明）
Slide 138
Slide 138 text
Analytics Innovation Company ©BrainPad Inc. 137
5-1-5. ウィンドウ関数④ 参考
ROWS
• 集計処理する対象行を定義する
• デフォルト「開始点： ウィンドウの最初の行、終了点：ウィンドウの最後の行」
（ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED
FOLLOWING）
UNBOUNDED PRECEDING パーティションの最初の行
CURRENT ROW 現在の行
UNBOUNDED FOLLOWING パーティションの最後の行
ROWS BETWEEN UNBOUNDED PRECEDING AND
UNBOUNDED FOLLOWING (※省略した時のデフォルト)
ROWS BETWEEN 2 PRECEDING AND CURRENT ROW
ROWS BETWEEN 1 PRECEDING AND 2 FOLLOWING
ROWS BETWEEN 1 FOLLOWING AND 3 FOLLOWING
Slide 139
Slide 139 text
Analytics Innovation Company ©BrainPad Inc. 138
5-2. EXISTS（存在検査）
5-2-1. EXISTS 存在検査
Slide 140
Slide 140 text
Analytics Innovation Company ©BrainPad Inc. 139
5-2-1. EXISTS 存在検査
userテーブルのレコードで、idがreceiptテーブルのuser_idとして存在しているものを抽出する。
・抽出フィールド：id, gender, birthday
・user_idの昇順で5件分表示する
/*
userテーブルのレコードで、idがreceiptテーブルのuser_idとして存在している
ものを抽出する。
・抽出フィールド：id, gender, birthday
・user_idの昇順で5件分表示する
*/
SELECT
id AS user_id
,gender
,birthday
FROM
`user` AS u
WHERE EXISTS(
SELECT
1
FROM
receipt AS r
WHERE
r.user_id = u.id)
ORDER BY
u.id
LIMIT
5
;
例題
実行結果 レコード数 5
SQL
Slide 141
Slide 141 text
Analytics Innovation Company ©BrainPad Inc. 140
5-2-1. EXISTS 存在検査
EXISTS句による存在検査
SELECT
id AS user_id
…
FROM
`user` AS u
WHERE EXISTS(
SELECT
1
FROM
receipt AS r
WHERE
r.user_id = u.id)
NOT EXISTS句による非存在検査
• 存在しないものを抽出できる
解説
/*
userテーブルのレコードで、idがreceiptテーブルのuser_idと
して存在しているものを抽出する。
・抽出フィールド：id, gender, birthday
・user_idの昇順で5件分表示する
*/
SELECT
id AS user_id
,gender
,birthday
FROM
`user` AS u
WHERE EXISTS(
SELECT
1
FROM
receipt AS r
WHERE
r.user_id = u.id)
ORDER BY
u.id
LIMIT
5;
SQL
② receiptテーブルの
③ user_idの中に存在して
いるとき(TRUEを返す)
④ userテーブルの
⑤ idを抽出する
① userテーブルのidが
* や他の値でもOK
Slide 142
Slide 142 text
Analytics Innovation Company ©BrainPad Inc. 141
EXISTS句による存在検査のイメージ
5-2-1. EXISTS 存在検査 解説
/*
userテーブルのレコードで、idがreceiptテーブルのuser_idと
して存在しているものを抽出する。
・抽出フィールド：id, gender, birthday
・user_idの昇順で5件分表示する
*/
SELECT
id AS user_id
,gender
,birthday
FROM
`user` AS u
WHERE EXISTS(
SELECT
1
FROM
receipt AS r
WHERE
r.user_id = u.id)
ORDER BY
u.id
LIMIT
5;
user
id …
5
6
7
10
12
receipt
user_id …
3
4
5
15
20
1レコードず
つ処理
① userテーブルの1行目id=5が ② receiptテーブルの
③ user_idの中に存
在しているとき
④ userテーブルの
⑤ id, gender, birthday
を抽出する
WHERE EXISTSの中の処理
① userテーブルidが
② receiptrテーブルの
③ user_id
の中に存在し
ているとき
④ userテーブルの
⑤ id, gender, birthdayを抽出する
SELECT … FROM…の処理
抽出結果
id gender birthday
5 1 1990-01-01
WHERE TRUE
該当レコードを抽出対象とする
Slide 143
Slide 143 text
Analytics Innovation Company ©BrainPad Inc. 142
EXISTSとINによる処理速度の違い（以下2つは同じ結果が得られる）
5-2-1. EXISTS 存在検査 解説
EXISTSを利用
SELECT
id AS user_id
,gender
,birthday
FROM
`user` AS u
WHERE EXISTS(
SELECT
1
FROM
receipt AS r
WHERE
r.user_id = u.id)
;
INを利用
SELECT
id AS user_id
,gender
,birthday
FROM
`user` AS u
WHERE u.id IN (
SELECT
r.user_id
FROM
receipt AS r)
;
INよりもEXISTSを
利用した場合の方が
高速に処理されること
が多い
INと複数行サブクエリ
を利用
Slide 144
Slide 144 text
Analytics Innovation Company ©BrainPad Inc. 143
5-2-1. EXISTS（存在検査）
userテーブルのレコードで、idがreceiptテーブルのuser_idとして存在していないものをカウン
トする。
/*
userテーブルのレコードで、idがreceiptテーブルのuser_idとして存在していないものをカウントする。
*/
類題
SQL
実行結果 レコード数 1
Slide 145
Slide 145 text
Analytics Innovation Company ©BrainPad Inc. 144
5-3. 縦持ち・横持ち
5-3-1. 縦持ち・横持ちとは
5-3-2. 横持ち→縦持ち変換
5-3-3. 縦持ち→横持ち変換
Slide 146
Slide 146 text
Analytics Innovation Company ©BrainPad Inc. 145
5-3-1. 縦持ち・横持ちとは
横持ち
• 可読性が高い、いわゆる普通のデータの保持
縦持ち
• データを行単位で保持
• SQLは行に対する処理を得意とするため、列数が多い場合など、縦持ちの方
が高速に処理できることがある
例. ユーザー情報の横持ち
id gender state_code
1 1 13
2 2 27
3 2 30
例. ユーザー情報の縦持ち
id category value
1 gender 1
1 state_code 13
2 gender 2
2 state_code 27
3 gender 2
3 state_code 30
分析目的に合わせたデータマート
を作成する際に、縦持ち横持ちの
変換をよく行う
変換
Slide 147
Slide 147 text
Analytics Innovation Company ©BrainPad Inc. 146
5-3-2. 横持ち→縦持ち変換
userテーブルのgender, state_codeを、それぞれのフィールド名をcategory、値をvalueと
した縦持ちに変換し、一時テーブルtmp_v_userとして保存する。
その後、確認のために一時テーブルtmp_v_userからデータを取得する。
/*
userテーブルのgender, state_codeを、それぞれのフィールド名をcategory、
値をvalueとした縦持ちに変換し、一時テーブルtmp_v_userとして保存する。
その後、確認のために一時テーブルtmp_v_userからデータを取得する。
*/
CREATE TEMPORARY TABLE tmp_v_user (
SELECT
id
, 'gender' AS category
, gender AS value
FROM
`user`
UNION ALL
SELECT
id
, 'state_code' AS category
, CAST(state_code AS UNSIGNED) AS value
FROM
`user`
)
;
一時テーブル作成後、確認のため以下のクエリでデータを取得
SELECT
*
FROM
tmp_v_user
ORDER BY
id, category
LIMIT
10;
例題
SQL
実行結果
Slide 148
Slide 148 text
Analytics Innovation Company ©BrainPad Inc. 147
5-3-2. 横持ち→縦持ち変換
/*
userテーブルのgender, state_codeを、それ
ぞれのフィールド名をcategory、値をvalueとし
た縦持ちに変換し、一時テーブルtmp_v_user
として保存する。
*/
CREATE TEMP TABLE tmp_v_user (
SELECT
id
, 'gender' AS category
, gender AS value
FROM
`user`
UNION ALL
SELECT
id
, 'state_code' AS category
, CAST(state_code AS
UNSIGNED) AS value
FROM
`user`
);
解説
UNION ALL
• 複数のテーブルから取得した結果セットをひとつに結合
• 重複する行を1つにまとめずに、そのまま抽出する
• UNIONだけを指定すると、重複行は削除される
UNION ALLの注意点
• フィールドの数・順番、データ型が一致している必要があ
る
• 右の例では、genderの値（整数型）とstate_codeの
値（文字列型）を整数型に揃えてvalueに格納
SQL
Slide 149
Slide 149 text
Analytics Innovation Company ©BrainPad Inc. 148
/*
userテーブルのgender, state_codeを、それ
ぞれのフィールド名をcategory、値をvalueとし
た縦持ちに変換し、一時テーブル#v_userとし
て保存する。
*/
SELECT
id
, 'gender' AS category
, gender AS value
FROM
`user`
UNION ALL
SELECT
id
, 'state_code' AS category
, CAST(state_code AS
UNSIGNED) AS value
FROM
`user`
;
5-3-2. 横持ち→縦持ち変換 解説
UNION ALLのイメージ SQL
id category value
1 gender 1
2 gender 2
3 gender 3
+ UNION ALL ＝
id category value
1 state_code 13
2 state_code 22
3 state_code 17
id category value
1 gender 1
2 gender 2
3 gender 3
1 state_code 13
2 state_code 22
3 state_code 17
1
2
結合結果
1
2
Slide 150
Slide 150 text
Analytics Innovation Company ©BrainPad Inc. 149
5-3-2. 横持ち→縦持ち変換
receiptテーブルのpaid_at, read_at, update_atを、それぞれのフィールド名をxxx_at、
値をdateとした縦持ちに変換する。
/*
receiptテーブルのpaid_at, read_at, update_atそれぞれのフィー
ルド名をxxx_at、値をdateとした縦持ちに変換する。
*/
類題
実行結果 レコード数 10
SQL
Slide 151
Slide 151 text
Analytics Innovation Company ©BrainPad Inc. 150
5-3-3. 縦持ち→横持ち変換
5-3-2で作成した縦持ちの一時テーブルtmp_v_userを横持ちに変換し、idの昇順で10件
表示する。
/*
5-3-2で作成した縦持ちの一時テーブルtmp_v_userを横持ちに変換し、10件表示する。
*/
SELECT
id
,MAX(CASE WHEN category = 'gender' THEN value END) AS gender
,MAX(CASE WHEN category = 'state_code' THEN value END) AS state_code
FROM
tmp_v_user
GROUP BY
id
ORDER BY
id
LIMIT
10;
例題
実行結果
レコード数 10
SQL
Slide 152
Slide 152 text
Analytics Innovation Company ©BrainPad Inc. 151
/*
5-3-2で作成した縦持ちの一時テーブル
tmp_v_userを横持ちに変換し、10件表示す
る。
*/
SELECT
id
,MAX(CASE
WHEN category = ‘gender’
THEN value END) AS gender
,MAX(CASE
WHEN category = ‘state_code’
THEN value END) AS state_code
FROM
tmp_v_user
GROUP BY
id
…
以下省略
5-3-3. 縦持ち→横持ち変換 解説
処理①（グループ化、集計する前のCASE文の処理）
• 結果の空欄はNULLを表す
処理②（グループ化）
• MAX()は、NULLを対象としない
SQL
SELECT
id
,CASE WHEN category = 'gender’
THEN value END AS gender
,CASE WHEN category = 'state_code’
THEN value END AS state_code
FROM
#v_user;
…
MAX(CASE WHEN category = 'gender’
THEN value END) AS gender
…
GROUP BY
id
最終結果
途中結果
Slide 153
Slide 153 text
Analytics Innovation Company ©BrainPad Inc. 152
5-3-3. 縦持ち→横持ち変換
user, receiptテーブルを用いて、都道府県(state_code)ごとの3〜5月の購入件数を表示
する。ただし、月についてはpaid_atから月部分のみ文字列を切りだす。
/*
user, receiptテーブルを用いて、
都道府県(state_code)ごとの3〜5月の購入件数を表示する。
*/
類題
実行結果
SQL
サブクエリの実行結果
receipt
id (PK)
user_id (FK)
paid_at
user
id (PK)
state_code
※本問題で用いる項目のみ掲載したER図
Slide 154
Slide 154 text
Analytics Innovation Company ©BrainPad Inc. 153
本資料の著作権は、第三者に帰属する著作権を除き、本資料を作成した株式会社ブレインパッドに帰属します。当社の許可なく無断で、複製、
改変・翻訳、販売等をすることはできません。 ただし、本資料の閲覧者は、株式会社ブレインパッドの著作物である旨を表示し、かつ、非営利目
的および本資料を改変しない場合に限り、本資料をダウンロード、プリントアウト、またはコピーし、自己のために閲覧・利用することができます。な
お、著作権法上認められている範囲内での引用を行うことは可能です。本資料を引用するには、以下の条件を満たす必要がありますので、ご留
意ください。 1. 引用先と引用部分に主従関係があること。 2. 引用部分と本文が明確に区別できること。 3. 引用する必然性があり、その範囲
についても必然性・合理性があること。 4. 出所を明示すること。 5. 部分的な改変などをせず、原文のまま引用すること。
株式会社ブレインパッド
〒108-0071 東京都港区白金台3-2-10 白金台ビル
TEL：03-6721-7002 FAX：03-6721-7010
www.brainpad.co.jp [email protected]
Analytics Innovation Company