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

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

Transcript

1. ⼭本 祐輔 静岡⼤学 情報学部 yusuke_yamamoto@acm.org 2022年度前期 データアナリティクスII – 山本担当モジュール3 2022年4月25日 ⼭本祐輔 クリエイティブコモンズライセンス (CC BY-NC-SA 4.0) データの結合やデータ変換のため のSQL操作
2. 前回のおさらい：シナリオ あなたは新⽶データサイエンティスト． 前回の案件でデータ分析の可能性を感じた「杏森堂」から 新たな案件がやってきました． 杏森堂のセールス担当者 あの後，弊社では関係データベースを導入し， 購買データをきっちり管理し，生データを収集して います．その結果，Excelでは扱えないくらいの量の データが集まりつつあります． 弊社の手に負えないので，データサイエンティストの 力をお借りして，データの傾向を把握したいです！
3. 演習で使うデータベースの構造 レシート明細（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本ノック」
4. WINDOW関数に関する補足 0
5. Window関数の形式 SUM(列名) MAX(列名) AVG(列名) ROW_NUMBER() LAG(列名) COUNT(*) … Window関数は必ずWINDOW句とOVER句がセット WINDOW( ) OVER( ) OVER(PARTION BY 列名) OVER(ORDR BY 列名)
6. WINDOW( ) OVER( ) Window関数の形式 Window関数は必ずWINDOW句とOVER句がセット OVER(PARTITION BY 列名) OVER(ORDR BY 列名)
7. WINDOW( ) OVER( ) Window関数の形式 SUM(列名) MAX(列名) AVG(列名) ROW_NUMBER() LAG(列名) COUNT(*) … Window関数は必ずWINDOW句とOVER句がセット
8. 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% … … …
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 61% B ビール1 2200 55% C お菓子1 500 42% … … … ? ? ?
10. カテゴリ 商品名 売上 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% … … … ?
11. カテゴリ 商品名 売上 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% … … … ?
12. カテゴリ 商品名 売上 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% … … … ?
13. カテゴリ 商品名 売上 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句の関数を適⽤
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. ウィンドウ関数における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 ?
16. ウィンドウ関数における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 ?
17. ウィンドウ関数における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句の関数を適⽤
18. ウィンドウ関数における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
19. 課題: 購買データ × SQL 1
20. 今回のお題 関係データベース上に格納された 架空の小売店の購買データを題材に、 SQLでデータの結合・変換・集約を行う 20
21. Day 2 の課題 授業サポートWebsiteで入手できるデータとSQL を用いて、顧客の購買頻度を分析し，以下の項目 について分析結果を得なさい． 1. 顧客ごとの購買頻度 2. 購買頻度に対応する顧客の数 3. 該当する購買頻度以下の顧客数の累積値
22. Day 3 の課題（選択課題3-1） 2017年から2019年までの期間の売上を把握する ために，1ヶ月ごとに以下の情報を集約表示せよ： • 年月 • 購買回数 • 購買1回あたりの平均購入額 • 月間売上高 • 当該月の前年売上高 • 売上の対前年比
23. ABC分析 ABC分析は販売戦略を考えるために， 売上によって商品をランク付けする手法である． 一般に，ABC分析では売上総額の • 上位0〜70％の商品をAランク • 上位70〜90％の商品をBランク • 上位90〜100%の商品をCランク とするランク付けを行う．
24. Day 3 の課題（選択課題3-2） 商品カテゴリ「菓子に属する商品について， 小区分ごとに売上を集計し，菓子カテゴリの 売上総額に占める割合（構成比）を計算せよ． 売上がN位の小区分の行には売上額上位 N位までの構成比累計，および構成比累計に 基づくABC分析のランク付け結果も表示せよ． 課題3-2-2 課題3-2-1
25. 授業サポートWebsite https://data-analytics2022.hontolab.org/
26. 2 データ分析で用いるSQL
27. 表の結合 日時 顧客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 … … …
28. 内部結合（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 共通する値が存在しない時，⾏は無視される
29. 内部結合（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に関するデータがないので無視される
30. 日時 顧客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
31. 左外部結合（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 共通する値が存在しない時，結合する側の表の⾏は空で結合 （結合される側（左側）の表の情報のみ残す）
32. 左外部結合（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
33. 直積（1/2） 複数の集合が与えられたとき，各集合から要素を 1つずつ取り出して作る組の集合 A a1 a2 a3 b1 b2 B (a1 , b1 ) (a1 , b2 ) (a2 , b1 ) (a2 , b2 ) (a3 , b1 ) (a3 , b2 ) A×B
34. 直積 （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 ⾷材, 調味料
35. NULL値: 空データの扱い l 「値が空である」 = NULL値 l テーブルの定義次第でNULL値を許すことが可能 SELECT * FROM 商品マスタ WHERE 単価 IS NOT NULL NULL値の判定 “単価 = NULL”ではダメ． NULL値は⽐較演算の対象外
36. CASE式 SELECT⽂の中で条件分岐が可能 SELECT 商品名, 単価, CASE 単価 WHEN 単価 >= 1000 THEN ⾼い ELSE 安い END AS 判定 FROM 商品マスタ 商品ID 商品名 単価 A 伊左衛門 130 B 午前の紅茶 150 C おいしい牛乳 250 D 余市ウイスキー 3500 商品マスタ 商品名 単価 判定 伊左衛門 130 安い 午前の紅茶 150 安い おいしい牛乳 250 安い 余市ウイスキー 3500 高い