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

2022年度データアナリティクスII-第3回-20220425

 2022年度データアナリティクスII-第3回-20220425

データの結合やデータ変換のためのSQL操作

Y. Yamamoto

April 25, 2022
Tweet

More Decks by Y. Yamamoto

Other Decks in Education

Transcript

  1. ⼭本 祐輔 静岡⼤学 情報学部 [email protected] 2022年度前期 データアナリティクスII – 山本担当モジュール3 2022年4月25日

    ⼭本祐輔 クリエイティブコモンズライセンス (CC BY-NC-SA 4.0) データの結合やデータ変換のため のSQL操作
  2. 演習で使うデータベースの構造 レシート明細(receipt) 論理名 物理名 型 Key 売上年⽉⽇ sales_ymd 整数 PK

    売上エポック秒 sales_epoch ⽂字列 店舗コード store_cd ⽂字列 PK,FK レシート番号 receipt_no ⽂字列 PK レシートサブ番号 receipt_sub_no ⽂字列 PK 顧客ID customer_id ⽂字列 FK 商品コード product_cd ⽂字列 FK 売上数量 quantity 数値 売上⾦額 amount 数値 顧客(customer) 論理名 物理名 型 Key 顧客ID customer_id ⽂字列 PK 名前 customer_name 数値 性別コード gender_cd ⽂字列 性別 gender ⽂字列 ⽣年⽉⽇ birth_day ⽇付 年齢 age 数値 郵便番号 postal_cd ⽂字列 住所 address ⽂字列 会員申込店舗コード application_store_cd ⽂字列 FK 会員申込⽇ application_date ⽂字列 ステータスコード status_cd ⽂字列 ジオコード(geocode) 論理名 物理名 型 Key 郵便番号 postal_cd ⽂字列 都道府県 prefecture ⽂字列 市区町村 city ⽂字列 町域 town ⽂字列 通り street ⽂字列 字丁⽬ address ⽂字列 住所 full_address ⽂字列 経度 longitude 数値 緯度 latitude 数値 商品(product) 論理名 物理名 型 Key 商品コード product_cd ⽂字列 PK カテゴリ⼤区分コード category_major_cd ⽂字列 カテゴリ中区分コード category_medium_cd ⽂字列 カテゴリ⼩区分コード category_small_cd ⽂字列 FK 単価 unit_price 数値 原価 unit_cost 数値 カテゴリ(category) 論理名 物理名 型 Key カテゴリ⼤区分コード category_major_cd ⽂字列 カテゴリ⼤区分名 category_major_name ⽂字列 カテゴリ中区分コード category_medium_cd ⽂字列 カテゴリ中区分名 category_medium_name ⽂字列 カテゴリ⼩区分コード category_small_cd ⽂字列 PK カテゴリ⼩区分名 category_small_name ⽂字列 店舗(store) 論理名 物理名 型 Key 店舗コード store_cd ⽂字列 PK 店舗名 store_name ⽂字列 都道府県コード prefecture_cd ⽂字列 都道府県 prefecture ⽂字列 住所 address ⽂字列 住所カナ address_kana ⽂字列 電話番号 tel_no ⽂字列 経度 longitude 数値 緯度 latitude 数値 フロア⾯積 floor_area 数値 出典:データサイエンティスト協会「データサイエンス100本ノック」
  3. WINDOW( ) OVER( ) Window関数の形式 SUM(列名) MAX(列名) AVG(列名) ROW_NUMBER() LAG(列名)

    COUNT(*) … Window関数は必ずWINDOW句とOVER句がセット
  4. WINDOW関数の利⽤シーン カテゴリ 商品名 売上 A お茶1 8000 B ビール1 2200

    C お菓子1 500 A お茶2 3200 B ビール2 1800 C お菓子2 700 カテゴリ 売上 A 11200 B 4000 C 1200 カテゴリ毎の売上総額を使って売上割合を出したい… カテ 商品名 売上 売上割合 A お茶1 8000 71% B ビール1 2200 55% C お菓子1 500 42% … … …
  5. カテゴリ 商品名 売上 A お茶1 8000 B ビール1 2200 C

    お菓子1 500 A お茶2 3200 B ビール2 1800 C お菓子2 700 ウィンドウ関数における PARTITION BYの挙動 SELECT *, 売上 / SUM(売り上げ) OVER (PARTITION BY カテゴリ) AS 売上割合 FROM 売上情報 売上情報 カテ 商品名 売上 売上割合 A お茶1 8000 61% B ビール1 2200 55% C お菓子1 500 42% … … … ? ? ?
  6. カテゴリ 商品名 売上 A お茶1 8000 B ビール1 2200 C

    お菓子1 500 A お茶2 3200 B ビール2 1800 C お菓子2 700 ウィンドウ関数におけるPARTITION BYの挙動 SELECT *, 売上 / SUM(売り上げ) OVER (PARTITION BY カテゴリ) AS 売上割合 FROM 売上情報 売上情報 カテ 商品名 売上 売上割合 A お茶1 8000 61% B ビール1 2200 55% C お菓子1 500 42% … … … ?
  7. カテゴリ 商品名 売上 A お茶1 8000 B ビール1 2200 C

    お菓子1 500 A お茶2 3200 B ビール2 1800 C お菓子2 700 ウィンドウ関数における PARTITION BYの挙動 SELECT *, 売上 / SUM(売り上げ) OVER (PARTITION BY カテゴリ) AS 売上割合 FROM 売上情報 売上情報 カテ 商品名 売上 売上割合 A お茶1 8000 71% B ビール1 2200 55% C お菓子1 500 42% … … … ?
  8. カテゴリ 商品名 売上 A お茶1 8000 B ビール1 2200 C

    お菓子1 500 A お茶2 3200 B ビール2 1800 C お菓子2 700 ウィンドウ関数における PARTITION BYの挙動 SELECT *, 売上 / SUM(売り上げ) OVER (PARTITION BY カテゴリ) AS 売上割合 FROM 売上情報 売上情報 カテ 商品名 売上 売上割合 A お茶1 8000 71% B ビール1 2200 55% C お菓子1 500 42% … … … ?
  9. カテゴリ 商品名 売上 A お茶1 8000 B ビール1 2200 C

    お菓子1 500 A お茶2 3200 B ビール2 1800 C お菓子2 700 ウィンドウ関数における PARTITION BYの挙動 SELECT *, 売上 / SUM(売り上げ) OVER (PARTITION BY カテゴリ) AS 売上割合 FROM 売上情報 売上情報 カテ 商品名 売上 売上割合 A お茶1 8000 71% B ビール1 2200 55% C お菓子1 500 42% … … … 各⾏が属するPARTITIONに対してWindow句の関数を適⽤
  10. ウィンドウ関数におけるORDER BYの挙動 日付 顧客 売上 2月10日 織田 700 2月11日 豊臣

    620 2月13日 徳川 440 2月17日 上杉 180 2月26日 織田 150 3月2日 武田 270 SELECT *, SUM(売り上げ) OVER (ORDER BY ⽇付) AS 売上累積 FROM 顧客売上 顧客売上 日付 顧客 売上 累計 2月10日 織田 700 700 2月11日 豊臣 620 1320 2月13日 徳川 440 1760 2月17日 上杉 180 1940 2月26日 織田 150 2090 3月2日 武田 270 2360 ?
  11. ウィンドウ関数におけるORDER BYの挙動 日付 顧客 売上 2月10日 織田 700 2月11日 豊臣

    620 2月13日 徳川 440 2月17日 上杉 180 2月26日 織田 150 3月2日 武田 270 SELECT *, SUM(売り上げ) OVER (ORDER BY ⽇付) AS 売上累積 FROM 顧客売上 顧客売上 日付 顧客 売上 累計 2月10日 織田 700 700 2月11日 豊臣 620 1320 2月13日 徳川 440 1760 2月17日 上杉 180 1940 2月26日 織田 150 2090 3月2日 武田 270 2360 ?
  12. ウィンドウ関数におけるORDER BYの挙動 日付 顧客 売上 2月10日 織田 700 2月11日 豊臣

    620 2月13日 徳川 440 2月17日 上杉 180 2月26日 織田 150 3月2日 武田 270 SELECT *, SUM(売り上げ) OVER (ORDER BY ⽇付) AS 売上累積 FROM 顧客売上 顧客売上 日付 顧客 売上 累計 2月10日 織田 700 700 2月11日 豊臣 620 1320 2月13日 徳川 440 1760 2月17日 上杉 180 1940 2月26日 織田 150 2090 3月2日 武田 270 2360 ?
  13. ウィンドウ関数におけるORDER BYの挙動 日付 顧客 売上 2月10日 織田 700 2月11日 豊臣

    620 2月13日 徳川 440 2月17日 上杉 180 2月26日 織田 150 3月2日 武田 270 SELECT *, SUM(売り上げ) OVER (ORDER BY ⽇付) AS 売上累積 FROM 顧客売上 顧客売上 日付 顧客 売上 累計 2月10日 織田 700 700 2月11日 豊臣 620 1320 2月13日 徳川 440 1760 2月17日 上杉 180 1940 2月26日 織田 150 2090 3月2日 武田 270 2360 ? ORDER BY後)その⾏が出てくるまでの 全⾏に対してWindow句の関数を適⽤
  14. ウィンドウ関数におけるORDER BYの挙動 日付 顧客 売上 2月10日 織田 700 2月11日 豊臣

    620 2月13日 徳川 440 2月17日 上杉 180 2月26日 織田 150 3月2日 武田 270 SELECT *, SUM(売り上げ) OVER (ORDER BY ⽇付) AS 売上累積 FROM 顧客売上 顧客売上 日付 顧客 売上 累計 2月10日 織田 700 700 2月11日 豊臣 620 1320 2月13日 徳川 440 1760 2月17日 上杉 180 1940 2月26日 織田 150 2090 3月2日 武田 270 2360
  15. 表の結合 日時 顧客ID 商品ID 個数 2019/1/1 い A 2 2019/1/1

    い B 3 … … … 2022/3/3 に C 2 購買履歴 商品ID 商品名 単価 A 伊左衛門 130 B 午前の紅茶 150 C おいしい牛乳 250 … … 商品マスタ l 関係データベースで頻繁に⾏う操作 l 冗⻑性を排した表データから分析に使うデータを揃える 日時 顧客ID 商品ID 個数 商品名 単価 2019/1/1 い A 2 伊左衛門 130 2019/1/1 い B 3 午前の紅茶 150 … … …
  16. 内部結合(INNER JOIN)(1/2) 日時 顧客ID 商品ID 個数 2019/1/1 い A 2

    2019/1/1 い B 3 2022/3/7 に D 8 購買履歴 商品ID 商品名 単価 A 伊左衛門 130 B 午前の紅茶 150 C おいしい牛乳 250 商品マスタ 日時 顧客ID 商品ID 個数 商品名 単価 2019/1/1 い A 2 伊左衛門 130 2019/1/1 い B 3 午前の紅茶 150 l 指定した列に共通する値が存在する時,2つの表の⾏を結合 l 共通する値が存在しない時,⾏は無視される
  17. 内部結合(INNER JOIN)(1/2) 日時 顧客ID 商品ID 個数 2019/1/1 い A 2

    2019/1/1 い B 3 2022/3/7 に D 8 購買履歴 商品ID 商品名 単価 A 伊左衛門 130 B 午前の紅茶 150 C おいしい牛乳 250 商品マスタ 日時 顧客ID 商品ID 個数 商品名 単価 2019/1/1 い A 2 伊左衛門 130 2019/1/1 い B 3 午前の紅茶 150 l 指定した列に共通する値が存在する時,2つの表の⾏を結合 l 共通する値が存在しない時,⾏は無視される 商品マスタに商品Dに関するデータがないので無視される
  18. 日時 顧客ID 商品ID 個数 商品名 単価 2019/1/1 い A 2

    伊左衛門 130 2019/1/1 い B 3 午前の紅茶 150 内部結合(INNER JOIN)(2/2) 日時 顧客ID 商品ID 個数 2019/1/1 い A 2 2019/1/1 い B 3 2022/3/7 に D 8 購買履歴 商品ID 商品名 単価 A 伊左衛門 130 B 午前の紅茶 150 C おいしい牛乳 250 商品マスタ SELECT 購買履歴.*, 商品マスタ.* FROM 購買履歴 JOIN 商品マスタ ON 購買履歴.商品ID = 商品マスタ.商品ID
  19. 左外部結合(LEFT INNER JOIN )(1/2) 日時 顧客ID 商品ID 個数 2019/1/1 い

    A 2 2019/1/1 い B 3 2022/3/7 に D 8 購買履歴 商品ID 商品名 単価 A 伊左衛門 130 B 午前の紅茶 150 C おいしい牛乳 250 商品マスタ 日時 顧客ID 商品ID 個数 商品名 単価 2019/1/1 い A 2 伊左衛門 130 2019/1/1 い B 3 午前の紅茶 150 2022/3/7 に D 8 l 指定した列に共通する値が存在する時,2つの表の⾏を結合 l 共通する値が存在しない時,結合する側の表の⾏は空で結合 (結合される側(左側)の表の情報のみ残す)
  20. 左外部結合(LEFT INNER JOIN )(2/2) 日時 顧客ID 商品ID 個数 2019/1/1 い

    A 2 2019/1/1 い B 3 2022/3/7 に D 8 購買履歴 商品ID 商品名 単価 A 伊左衛門 130 B 午前の紅茶 150 C おいしい牛乳 250 商品マスタ 日時 顧客ID 商品ID 個数 商品名 単価 2019/1/1 い A 2 伊左衛門 130 2019/1/1 い B 3 午前の紅茶 150 2022/3/7 に D 8 NULL NULL SELECT 購買履歴.*, 商品マスタ.* FROM 購買履歴 LEFT OUTER JOIN 商品マスタ ON 購買履歴.商品ID = 商品マスタ.商品ID
  21. 直積 (2/2) 食材ID 食材名 1 ご飯 2 パン 3 うどん

    ⾷材 調味料ID 調味料名 A カレー B 卵 調味料 食材ID 食材名 調味料ID 調味料名 1 ごはん 1 カレー 1 ごはん 2 卵 2 パン 1 カレー 2 パン 2 卵 3 うどん 1 カレー 3 うどん 2 卵 SELECT * FROM ⾷材, 調味料
  22. NULL値: 空データの扱い l 「値が空である」 = NULL値 l テーブルの定義次第でNULL値を許すことが可能 SELECT *

    FROM 商品マスタ WHERE 単価 IS NOT NULL NULL値の判定 “単価 = NULL”ではダメ. NULL値は⽐較演算の対象外
  23. CASE式 SELECT⽂の中で条件分岐が可能 SELECT 商品名, 単価, CASE 単価 WHEN 単価 >=

    1000 THEN ⾼い ELSE 安い END AS 判定 FROM 商品マスタ 商品ID 商品名 単価 A 伊左衛門 130 B 午前の紅茶 150 C おいしい牛乳 250 D 余市ウイスキー 3500 商品マスタ 商品名 単価 判定 伊左衛門 130 安い 午前の紅茶 150 安い おいしい牛乳 250 安い 余市ウイスキー 3500 高い