Upgrade to Pro — share decks privately, control downloads, hide ads and more …

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

BrainPad
November 22, 2019

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

株式会社ブレインパッドの2019年新卒研修資料です。SQL操作に関する基本的な内容を扱っています。
SQLローカル環境の構築方法については事前にこちらをご参照ください
https://speakerdeck.com/brainpadpr/sql-local-environment-construction

BrainPad

November 22, 2019
Tweet

More Decks by BrainPad

Other Decks in Technology

Transcript

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

    View Slide

  2. 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

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

    View Slide

  4. 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

    View Slide

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

    View Slide

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

    View Slide

  7. Analytics Innovation Company ©BrainPad Inc. 6
    1. データベースとSQL
    1-1. データベースとは
    ① 利用例:家計簿アプリ
    ② テーブル構造
    ③ テーブル定義
    ④ 代表的なデータ型
    ⑤ ER図 – エンティティ
    ⑥ ER図 – 主キー、外部キー
    ⑦ RDB/DWHとDBMS
    1-2. SQLとは
    ① クエリとSQL
    ② SQLでできることの例
    1-3. 実業務におけるデータベース

    View Slide

  8. Analytics Innovation Company ©BrainPad Inc. 7
    ユーザ情報
    1-1. データベースとは① 利用例:家計簿アプリ
    id … … … …
    1
    2

    レシート情報
    id … … … …
    1
    2

    レシートアイテム情報
    id … … … …
    1
    2

    ログイン情報
    (ユーザ名など)
    読込
    入力
    レシート
    データ登録
    蓄積されたデータを活用
    DB

    View Slide

  9. 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列分のデータ

    View Slide

  10. Analytics Innovation Company ©BrainPad Inc. 9
    • テーブルで定義するもの
    • テーブル名
    • フィールド名 ※カラム名と呼ぶこともある
    • データ型
    1-1. データベースとは③ テーブル構造
    user
    id name … … …
    1 佐藤
    2 田中


    user
    id name … … …
    1 佐藤
    2 田中


    例1
    フィールド名: id
    データ型: 整数型
    例2
    フィールド名: name
    データ型: 文字列型
    テーブル名:user

    View Slide

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

    View Slide

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

    View Slide

  13. 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

    ユーザ情報 レシート情報 レシートアイテム情報

    View Slide

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

    View Slide

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

    View Slide

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

    View Slide

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

    View Slide

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

    View Slide

  19. 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

    View Slide

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

    View Slide

  21. Analytics Innovation Company ©BrainPad Inc. 20
    2. SQLの基礎①
    2-1. 4つの基本機能CRUD
    2-2. WHERE(条件検索)
    2-3. GROUP BY(グループ化), HAVING(絞り込み)
    2-4. SQLの実行順序

    View Slide

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

    View Slide

  23. 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

    View Slide

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

    View Slide

  25. 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

    View Slide

  26. 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

    View Slide

  27. 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

    View Slide

  28. 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
    バッククォート

    View Slide

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

    View Slide

  30. Analytics Innovation Company ©BrainPad Inc. 29
    2-1-3. SELECT② COUNT, DISTINCT, AS
    receipt_itemテーブルのreceipt_idフィールドに対して「重複を許した場合の件数」と「重複
    を除いた場合の件数」をそれぞれ取得し、列名を別名に変更する
    ※フィールド名は自分で適当なものをつける
    /*
    receipt_itemテーブルのreceipt_idフィールドに対して
    「重複を許した場合の件数」
    「重複を除いた場合の件数」をそれぞれ取得し、列名を別名に変更する
    */
    類題
    実行結果 レコード数 1
    SQL

    View Slide

  31. 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

    View Slide

  32. 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

    View Slide

  33. 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
    昇順 降順

    View Slide

  34. 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

    View Slide

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

    View Slide

  36. 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

    View Slide

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

    View Slide

  38. 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日以降
    昇順

    View Slide

  39. 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
    バッククォート
    シングルクォーテーション(コピペ時は注意!)

    View Slide

  40. 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
    バッククォート
    シングルクォーテーション
    (コピペ時は注意!)

    View Slide

  41. Analytics Innovation Company ©BrainPad Inc. 40
    2-2-2. WHERE② 論理条件
    userテーブルで、「性別が2(女性)」かつ「state_codeが’13’(東京)または’27’(大
    阪)」である人の総数を取得する
    /*
    userテーブルで、「性別が2(女性)」かつ「state_codeが’13’(東京)または’27’(大阪)」
    である人の総数を取得する
    */
    類題
    実行結果 レコード数 1
    SQL

    View Slide

  42. 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
    「コンビニ」を含む
    シングルクォーテーション(コピペ時は注意!)

    View Slide

  43. 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文字
    %○○% 「○○」を含む文字列
    %○○ 「○○」で終わる文字列
    ○○% 「○○」で始まる文字列 シングルクォーテーション
    (コピペ時は注意!)

    View Slide

  44. Analytics Innovation Company ©BrainPad Inc. 43
    2-2-3. WHERE③ パターンマッチングLIKE
    receiptテーブルで、shop_nameが文字列「B」で終わるデータについて、全てのフィールドを5
    件分取得する
    /*
    receiptテーブルで、shop_nameが文字列「B」で終わるデータについて、
    全てのフィールドを5件分取得する
    */
    類題
    実行結果 レコード数 5
    SQL
    「B」で終わる

    View Slide

  45. 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

    View Slide

  46. 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

    View Slide

  47. 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

    View Slide

  48. 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

    View Slide

  49. 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

    View Slide

  50. 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

    View Slide

  51. 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

    View Slide

  52. 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

    View Slide

  53. 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

    View Slide

  54. 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`;

    View Slide

  55. 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

    View Slide

  56. 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

    View Slide

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

    View Slide

  58. 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

    View Slide

  59. 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
    ①検索
    ②集計
    ③抽出

    View Slide

  60. 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

    View Slide

  61. 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

    View Slide

  62. Analytics Innovation Company ©BrainPad Inc. 61
    3. SQLの基礎②
    3-1. 条件式
    3-2. データ型と型変換
    3-3. 文字列関数
    3-4. 日付関数

    View Slide

  63. Analytics Innovation Company ©BrainPad Inc. 62
    3-1. 条件式
    3-1-1. 条件式① CASE
    3-1-2. 条件式② COALESCE
    IS NULL, IS NOT NULL

    View Slide

  64. 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

    View Slide

  65. 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

    View Slide

  66. 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

    View Slide

  67. 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や空白文字とも異なる

    View Slide

  68. 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

    View Slide

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

    View Slide

  70. 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に置換

    View Slide

  71. Analytics Innovation Company ©BrainPad Inc. 70
    3-2. データ型と型変換
    3-2-1. 代表的なデータ型
    数値型
    文字列型
    日付型
    3-2-2. 型変換 CAST
    timestamp型 → date型
    varchar型 → date型

    View Slide

  72. 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)

    View Slide

  73. 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

    View Slide

  74. 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型

    View Slide

  75. 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

    View Slide

  76. Analytics Innovation Company ©BrainPad Inc. 75
    3-3. 文字列関数
    3-3-1. 文字列関数① SUBSTRING
    3-2-2. 文字列関数② REPLACE, LENGTH

    View Slide

  77. 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
    文字列

    View Slide

  78. 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文字分

    View Slide

  79. 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

    View Slide

  80. 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

    View Slide

  81. 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
    置換

    View Slide

  82. 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

    View Slide

  83. Analytics Innovation Company ©BrainPad Inc. 82
    3-4. 日付関数
    3-4-1. 日付関数
    CURRENT_DATE
    DATEDIFF
    3-4-2. 誕生日から年齢を求める

    View Slide

  84. 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
    実行日が格納される

    View Slide

  85. 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

    View Slide

  86. 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
    実行日によって結果が異なります

    View Slide

  87. 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

    View Slide

  88. 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

    View Slide

  89. 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

    View Slide

  90. Analytics Innovation Company ©BrainPad Inc. 89
    4. SQLの応用
    4-1. サブクエリ(副問い合わせ)
    4-2. テーブルの結合
    4-3. 一時テーブルの作成

    View Slide

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

    View Slide

  92. 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
    代入

    View Slide

  93. 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
    括弧で囲み代入
    (;は不要)

    View Slide

  94. 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

    View Slide

  95. 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行

    View Slide

  96. 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

    View Slide

  97. 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

    View Slide

  98. 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

    View Slide

  99. 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図

    View Slide

  100. 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

    View Slide

  101. 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図

    View Slide

  102. 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

    View Slide

  103. 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


    View Slide

  104. 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

    View Slide

  105. 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

    View Slide

  106. 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句で呼び出す場合 サブクエリで呼び出す場合

    View Slide

  107. Analytics Innovation Company ©BrainPad Inc. 106
    4-1-5. WITH句 サブクエリの代用
    receiptテーブルを用いて、「1ユーザーあたりのレシート登録数」の平均値を求める。
    /*
    receiptテーブルを用いて、「1ユーザーあたりのレシート登録数」の平均値を求める。
    • ヒント①先ずはWITH句を使って、ユーザーIDごとのレシート登録数を集計する。
    • ユーザーID,レシート登録数の2列からなる表を作成する
    • ヒント②ヒント①で作成した表から、レシート登録数の平均を計算する。
    */
    類題
    SQL
    実行結果
    レコード数1
    id
    user_id (FK)
    price

    receipt_item

    View Slide

  108. Analytics Innovation Company ©BrainPad Inc. 107
    4-2. テーブルの結合
    4-1-1. テーブルの結合とは
    4-2-2. INNER JOIN
    内部結合
    4-2-2. LEFT JOIN
    外部結合

    View Slide

  109. 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

    View Slide

  110. 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図

    View Slide

  111. 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

    View Slide

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

    View Slide

  113. 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図

    View Slide

  114. 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図

    View Slide

  115. 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

    View Slide

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

    View Slide

  117. 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図

    View Slide

  118. Analytics Innovation Company ©BrainPad Inc. 117
    4-3. 一時テーブル
    4-3-1. 一時テーブルの作成

    View Slide

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

    View Slide

  120. 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
    解説

    View Slide

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

    View Slide

  122. Analytics Innovation Company ©BrainPad Inc. 121
    5. SQLの応用②
    5-1. ウィンドウ関数
    5-2. EXISTS(存在検査)
    5-3. 縦持ち・横持ち

    View Slide

  123. Analytics Innovation Company ©BrainPad Inc. 122
    5-1. ウィンドウ関数
    5-1-1. ウィンドウ関数とは
    5-1-2. 集計関数群
    5-1-3. ランク付け関数群
    5-1-4. ラグ・リード関数群
    5-1-5. 参考

    View Slide

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

    View Slide

  125. 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
    … …

    View Slide

  126. 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
    … …

    View Slide

  127. 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での集計と、ウィンドウ関数での集計結果の違いを知る
    … …

    View Slide

  128. 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

    View Slide

  129. 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

    View Slide

  130. 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

    View Slide

  131. 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
    サブクエリ(次ページで解説)

    View Slide

  132. 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の降順に連番を振る

    View Slide

  133. 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
    (参考) サブクエリ部分の取得例

    View Slide

  134. 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

    View Slide

  135. 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)

    View Slide

  136. 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

    View Slide

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

    View Slide

  138. 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

    View Slide

  139. Analytics Innovation Company ©BrainPad Inc. 138
    5-2. EXISTS(存在検査)
    5-2-1. EXISTS 存在検査

    View Slide

  140. 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

    View Slide

  141. 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

    View Slide

  142. 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
    該当レコードを抽出対象とする

    View Slide

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

    View Slide

  144. Analytics Innovation Company ©BrainPad Inc. 143
    5-2-1. EXISTS(存在検査)
    userテーブルのレコードで、idがreceiptテーブルのuser_idとして存在していないものをカウン
    トする。
    /*
    userテーブルのレコードで、idがreceiptテーブルのuser_idとして存在していないものをカウントする。
    */
    類題
    SQL
    実行結果 レコード数 1

    View Slide

  145. Analytics Innovation Company ©BrainPad Inc. 144
    5-3. 縦持ち・横持ち
    5-3-1. 縦持ち・横持ちとは
    5-3-2. 横持ち→縦持ち変換
    5-3-3. 縦持ち→横持ち変換

    View Slide

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

    View Slide

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

    View Slide

  148. 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

    View Slide

  149. 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

    View Slide

  150. 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

    View Slide

  151. 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

    View Slide

  152. 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
    最終結果
    途中結果

    View Slide

  153. 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図

    View Slide

  154. 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

    View Slide