データハンドリングのためのSQL / SQL for data handling

データハンドリングのためのSQL
〜SQLを用いた集計・分析〜
新卒社員研修
株式会社ブレインパッド
2019年版

View Slide

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. 縦持ち・横持ち

View Slide

0. はじめに
• 0-1. 付属資料
• 0-2. 本研修の目的
• 0-3. SQL研修の流れ

View Slide

0-1. 付属資料
本資料には以下の付属資料があります。併せてご参照ください。
※実際の研修では社内環境のRedshiftを使用していましたが、本資料は各人のローカル環境で手を動
かしながら学べるように、MySQL8.0に合わせて一部内容を変更しています。
本資料内容を実行するためのSQLローカル環境構築
方法をまとめた資料を同じくSpeaker Deckに公開し
ています。
https://speakerdeck.com/brainpadpr/sql-local-
environment-construction
資料中の
・環境構築
・DBデータ＆解答コード
類題に対する解答コードをGitHubにて
本資料で使用するデータや、
公開しています。
https://github.com/BrainPad/SQLForBeginners2019

View Slide

0-2. 本研修の目的
• 本研修の目的
分析業務に必須となる基本的なデータ加工技術を身につける

View Slide

0-3. SQL研修の流れ
1.例題
最初に、学ぶSQLを写経して実行
2.解説
実行したSQLの詳細を解説
3.類題
類似したSQLを自分で書いて理解
各章の中でこの
サイクルを繰り返
します

View Slide

1. データベースとSQL
1-1. データベースとは
① 利用例：家計簿アプリ
② テーブル構造
③ テーブル定義
④ 代表的なデータ型
⑤ ER図 – エンティティ
⑥ ER図 – 主キー、外部キー
⑦ RDB/DWHとDBMS
1-2. SQLとは
① クエリとSQL
② SQLでできることの例

View Slide

ユーザ情報
1-1. データベースとは① 利用例：家計簿アプリ
id … … … …
1
2
…
レシート情報
id … … … …
1
2
…
レシートアイテム情報
id … … … …
1
2
…
ログイン情報
（ユーザ名など）
読込
入力
レシート
データ登録
蓄積されたデータを活用
DB

View Slide

user
1-1. データベースとは② テーブル構造
user
id … … … …
1
2
…
user
id … … … …
1
2
…
user
id … … … …
1
2
…
user
id … … … …
1
2
…
① テーブル：表 ② テーブル名：表の名前
③ レコード｜行：1行分のデータ ④ フィールド｜列：1列分のデータ

View Slide

• テーブルで定義するもの
• テーブル名
• フィールド名 ※カラム名と呼ぶこともある
• データ型
1-1. データベースとは③ テーブル構造
user
id name … … …
1 佐藤
2 田中
…
…
user
id name … … …
1 佐藤
2 田中
…
…
例1
フィールド名： id
データ型：整数型
例2
フィールド名： name
データ型：文字列型
テーブル名：user

View Slide

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(偽)のみ論理値

View Slide

1-1. データベースとは④ 代表的なデータ型
• データ型にまつわる失敗例1（エラーになる）
文字列 ’aaa’ を数値に変換しようとする
• データ型にまつわる失敗例2（エラーにはならない）
文字列で格納された ’1’ と ’2’ を足したつもり
‘1’ + ‘2’ 結果 ‘12’ （文字列が連結）
整数値で格納された1と2を足した
1 + 2 結果 3（整数値）
• このように、エラーでなくとも、望む結果にならないことがあるので、データ型をしっか
り確認する必要がある

View Slide

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
…
ユーザ情報レシート情報レシートアイテム情報

View Slide

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)という

View Slide

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図で、テーブル定義とテーブル間
の関係を把握することができる

View Slide

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図 – 例（物理モデル）
公開しているサンプルデータでは一部
のカラムのみのデータになっています

View Slide

1-1. データベースとは⑦ RDB/DWHとDBMS
• リレーショナルデータベース(RDB)
• データを複数のテーブル（表）として管理し、テーブル間の関係を定義することで複雑な
データの関連性を扱えるようにした管理方式
• データウェアハウス(DWH)
• 列指向データベースで大規模なデータ集計や分析に用いられる
• DBMS
• データベースマネジメントシステムの略
• データベースの構築、運用、管理を行うソフトウェア

View Slide

1-2. SQLとは① クエリとSQL
• データベースに対する問い合わせ、命令をクエリという
• データベースに命令する際に用いられる言語をSQLという
• 由来はStructured Query Language
人間 & データベースの場合
言語：日本語言語：SQL
質問「△△教えて」
返事「○○だよ」
人間＆人間の場合
問い合わせ
応答
DB
クエリ：SELECT * FROM …
○○, △△…
問い合わせ
応答

View Slide

1-2. SQLとは② SQLにできることの例
操作の例対応する句や関数の例
必要な列を選択する SELECT
レコードを検索する、フィルタをかける WHERE
重複したレコードを省く DISTINCT
レコードを並べ替える ORDER BY
列の結合 JOIN
四則演算、集合演算、数学演算、日付演算 UNION, ABS, DATEDIFF
値の追加、削除、更新 INSERT, UPDATE, DELETE
集計（集計関数の利用） COUNT, SUM, MAX
テーブル、ユーザ作成、権限の付与 CREATE, GRANT

View Slide

• 受託分析ではデータ分析を開始するに当たって、最初にお客様からデータを受領する
1.データ 3.バリュー
2.受託分析会社
データ受領
お客様お客様
データ分析
データ加工
• 分析処理
• 検索、フィルタリング、ソート、集計、結合など
• 管理
• 一元管理、統一的なアクセス
• 簡単に削除、変更されない
• 同時アクセスを可能、破壊されない、整合性
• アクセス権限の管理など
データ基盤：RDB
操作：SQL
実現

View Slide

2. SQLの基礎①
2-3. GROUP BY(グループ化), HAVING(絞り込み)

View Slide

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. コーディング規約(簡易版)

View Slide

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

View Slide

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
写経の際の注意点
• 大文字, 小文字を使い分ける
• インデントを下げる
• カンマを書く位置を統一する
※その他のコーディング規約は後述

View Slide

 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

View Slide

/*
receiptテーブルからid, user_id,
paid_atの3つのフィールドに対して
3件分のデータを取得する
*/
SELECT
id
,user_id
,paid_at
FROM
receipt
LIMIT
3;
解説
 SELECT
• 「どの項目（列）のデータを取得するか」を指定
 FROM
• 「どのテーブルから検索するか」を指定
 コメント（SQLの動作に関与しないメモ）の書き方
• /* （コメント）*/ 複数行
• -- （コメント）一行
 LIMIT句
• 取得するデータの件数を制限
 セミコロン ;
• SQLの最後に必ずセミコロンをつける ;
SQL

View Slide

receipt_itemテーブルから次のフィールドに対して2件分データを取得する
抽出フィールド：id, user_id, receipt_id, price
/*
receipt_itemテーブルから次のフィールドに対して2件分データを取得する
抽出フィールド：id, user_id, receipt_id, price
*/
類題
SQL

View Slide

receiptテーブルのuser_idフィールドに対して、「重複を許した場合の件数」と「重複を除いた
場合の件数」をそれぞれ取得する
/*
receipt テーブルのuser_idフィールドに対して、
「重複を許した場合の件数」
「重複を除いた場合の件数」をそれぞれ取得する
*/
SELECT
COUNT(user_id) AS `user`
,COUNT(DISTINCT user_id) AS distinct_user
FROM
receipt;
例題
SQL
バッククォート

View Slide

/*
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の場合は ” (ダブルクォーテーション)

View Slide

receipt_itemテーブルのreceipt_idフィールドに対して「重複を許した場合の件数」と「重複
を除いた場合の件数」をそれぞれ取得し、列名を別名に変更する
※フィールド名は自分で適当なものをつける
/*
receipt_itemテーブルのreceipt_idフィールドに対して
「重複を除いた場合の件数」をそれぞれ取得し、列名を別名に変更する
*/
類題
SQL

View Slide

userテーブルの全てのフィールドを5件分、データ取得する。
ただし、last_loginについて昇順に並び替えて表示する。
/*
user テーブルの全てのフィールドを5件分、データ取得する
idについて昇順に並び替えて表示する
*/
SELECT
*
FROM
`user`
ORDER BY
last_login
LIMIT
5;
例題
SQL

View Slide

/*
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

View Slide

receiptテーブルの全てのフィールドを5件分、データ取得する
ただし、 user_idの昇順、read_atの降順に並び替える
/*
receiptテーブルの全てのフィールドに5件分、データ取得する
ただし、 user_idの昇順、read_atの降順に並び替える
*/
類題
SQL
昇順降順

View Slide

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

View Slide

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条件

View Slide

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

View Slide

/*
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
比較演算子意味
>= 以上
<= 以下
> より大きい
< より小さい
= 等しい
<> , != 等しくない

View Slide

userテーブルで、誕生日が2006年1月1日以降の人のbirthday, genderの2つのフィール
ドについて5件分データ取得し、誕生日の昇順に並び替えて表示する。
/*
userテーブルで、誕生日が2006年1月1日以降の人のbirthday, genderの
2つのフィールドについて5件分データ取得し、誕生日の昇順に並び替えて表示する。
*/
類題
SQL
2006年1月1日以降
昇順

View Slide

userテーブルで、性別が1（男性）かつstate_codeが’13’（東京）である人の総数を取
得する
/*
userテーブルで、性別が1（男性）かつstate_codeが’13’（東京）である人の総数を取得する。
*/
SELECT
COUNT(*)
FROM
`user`
WHERE
gender = 1
AND state_code = ’13’
;
例題
SQL
シングルクォーテーション（コピペ時は注意！）

View Slide

/*
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
シングルクォーテーション
（コピペ時は注意！）

View Slide

userテーブルで、「性別が2（女性）」かつ「state_codeが’13’（東京）または’27’（大
阪）」である人の総数を取得する
/*
userテーブルで、「性別が2（女性）」かつ「state_codeが’13’（東京）または’27’（大阪）」
である人の総数を取得する
*/
類題
SQL

View Slide

receiptテーブルで、shop_nameに文字列「コンビニ」を含むデータについて、全てのフィールド
を5件分取得する。
/*
receiptテーブルで、shop_nameに「コンビニ」を含むデータについて、全てのフィールドを5件分取得する。
*/
SELECT
*
FROM
receipt
WHERE
shop_name LIKE ‘%コンビニ%’
LIMIT
5;
例題
SQL
「コンビニ」を含む
シングルクォーテーション（コピペ時は注意！）

View Slide

/*
receiptテーブルで、shop_nameに「コン
ビニ」を含むデータについて、全てのフィール
ドを5件分取得する。
*/
SELECT
*
FROM
receipt
WHERE
shop_name
LIKE ‘%コンビニ%’
LIMIT
5;
解説
 LIKE
• 文字列があるパターンに合致しているかをチェック
• 部分一致の検索が簡単に行える
 %を用いたパターン例
 %や_を含む文字列をLIKEで探したいとき
• ESCAPE句を用いる（詳細は各自調べてください）
SQL
パターン文字意味
% 任意の0文字以上の文字列
_（アンダースコア）任意の1文字
%○○% 「○○」を含む文字列
%○○ 「○○」で終わる文字列
○○% 「○○」で始まる文字列シングルクォーテーション
（コピペ時は注意！）

View Slide

receiptテーブルで、shop_nameが文字列「B」で終わるデータについて、全てのフィールドを5
件分取得する
/*
receiptテーブルで、shop_nameが文字列「B」で終わるデータについて、
全てのフィールドを5件分取得する
*/
類題
SQL
「B」で終わる

View Slide

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

View Slide

/*
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

View Slide

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

View Slide

userテーブルでstate_codeが’13’（東京）または’27’（大阪）である行をカウントする
/*
userテーブルでstate_codeが’13’（東京）または’27’（大阪）である行をカウントする
*/
SELECT
COUNT(*)
FROM
`user`
WHERE
state_code IN (‘13’, ’27’)
;
例題
SQL

View Slide

/*
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

View Slide

receipt_itemテーブルで、nameが文字列「まぐろ」「りんご」であるデータについて、id,
user_id, receipt_id, nameを5件取得する。ただし、idの昇順に並べる。
/*
receipt_itemテーブルで、nameが文字列「まぐろ」「りんご」であるデータについて、
id, user_id, receipt_id, nameを5件取得する。ただし、idの昇順に並べる。
*/
類題
SQL

View Slide

2-3. GROUP BY
2-3-1. GROUP BY① 集計関数
• COUNT, SUM, AVG, MAX, MINなど
2-3-2. GROUP BY② HAVING 集計結果に条件指定
• WHEREとHAVINGの違い
• ROUND

View Slide

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`
;
例題
SQL

View Slide

 GROUP BYの集計イメージ
解説
receipt_item テーブル
… receipt_id … price …
1 100
1 200
2 300
2 400
2 500
…
…
/*
receipt_id毎にグループ化した
レコードに対して、レコード数と、金額
priceの合計値・平均値・最大値を求め
る
*/
SELECT
receipt_id
FROM
receipt_item
WHERE
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

View Slide

2-3-1. GROUP BY① 集計関数解説
 集計関数
 グループ集計においてSELECTで指定できるもの
1. GROUP BYで指定されている基準列
2. 集計関数の集計対象
SQL
集計関数意味
COUNT レコード数
SUM 合計値
AVG 平均値
MAX 最大値
MIN 最小値
VARIANCE 分散
STDDEV 標準偏差
/*
receipt_id毎にグループ化した
レコードに対して、レコード数と、金額
priceの合計値・平均値・最大値を求め
る
*/
SELECT
receipt_id
FROM
receipt_item
WHERE
GROUP BY
receipt_id
ORDER BY
`count`;

View Slide

receipt_itemテーブルで、receipt_id毎にグループ化したレコードの金額priceの最小値を
求め、5件分表示する。ただし、receipt_idの昇順に並べる。
/*
receipt_itemテーブルで、receipt_id毎にグループ化したレコードの金額priceの
最小値を求め、5件分表示する。ただし、receipt_idの昇順に並べる。
*/
類題
SQL

View Slide

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;
例題
SQL

View Slide

 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である
ものを抽出
実行結果

View Slide

/*
*/
SELECT
receipt_id
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

View Slide

/*
*/
SELECT
receipt_id
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
①検索
②集計
③抽出

View Slide

receipt_itemテーブルで、receipt_id毎にグループ化し、レコードの金額priceに対して最大
値を求め、最大値が1000以上のものを5件取得する。ただし、receipt_idの昇順に並び替
えて表示する。
/*
receipt_itemテーブルで、receipt_id毎にグループ化し、レコードの金額priceに対して最大値を求め、最大値が1000以上のものを5件取
得する。ただし、receipt_idの昇順に並び替えて表示する。
*/
類題
SQL

View Slide

 実行順序
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

View Slide

3. SQLの基礎②
3-1. 条件式
3-2. データ型と型変換
3-3. 文字列関数
3-4. 日付関数

View Slide

3-1. 条件式
3-1-1. 条件式① CASE
3-1-2. 条件式② COALESCE
IS NULL, IS NOT NULL

View Slide

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;
例題
SQL

View Slide

 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

View Slide

userテーブルでstate_codeが’27’（大阪）であるユーザーの総数を求める。
（state_codeが’27’であるとき1、他は0のフラグを作成して合計する）
/*
（state_codeが’27’であるとき1、他は0のフラグを作成して合計する）
*/
類題
SQL

View Slide

【別解】COUNT, DISTINCT, CASEを組み合わせた方法
/*
【別解】COUNT, DISTINCT, CASEを組み合わせた方法
*/
SELECT
COUNT(DISTINCT CASE WHEN state_code = ‘27’ THEN id ELSE NULL END) AS `sum`
FROM
`user`
;
補足
SQL
処理①
state_code=’27’：id
それ以外：NULL
処理②
重複を除外してidの総数を集計
※NULLはカウントされない
※今回はidの重複はないため省略
しても良い
NULLとは
何も格納されていない、未定義
0や空白文字とも異なる

View Slide

NULLをもつフィールド`null`と、NULLを文字列‘不明’に置換したフィールド`co_null`を1件取得する。（テー
ブルは不要なので、FROM句も不要）
/*
NULLをもつフィールド`null`と、NULLを文字列‘不明’に置換したフィール
ド`co_null`を取得する。
*/
SELECT
NULL AS `null`
,COALESCE(NULL, ‘不明’) AS co_null
;
例題
SQL

View Slide

 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を‘不明’に置換

View Slide

NULLをもつフィールド`null`と、NULLを整数0に置換したフィールド`co_null`を1件取得する。（テーブルは
不要なので、FROM句も不要）
/*
NULLをもつフィールド`null`と、NULLを整数0に置換したフィールド`co_null`を取得する。
*/
類題
SQL
NULLを0に置換

View Slide

3-2. データ型と型変換
3-2-1. 代表的なデータ型
数値型
文字列型
日付型
3-2-2. 型変換 CAST
timestamp型 → date型
varchar型 → date型

View Slide

3-2-1. 代表的なデータ型
• データ型はデータ加工や集計操作で失敗しやすい
• エラーが出たときに確認するポイント
• 「データ型の定義がきちんと行われているか」
• 「関数の引数に正しいデータ型を用いているか」
数値型
int
tinyint
bigint
符号付き整数
float
real
浮動小数点数値
文字列型
char(n) 固定長の文字列（nはバイト数）
varchar(n) 可変長の文字列（nはバイト数）
日付型
date 日付
timestamp 日付時刻
論理値型 boolean 真（True）か偽（False）

View Slide

receiptテーブルで、paid_atをtimestamp型→date型に変換(cast_paid_at)する。
・抽出フィールド：id, paid_at, cast_paid_at
・idの昇順で5件分表示
/*
receiptテーブルで、paid_atをtimestamp型→date型に変換(cast_paid_at)する。
*/
SELECT
id
,paid_at
,CAST(paid_at AS DATE) AS cast_paid_at
FROM
receipt
ORDER BY
id
LIMIT
5;
例題
SQL

View Slide

 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)する。
*/
SELECT
id
,paid_at
,CAST(paid_at AS DATE)
AS cast_paid_at
FROM
receipt
ORDER BY
id
LIMIT
5;
SQL
varchar型 date型

View Slide

userテーブルで、create_dateをtimestamp型→ date型に変換する。
・抽出フィールド：id, create_date, 型変換後のcreate_date
・ idの昇順で5件分表示
/*
userテーブルで、create_dateをtimestamp型→date型に変換する。
・抽出フィールド：id, create_date, 型変換後のcreate_date
*/
類題
SQL

View Slide

3-3. 文字列関数
3-3-1. 文字列関数① SUBSTRING
3-2-2. 文字列関数② REPLACE, LENGTH

View Slide

receiptテーブルで、paid_atから西暦部分を取り出す(paid_yearとする)。
・抽出フィールド：id, paid_at, paid_year
/*
receiptテーブルで、paid_atから西暦部分を取り出す(paid_yearとする)。
*/
SELECT
id
,paid_at
,SUBSTRING(paid_at, 1, 4) AS paid_year
FROM
receipt
ORDER BY
id
LIMIT
5;
例題
SQL
文字列

View Slide

 SUBSTRING
• 文字列の一部分だけを取り出す
• SUBSTRING(文字列, 開始位置, 抽出文字数)
 例文字列’20180402’の5文字目から2文字分を抽出
SELECT
SUBSTRING(‘20180402’, 5, 2) AS month;
※DBMS製品によって、文字数指定かバイト数指定か異なる
解説
/*
receiptテーブルで、paid_atから西暦部分を取り
出す(paid_yearとする)。
*/
SELECT
id
,paid_at
,SUBSTRING(paid_at, 1, 4)
AS paid_year
FROM
receipt
ORDER BY
id
LIMIT
5;
SQL
’20180402’
5文字目
2文字分

View Slide

userテーブルで、last_loginからを月を取り出す(last_login_monthとする)。
・抽出フィールド：id, last_login, last_login_month
/*
userテーブルで、last_loginからを月を取り出す(last_login_monthとする)。
・抽出フィールド：id, last_login, last_login_month
*/
類題
SQL

View Slide

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
/*
receiptテーブルでshop_nameの文字数を取得する(length_shop_nameとする)。
また、shop_nameの ’スーパー’ を ’デパート’ に置換する(replace_shop_nameとする)。
・抽出フィールド：id, shop_name, length_shop_name, replace_shop_name
*/
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

View Slide

 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
*/
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
置換

View Slide

receipt_itemテーブルでnameの文字数を取得する(length_nameとする)。
また、nameの ’スナック’ を ’ポテトチップス’ に置換する(replace_nameとする)。
・抽出フィールド：id, name, length_name, replace_name
/*
receipt_itemテーブルでnameの文字数を取得する(length_nameとする)。
また、nameの ’スナック’ を ’ポテトチップス’ に置換する(replace_nameとする)。
抽出フィールド：id, name, length_name, replace_name ※idの昇順で5件分表示
*/
類題
SQL

View Slide

3-4. 日付関数
3-4-1. 日付関数
CURRENT_DATE
DATEDIFF
3-4-2. 誕生日から年齢を求める

View Slide

3-4-1. 日付関数 CURRENT_DATE, DATEDIFF
receiptテーブルで、現在の日付(current_dateとする)、
及びpaid_atの日付から2018年04月02日までの日数を計算する(date_diffとする)。
・抽出フィールド：id, paid_at, current_date, date_diff
/*
receiptテーブルで、現在の日付(current_dateとする)、
及びpaid_atの日付から2018年04月02日までの日数を計算する(date_diffとする)。
・抽出フィールド：id, paid_at, current_date, date_diff
*/
SELECT
id
,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
実行日が格納される

View Slide

 CURRENT_DATE
• 現在の日付を取得する
 DATEDIFF
• 2つの日付または時刻式の日付部分の差を返す
• DATEDIFF(日付①, 日付②)
• 日付①-日付②を返す
• RedshiftのDATEDIFFと少し異なります
解説
/*
receiptテーブルで、現在の日付(current_date
とする)、
及びpaid_atの日付から2018年04月02日まで
の日数を計算する(date_diffとする)。
・抽出フィールド：id, paid_at, current_date,
date_diff
*/
SELECT
id
,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

View Slide

userテーブルで、birthdayの日付から現在の日付までの日数を計算する(date_diffとする)。
・抽出フィールド：id, birthday, current_date, date_diff
/*
userテーブルで、birthdayの日付から現在の日付までの日数を計算する(date_diffとする)。
・抽出フィールド：id, birthday, current_date, date_diff
*/
類題
SQL
実行日によって結果が異なります

View Slide

userテーブルのbirthdayから、現在の日付におけるユーザーの年齢を求める。
・抽出フィールド：id, current_date, birthday, age
/*
userテーブルのbirthdayから、現在の日付におけるユーザーの年齢を求め
る。
・抽出フィールド：id, current_date, birthday, age
*/
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;
例題
SQL

View Slide

 誕生日から年齢を求める手順
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
*/
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

View Slide

userテーブルのbirthdayから、最終ログインの日付におけるユーザーの年齢を求める。
・抽出フィールド：id, last_login, birthday, age_at_last_login
/*
userテーブルのbirthdayから、最終ログインの日付におけるユーザーの年齢を求める。
・抽出フィールド：id, last_login, birthday, age_at_last_login
*/
類題
SQL

View Slide

4. SQLの応用
4-1. サブクエリ（副問い合わせ）
4-2. テーブルの結合
4-3. 一時テーブルの作成

View Slide

4-1. サブクエリ
4-1-1. サブクエリとは
4-1-2. パターン① 単一の値を返す
4-1-3. パターン② 複数の値を返す
4-1-4. パターン③ 表形式の値を返す
4-1-5. WITH句サブクエリの代用

View Slide

例「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
1 1 100 A
2 1 2000 B
3 2 300 C
4 2 1500 D
5 3 800 E
代入

View Slide

結果を代入するのではなく、クエリそのものを条件に代入する
クエリが入れ子（ネスト構造）になる
手順1
priceの最大値を求め、結果をメモ
SELECT MAX(price) FROM receipt_item;
手順2
手順1の結果2000を条件に検索
SELECT name FROM receipt_item
WHERE price =
;
結果 B
receipt_item
1 1 100 A
2 1 2000 B
3 2 300 C
4 2 1500 D
5 3 800 E
receipt_item
1 1 100 A
2 1 2000 B
3 2 300 C
4 2 1500 D
5 3 800 E
括弧で囲み代入
（;は不要）

View Slide

前ページのクエリを整理すると、以下のようになる
SELECT
name
FROM
receipt_item
WHERE
price = (SELECT MAX(price) FROM receipt_item)
;
他のSQLの一部分として登場するSELECT文のことを、サブクエリや副問い合わせと
いう。
receipt_item
1 1 100 A
2 1 2000 B
3 2 300 C
4 2 1500 D
5 3 800 E

View Slide

サブクエリの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行

View Slide

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

View Slide

 単一行サブクエリ
• 検索結果が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

View Slide

receipt_itemテーブルで、user_id, priceとpriceの標準偏差を並べて表示する。
ただし、user_idの昇順で5件表示とし、標準偏差はSTDDEV_POP()を使用せよ。
/*
receipt_itemテーブルで、user_id, priceとpriceの標準偏差を並べて表示する。
ただし、user_idの昇順で5件表示とし、標準偏差はSTDDEV_POP()を使用せよ。
*/
類題
SQL

View Slide

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図

View Slide

 複数行サブクエリ
• 検索結果がn行1列の複数の値となるサブクエリ
… WHERE id IN (SELECT DISTINCT user_id
FROM receipt_item
… 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
…
（以下省略）
SQL
user_id
10
20
…

View Slide

receipt_itemテーブルとuserテーブルを用いて、name(商品名)が’カップラーメンA‘を買った
ことのある人を性別にカウントする。
/*
receipt_itemテーブルとuserテーブルを用いて、name(商品名)が’カップラーメンA‘を
買ったことのある人を性別にカウントする。
*/
類題
SQL
id
user_id (FK)
name
…
receipt_item
id
gender
…
user ER図

View Slide

4-1-4. サブクエリパターン③ 表形式の値を返す
receipt_itemテーブルを用いて、1回の買い物の合計金額の平均値を求める。
/*
receipt_itemテーブルを用いて、1回の買い物の合計金額の平均
値を求める。
*/
SELECT
AVG(sum_price)
FROM
(SELECT receipt_id
FROM receipt_item
GROUP BY receipt_id) AS tbl
;
例題
実行結果
レコード数1
SQL
id
user_id (FK)
price
…
receipt_item

View Slide

 表形式の結果となるサブクエリ
• 検索結果がn行m列の表となるサブクエリ
… FROM
(SELECT receipt_id
FROM receipt_item
… FROM
• この表のsum_priceの平均値を求めている
解説
/*
receipt_itemテーブルとuserテーブルを用いて、1
回の買い物の合計金額の平均値を求める。
*/
SELECT
AVG(sum_price)
FROM
(SELECT receipt_id
FROM receipt_item
;
SQL
receipt_id sum_price
1 409
2 198
…
…

View Slide

receipt_itemテーブルを用いて、1ユーザーあたりの累計合計金額の平均値を
求める。
/*
receipt_itemテーブルを用いて、1ユーザーあたりの累計合計金額の平均値を求める。
ヒント ① サブクエリでuser_idでグループ化し、合計金額を求める ② サブクエリの結果をもとに平均値を求める
*/
類題
SQL
実行結果
レコード数1
id
user_id (FK)
price
…
receipt_item

View Slide

4-1-4.例題と同じく、receipt_itemテーブルを用いて、1回の買い物の合計金額の平均値
を求める。ただし、サブクエリの代わりにWITH句を利用する。
/*
receipt_itemテーブルを用いて、1回の買い物の合計金額の平均
値を求める。ただし、サブクエリの代わりにWITH句を利用する。
*/
WITH tbl AS (
SELECT receipt_id
FROM receipt_item
GROUP BY receipt_id
)
SELECT
AVG(sum_price)
FROM
tbl;
例題
実行結果
レコード数1
SQL
id
user_id (FK)
price
…
receipt_item

View Slide

 WITH句（WITH問い合わせ）
• サブクエリに当たる部分をもとのSQLと切り離す
• サブクエリが入れ子になるのに対して、WITH句の場合は構造
がわかりやすい（可読性が高い）
• WITH句はカンマで区切ることで複数続けて書くこともできる
例：WITH tblA AS(…), tblB AS(…) SELECT ・・・;
解説
/*
receipt_itemテーブルを用いて、1回の買い物の
合計金額の平均値を求める。
ただし、サブクエリの代わりにWITH句を利用する。
*/
WITH tbl AS (
SELECT receipt_id
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句で呼び出す場合サブクエリで呼び出す場合

View Slide

receiptテーブルを用いて、「1ユーザーあたりのレシート登録数」の平均値を求める。
/*
receiptテーブルを用いて、「1ユーザーあたりのレシート登録数」の平均値を求める。
• ヒント①先ずはWITH句を使って、ユーザーIDごとのレシート登録数を集計する。
• ユーザーID,レシート登録数の２列からなる表を作成する
• ヒント②ヒント①で作成した表から、レシート登録数の平均を計算する。
*/
類題
SQL
実行結果
レコード数1
id
user_id (FK)
price
…
receipt_item

View Slide

4-2. テーブルの結合
4-1-1. テーブルの結合とは
4-2-2. INNER JOIN
内部結合
4-2-2. LEFT JOIN
外部結合

View Slide

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

View Slide

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;
例題
SQL
id
user_id (FK)
shop_name
…
receipt
id
state_code
…
user ER図

View Slide

 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

View Slide

 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の行
が消えている)
• 内部結合はデータ件数の不一致が生じてしまう場合があるため、注意が必要
• 結合相手がいない場合にもデータを残す結合は外部結合で行う（後述）

View Slide

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の昇順で5件分表示
*/
類題
SQL
id
user_id (FK)
receipt_id
…
receipt_item
id
gender
…
user ER図

View Slide

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;
例題
SQL
id
user_id (FK)
shop_name
…
receipt
id
state_code
…
user ER図

View Slide

 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

View Slide

 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を用いる

View Slide

receipt_itemテーブル(左表)とuserテーブル(右表)を、userテーブルのidと
receipt_itemテーブルのuser_idを用いて外部結合する。
・receipt_itemテーブルのid >= 135を対象とし、昇順で5件分表示する。
/*
receipt_itemテーブル(左表)とuserテーブル(右表)を、
userテーブルのidとreceipt_itemテーブルのuser_idを用いて外部結合する。
・receipt_itemテーブルのid >=135を対象とし、昇順で5件分表示
*/
類題
SQL
id
user_id (FK)
receipt_id
…
receipt_item
id
gender
…
user ER図

View Slide

4-3. 一時テーブル
4-3-1. 一時テーブルの作成

View Slide

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
一時テーブル作成
一時テーブルからデータ取得結果

View Slide

CREATE TABLE 〈テーブル名〉 (); テーブルの作成
GRANT ALL ON 〈テーブル名〉 TO PUBLIC; 作成したテーブルに権限を付ける
1. テーブルを作る
2. テーブルを消す
DROP TABLE 〈テーブル名〉 ; テーブルの削除
DROP TABLE IF EXISTS 〈テーブル名〉 ; テーブルが存在している場合のみ削除
3.一時テーブルを作る
CREATE TEMPORARY TABLE 〈テーブル名〉 () ; セッション中に一時的に作成
 テーブル作成・削除に用いるSQL
解説

View Slide

userテーブルからcreate_dateが2013年2月のものを抽出し、一時テーブルuser_201302として保存せよ。
また、確認のため作成した一時テーブルuser_201302からデータを5件取得せよ。
/*
userテーブルからcreate_dateが2013年2月のものを抽出し、一時テーブルuser_201302として保存せよ。
また、確認のため作成した一時テーブルuser_201302からデータを5件取得せよ。
*/
類題
SQL
一時テーブル作成結果
一時テーブルからデータ取得結果

View Slide

5. SQLの応用②

View Slide

5-1-1. ウィンドウ関数とは
5-1-2. 集計関数群
5-1-3. ランク付け関数群
5-1-4. ラグ・リード関数群
5-1-5. 参考

View Slide

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 など

View Slide

5-1-2. ウィンドウ関数① 集計関数群
receipt_itemテーブルで、user_idで区切ったパーティション毎に
SUM, AVG, MIN, MAXで集計を行う。
・user_idが5以下のデータを対象とする。
/*
receipt_itemテーブルで、user_idで区切ったパーティション毎にSUM, AVG,
MIN, MAXで集計を行う。
*/
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
;
例題
SQL
… …

View Slide

 ウィンドウ
• 基本構文
関数 OVER(PARTITION BY フィールド ORDER BY フィールド)
例. SUM() OVER(PARTITION BY user_id)
user_id毎で分割して、その集合内で合計する
• ウィンドウ関数は、結果の行を集計するグループ関数とは異なり、
テーブルのすべての行が保持される
（詳細は次のページを参照）
解説
/*
receipt_itemテーブルで、user_id区切ったパー
ティション毎にSUM, AVG, MIN, MAXで集計を
行う。
*/
SELECT
user_id
,price
,SUM(price)
OVER(PARTITION BY user_id)
,AVG(price)
,MIN(price)
…（省略）…
FROM
receipt_item
…
（以下省略）
SQL
… …

View Slide

 ウィンドウ関数の集計関数SUM
SELECT
user_id
,price
,SUM(price)
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での集計と、ウィンドウ関数での集計結果の違いを知る
… …

View Slide

receiptテーブルで、user_idで区切ったパーティション毎にCOUNTで集計を行う。
/*
receiptテーブルで、user_idで区切ったパーティション毎にCOUNTで集計を行う。
*/
類題
SQL

View Slide

5-1-3. ウィンドウ関数② ランク付け関数群
receiptテーブルで、user_idで区切ったパーティション毎にpaid_atの昇順で
連番を振る。
・user_idが5以下、paid_atが’2010-01-31’以前のデータを対象とする。
/*
receiptテーブルで、user_idで区切ったパーティション毎にpaid_atの昇順で
連番を振る。
*/
SELECT
user_id
,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
;
例題
SQL

View Slide

 ランク付け関数
 例 XX() OVER(PARTITION BY id ORDER BY year)
解説
/*
receiptテーブルで、user_idで区切ったパーティション毎
にpaid_atの昇順で連番を振る。
*/
SELECT
user_id
,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

View Slide

 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
;
解説
サブクエリ（次ページで解説）

View Slide

 サブクエリ部分
解説
(SELECT
*
,ROW_NUMBER() OVER(PARTITION BY user_id ORDER BY price DESC) AS seq
FROM
receipt_item) AS ri
※都合により一部のカラムのみ掲載
サブクエリによって得られたテーブル ri
最終結果
WHERE
seq = 1
各グループの一
番上を取得
priceの降順に連番を振る
…

View Slide

receipt_itemテーブルで、receipt_id毎にpriceの最大値を求める。
・receipt_idが10以下のデータを対象とする。
/*
receipt_itemテーブルで、receipt_id毎にpriceの最大値を求める。
・receipt_idが10以下のデータを対象とする。
*/
類題
SQL
（参考）サブクエリ部分の取得例

View Slide

5-1-4. ウィンドウ関数③ ラグ・リード関数群
receipt_itemテーブルで、user_idで区切ったパーティション毎にidの昇順に並び替え、priceについて1行前の
値をlag1_price、2行前の値をlag2_priceとして取得する。
・抽出フィールド：user_id, id, price, lag1_price, lag2_price
/*
receipt_itemテーブルで、user_idで区切ったパーティション毎にidの昇順に
並び替え、priceについて1行前の値をlag1_price、2行前の値を
lag2_priceとして取得する。
・抽出フィールド：user_id, id, price, lag1_price, lag2_price
*/
SELECT
user_id
,id
,price
,LAG(price, 1) OVER(PARTITION BY user_id
ORDER BY id) AS lag1_price
FROM
receipt_item
WHERE
user_id <= 3
ORDER BY
user_id, id
;
例題
実行結果
SQL
…

View Slide

ラグ・リード関数群
解説
/*
receipt_itemテーブルで、user_idで区切ったパーティション
毎にidの昇順に並び替え、priceについて1行前の値を
lag1_price、2行前の値をlag2_priceとして取得する。
・抽出フィールド：user_id, id, price, lag1_price,
lag2_price
*/
SELECT
user_id
,id
,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)

View Slide

receipt_itemテーブルで、user_idで区切ったパーティション毎にidの昇順に並び替え、priceについて
1行後の値をlead1_price、2行後の値をlead2_priceとして取得する。
・抽出フィールド：user_id, id, price, lead1_price, lead2_price
/*
receipt_itemテーブルで、user_idで区切ったパーティション毎にidの昇順に並び替え、priceについて1行後の値をlead1_price、2行後の
値をlead2_priceとして取得する。
・抽出フィールド：user_id, id, price, lead1_price, lead2_price
*/
類題
SQL
…

View Slide

5-1-5. ウィンドウ関数④ 参考
• ウィンドウ関数全体の基本構文（※関数によって細かな差があるので注意）
関数(集計フィールド名)
OVER(PARTITION BY フィールド名
ORDER BY フィールド名
ROWS 集計対象行の範囲)
PARTITION BY
• パーティションを分ける。「○○別で●●順に集計」の○○
• 省略するとテーブル全体について集計
ORDER BY
• 各パーティション内の行の順序。「○○別で●●順に集計」の●●
ROWS
• 集計処理する対象行を定義する（詳細は次のページで説明）

View Slide

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

View Slide

5-2-1. EXISTS 存在検査

View Slide

userテーブルのレコードで、idがreceiptテーブルのuser_idとして存在しているものを抽出する。
・抽出フィールド：id, gender, birthday
・user_idの昇順で5件分表示する
/*
userテーブルのレコードで、idがreceiptテーブルのuser_idとして存在している
ものを抽出する。
*/
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

View Slide

 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と
して存在しているものを抽出する。
*/
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

View Slide

 EXISTS句による存在検査のイメージ
5-2-1. EXISTS 存在検査解説
/*
userテーブルのレコードで、idがreceiptテーブルのuser_idと
して存在しているものを抽出する。
*/
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の中に存
在しているとき
⑤ id, gender, birthday
を抽出する
WHERE EXISTSの中の処理
① userテーブルidが
② receiptrテーブルの
③ user_id
の中に存在し
ているとき
⑤ id, gender, birthdayを抽出する
SELECT … FROM…の処理
抽出結果
id gender birthday
5 1 1990-01-01
WHERE TRUE
該当レコードを抽出対象とする

View Slide

 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と複数行サブクエリ
を利用

View Slide

5-2-1. EXISTS（存在検査）
userテーブルのレコードで、idがreceiptテーブルのuser_idとして存在していないものをカウン
トする。
/*
userテーブルのレコードで、idがreceiptテーブルのuser_idとして存在していないものをカウントする。
*/
類題
SQL

View Slide

5-3-1. 縦持ち・横持ちとは
5-3-2. 横持ち→縦持ち変換
5-3-3. 縦持ち→横持ち変換

View Slide

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
分析目的に合わせたデータマート
を作成する際に、縦持ち横持ちの
変換をよく行う
変換

View Slide

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
実行結果

View Slide

/*
userテーブルのgender, state_codeを、それ
ぞれのフィールド名をcategory、値をvalueとし
た縦持ちに変換し、一時テーブルtmp_v_user
として保存する。
*/
CREATE TEMP TABLE tmp_v_user (
SELECT
id
, gender AS value
FROM
`user`
UNION ALL
SELECT
id
, CAST(state_code AS
UNSIGNED) AS value
FROM
`user`
);
解説
 UNION ALL
• 複数のテーブルから取得した結果セットをひとつに結合
• 重複する行を1つにまとめずに、そのまま抽出する
• UNIONだけを指定すると、重複行は削除される
 UNION ALLの注意点
• フィールドの数・順番、データ型が一致している必要があ
る
• 右の例では、genderの値（整数型）とstate_codeの
値（文字列型）を整数型に揃えてvalueに格納
SQL

View Slide

/*
userテーブルのgender, state_codeを、それ
ぞれのフィールド名をcategory、値をvalueとし
た縦持ちに変換し、一時テーブル#v_userとし
て保存する。
*/
SELECT
id
, gender AS value
FROM
`user`
UNION ALL
SELECT
id
, 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

View Slide

receiptテーブルのpaid_at, read_at, update_atを、それぞれのフィールド名をxxx_at、
値をdateとした縦持ちに変換する。
/*
receiptテーブルのpaid_at, read_at, update_atそれぞれのフィー
ルド名をxxx_at、値をdateとした縦持ちに変換する。
*/
類題
SQL

View Slide

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

View Slide

/*
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
最終結果
途中結果

View Slide

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図

View Slide

本資料の著作権は、第三者に帰属する著作権を除き、本資料を作成した株式会社ブレインパッドに帰属します。当社の許可なく無断で、複製、
改変・翻訳、販売等をすることはできません。ただし、本資料の閲覧者は、株式会社ブレインパッドの著作物である旨を表示し、かつ、非営利目
的および本資料を改変しない場合に限り、本資料をダウンロード、プリントアウト、またはコピーし、自己のために閲覧・利用することができます。な
お、著作権法上認められている範囲内での引用を行うことは可能です。本資料を引用するには、以下の条件を満たす必要がありますので、ご留
意ください。 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

View Slide

データハンドリングのためのSQL / SQL for data handling

データハンドリングのためのSQL / SQL for data handling

BrainPad

More Decks by BrainPad

Other Decks in Technology

Featured

Transcript