Slide 1

Slide 1 text

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

Slide 2

Slide 2 text

Analytics Innovation Company ©BrainPad Inc. 1 目次 0. はじめに 0-1. 付属資料 0-2. 本研修の目的 0-3. SQL研修の流れ 1. データベースとSQL 1-1. データベースとは 1-2. SQLとは 1-3. 実業務におけるデータベース 2. SQLの基礎① 2-1. 4つの基本機能CRUD 2-2. WHERE(条件検索) 2-3. GROUP BY(集約), HAVING(絞込) 2-4. SQLの実行順序 3. SQLの基礎② 3-1. 条件式 CASE, COALESCE 3-2. データ型と型変換CAST 3-3. 文字列関数 SUBSTRING 3-4. 日付関数 CURRENT_DATE, DATE_DIFF 4. SQLの応用① 4-1. サブクエリ(副問い合わせ) 4-2. テーブルの結合 JOIN 4-3. 一時テーブル 5. SQLの応用② 5-1. ウィンドウ関数 5-2. EXISTS(存在検査) 5-3. 縦持ち・横持ち

Slide 3

Slide 3 text

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

Slide 4

Slide 4 text

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

Slide 5

Slide 5 text

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

Slide 6

Slide 6 text

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

Slide 7

Slide 7 text

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

Slide 8

Slide 8 text

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

Slide 9

Slide 9 text

Analytics Innovation Company ©BrainPad Inc. 8 user 1-1. データベースとは② テーブル構造 user id … … … … 1 2 … user id … … … … 1 2 … user id … … … … 1 2 … user id … … … … 1 2 … ① テーブル:表 ② テーブル名:表の名前 ③ レコード|行:1行分のデータ ④ フィールド|列:1列分のデータ

Slide 10

Slide 10 text

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

Slide 11

Slide 11 text

Analytics Innovation Company ©BrainPad Inc. 10 1-1. データベースとは④ 代表的なデータ型 • データ型はデータ加工や集計操作で失敗しやすい • エラーが出たときに確認するポイント • 「データ型の定義がきちんと行われているか」 • 「関数の引数に正しいデータ型を用いているか」 数値型 int tinyint bigint 1 符号付き整数 float real 0.5 浮動小数点数値 文字列型 char(n) ‘apple’ 固定長の文字列(n:バイト数) varchar(n) 可変長の文字列(n:バイト数) 日付型 date 2018-04-01 日付 timestamp 2018-04-01 01:00:00 日付時刻 論理値型 boolean true(真)かfalse(偽)のみ 論理値

Slide 12

Slide 12 text

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

Slide 13

Slide 13 text

Analytics Innovation Company ©BrainPad Inc. 12 1-1. データベースとは⑤ ER図 – エンティティ user id name gender … 1 2 … receipt receipt_item テ ー ブ ル id name gender … user id user_id (FK) shop_name … receipt id user_id(FK) receipt_id(FK) … receipt_item • テーブルに含まれるデータ項目をまとめたものをエンティティ( Entity)という エ ン テ ィ テ ィ ( 物 理 モ デ ル ) id user _id shop _name … 1 2 … id user _id receipt _id … 1 2 … ユーザ情報 レシート情報 レシートアイテム情報

Slide 14

Slide 14 text

Analytics Innovation Company ©BrainPad Inc. 13 1-1. データベースとは⑥ ER図 – 主キー・外部キー id name gender … user id user_id (FK) shop_name … receipt id user_id (FK) receipt_id (FK) … receipt_item id name gender … user id user_id (FK) shop_name … receipt id user_id (FK) receipt_id (FK) … receipt_item • レコードを一意に特定できるデータ項目を主キー(PK)という ※下図の各 “id” は同じカラム名がついているが意味が異なるので注意 • 他テーブルとの関連付けに用いるデータ項目を外部キー(FK)という • エンティティ間の関係をリレーション( Relation)という

Slide 15

Slide 15 text

Analytics Innovation Company ©BrainPad Inc. 14 user id (PK) BIGINT auth_type INTEGER device_id CHAR(32) hashed_external_id VARCHAR(64) hashed_password VARCHAR(64) gender INTEGER birthday DATE location VARCHAR(64) state_code CHAR(8) last_login DATE create_date TIMESTAMP receipt id (PK) BIGINT user_id (FK) BIGINT shop_name VARCHAR(464) paid_at TIMESTAMP read_at TIMESTAMP update_at TIMESTAMP client_id VARCHAR(32) receipt_item id (PK) BIGINT user_id (FK) BIGINT receipt_id (FK) BIGINT name VARCHAR(200) price FLOAT8 expense (FK) INTEGER expense_parent (FK) INTEGER image_file_name VARCHAR(64) type_expense id (PK) BIGINT main_heading VARCHAR(64) sub_heading VARCHAR(64) 1-1. データベースとは⑦ ER図 – 例(物理モデル) ER図で、テーブル定義とテーブル間 の関係を把握することができる

Slide 16

Slide 16 text

Analytics Innovation Company ©BrainPad Inc. 15 user id (PK) BIGINT auth_type INTEGER device_id CHAR(32) hashed_external_id VARCHAR(64) hashed_password VARCHAR(64) gender INTEGER birthday DATE location VARCHAR(64) state_code CHAR(8) last_login DATE create_date TIMESTAMP receipt id (PK) BIGINT user_id (FK) BIGINT shop_name VARCHAR(464) paid_at TIMESTAMP read_at TIMESTAMP update_at TIMESTAMP client_id VARCHAR(32) receipt_item id (PK) BIGINT user_id (FK) BIGINT receipt_id (FK) BIGINT name VARCHAR(200) price FLOAT8 expense (FK) INTEGER expense_parent (FK) INTEGER image_file_name VARCHAR(64) type_expense id (PK) BIGINT main_heading VARCHAR(64) sub_heading VARCHAR(64) 1-1. データベースとは⑦ ER図 – 例(物理モデル) 公開しているサンプルデータでは一部 のカラムのみのデータになっています

Slide 17

Slide 17 text

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

Slide 18

Slide 18 text

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

Slide 19

Slide 19 text

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

Slide 20

Slide 20 text

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

Slide 21

Slide 21 text

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

Slide 22

Slide 22 text

Analytics Innovation Company ©BrainPad Inc. 21 2-1. 4つの基本機能CRUD 2-1-1. 4つの基本機能CRUDとSQL 2-1-2. SELECT① 特定フィールドの抽出 • コメントの書き方 • LIMIT 2-1-3. SELECT② COUNT, DISTINCT, AS • 予約語 2-1-4. SELECT③ ORDER BY, SELECT * 2-1-5. コーディング規約(簡易版)

Slide 23

Slide 23 text

Analytics Innovation Company ©BrainPad Inc. 22 2-1-1. 4つの基本機能CRUDとSQL • ほとんど全てのコンピュータが持つ4つの基本機能を、それぞれの頭文字をとって CRUDという • Create 生成 • Read 読取 • Update 更新 • Delete 削除 • 標準SQLとの対応 • Create 生成 → INSERT (データ挿入) • Read 読取 → SELECT (データ取得) • Update 更新 → UPDATE (データ更新) • Delete 削除 → DELETE (データ削除) • 本研修ではSELECTに関するSQLを中心に扱う 基本4機能のSQL

Slide 24

Slide 24 text

Analytics Innovation Company ©BrainPad Inc. 23 2-1-2. SELECT① 特定フィールドの抽出 receiptテーブルからid, user_id, paid_atの3つのフィールドに対して3件分の データを取得する /* receiptテーブルからid, user_id, paid_atの3つの フィールドに対して3件分のデータを取得する */ SELECT id ,user_id ,paid_at FROM receipt LIMIT 3; 例題 実行結果 レコード数 3 SQL 写経の際の注意点 • 大文字, 小文字を使い分ける • インデントを下げる • カンマを書く位置を統一する ※その他のコーディング規約は後述

Slide 25

Slide 25 text

Analytics Innovation Company ©BrainPad Inc. 24 2-1-2. SELECT① 特定フィールドの抽出  SELECTのイメージ 解説 receipt テーブル id user_id … paid_at … 1 2 3 4 … /* receiptテーブルからid, user_id, paid_atの3つのフィールドに対して 3件分のデータを取得する */ SELECT id ,user_id ,paid_at FROM receipt LIMIT 3; id user_id paid_at 1 2 3 3件のみ LIMIT 3 特定のフィールドのみ抽出 実行結果 SQL

Slide 26

Slide 26 text

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

Slide 27

Slide 27 text

Analytics Innovation Company ©BrainPad Inc. 26 2-1-2. SELECT① 特定フィールドの抽出 receipt_itemテーブルから次のフィールドに対して2件分データを取得する 抽出フィールド:id, user_id, receipt_id, price /* receipt_itemテーブルから次のフィールドに対して2件分データを取得する 抽出フィールド:id, user_id, receipt_id, price */ 類題 実行結果 レコード数 2 SQL

Slide 28

Slide 28 text

Analytics Innovation Company ©BrainPad Inc. 27 2-1-3. SELECT② COUNT, DISTINCT, AS receiptテーブルのuser_idフィールドに対して、「重複を許した場合の件数」と「重複を除いた 場合の件数」をそれぞれ取得する /* receipt テーブルのuser_idフィールドに対して、 「重複を許した場合の件数」 「重複を除いた場合の件数」をそれぞれ取得する */ SELECT COUNT(user_id) AS `user` ,COUNT(DISTINCT user_id) AS distinct_user FROM receipt; 例題 実行結果 レコード数 1 SQL バッククォート

Slide 29

Slide 29 text

Analytics Innovation Company ©BrainPad Inc. 28 2-1-3. SELECT② COUNT, DISTINCT, AS /* receipt テーブルのuser_idフィールドに 対して、 「重複を許した場合の件数」 「重複を除いた場合の件数」 をそれぞれ取得する */ SELECT COUNT(user_id) AS `user` ,COUNT(DISTINCT user_id) AS distinct_user FROM receipt; 解説  COUNT • レコード(行)の件数を求める  DISTINCT • 重複行を除外する  AS • 列名やテーブル名に別名をつける • [列名]AS [別名] • [テーブル名] AS [別名]  予約語 • SELECT, FROM, USERなどはSQLの機能として特別な意 味をもつため、そのままでは列名として使用できない • 列名として使用するときは `(バッククォート)で囲む ※文字列は ‘ (シングルクォーテーション)で囲む SQL バッククォート (注)Redshiftの場合は ” (ダブルクォーテーション)

Slide 30

Slide 30 text

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

Slide 31

Slide 31 text

Analytics Innovation Company ©BrainPad Inc. 30 2-1-4. SELECT③ ORDER BY, SELECT * userテーブルの全てのフィールドを5件分、データ取得する。 ただし、last_loginについて昇順に並び替えて表示する。 /* user テーブルの全てのフィールドを5件分、データ取得する idについて昇順に並び替えて表示する */ SELECT * FROM `user` ORDER BY last_login LIMIT 5; 例題 実行結果 レコード数 5 SQL

Slide 32

Slide 32 text

Analytics Innovation Company ©BrainPad Inc. 31 2-1-4. SELECT③ ORDER BY, SELECT * /* user_master テーブルの全てのフィール ドを5件分、データ取得する last_loginについて昇順に並び替えて表 示する */ SELECT * FROM `user` ORDER BY last_login LIMIT 5; 解説  *(アスタリスク) • 全てのフィールドを選択する  ORDER BY • 指定したフィールドで検索結果を並び替える • ORDER BY [フィールド名] ASC 昇順 • ORDER BY [フィールド名] DESC 降順 • ASC/DESCを無指定の場合はASCがデフォルト 【参考】複数列で並び替えたい場合 • [フィールド1] ASC, [フィールド2] DESC…のようにカ ンマで区切って指定する • 上記の場合、「フィールド1で昇順に並び替え、フィールド1が同 じ値の場合にはフィールド2で降順で並び替える」 SQL

Slide 33

Slide 33 text

Analytics Innovation Company ©BrainPad Inc. 32 2-1-4. SELECT③ ORDER BY, SELECT * receiptテーブルの全てのフィールドを5件分、データ取得する ただし、 user_idの昇順、read_atの降順に並び替える /* receiptテーブルの全てのフィールドに5件分、データ取得する ただし、 user_idの昇順、read_atの降順に並び替える */ 類題 実行結果 レコード数 5 SQL 昇順 降順

Slide 34

Slide 34 text

Analytics Innovation Company ©BrainPad Inc. 33 2-1-5. コーディング規約 1. 大文字と小文字を使い分ける 大文字:予約語 SELECTなど 小文字:予約語以外 列名など 2. インデントを下げる インデント幅は揃える 3. カンマを書く位置を統一する 最初に書くか、最後に書くか 4. 1行の長さが長くなりすぎないようにする 5. わかりやすいフィールド名、テーブル名をつける 何を意味しているかすぐわかるように その他、より詳細なコーディング規約については各自調べてみてください。 SELECT col_1 ,col_2 ,SUM(col_3) AS sum_col_3 FROM table ORDER BY col_1 ,col_2 ,SUM(col_3) AS sum_col_3 ; SQL

Slide 35

Slide 35 text

Analytics Innovation Company ©BrainPad Inc. 34 2-2. WHERE(条件検索) 2-2-1. WHERE① 比較条件 • <, >, <=, >=, =, <>など 2-2-2. WHERE② 論理条件 • AND, OR, NOT 2-2-3. WHERE③ パターンマッチングLIKE • パターン文字%, _(アンダースコア) 2-2-4. WHERE④ 範囲条件BETWEEN 2-2-5. WHERE⑤ IN条件

Slide 36

Slide 36 text

Analytics Innovation Company ©BrainPad Inc. 35 2-2-1. WHERE① 比較条件 userテーブルで、性別が1(男性)のid, gender, birthdayの3つのフィールドについて5件 分データ取得する。 /* userテーブルで性別が1(男性)のid, gender, birthdayの 3つのフィールドについて5件分データ取得する */ SELECT id ,gender ,birthday FROM `user` WHERE gender = 1 LIMIT 5; 例題 SQL 実行結果 レコード数 5

Slide 37

Slide 37 text

Analytics Innovation Company ©BrainPad Inc. 36 2-2-1. WHERE① 比較条件 /* user_master テーブルで性別が1(男 性)のid, gender, birthdayの3つの フィールドについて5件分データ取得する */ SELECT id ,gender ,birthday FROM `user` WHERE gender = 1 LIMIT 5 ; 解説  比較演算子 • 2つの値の間の論理的な関係を指定 • 日付の場合 例 「誕生日が1995年1月1日以降」 WHERE birthday >= ‘1995-01-01’ SQL 比較演算子 意味 >= 以上 <= 以下 > より大きい < より小さい = 等しい <> , != 等しくない

Slide 38

Slide 38 text

Analytics Innovation Company ©BrainPad Inc. 37 2-2-1. WHERE① 比較条件 userテーブルで、誕生日が2006年1月1日以降の人のbirthday, genderの2つのフィール ドについて5件分データ取得し、誕生日の昇順に並び替えて表示する。 /* userテーブルで、誕生日が2006年1月1日以降の人のbirthday, genderの 2つのフィールドについて5件分データ取得し、誕生日の昇順に並び替えて表示する。 */ 類題 実行結果 レコード数 5 SQL 2006年1月1日以降 昇順

Slide 39

Slide 39 text

Analytics Innovation Company ©BrainPad Inc. 38 2-2-2. WHERE② 論理条件 userテーブルで、性別が1(男性)かつstate_codeが’13’(東京)である人の総数を取 得する /* userテーブルで、性別が1(男性)かつstate_codeが’13’(東京)である人の総数を取得する。 */ SELECT COUNT(*) FROM `user` WHERE gender = 1 AND state_code = ’13’ ; 例題 実行結果 レコード数 1 SQL バッククォート シングルクォーテーション(コピペ時は注意!)

Slide 40

Slide 40 text

Analytics Innovation Company ©BrainPad Inc. 39 2-2-2. WHERE② 論理条件 /* userテーブルで、性別が1(男性)かつ state_codeが’13’(東京)である人の 総数を取得する。 */ SELECT COUNT(*) FROM `user` WHERE gender = 1 AND state_code = ’13’ ; 解説  論理演算子 • A AND B (AかつB) • 2つの条件式の両方が真の場合だけ、真となる • A OR B(AまたはB) • 2つの条件式のどちらかが真ならば、真となる  AND, ORの優先順位 • AND → ORの順に処理される 例 A OR B AND C ① B AND Cが処理される(=結果) ② A OR (結果)が処理される(=最終結果) ※(A OR B) AND C のように括弧で囲むとORが優先される SQL バッククォート シングルクォーテーション (コピペ時は注意!)

Slide 41

Slide 41 text

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

Slide 42

Slide 42 text

Analytics Innovation Company ©BrainPad Inc. 41 2-2-3. WHERE③ パターンマッチングLIKE receiptテーブルで、shop_nameに文字列「コンビニ」を含むデータについて、全てのフィールド を5件分取得する。 /* receiptテーブルで、shop_nameに「コンビニ」を含むデータについて、全てのフィールドを5件分取得する。 */ SELECT * FROM receipt WHERE shop_name LIKE ‘%コンビニ%’ LIMIT 5; 例題 SQL 実行結果 レコード数 5 「コンビニ」を含む シングルクォーテーション(コピペ時は注意!)

Slide 43

Slide 43 text

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

Slide 44

Slide 44 text

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

Slide 45

Slide 45 text

Analytics Innovation Company ©BrainPad Inc. 44 2-2-4. WHERE④ 範囲条件BETWEEN userテーブルで、last_loginが2010年1月1日から2015年12月31日までであるデータにつ いて、id, gender, last_loginを取得する。ただし、last_loginの昇順に並び替える。 /* userテーブルで、last_loginが2010年1月1日から2015年12月31日までであるデータについて、 id, gender, last_loginのフィールドを取得する。ただし、last_loginの昇順に並び替える。 */ SELECT id ,gender ,last_login FROM `user` WHERE last_login BETWEEN ‘2010-01-01’ AND ‘2015-12-31’ ORDER BY last_login; 例題 実行結果 レコード数416 SQL

Slide 46

Slide 46 text

Analytics Innovation Company ©BrainPad Inc. 45 2-2-4. WHERE④ 範囲条件BETWEEN /* userテーブルで、last_loginが2010年1 月1日から2015年12月31日までである データについて、 id, gender, last_loginのフィールドを取 得する。ただし、last_loginの昇順に並び 替える。 */ SELECT id ,gender ,last_login FROM `user` WHERE last_login BETWEEN ‘2010-01-01’ AND ‘2015-12-31’ ORDER BY last_login; 解説  BETWEEN • BETWEEN 値1 AND 値2 • ある範囲内に値が収まっているか判定する • 指定した範囲の境界は含まれるので注意 例 BETWEEN 100 AND 3000 → 100以上 3000以下  BETWEENと比較演算子>=, <= • 例 「priceが100以上、3000以下」 WHERE price BETWEEN 100 AND 3000 WHERE price >= 100 AND price <= 3000 SQL

Slide 47

Slide 47 text

Analytics Innovation Company ©BrainPad Inc. 46 2-2-4. WHERE④ 範囲条件BETWEEN receiptテーブルで、paid_atが2010年1月1日から2010年12月31日までであるデータにつ いて、id, user_id, paid_atを5件分取得する。ただし、paid_atの昇順に並び替える。 /* receiptテーブルで、paid_atが2010年1月1日から2010年12月31日までであるデータについて、 id, user_id, paid_atを5件分取得する。ただし、paid_atの昇順に並び替える。 */ 類題 SQL 実行結果 レコード数5

Slide 48

Slide 48 text

Analytics Innovation Company ©BrainPad Inc. 47 2-2-5. WHERE⑤ IN条件 userテーブルでstate_codeが’13’(東京)または’27’(大阪)である行をカウントする /* userテーブルでstate_codeが’13’(東京)または’27’(大阪)である行をカウントする */ SELECT COUNT(*) FROM `user` WHERE state_code IN (‘13’, ’27’) ; 例題 実行結果 レコード数 1 SQL

Slide 49

Slide 49 text

Analytics Innovation Company ©BrainPad Inc. 48 2-2-5. WHERE⑤ IN条件 /* userテーブルでstate_codeが’13’(東 京)または’27’(大阪)である行をカウ ントする */ SELECT COUNT(*) FROM `user` WHERE state_code IN ( ‘13’, ’27’) ; 解説  IN • IN (値1, 値2, 値3, …) • ある値が列挙した値のどれかと等しいかを判定  NOT IN • NOT IN (値1, 値2, 値3, …) • ある値が列挙した値のどれとも一致しないことを判定 【参考】ANY / ALL演算子(詳細は各自調べてください) • 比較演算子 ANY (値1, 値2, 値3, …) • 値リストとそれぞれ比較して、いずれかが真なら真 • 比較演算子 ALL (値1, 値2, 値3, …) • 値リストとそれぞれ比較して、すべて真なら真 SQL

Slide 50

Slide 50 text

Analytics Innovation Company ©BrainPad Inc. 49 2-2-5. WHERE⑤ IN条件 receipt_itemテーブルで、nameが文字列「まぐろ」「りんご」であるデータについて、id, user_id, receipt_id, nameを5件取得する。ただし、idの昇順に並べる。 /* receipt_itemテーブルで、nameが文字列「まぐろ」「りんご」であるデータについて、 id, user_id, receipt_id, nameを5件取得する。ただし、idの昇順に並べる。 */ 類題 実行結果 レコード数 5 SQL

Slide 51

Slide 51 text

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

Slide 52

Slide 52 text

Analytics Innovation Company ©BrainPad Inc. 51 2-3-1. GROUP BY① 集計関数 receipt_itemテーブルで、receipt_id毎にグループ化したレコードに対して、 レコード数と、金額priceの合計値・平均値・最大値を求める。ただし、receipt_idが’100’ と’1000’のデータを対象とし、レコード数の昇順に並び替えて表示する。 /* receipt_itemテーブルで、receipt_id毎にグループ化した レコードに対して、レコード数と、金額priceの合計値・ 平均値・最大値を求める。 ただし、receipt_idが’100’と’1000’のデータを対象とし、レコード数 の昇順に並び替えて表示する*/ SELECT receipt_id ,COUNT(*) AS `count` ,SUM(price) AS sum_price ,AVG(price) AS avg_price ,MAX(price) AS max_price FROM receipt_item WHERE receipt_id IN (100, 1000) GROUP BY receipt_id ORDER BY `count` ; 例題 実行結果 レコード数 2 SQL

Slide 53

Slide 53 text

Analytics Innovation Company ©BrainPad Inc. 52 2-3-1. GROUP BY① 集計関数  GROUP BYの集計イメージ 解説 receipt_item テーブル … receipt_id … price … 1 100 1 200 2 300 2 400 2 500 … … /* receipt_id毎にグループ化した レコードに対して、レコード数と、金額 priceの合計値・平均値・最大値を求め る */ SELECT receipt_id ,COUNT(*) AS `count` ,SUM(price) AS sum_price ,AVG(price) AS avg_price ,MAX(price) AS max_price FROM receipt_item WHERE receipt_id IN (100, 1000) GROUP BY receipt_id ORDER BY `count`; receipt_id count sum_price avg_price max_price 1 2 300 150 200 2 3 1200 400 500 … receipt_id毎で集計 実行結果 SQL

Slide 54

Slide 54 text

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

Slide 55

Slide 55 text

Analytics Innovation Company ©BrainPad Inc. 54 2-3-1. GROUP BY① 集計関数 receipt_itemテーブルで、receipt_id毎にグループ化したレコードの金額priceの最小値を 求め、5件分表示する。ただし、receipt_idの昇順に並べる。 /* receipt_itemテーブルで、receipt_id毎にグループ化したレコードの金額priceの 最小値を求め、5件分表示する。ただし、receipt_idの昇順に並べる。 */ 類題 実行結果 レコード数 5 SQL

Slide 56

Slide 56 text

Analytics Innovation Company ©BrainPad Inc. 55 2-3-2. GROUP BY② HAVING 集計結果に条件指定 receipt_itemテーブルで、receipt_id毎にグループ化し、金額priceの平均値を求め、その 平均値が500であるものを5件取得する。ただし、receipt_idの昇順に並べる。 /* receipt_itemテーブルで、receipt_id毎にグループ化し、金額 priceの平均値を求め、その平均値が500であるものを5件取得する。 */ SELECT receipt_id ,ROUND(AVG(price), 0) AS avg_price FROM receipt_item WHERE price >= 0 GROUP BY receipt_id HAVING AVG(price) = 500 ORDER BY receipt_id LIMIT 5; 例題 実行結果 レコード数 5 SQL

Slide 57

Slide 57 text

Analytics Innovation Company ©BrainPad Inc. 56 2-3-2. GROUP BY② HAVING 集計結果に条件指定  HAVINGのイメージ • HAVINGは集計結果に対して抽出条件を指定する 解説 receipt_item テーブル … receipt_id … price … 1 300 1 700 2 3000 2 400 2 500 /* receipt_itemテーブルで、receipt_id毎に グループ化し、金額priceの平均値を求め、そ の平均値が500であるものを5件取得する。 */ SELECT receipt_id ,ROUND(AVG(price), 0) AS avg_price FROM receipt_item WHERE price >= 0 GROUP BY receipt_id HAVING AVG(price) = 500 ORDER BY receipt_id LIMIT 5; receipt_id avg_price 1 500 2 1300 ①集計 GROUP BY SQL receipt_id avg_price 1 500 ②抽出 HAVING 500である ものを抽出 実行結果

Slide 58

Slide 58 text

Analytics Innovation Company ©BrainPad Inc. 57 2-3-2. GROUP BY② HAVING 集計結果に条件指定 /* receipt_itemテーブルで、receipt_id毎に グループ化し、金額priceの平均値を求め、そ の平均値が500であるものを5件取得する。 */ SELECT receipt_id ,ROUND(AVG(price), 0) AS avg_price FROM receipt_item WHERE price >= 0 GROUP BY receipt_id HAVING AVG(price) = 500 ORDER BY receipt_id LIMIT 5; 解説  ROUND • ROUND(値, 桁数) • 指定した桁数に数値を丸める(四捨五入) • 似た関数にFLOOR(切り捨て),CEILING(切り上げ)も あるので詳細は各自調べてください  WHEREとHAVINGの違い • WHERE • もとの表に対する絞り込み条件 • グループ化する前の検索条件 • HAVING • 集計結果に対する絞り込み条件 • グループ化した後の抽出条件 SQL 例 12.15を丸める(四捨五入) 結果 ROUND(12.15, 1) 小数第1位までの数に丸める 12.2 ROUND(12.15, 0) 最も近い整数に丸める 12 ROUND(12.15, -1) 一の位を丸める 10

Slide 59

Slide 59 text

Analytics Innovation Company ©BrainPad Inc. 58 2-3-2. GROUP BY② HAVING 集計結果に条件指定 /* receipt_itemテーブルで、receipt_id毎に グループ化し、金額priceの平均値を求め、そ の平均値が500であるものを5件取得する。 */ SELECT receipt_id ,ROUND(AVG(price), 0) AS avg_price FROM receipt_item WHERE price >= 0 GROUP BY receipt_id HAVING AVG(price) = 500 ORDER BY receipt_id LIMIT 5; 解説  WHEREとHAVINGを含む集計基本構文 SELECT グループ化の基準列名…, 集計関数 FROM テーブル名 WHERE もとの表に対する絞り込み条件 GROUP BY グループ化の基準列名 HAVING 集計結果に対する絞り込み条件 ; SQL ①検索 ②集計 ③抽出

Slide 60

Slide 60 text

Analytics Innovation Company ©BrainPad Inc. 59 2-3-2. GROUP BY② HAVING 集計結果に条件指定 receipt_itemテーブルで、receipt_id毎にグループ化し、レコードの金額priceに対して最大 値を求め、最大値が1000以上のものを5件取得する。ただし、receipt_idの昇順に並び替 えて表示する。 /* receipt_itemテーブルで、receipt_id毎にグループ化し、レコードの金額priceに対して最大値を求め、最大値が1000以上のものを5件取 得する。ただし、receipt_idの昇順に並び替えて表示する。 */ 類題 実行結果 レコード数 5 SQL

Slide 61

Slide 61 text

Analytics Innovation Company ©BrainPad Inc. 60 2-4. SQLの実行順序  実行順序 1. FROMで処理対象テーブルを選択 2. WHEREによる絞り込み 3. GROUP BYによるグループ化 4. HAVINGによる絞り込み 5. SELECT 6. ORDER BYによるソート 7. LIMITによる絞り込み • 実行順序を理解することは、SQLのパフォー マンス向上や、複雑なクエリを作成する上で 重要となる SELECT ⑤ receipt_id ,ROUND(AVG(price), 0) AS avg_price FROM ① receipt_item WHERE ② price >= 0 GROUP BY ③ receipt_id HAVING ④ AVG(price) = 500 ORDER BY ⑥ receipt_id LIMIT 5; ⑦ SQL

Slide 62

Slide 62 text

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

Slide 63

Slide 63 text

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

Slide 64

Slide 64 text

Analytics Innovation Company ©BrainPad Inc. 63 3-1-1. 条件式① CASE receipt_itemテーブルで、priceを次の条件に従ってカテゴリを分ける。ただし、idの昇順に並べる。 ① 0円〜800円:low_price ② 800円〜1500円:middle_price ③ 1500円以上:high_price /* receipt_itemテーブルで、priceを次の条件に従ってカテゴリを分ける。 ① 0円〜800円:low_price ② 800円〜1500円:middle_price ③ 1500円以上: high_price */ SELECT id ,price ,CASE WHEN price <= 800 THEN ‘low’ WHEN price <= 1500 THEN ‘middle’ ELSE ’high’ END AS price_level FROM receipt_item WHERE price >= 0 ORDER BY id LIMIT 10; 例題 実行結果 レコード数 10 SQL

Slide 65

Slide 65 text

Analytics Innovation Company ©BrainPad Inc. 64 3-1-1. 条件式① CASE  CASE • 条件分岐で値を変換する • 基本構文 CASE WHEN 条件1 THEN 返す値 WHEN 条件2 THEN 返す値… ELSE 上記に合致しないときに返す値 END (AS 別名) • フラグ(条件該当の印)とCASE 例 「state_codeが’13’(東京)のとき1, 他は0」 CASE WHEN state_code = ‘13’ THEN 1 ELSE 0 END 解説 /* receipt_itemテーブルで、priceを次の条件に 従ってカテゴリを分ける。 ① 0円〜800円:low_price ② 800円〜1500円:middle_price ③ 1500円以上:high_price */ SELECT id ,price ,CASE WHEN price <= 800 THEN ‘low’ WHEN price <= 1500 THEN ‘middle’ ELSE ’high’ END AS price_level FROM receipt_item (以下省略) SQL

Slide 66

Slide 66 text

Analytics Innovation Company ©BrainPad Inc. 65 3-1-1. 条件式① CASE userテーブルでstate_codeが’27’(大阪)であるユーザーの総数を求める。 (state_codeが’27’であるとき1、他は0のフラグを作成して合計する) /* userテーブルでstate_codeが’27’(大阪)であるユーザーの総数を求める。 (state_codeが’27’であるとき1、他は0のフラグを作成して合計する) */ 類題 実行結果 レコード数 1 SQL

Slide 67

Slide 67 text

Analytics Innovation Company ©BrainPad Inc. 66 3-1-1. 条件式① CASE userテーブルでstate_codeが’27’(大阪)であるユーザーの総数を求める。 【別解】COUNT, DISTINCT, CASEを組み合わせた方法 /* userテーブルでstate_codeが’27’(大阪)であるユーザーの総数を求める。 【別解】COUNT, DISTINCT, CASEを組み合わせた方法 */ SELECT COUNT(DISTINCT CASE WHEN state_code = ‘27’ THEN id ELSE NULL END) AS `sum` FROM `user` ; 補足 実行結果 レコード数 1 SQL 処理① state_code=’27’:id それ以外:NULL 処理② 重複を除外してidの総数を集計 ※NULLはカウントされない ※今回はidの重複はないため省略 しても良い NULLとは 何も格納されていない、未定義 0や空白文字とも異なる

Slide 68

Slide 68 text

Analytics Innovation Company ©BrainPad Inc. 67 3-1-2. 条件式② COALESCE NULLをもつフィールド`null`と、NULLを文字列‘不明’に置換したフィールド`co_null`を1件取得する。(テー ブルは不要なので、FROM句も不要) /* NULLをもつフィールド`null`と、NULLを文字列‘不明’に置換したフィール ド`co_null`を取得する。 */ SELECT NULL AS `null` ,COALESCE(NULL, ‘不明’) AS co_null ; 例題 実行結果 レコード数14 SQL

Slide 69

Slide 69 text

Analytics Innovation Company ©BrainPad Inc. 68 3-1-2. 条件式② COALESCE  NULLとは • 何も格納されていない、未定義 • 0や空白文字とも異なる  NULLの判定 • IS NULL / IS NOT NULL • 比較演算子=ではNULLの判定はできないので注意!  COALESCE(コアレス) • 引数のうち、最初に現れたNULLでない引数を返す • COALESCE(value, 0) • value列の値がNULLのとき、代わりに0を入れる 解説 /* NULLをもつフィールド`null`と、NULLを文字列 ‘不明’に置換したフィールド`co_null`を取得する。 */ SELECT NULL AS `null` ,COALESCE(NULL, ‘不明’) AS co_null ; SQL COALESCEの例 返り値 COALESCE(NULL, 1, 2) 1 COALESCE(NULL, 1, NULL) 1 COALESCE(NULL, NULL, 2) 2 NULLを‘不明’に置換

Slide 70

Slide 70 text

Analytics Innovation Company ©BrainPad Inc. 69 3-1-2. 条件式② COALESCE NULLをもつフィールド`null`と、NULLを整数0に置換したフィールド`co_null`を1件取得する。(テーブルは 不要なので、FROM句も不要) /* NULLをもつフィールド`null`と、NULLを整数0に置換したフィールド`co_null`を取得する。 */ 類題 実行結果 レコード数 1 SQL NULLを0に置換

Slide 71

Slide 71 text

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

Slide 72

Slide 72 text

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

Slide 73

Slide 73 text

Analytics Innovation Company ©BrainPad Inc. 72 3-2-2. 型変換 CAST receiptテーブルで、paid_atをtimestamp型→date型に変換(cast_paid_at)する。 ・抽出フィールド:id, paid_at, cast_paid_at ・idの昇順で5件分表示 /* receiptテーブルで、paid_atをtimestamp型→date型に変換(cast_paid_at)する。 ・抽出フィールド:id, paid_at, cast_paid_at ・idの昇順で5件分表示 */ SELECT id ,paid_at ,CAST(paid_at AS DATE) AS cast_paid_at FROM receipt ORDER BY id LIMIT 5; 例題 実行結果 レコード数5 SQL

Slide 74

Slide 74 text

Analytics Innovation Company ©BrainPad Inc. 73 3-2-2. 型変換 CAST  CAST • データ型を変換する • CAST(フィールド名 AS 変換後のデータ型)  文字列varchar型 → date型の変換 SELECT ‘20180402’ AS v_date ,CAST(‘20180402’ AS DATE) AS d_date ; 解説 /* receiptテーブルで、paid_atをtimestamp型 →date型に変換(cast_paid_at)する。 ・抽出フィールド:id, paid_at, cast_paid_at ・idの昇順で5件分表示 */ SELECT id ,paid_at ,CAST(paid_at AS DATE) AS cast_paid_at FROM receipt ORDER BY id LIMIT 5; SQL 実行結果 レコード数1 varchar型 date型

Slide 75

Slide 75 text

Analytics Innovation Company ©BrainPad Inc. 74 3-2-2. 型変換 CAST userテーブルで、create_dateをtimestamp型→ date型に変換する。 ・抽出フィールド:id, create_date, 型変換後のcreate_date ・ idの昇順で5件分表示 /* userテーブルで、create_dateをtimestamp型→date型に変換する。 ・抽出フィールド:id, create_date, 型変換後のcreate_date ・idの昇順で5件分表示 */ 類題 実行結果 レコード数 5 SQL

Slide 76

Slide 76 text

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

Slide 77

Slide 77 text

Analytics Innovation Company ©BrainPad Inc. 76 3-3-1. 文字列関数① SUBSTRING receiptテーブルで、paid_atから西暦部分を取り出す(paid_yearとする)。 ・抽出フィールド:id, paid_at, paid_year ・idの昇順で5件分表示 /* receiptテーブルで、paid_atから西暦部分を取り出す(paid_yearとする)。 ・抽出フィールド:id, paid_at, paid_year ・idの昇順で5件分表示 */ SELECT id ,paid_at ,SUBSTRING(paid_at, 1, 4) AS paid_year FROM receipt ORDER BY id LIMIT 5; 例題 実行結果 レコード数5 SQL 文字列

Slide 78

Slide 78 text

Analytics Innovation Company ©BrainPad Inc. 77 3-3-1. 文字列関数① SUBSTRING  SUBSTRING • 文字列の一部分だけを取り出す • SUBSTRING(文字列, 開始位置, 抽出文字数)  例 文字列’20180402’の5文字目から2文字分を抽出 SELECT SUBSTRING(‘20180402’, 5, 2) AS month; ※DBMS製品によって、文字数指定かバイト数指定か異なる 解説 /* receiptテーブルで、paid_atから西暦部分を取り 出す(paid_yearとする)。 ・抽出フィールド:id, paid_at, paid_year ・idの昇順で5件分表示 */ SELECT id ,paid_at ,SUBSTRING(paid_at, 1, 4) AS paid_year FROM receipt ORDER BY id LIMIT 5; SQL 実行結果 レコード数1 ’20180402’ 5文字目 2文字分

Slide 79

Slide 79 text

Analytics Innovation Company ©BrainPad Inc. 78 3-3-1. 文字列関数① SUBSTRING userテーブルで、last_loginからを月を取り出す(last_login_monthとする)。 ・抽出フィールド:id, last_login, last_login_month ・idの昇順で5件分表示 /* userテーブルで、last_loginからを月を取り出す(last_login_monthとする)。 ・抽出フィールド:id, last_login, last_login_month ・idの昇順で5件分表示 */ 類題 実行結果 レコード数 5 SQL

Slide 80

Slide 80 text

Analytics Innovation Company ©BrainPad Inc. 79 3-3-2. 文字列関数② LENGTH, REPLACE receiptテーブルでshop_nameの文字数を取得する(length_shop_nameとする)。 また、shop_nameの ’スーパー’ を ’デパート’ に置換する(replace_shop_nameとする)。 ・抽出フィールド:id, shop_name, length_shop_name, replace_shop_name ・idの昇順で5件分表示 /* receiptテーブルでshop_nameの文字数を取得する(length_shop_nameとする)。 また、shop_nameの ’スーパー’ を ’デパート’ に置換する(replace_shop_nameとする)。 ・抽出フィールド:id, shop_name, length_shop_name, replace_shop_name ・idの昇順で5件分表示 */ SELECT id ,shop_name ,LENGTH(shop_name) AS length_shop_name ,REPLACE(shop_name, ‘スーパー’, ‘デパート’ ) AS replace_shop_name FROM receipt ORDER BY id LIMIT 5; 例題 実行結果 レコード数5 SQL

Slide 81

Slide 81 text

Analytics Innovation Company ©BrainPad Inc. 80 3-3-2. 文字列関数② LENGTH, REPLACE  LENGTH • 文字列の長さを取得する • LENGTH(文字列)  REPLACE • 文字列の一部を別の文字列に置換する • REPLACE(文字列, 置換前の文字列, 置換後の文字列) • 例 shop_nameの ’スーパー’ を ’デパート’ に置換 REPLACE(shop_name, ‘スーパー’, ‘デパート’) ※DBMS製品によって、結果が文字数かバイト数か異なる 解説 /* receiptテーブルでshop_nameの文字数を取得する (length_shop_nameとする)。 また、shop_nameの ’スーパー’ を ’デパート’ に置換 する(replace_shop_nameとする)。 ・抽出フィールド:id, shop_name, length_shop_name, replace_shop_name ・idの昇順で5件分表示 */ SELECT id ,shop_name ,LENGTH(shop_name) AS length_shop_name ,REPLACE(shop_name, ‘スーパー’, ‘デパート’ ) AS replace_shop_name FROM receipt ORDER BY id LIMIT 5; SQL 置換

Slide 82

Slide 82 text

Analytics Innovation Company ©BrainPad Inc. 81 3-3-2. 文字列関数② LENGTH, REPLACE receipt_itemテーブルでnameの文字数を取得する(length_nameとする)。 また、nameの ’スナック’ を ’ポテトチップス’ に置換する(replace_nameとする)。 ・抽出フィールド:id, name, length_name, replace_name ・idの昇順で5件分表示 /* receipt_itemテーブルでnameの文字数を取得する(length_nameとする)。 また、nameの ’スナック’ を ’ポテトチップス’ に置換する(replace_nameとする)。 抽出フィールド:id, name, length_name, replace_name ※idの昇順で5件分表示 */ 類題 実行結果 レコード数 5 SQL

Slide 83

Slide 83 text

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

Slide 84

Slide 84 text

Analytics Innovation Company ©BrainPad Inc. 83 3-4-1. 日付関数 CURRENT_DATE, DATEDIFF receiptテーブルで、現在の日付(current_dateとする)、 及びpaid_atの日付から2018年04月02日までの日数を計算する(date_diffとする)。 ・抽出フィールド:id, paid_at, current_date, date_diff ・idの昇順で5件分表示 /* receiptテーブルで、現在の日付(current_dateとする)、 及びpaid_atの日付から2018年04月02日までの日数を計算する(date_diffとする)。 ・抽出フィールド:id, paid_at, current_date, date_diff ・idの昇順で5件分表示 */ SELECT id ,CAST(paid_at AS DATE) ,CURRENT_DATE AS `current_date` ,DATEDIFF(CAST(’2018-04-02’ AS DATE), paid_at) AS date_diff FROM receipt ORDER BY id LIMIT 5; 例題 SQL 実行結果 レコード数5 実行日が格納される

Slide 85

Slide 85 text

Analytics Innovation Company ©BrainPad Inc. 84 3-4-1. 日付関数 CURRENT_DATE, DATEDIFF  CURRENT_DATE • 現在の日付を取得する  DATEDIFF • 2つの日付または時刻式の日付部分の差を返す • DATEDIFF(日付①, 日付②) • 日付①-日付②を返す • RedshiftのDATEDIFFと少し異なります 解説 /* receiptテーブルで、現在の日付(current_date とする)、 及びpaid_atの日付から2018年04月02日まで の日数を計算する(date_diffとする)。 ・抽出フィールド:id, paid_at, current_date, date_diff ・idの昇順で5件分表示 */ SELECT id ,CAST(paid_at AS DATE) ,CURRENT_DATE AS `current_date` ,DATEDIFF(paid_at, CAST(’2018-04-02’ AS DATE)) AS date_diff FROM receipt ORDER BY id LIMIT 5; SQL

Slide 86

Slide 86 text

Analytics Innovation Company ©BrainPad Inc. 85 3-4-1. 日付関数 CURRENT_DATE, DATEDIFF userテーブルで、birthdayの日付から現在の日付までの日数を計算する(date_diffとする)。 ・抽出フィールド:id, birthday, current_date, date_diff ・idの昇順で5件分表示 /* userテーブルで、birthdayの日付から現在の日付までの日数を計算する(date_diffとする)。 ・抽出フィールド:id, birthday, current_date, date_diff ・idの昇順で5件分表示 */ 類題 実行結果 レコード数 5 SQL 実行日によって結果が異なります

Slide 87

Slide 87 text

Analytics Innovation Company ©BrainPad Inc. 86 3-4-2. 誕生日から年齢を求める userテーブルのbirthdayから、現在の日付におけるユーザーの年齢を求める。 ・抽出フィールド:id, current_date, birthday, age ・idの昇順で5件分表示 /* userテーブルのbirthdayから、現在の日付におけるユーザーの年齢を求め る。 ・抽出フィールド:id, current_date, birthday, age ・idの昇順で5件分表示 */ SELECT id ,CURRENT_DATE AS `current_date` ,birthday ,TRUNCATE((CAST(REPLACE(CURRENT_DATE, ‘-’, ‘’) AS UNSIGNED) - CAST(REPLACE(birthday, ‘-’, ‘’) AS UNSIGNED) ) / 10000, 0) AS `age` FROM `user` ORDER BY id LIMIT 5; 例題 実行結果 レコード数5 SQL

Slide 88

Slide 88 text

Analytics Innovation Company ©BrainPad Inc. 87 3-4-2. 誕生日から年齢を求める  誕生日から年齢を求める手順 1. REPLACE(日付, ‘-’, ‘’) 現在日付と誕生日について、yyyy-mm-dd形式のハイフン’-’ を空文字’’に置換し、yyyymmdd形式に直す 2. CAST(yyyymmdd AS UNSIGNED) 整数型(INTEGER)に変換 3. 差をとって10000で割り、小数部分切り捨て 切り捨てはTRUNCATE(数値 , 0) 例. 現在日付2018-04-24, 誕生日1995-06-21 (20180424 – 19950621) / 10000 =22.98 → 22歳 ※この方法でなぜ年齢を求めることができるのかについては、各自 調べてください(年齢を求めるAGE関数がサポートされているDB製 品もあります) 解説 /* userテーブルのbirthdayから、現在の日付におけ るユーザーの年齢を求める。 ・抽出フィールド:id, current_date, birthday, age ・idの昇順で5件分表示 */ SELECT id ,CURRENT_DATE AS `current_date` ,birthday ,TRUNCATE( (CAST(REPLACE(CURRENT_DATE, ‘-’, ‘’) AS UNSIGNED) - CAST(REPLACE(birthday, ‘-’, ‘’)AS UNSIGNED)) / 10000, 0) AS `age` …以下省略 SQL

Slide 89

Slide 89 text

Analytics Innovation Company ©BrainPad Inc. 88 3-4-2. 誕生日から年齢を求める userテーブルのbirthdayから、最終ログインの日付におけるユーザーの年齢を求める。 ・抽出フィールド:id, last_login, birthday, age_at_last_login ・idの昇順で5件分表示 /* userテーブルのbirthdayから、最終ログインの日付におけるユーザーの年齢を求める。 ・抽出フィールド:id, last_login, birthday, age_at_last_login ・idの昇順で5件分表示 */ 類題 実行結果 レコード数 5 SQL

Slide 90

Slide 90 text

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

Slide 91

Slide 91 text

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

Slide 92

Slide 92 text

Analytics Innovation Company ©BrainPad Inc. 91 4-1-1. サブクエリとは 例 「price(価格)が最も高いデータのname(品目)を知りたい」 これまでに学んだ知識で実現すると、例えば以下のようになる 手順1 priceの最大値を求め、結果をメモ SELECT MAX(price) FROM receipt_item; 結果 2000 手順2 手順1の結果2000を条件に検索 SELECT name FROM receipt_item WHERE price = 2000 ; 結果 B receipt_item id user_id price name 1 1 100 A 2 1 2000 B 3 2 300 C 4 2 1500 D 5 3 800 E receipt_item id user_id price name 1 1 100 A 2 1 2000 B 3 2 300 C 4 2 1500 D 5 3 800 E 代入

Slide 93

Slide 93 text

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

Slide 94

Slide 94 text

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

Slide 95

Slide 95 text

Analytics Innovation Company ©BrainPad Inc. 94 4-1-1. サブクエリとは サブクエリの3つのパターン パターン① 単一の値(1行1列)を返す パターン② 複数の値(n行1列)を返す パターン③ 表形式の値(n行m列)を返す SELECT… (SELECT… ) SELECT… 2000 SELECT… (SELECT… ) SELECT… SELECT… (SELECT… ) SELECT… 10 20 30 A 1 10 B 2 20 C 3 30 単一の値 複数の値 表の値 n行 1列 n行 m列 1列 1行

Slide 96

Slide 96 text

Analytics Innovation Company ©BrainPad Inc. 95 4-1-2. サブクエリ パターン① 単一の値を返す receipt_itemテーブルでpriceが最も低いデータのid, user_id, priceを サブクエリを用いて取得する。 /* receipt_itemテーブルでpriceが最も低いデータのid, user_id, priceを サブクエリを用いて取得する。 */ SELECT id ,user_id ,price FROM receipt_item WHERE price = (SELECT MIN(price) FROM receipt_item) ORDER BY id LIMIT 1 ; 例題 SQL 実行結果 レコード数 1

Slide 97

Slide 97 text

Analytics Innovation Company ©BrainPad Inc. 96 4-1-2. サブクエリ パターン① 単一の値を返す  単一行サブクエリ • 検索結果が1行1列の1つの値になるサブクエリ • WHEREの他にSELECTの選択列リストにも記述できる 例 user_id, priceとpriceの全体平均値を並べて表示 SELECT user_id ,price ,(SELECT AVG(price) FROM receipt_item) AS avg_price FROM receipt_item ORDER BY user_id LIMIT 5 ; 解説 /* receipt_itemテーブルでpriceが最も低いデータ のid, user_id, priceをサブクエリを用いて取得す る。 */ SELECT id ,user_id ,price FROM receipt_item WHERE price = (SELECT MIN(price) FROM receipt_item) ORDER BY id LIMIT 1 ; SQL 実行結果 レコード数 5

Slide 98

Slide 98 text

Analytics Innovation Company ©BrainPad Inc. 97 4-1-2. サブクエリ パターン① 単一の値を返す receipt_itemテーブルで、user_id, priceとpriceの標準偏差を並べて表示する。 ただし、user_idの昇順で5件表示とし、標準偏差はSTDDEV_POP()を使用せよ。 /* receipt_itemテーブルで、user_id, priceとpriceの標準偏差を並べて表示する。 ただし、user_idの昇順で5件表示とし、標準偏差はSTDDEV_POP()を使用せよ。 */ 類題 実行結果 レコード数 5 SQL

Slide 99

Slide 99 text

Analytics Innovation Company ©BrainPad Inc. 98 4-1-3. サブクエリ パターン② 複数の値を返す receipt_itemテーブルとuserテーブルを用いて、’ビールA’ を購入したことの ある人をstate_code(都道府県)別にカウントする。 /* receipt_itemテーブルとuserテーブルを用いて、’ビールA’ を購入し たことのある人を都道府県別にカウントする。 */ SELECT state_code ,COUNT(id) FROM `user` WHERE id IN (SELECT DISTINCT user_id FROM receipt_item WHERE name = ‘ビールA’) GROUP BY state_code ORDER BY state_code; 例題 実行結果 レコード数47 SQL id user_id (FK) … receipt_item id state_code … user ER図

Slide 100

Slide 100 text

Analytics Innovation Company ©BrainPad Inc. 99 4-1-3. サブクエリ パターン② 複数の値を返す  複数行サブクエリ • 検索結果がn行1列の複数の値となるサブクエリ … WHERE id IN (SELECT DISTINCT user_id FROM receipt_item WHERE name = ‘ビールA’) … WHERE id IN 複数の値と比較するするときはIN演算子, ANY/ALL演算子を用い る。比較演算子=,>などは使えないので注意! (復習) • IN演算子:列挙した値のいずれかに一致するか判定 • ANY演算子:それぞれと比較して、いずれかが真なら真 • ALL演算子:それぞれと比較して、全て真なら真 ※redshiftでは一般的なALLはサポートしておらず、<> ALL (= NOT IN)しか使えないので注意 解説 /* receipt_itemテーブルとuserテーブルを用いて、’ アイス’ を購入したことのある人を都道府県別にカ ウントする。 */ SELECT state_code ,COUNT(id) FROM `user` WHERE id IN (SELECT DISTINCT user_id FROM receipt_item WHERE name = ‘ビールA’) … (以下省略) SQL user_id 10 20 …

Slide 101

Slide 101 text

Analytics Innovation Company ©BrainPad Inc. 100 4-1-3. サブクエリ パターン② 複数の値を返す receipt_itemテーブルとuserテーブルを用いて、name(商品名)が’カップラーメンA‘を買った ことのある人を性別にカウントする。 /* receipt_itemテーブルとuserテーブルを用いて、name(商品名)が’カップラーメンA‘を 買ったことのある人を性別にカウントする。 */ 類題 実行結果 レコード数 2 SQL id user_id (FK) name … receipt_item id gender … user ER図

Slide 102

Slide 102 text

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

Slide 103

Slide 103 text

Analytics Innovation Company ©BrainPad Inc. 102 4-1-4. サブクエリ パターン③ 表形式の値を返す  表形式の結果となるサブクエリ • 検索結果がn行m列の表となるサブクエリ … FROM (SELECT receipt_id ,SUM(price) AS sum_price FROM receipt_item GROUP BY receipt_id) AS tbl … FROM • この表のsum_priceの平均値を求めている 解説 /* receipt_itemテーブルとuserテーブルを用いて、1 回の買い物の合計金額の平均値を求める。 */ SELECT AVG(sum_price) FROM (SELECT receipt_id ,SUM(price) AS sum_price FROM receipt_item GROUP BY receipt_id) AS tbl ; SQL receipt_id sum_price 1 409 2 198 … …

Slide 104

Slide 104 text

Analytics Innovation Company ©BrainPad Inc. 103 4-1-4. サブクエリ パターン③ 表形式の値を返す receipt_itemテーブルを用いて、1ユーザーあたりの累計合計金額の平均値を 求める。 /* receipt_itemテーブルを用いて、1ユーザーあたりの累計合計金額の平均値を求める。 ヒント ① サブクエリでuser_idでグループ化し、合計金額を求める ② サブクエリの結果をもとに平均値を求める */ 類題 SQL 実行結果 レコード数1 id user_id (FK) price … receipt_item

Slide 105

Slide 105 text

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

Slide 106

Slide 106 text

Analytics Innovation Company ©BrainPad Inc. 105 4-1-5. WITH句 サブクエリの代用  WITH句(WITH問い合わせ) • サブクエリに当たる部分をもとのSQLと切り離す • サブクエリが入れ子になるのに対して、WITH句の場合は構造 がわかりやすい(可読性が高い) • WITH句はカンマで区切ることで複数続けて書くこともできる 例:WITH tblA AS(…), tblB AS(…) SELECT ・・・; 解説 /* receipt_itemテーブルを用いて、1回の買い物の 合計金額の平均値を求める。 ただし、サブクエリの代わりにWITH句を利用する。 */ WITH tbl AS ( SELECT receipt_id ,SUM(price) AS sum_price FROM receipt_item GROUP BY receipt_id ) SELECT AVG(sum_price) FROM tbl ; SQL WITH [呼び出し名] AS ( SELECT … FROM … ) SELECT … FROM … ; SELECT … FROM( SELECT … FROM … ) AS [呼び出し名] ; WITH句で呼び出す場合 サブクエリで呼び出す場合

Slide 107

Slide 107 text

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

Slide 108

Slide 108 text

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

Slide 109

Slide 109 text

Analytics Innovation Company ©BrainPad Inc. 108 4-2-1. テーブルの結合とは • 複数のテーブルを、共通項目(キー)を用いて1つにまとめることを結合という • 結合に用いる共通項目はER図から調べる user(右表) id state_code 1 13 2 14 3 27 4 45 receipt(左表) id user_id 1 1 2 2 3 3 4 4 結合した結果 receipt_id user_id state_code 1 1 13 2 2 14 3 3 27 4 4 45 id gender … user id user_id (FK) shop_name … receipt id user_id (FK) receipt_id (FK) … receipt_item

Slide 110

Slide 110 text

Analytics Innovation Company ©BrainPad Inc. 109 4-2-2. INNER JOIN(内部結合) receiptテーブル(左表)とuserテーブル(右表)を、userテーブルのidとreceiptテーブルのuser_id を用いて内部結合する。 ・抽出フィールド:receipt テーブルid, shop_name / userテーブル id, state_code /* receiptテーブルとuserテーブルを、userテーブルのidとreceiptテーブルの user_idを用いて内部結合する。 ・抽出フィールド:receipt テーブルid, shop_name / userテーブル id, state_code */ SELECT r.id AS receipt_id ,u.id AS user_id ,r.shop_name ,u.state_code FROM receipt AS r INNER JOIN `user` AS u ON r.user_id = u.id ORDER BY r.id LIMIT 5; 例題 実行結果 レコード数5 SQL id user_id (FK) shop_name … receipt id state_code … user ER図

Slide 111

Slide 111 text

Analytics Innovation Company ©BrainPad Inc. 110 4-2-2. INNER JOIN(内部結合)  INNER JOIN • 内部結合 共通項目をキーとし、一致するレコードのみ取り出す • 基本構文 FROM テーブルA(左表) INNER JOIN テーブルB(右表) ON 結合条件  .(ドット)の意味 • 日本語の助詞「〜の」と解釈するとわかりやすい • 例1. u.id:「u(user)テーブルのidフィールド」 • 例2. u.*:「u(user)テーブルの全てのフィールド」  r.idとu.idの区別 • 元のテーブルでは同じ”id”というフィールド名が付いているが、意 味が異なるのでそれぞれ別名をつける • r.id AS receipt_id 「レシートを一意に決めるid」 • u.id AS user_id 「ユーザーを一意に決めるid」 解説 /* receiptテーブルとuserテーブルを、userテーブル のidとreceiptテーブルのuser_idを用いて内部結 合する。 抽出フィールド:receipt テーブルid, shop_name / userテーブル id, state_code */ SELECT r.id AS receipt_id ,u.id AS user_id ,r.shop_name ,u.state_code FROM receipt AS r INNER JOIN `user` AS u ON r.user_id = u.id … (以下省略) SQL

Slide 112

Slide 112 text

Analytics Innovation Company ©BrainPad Inc. 111 4-2-2. INNER JOIN(内部結合)  INNER JOINのイメージ 解説 user(右表) id state_code 1 13 2 14 3 27 receipt(左表) id user_id shop_name 1 1 A 2 2 B 3 2 C 4 3 C 5 4 D 内部結合した結果 receipt_id user_id shop_name state_code 1 1 A 13 2 2 B 14 3 2 C 14 4 3 C 27 結合の相手がいない行は消える(こ の例では左表のuser_id = 4の行 が消えている) • 内部結合はデータ件数の不一致が生じてしまう場合があるため、注意が必要 • 結合相手がいない場合にもデータを残す結合は外部結合で行う(後述)

Slide 113

Slide 113 text

Analytics Innovation Company ©BrainPad Inc. 112 4-2-2. INNER JOIN(内部結合) receipt_itemテーブル(左表)とuserテーブル(右表)を、userテーブルのidとreceipt_itemテーブルの user_idを用いて内部結合する。 ・抽出フィールド:receipt_item テーブルid, name / userテーブル id, gender ・receipt_itemテーブルのidの昇順で5件分表示。 /* receipt_itemテーブル(左表)とuserテーブル(右表)を、 userテーブルのidとreceipt_itemテーブルのuser_idを用いて内部結合する。 ・抽出フィールド:receipt_item テーブルid, name / userテーブル id, gender ・receipt_itemテーブルのidの昇順で5件分表示 */ 類題 実行結果 レコード数 5 SQL id user_id (FK) receipt_id … receipt_item id gender … user ER図

Slide 114

Slide 114 text

Analytics Innovation Company ©BrainPad Inc. 113 4-2-3. LEFT JOIN(外部結合) receiptテーブル(左表)とuserテーブル(右表)を、userテーブルのidとreceiptテーブルのuser_idを用いて外部 結合する。 ・抽出フィールド:receipt テーブルid, shop_name / userテーブル id, state_code ・receiptテーブルのid >= 60を対象とし、昇順で5件表示する。 /* receiptテーブル(左表)とuserテーブル(右表)を、userテーブルのidとreceipt テーブルのuser_idを用いて外部結合する。 ・抽出フィールド:receipt テーブルid, shop_name / userテーブル id, state_code */ SELECT r.id AS receipt_id ,u.id AS user_id ,r.shop_name ,u.state_code FROM receipt AS r LEFT JOIN `user` AS u ON r.user_id = u.id WHERE r.id >= 60 ORDER BY r.id LIMIT 5; 例題 実行結果 レコード数5 SQL id user_id (FK) shop_name … receipt id state_code … user ER図

Slide 115

Slide 115 text

Analytics Innovation Company ©BrainPad Inc. 114 4-2-3. LEFT JOIN(外部結合)  LEFT JOIN • 左外部結合 対応するレコードがない場合でもレコードが削除されない結合 • 基本構文 FROM テーブルA(左表) LEFT JOIN テーブルB(右表) ON 結合条件  対応するレコードがない場合 • 全てNULLになる 解説 /* receiptテーブルとuserテーブルを、userテーブル のidとreceiptテーブルのuser_idを用いて内部結 合する。 抽出フィールド:receipt テーブルid, shop_name / userテーブル id, state_code */ SELECT r.id AS receipt_id ,u.id AS user_id ,r.shop_name ,u.state_code FROM receipt AS r LEFT JOIN `user` AS u ON r.user_id = u.id … (以下省略) SQL

Slide 116

Slide 116 text

Analytics Innovation Company ©BrainPad Inc. 115 4-2-3. LEFT JOIN(外部結合)  LEFT JOINのイメージ 解説 user(右表) id state_code 1 13 2 14 3 27 receipt(左表) id user_id shop_name 1 1 A 2 2 B 3 2 C 4 3 C 5 4 D 外部結合した結果 receipt_id user_id shop_name state_code 1 1 A 13 2 2 B 14 3 2 C 14 4 3 C 27 5 4 D (NULL) 結合の相手がいない行も削除され ず、存在しないデータは全てNULL になる • 分析用のデータマートを作成する際は、件数の不整合が生じないよう、一般的にLEFT JOINを用いる

Slide 117

Slide 117 text

Analytics Innovation Company ©BrainPad Inc. 116 4-2-3. LEFT JOIN(外部結合) receipt_itemテーブル(左表)とuserテーブル(右表)を、userテーブルのidと receipt_itemテーブルのuser_idを用いて外部結合する。 ・抽出フィールド:receipt_item テーブルid, name / userテーブル id, gender ・receipt_itemテーブルのid >= 135を対象とし、昇順で5件分表示する。 /* receipt_itemテーブル(左表)とuserテーブル(右表)を、 userテーブルのidとreceipt_itemテーブルのuser_idを用いて外部結合する。 ・抽出フィールド:receipt_item テーブルid, name / userテーブル id, gender ・receipt_itemテーブルのid >=135を対象とし、昇順で5件分表示 */ 類題 実行結果 レコード数 5 SQL id user_id (FK) receipt_id … receipt_item id gender … user ER図

Slide 118

Slide 118 text

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

Slide 119

Slide 119 text

Analytics Innovation Company ©BrainPad Inc. 118 4-3-1. 一時テーブルの作成 userテーブルからlast_loginが2013年3月のものを抽出し、一時テーブルtmp_user_201303として保存せ よ。また、確認のため作成した一時テーブルtmp_user_201303からデータを5件取得せよ。 /* userテーブルからlast_loginが2013年3月のものを抽出し、一時テーブル #user_201303として保存せよ。また、確認のため作成した一時テーブルから データを5件取得せよ。 */ CREATE TEMPORARY TABLE tmp_user_201303 SELECT * FROM `user` WHERE last_login BETWEEN ‘2013-03-01’ AND ‘2013-03-31’ ; 一時テーブル作成後、確認のため以下のクエリでデータを取得 SELECT * FROM tmp_user_201303 ORDER BY id LIMIT 5; 例題 SQL 一時テーブル作成 一時テーブルからデータ取得結果

Slide 120

Slide 120 text

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

Slide 121

Slide 121 text

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

Slide 122

Slide 122 text

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

Slide 123

Slide 123 text

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

Slide 124

Slide 124 text

Analytics Innovation Company ©BrainPad Inc. 123 5-1-1. ウィンドウ関数とは  ウィンドウ関数 • より効率的に分析用のクエリを作成することができる • SQLの中でも比較的新しい機能 ウィンドウ関数の種類 カテゴリ 使用例 関数例 集計関数群 累積集計を計算する SUM, COUNT, AVG, MIN, MAX, MEDIAN など ランク付け関数群 ウィンドウ内のランキングを計算す る ROW_NUMBER, DENSE_RANK, RANK など ラグ・リード関数群 前後のレコードを取得する LAG, LEAD, FIRST_VALUE, LAST_VALUE, NTH_VALUE など レポート関数群 ウィンドウ内の構成比を計算する RATIO_TO_REPORT, CUME_DIST, NTILE, PERCENTILE_DISC など 線形回帰関数群 統計量を計算する STDDEV_SAMP, VAR_SAMP など

Slide 125

Slide 125 text

Analytics Innovation Company ©BrainPad Inc. 124 5-1-2. ウィンドウ関数① 集計関数群 receipt_itemテーブルで、user_idで区切ったパーティション毎に SUM, AVG, MIN, MAXで集計を行う。 ・user_idが5以下のデータを対象とする。 /* receipt_itemテーブルで、user_idで区切ったパーティション毎にSUM, AVG, MIN, MAXで集計を行う。 ・user_idが5以下のデータを対象とする。 */ SELECT user_id ,price ,SUM(price) OVER(PARTITION BY user_id) AS `sum` ,AVG(price) OVER(PARTITION BY user_id) AS `avg` ,MIN(price) OVER(PARTITION BY user_id) AS `min` ,MAX(price) OVER(PARTITION BY user_id) AS `max` FROM receipt_item WHERE user_id <=5 ORDER BY user_id ; 例題 実行結果 レコード数4058 SQL … …

Slide 126

Slide 126 text

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

Slide 127

Slide 127 text

Analytics Innovation Company ©BrainPad Inc. 126 5-1-2. ウィンドウ関数① 集計関数群  ウィンドウ関数の集計関数SUM SELECT user_id ,price ,SUM(price) OVER(PARTITION BY user_id) FROM receipt_item ORDER BY user_id; 解説  GROUP BYで用いる集計関数SUM SELECT user_id ,SUM(price) FROM receipt_item GROUP BY user_id ORDER BY user_id; 実行結果の一部 実行結果の一部 元のレコードが 保持されたまま user_id毎に レコードが集約される 集約の基準列以外は集計関 数しか選択できない(price は選択できない) 以前学んだGROUP BYでの集計と、ウィンドウ関数での集計結果の違いを知る … …

Slide 128

Slide 128 text

Analytics Innovation Company ©BrainPad Inc. 127 5-1-2. ウィンドウ関数① 集計関数群 receiptテーブルで、user_idで区切ったパーティション毎にCOUNTで集計を行う。 ・user_idが3以下のデータを対象とする。 /* receiptテーブルで、user_idで区切ったパーティション毎にCOUNTで集計を行う。 ・user_idが3以下のデータを対象とする。 */ 類題 実行結果 レコード数 82 SQL

Slide 129

Slide 129 text

Analytics Innovation Company ©BrainPad Inc. 128 5-1-3. ウィンドウ関数② ランク付け関数群 receiptテーブルで、user_idで区切ったパーティション毎にpaid_atの昇順で 連番を振る。 ・user_idが5以下、paid_atが’2010-01-31’以前のデータを対象とする。 /* receiptテーブルで、user_idで区切ったパーティション毎にpaid_atの昇順で 連番を振る。 ・user_idが5以下のデータを対象とする。 */ SELECT user_id ,CAST(paid_at AS DATE) ,ROW_NUMBER() OVER(PARTITION BY user_id ORDER BY CAST(paid_at AS DATE)) AS `row` ,DENSE_RANK() OVER(PARTITION BY user_id ORDER BY CAST(paid_at AS DATE)) AS `dense` ,RANK() OVER(PARTITION BY user_id ORDER BY CAST(paid_at AS DATE)) AS `rank` FROM receipt WHERE user_id <=5 AND paid_at <= '2010-01-31' ORDER BY user_id, paid_at ; 例題 実行結果 レコード数18 SQL

Slide 130

Slide 130 text

Analytics Innovation Company ©BrainPad Inc. 129 5-1-3. ウィンドウ関数② ランク付け関数群  ランク付け関数  例 XX() OVER(PARTITION BY id ORDER BY year) 解説 /* receiptテーブルで、user_idで区切ったパーティション毎 にpaid_atの昇順で連番を振る。 ・user_idが5以下のデータを対象とする。 */ SELECT user_id ,CAST(paid_at AS DATE) ,ROW_NUMBER() OVER(PARTITION BY user_id ORDER BY CAST(paid_at AS DATE)) …(省略)… FROM receipt WHERE user_id <=5 AND paid_at <= '2010-01-31' ORDER BY user_id, paid_at ; SQL ランク付け関数 処理内容 ROW_NUMBER ORDER BYで重複しても気にせずに連番を振る DENSE_RANK ORDER BYで重複したレコードには同じ番号を振り、 以降は連番が続く RANK ORDER BYで重複したレコードには同じ番号を振る が、以降の連番は重複した分番号がとぶ id year rn 1 2016 1 1 2017 2 2 2017 1 2 2017 2 2 2018 3 id year dr 1 2016 1 1 2017 2 2 2017 1 2 2017 1 2 2018 2 id year r 1 2016 1 1 2017 2 2 2017 1 2 2017 1 2 2018 3 ROW_NUMBER DENSE_RANK RANK

Slide 131

Slide 131 text

Analytics Innovation Company ©BrainPad Inc. 130 5-1-3. ウィンドウ関数② ランク付け関数群  ROW_NUMBERの使い道 • グループ毎に最大値(最小値)を持つレコードを抽出する • 例 user_id毎にpriceの最大値を求める(※user_idが5以下のデータを対象) SELECT user_id ,price AS max_price ,name FROM (SELECT * ,ROW_NUMBER() OVER(PARTITION BY user_id ORDER BY price DESC) AS seq FROM receipt_item) AS ri WHERE seq = 1 AND user_id <= 5 ORDER BY user_id ; 解説 実行結果 レコード数5 サブクエリ(次ページで解説)

Slide 132

Slide 132 text

Analytics Innovation Company ©BrainPad Inc. 131 5-1-3. ウィンドウ関数② ランク付け関数群  サブクエリ部分 解説 (SELECT * ,ROW_NUMBER() OVER(PARTITION BY user_id ORDER BY price DESC) AS seq FROM receipt_item) AS ri ※都合により一部のカラムのみ掲載 サブクエリによって得られたテーブル ri 最終結果 WHERE seq = 1 各グループの一 番上を取得 priceの降順に連番を振る …

Slide 133

Slide 133 text

Analytics Innovation Company ©BrainPad Inc. 132 5-1-3. ウィンドウ関数② ランク付け関数群 receipt_itemテーブルで、receipt_id毎にpriceの最大値を求める。 ・receipt_idが10以下のデータを対象とする。 /* receipt_itemテーブルで、receipt_id毎にpriceの最大値を求める。 ・receipt_idが10以下のデータを対象とする。 */ 類題 実行結果 レコード数 10 SQL (参考) サブクエリ部分の取得例

Slide 134

Slide 134 text

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

Slide 135

Slide 135 text

Analytics Innovation Company ©BrainPad Inc. 134 5-1-4. ウィンドウ関数③ ラグ・リード関数群 ラグ・リード関数群 解説 /* receipt_itemテーブルで、user_idで区切ったパーティション 毎にidの昇順に並び替え、priceについて1行前の値を lag1_price、2行前の値をlag2_priceとして取得する。 ・抽出フィールド:user_id, id, price, lag1_price, lag2_price ・user_idが3以下のデータを対象とする。 */ SELECT user_id ,id ,price ,LAG(price, 1) OVER(PARTITION BY user_id ORDER BY id) AS lag1_price ,LAG(price, 2) OVER(PARTITION BY user_id ORDER BY id) AS lag2_price FROM receipt_item WHERE user_id <= 3 ORDER BY user_id, id ; SQL ラグ・リード関数 処理内容 LAG(フィールド名, n) ORDER BYの順でn行前のレコードの値 を取得 LEAD(フィールド名, n) ORDER BYの順でn行後のレコードの値 を取得 FIRST_VALUE(フィールド名) ORDER BYの順で最初のレコードの値を 取得 LAST_VALUE(フィールド名) ORDER BYの順で最後のレコードの値を 取得 id val lag1_val lead1_val first_value last_value 1 10 (NULL) 20 10 20 1 20 10 (NULL) 10 20 2 100 (NULL) 200 100 300 2 200 100 300 100 300 2 300 200 (NULL) 100 300 例 XX() OVER(PARTITION BY id ORDER BY value)

Slide 136

Slide 136 text

Analytics Innovation Company ©BrainPad Inc. 135 5-1-4. ウィンドウ関数③ ラグ・リード関数群 receipt_itemテーブルで、user_idで区切ったパーティション毎にidの昇順に並び替え、priceについて 1行後の値をlead1_price、2行後の値をlead2_priceとして取得する。 ・抽出フィールド:user_id, id, price, lead1_price, lead2_price ・user_idが3以下のデータを対象とする。 /* receipt_itemテーブルで、user_idで区切ったパーティション毎にidの昇順に並び替え、priceについて1行後の値をlead1_price、2行後の 値をlead2_priceとして取得する。 ・抽出フィールド:user_id, id, price, lead1_price, lead2_price ・user_idが3以下のデータを対象とする。 */ 類題 SQL 実行結果 レコード数 25 …

Slide 137

Slide 137 text

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

Slide 138

Slide 138 text

Analytics Innovation Company ©BrainPad Inc. 137 5-1-5. ウィンドウ関数④ 参考  ROWS • 集計処理する対象行を定義する • デフォルト「開始点: ウィンドウの最初の行、終了点:ウィンドウの最後の行」 (ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) UNBOUNDED PRECEDING パーティションの最初の行 CURRENT ROW 現在の行 UNBOUNDED FOLLOWING パーティションの最後の行 ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING (※省略した時のデフォルト) ROWS BETWEEN 2 PRECEDING AND CURRENT ROW ROWS BETWEEN 1 PRECEDING AND 2 FOLLOWING ROWS BETWEEN 1 FOLLOWING AND 3 FOLLOWING

Slide 139

Slide 139 text

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

Slide 140

Slide 140 text

Analytics Innovation Company ©BrainPad Inc. 139 5-2-1. EXISTS 存在検査 userテーブルのレコードで、idがreceiptテーブルのuser_idとして存在しているものを抽出する。 ・抽出フィールド:id, gender, birthday ・user_idの昇順で5件分表示する /* userテーブルのレコードで、idがreceiptテーブルのuser_idとして存在している ものを抽出する。 ・抽出フィールド:id, gender, birthday ・user_idの昇順で5件分表示する */ SELECT id AS user_id ,gender ,birthday FROM `user` AS u WHERE EXISTS( SELECT 1 FROM receipt AS r WHERE r.user_id = u.id) ORDER BY u.id LIMIT 5 ; 例題 実行結果 レコード数 5 SQL

Slide 141

Slide 141 text

Analytics Innovation Company ©BrainPad Inc. 140 5-2-1. EXISTS 存在検査  EXISTS句による存在検査 SELECT id AS user_id … FROM `user` AS u WHERE EXISTS( SELECT 1 FROM receipt AS r WHERE r.user_id = u.id)  NOT EXISTS句による非存在検査 • 存在しないものを抽出できる 解説 /* userテーブルのレコードで、idがreceiptテーブルのuser_idと して存在しているものを抽出する。 ・抽出フィールド:id, gender, birthday ・user_idの昇順で5件分表示する */ SELECT id AS user_id ,gender ,birthday FROM `user` AS u WHERE EXISTS( SELECT 1 FROM receipt AS r WHERE r.user_id = u.id) ORDER BY u.id LIMIT 5; SQL ② receiptテーブルの ③ user_idの中に存在して いるとき(TRUEを返す) ④ userテーブルの ⑤ idを抽出する ① userテーブルのidが * や他の値でもOK

Slide 142

Slide 142 text

Analytics Innovation Company ©BrainPad Inc. 141  EXISTS句による存在検査のイメージ 5-2-1. EXISTS 存在検査 解説 /* userテーブルのレコードで、idがreceiptテーブルのuser_idと して存在しているものを抽出する。 ・抽出フィールド:id, gender, birthday ・user_idの昇順で5件分表示する */ SELECT id AS user_id ,gender ,birthday FROM `user` AS u WHERE EXISTS( SELECT 1 FROM receipt AS r WHERE r.user_id = u.id) ORDER BY u.id LIMIT 5; user id … 5 6 7 10 12 receipt user_id … 3 4 5 15 20 1レコードず つ処理 ① userテーブルの1行目id=5が ② receiptテーブルの ③ user_idの中に存 在しているとき ④ userテーブルの ⑤ id, gender, birthday を抽出する WHERE EXISTSの中の処理 ① userテーブルidが ② receiptrテーブルの ③ user_id の中に存在し ているとき ④ userテーブルの ⑤ id, gender, birthdayを抽出する SELECT … FROM…の処理 抽出結果 id gender birthday 5 1 1990-01-01 WHERE TRUE 該当レコードを抽出対象とする

Slide 143

Slide 143 text

Analytics Innovation Company ©BrainPad Inc. 142  EXISTSとINによる処理速度の違い(以下2つは同じ結果が得られる) 5-2-1. EXISTS 存在検査 解説  EXISTSを利用 SELECT id AS user_id ,gender ,birthday FROM `user` AS u WHERE EXISTS( SELECT 1 FROM receipt AS r WHERE r.user_id = u.id) ;  INを利用 SELECT id AS user_id ,gender ,birthday FROM `user` AS u WHERE u.id IN ( SELECT r.user_id FROM receipt AS r) ; INよりもEXISTSを 利用した場合の方が 高速に処理されること が多い INと複数行サブクエリ を利用

Slide 144

Slide 144 text

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

Slide 145

Slide 145 text

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

Slide 146

Slide 146 text

Analytics Innovation Company ©BrainPad Inc. 145 5-3-1. 縦持ち・横持ちとは  横持ち • 可読性が高い、いわゆる普通のデータの保持  縦持ち • データを行単位で保持 • SQLは行に対する処理を得意とするため、列数が多い場合など、縦持ちの方 が高速に処理できることがある 例. ユーザー情報の横持ち id gender state_code 1 1 13 2 2 27 3 2 30 例. ユーザー情報の縦持ち id category value 1 gender 1 1 state_code 13 2 gender 2 2 state_code 27 3 gender 2 3 state_code 30 分析目的に合わせたデータマート を作成する際に、縦持ち横持ちの 変換をよく行う 変換

Slide 147

Slide 147 text

Analytics Innovation Company ©BrainPad Inc. 146 5-3-2. 横持ち→縦持ち変換 userテーブルのgender, state_codeを、それぞれのフィールド名をcategory、値をvalueと した縦持ちに変換し、一時テーブルtmp_v_userとして保存する。 その後、確認のために一時テーブルtmp_v_userからデータを取得する。 /* userテーブルのgender, state_codeを、それぞれのフィールド名をcategory、 値をvalueとした縦持ちに変換し、一時テーブルtmp_v_userとして保存する。 その後、確認のために一時テーブルtmp_v_userからデータを取得する。 */ CREATE TEMPORARY TABLE tmp_v_user ( SELECT id , 'gender' AS category , gender AS value FROM `user` UNION ALL SELECT id , 'state_code' AS category , CAST(state_code AS UNSIGNED) AS value FROM `user` ) ; 一時テーブル作成後、確認のため以下のクエリでデータを取得 SELECT * FROM tmp_v_user ORDER BY id, category LIMIT 10; 例題 SQL 実行結果

Slide 148

Slide 148 text

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

Slide 149

Slide 149 text

Analytics Innovation Company ©BrainPad Inc. 148 /* userテーブルのgender, state_codeを、それ ぞれのフィールド名をcategory、値をvalueとし た縦持ちに変換し、一時テーブル#v_userとし て保存する。 */ SELECT id , 'gender' AS category , gender AS value FROM `user` UNION ALL SELECT id , 'state_code' AS category , CAST(state_code AS UNSIGNED) AS value FROM `user` ; 5-3-2. 横持ち→縦持ち変換 解説  UNION ALLのイメージ SQL id category value 1 gender 1 2 gender 2 3 gender 3 + UNION ALL = id category value 1 state_code 13 2 state_code 22 3 state_code 17 id category value 1 gender 1 2 gender 2 3 gender 3 1 state_code 13 2 state_code 22 3 state_code 17 1 2 結合結果 1 2

Slide 150

Slide 150 text

Analytics Innovation Company ©BrainPad Inc. 149 5-3-2. 横持ち→縦持ち変換 receiptテーブルのpaid_at, read_at, update_atを、それぞれのフィールド名をxxx_at、 値をdateとした縦持ちに変換する。 /* receiptテーブルのpaid_at, read_at, update_atそれぞれのフィー ルド名をxxx_at、値をdateとした縦持ちに変換する。 */ 類題 実行結果 レコード数 10 SQL

Slide 151

Slide 151 text

Analytics Innovation Company ©BrainPad Inc. 150 5-3-3. 縦持ち→横持ち変換 5-3-2で作成した縦持ちの一時テーブルtmp_v_userを横持ちに変換し、idの昇順で10件 表示する。 /* 5-3-2で作成した縦持ちの一時テーブルtmp_v_userを横持ちに変換し、10件表示する。 */ SELECT id ,MAX(CASE WHEN category = 'gender' THEN value END) AS gender ,MAX(CASE WHEN category = 'state_code' THEN value END) AS state_code FROM tmp_v_user GROUP BY id ORDER BY id LIMIT 10; 例題 実行結果 レコード数 10 SQL

Slide 152

Slide 152 text

Analytics Innovation Company ©BrainPad Inc. 151 /* 5-3-2で作成した縦持ちの一時テーブル tmp_v_userを横持ちに変換し、10件表示す る。 */ SELECT id ,MAX(CASE WHEN category = ‘gender’ THEN value END) AS gender ,MAX(CASE WHEN category = ‘state_code’ THEN value END) AS state_code FROM tmp_v_user GROUP BY id … 以下省略 5-3-3. 縦持ち→横持ち変換 解説  処理①(グループ化、集計する前のCASE文の処理) • 結果の空欄はNULLを表す  処理②(グループ化) • MAX()は、NULLを対象としない SQL SELECT id ,CASE WHEN category = 'gender’ THEN value END AS gender ,CASE WHEN category = 'state_code’ THEN value END AS state_code FROM #v_user; … MAX(CASE WHEN category = 'gender’ THEN value END) AS gender … GROUP BY id 最終結果 途中結果

Slide 153

Slide 153 text

Analytics Innovation Company ©BrainPad Inc. 152 5-3-3. 縦持ち→横持ち変換 user, receiptテーブルを用いて、都道府県(state_code)ごとの3〜5月の購入件数を表示 する。ただし、月についてはpaid_atから月部分のみ文字列を切りだす。 /* user, receiptテーブルを用いて、 都道府県(state_code)ごとの3〜5月の購入件数を表示する。 */ 類題 実行結果 SQL サブクエリの実行結果 receipt id (PK) user_id (FK) paid_at user id (PK) state_code ※本問題で用いる項目のみ掲載したER図

Slide 154

Slide 154 text

Analytics Innovation Company ©BrainPad Inc. 153 本資料の著作権は、第三者に帰属する著作権を除き、本資料を作成した株式会社ブレインパッドに帰属します。当社の許可なく無断で、複製、 改変・翻訳、販売等をすることはできません。 ただし、本資料の閲覧者は、株式会社ブレインパッドの著作物である旨を表示し、かつ、非営利目 的および本資料を改変しない場合に限り、本資料をダウンロード、プリントアウト、またはコピーし、自己のために閲覧・利用することができます。な お、著作権法上認められている範囲内での引用を行うことは可能です。本資料を引用するには、以下の条件を満たす必要がありますので、ご留 意ください。 1. 引用先と引用部分に主従関係があること。 2. 引用部分と本文が明確に区別できること。 3. 引用する必然性があり、その範囲 についても必然性・合理性があること。 4. 出所を明示すること。 5. 部分的な改変などをせず、原文のまま引用すること。 株式会社ブレインパッド 〒108-0071 東京都港区白金台3-2-10 白金台ビル TEL:03-6721-7002 FAX:03-6721-7010 www.brainpad.co.jp [email protected] Analytics Innovation Company