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

2022年前期に静岡大学情報学部で開講される講義「データアナリティクスII」（山本担当パート）の講義資料．

Transcript

1. ⼭本 祐輔 静岡⼤学 情報学部 yusuke_yamamoto@acm.org 2022年度前期 データアナリティクスII – 山本担当モジュール2 2022年4月18日 ⼭本祐輔 クリエイティブコモンズライセンス (CC BY-NC-SA 4.0) データ選択・集約のための 基礎的なSQL操作
2. 前回のおさらい：シナリオ あなたは新⽶データサイエンティスト． 初めての案件が、⼩売店「杏森堂」からやってきました． 杏森堂のセールス担当者 杏森堂では，顧客台帳をオリジナルのExcelを使っ て管理しています．おかげさまで，売り上げも安定し ています．Excelのデータも結構な量になってきた ので，データ分析できると，いろんな知見が分かると 思うんです．試しに少し分析していただけますか？
3. 前回のおさらい：課題 授業サポートWebsiteに掲載されたファイルと Excelを用いて、以下の3つの表を作成せよ 商品A … 商品Z 2019年1月 2019年2月 … 2019年7月 商品ごとの年⽉別売上情報 A市 … H市 2019年1月 2019年2月 … 2019年7月 各市ごとの年⽉別売上情報 顧客名 山本祐輔 … 静岡花子 集計期間中に買い物を ⾏っていない顧客のリスト
4. 前回のおさらい： 課題に⽤いるExcelファイル 顧客台帳データ（2ファイル：kokyaku_daicho_X.csv） 購買記録に関するデータ（ 2ファイル： uriage_X.csv）
5. 世に存在する様々なバッドデータ フォーマットが統一されていない… 1つのセルに複数データが入っている… データが空… データに重複がある… データが構造化されていない… ファイルが文字化けしている… 入力ルールに反している…
6. 人間が読みやすいデータ 計算機が処理しやすいデータ 変換・加⼯
7. データの前処理のパターン（1/2） ⽣データ 絞り込みデータ 結合データ 変換データ 条件にマッチするデータを検索 複数のデータを統合 データ内容の変換（加工，欠損値処理） 集約データ ある基準でデータを集約し，新データを作成 データセットの特徴の把握
8. データの前処理のパターン（2/2） ⽣データ 変換データ 集約データ 機械学習⽤データ 機械学習が可能な形式に変換（例：ダミー変数化） 学習/テストデータ データ分割 教師なし学習/統計モデリング 教師あり学習
9. ⽣データの重要性（1/2） 店舗 日時 金額 A店 2019年1月 480万 A店 … … A店 2022年3月 600万 B店 2019年1月 400万 B店 … … 店舗別の売上 商品 日時 金額 商品い 2019年1月 480万 商品ろ … … 商品は 2022年3月 600万 … … … 商品別の売上 集約済みのデータは再利用が難しい
10. ⽣データの重要性（2/2） 日時 顧客ID 店舗 商品 個数 小計 2019-12-11 C1 A店 商品い 1 270 2019-12-11 C1 A店 商品は 3 450 … 2022-4-18 C100 Z店 商品ろ 2 300 購買履歴（⽣データ） 店舗 日時 金額 A店 2019年1月 480万 A店 … … A店 2022年3月 600万 B店 … … 店舗別の売上 商品 日時 金額 商品い 2019年1月 480万 商品ろ … … 商品は 2022年3月 600万 … … … 商品別の売上 データの統合と集約
11. ⽣データの重要性（2/2） 店舗 日時 金額 A店 2019年1月 480万 A店 … … A店 2022年3月 600万 B店 … … 店舗別の売上 商品 日時 金額 商品い 2019年1月 480万 商品ろ … … 商品は 2022年3月 600万 … … … 商品別の売上 データの統合と集約 日時 顧客ID 店舗 商品 個数 小計 2019-12-11 C1 A店 商品い 1 270 2019-12-11 C1 A店 商品は 3 450 … 2022-4-18 C100 Z店 商品ろ 2 300 購買履歴（⽣データ） 生データであるビッグデータを うまくデータ管理・処理する仕組みが必要
12. 関係データベース（RDB: Relational Database） l データ管理者が指定したデータの整合性を担保 l ⼤規模データを効率よく検索・集計できる Wikipediaを扱う関係データベースのER図 （画像出典：https://ja.wikipedia.org/）
13. SQL 関係データベース（RDB）に対する問い合わせ言語 ｰ データの定義，作成，更新，削除，検索，集約に使う ｰ サイズが⼤きいデータセットに対して効率よく検索/抽出処理できる SELECT * FROM 購買履歴 WHERE 店舗 = “A店” AND ⽇時 BETWEEN X AND Y ORDER BY 顧客ID !"#$%&'()*+,-./0123456789:
14. データサイエンスにおける主要⾔語の⽐較 SQL Python R 扱える データ量 DBのサイズ （メモリにのらない データ量でもOK） メモリにのりきる 量に限定 メモリにのりきる 量に限定 処理速度 速い 普通 遅い 分散処理 自動最適化 記述コスト大 記述コスト大 計算機能 ・基本的な統計処理 ・集約演算に長ける あらゆる分野で充実 （言語処理，深層学習 はPython一択?） 統計分野に強み データ 可視化 なし あり あり 必要なデータをSQLで引っ張ってきて，Python/Rで変換・分析
15. 今回のお題 関係データベース上に格納された 架空の小売店の購買データを題材に、 SQLでデータの抽出・集約を行う 15
16. 様々な関係データベースのマネジメントシステム（RDBMS） 商⽤ オープンソース 本番環境 テスト環境/組込 本演習で使うのはコレ
17. 課題: 購買データ × SQL 1
18. シナリオ（1/2） あなたは新⽶データサイエンティスト． 前回の案件でデータ分析の可能性を感じた「杏森堂」から 新たな案件がやってきました． 杏森堂のセールス担当者 データ分析を試しにやっていただいて，データ分析 の可能性を強く感じました．同時に，何も考えずに Excelでデータ管理をしていては、有意義なデータ 分析ができないことも痛いほど分かりました…
19. シナリオ（2/2） あなたは新⽶データサイエンティスト． 前回の案件でデータ分析の可能性を感じた「杏森堂」から 新たな案件がやってきました． 杏森堂のセールス担当者 あの後，弊社では関係データベースを導入し， 購買データをきっちり管理し，生データを収集して います．その結果，Excelでは扱えないくらいの量の データが集まりつつあります． 弊社の手に負えないので，データサイエンティストの 力をお借りして，データの傾向を把握したいです！
20. Day 2 (& Day 3) の課題 授業サポートWebsiteで入手できるデータとSQL を用いて、顧客の購買頻度を分析し，以下の項目 について分析結果を得なさい． 1. 顧客ごとの購買頻度 2. 購買頻度に対応する顧客の数 3. 該当する購買頻度以下の顧客数の累積値
21. データベースの構造 レシート明細（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本ノック」
22. 授業サポートWebsite https://data-analytics2022.hontolab.org/
23. 2 Google Colaboratoryの使い方
24. Google Colaboratoryとは？ l ブラウザで実⾏できるPython(& R)の実⾏環境 l Googleのインフラ上で対話的にPythonを実⾏できる
25. セル：コードを書く箇所 セル セル
26. コードの実⾏ セル左端の再⽣ボタンをクリックすると， そのセルのコードを実⾏できる
27. 新規セルの作り⽅ 1. 画⾯左上の「+コード」をクリック 2. 空のセルが追加される
28. SQLの実⾏ 1. 読み込むデータベースを設定 2. セルの冒頭に“%%sql”と書く． 1⾏空けて、その下にSQL⽂を書いて実⾏
29. 3 データ分析で用いるSQL
30. 射影（1/2） SELECT * FROM 顧客リスト; 顧客ID 姓 名 誕生年 誕生日 居住県 1 青木 葵 1982 9月25日 A県 2 伊藤 博 1966 7月7日 B県 … 100 山本 五十八 2002 8月3日 Z県 テーブル：顧客リスト 指定したテーブルから抽出する属性（列）を指定し レコードを抽出 アスタリスクはすべての属性
31. 射影（2/2） SELECT 顧客ID, 居住県 FROM 顧客リスト; 顧客ID 居住県 1 A県 2 B県 … 100 Z県 テーブル：顧客リスト 指定したテーブルから抽出する属性（列）を指定し レコードを抽出
32. 選択 SELECT * FROM 顧客リスト WHERE 居住県 = “A県”; 顧客ID 姓 名 誕生年 誕生日 居住県 1 青木 葵 1982 9月25日 A県 17 鈴木 次郎 1988 1月4日 A県 … 50 浜松 マッコイ 1997 2月29日 A県 テーブル：顧客リスト テーブルから条件にマッチするレコードを抽出
33. WHERE句の中で使える演算⼦の例 演算子 意味 A = B AはBである A != B AはBでない A > B AはBより大きい A >= B AはB以上 X BETWEEN A AND B XはA以上B以下 A IN (B, C, D) AはB,C，Dのいずれか A LIKE ‘xxx%’ Aはxxxから始まる文字列 A AND B AかつB A OR B AまたはB 参考：https://www.sejuku.net/blog/51583
34. 整列（1/2） SELECT * FROM 顧客リスト ORDER BY 利⽤歴 DESC; 顧客ID 姓 名 利用歴 購買額 1 青木 葵 10 300万 2 伊藤 博 10 400万 … 100 山本 五十八 1 20万 テーブル：顧客リスト 指定した列情報を⽤いてレコードを並び替る DESCは降順．ASCだと昇順
35. 整列（2/2） SELECT * FROM 顧客リスト ORDER BY 利⽤歴, 購買額 DESC; 顧客ID 姓 名 利用歴 購買額 2 伊藤 博 10 400万 1 青木 葵 10 300万 … 100 山本 五十八 1 20万 テーブル：顧客リスト 整列基準を複数設けることも可能 利⽤歴が同⼀の場合、購買額で整列
36. 集約（1/2） SELECT 書籍, COUNT(*) FROM 貸出履歴 GROUP BY 書籍; 貸出日 顧客ID 書籍 2019-01-28 001 走れメロス 2019-02-07 003 走れメロス 2019-02-28 016 走れメロス 2019-03-11 021 雪国 2019-04-01 001 雪国 テーブル：貸出履歴 指定した列の情報が⼀致するレコードをまとめ， 情報を集約する COUNTはまとめたレコードの総数を返す 書籍 COUNT(*) 走れメロス 3 雪国 2
37. COUNT以外の代表的な集計関数（例） 売上日 商品ID 小計 2019-01-28 A 500 2019-02-07 A 1000 2019-02-28 B 700 2019-03-11 B 600 2019-04-01 C 400 テーブル：売上履歴 商品ID MAX(小計) A 1000 B 700 C 400 商品ID SUM(小計) A 1500 B 1300 C 400 商品ID AVG(小計) A 750 B 650 C 400 最⼤値 合計 平均 SELECT 商品ID, f(⼩計) FROM 売上履歴 GROUP BY 商品ID;
38. 集約（2/2） 売上日 商品ID 小計 2019-01-28 A 500 2019-02-07 A 1000 2019-02-28 B 700 2019-03-11 B 600 2019-04-01 C 400 テーブル：売上履歴 SELECT AVG(⼩計) AS 平均⼩計 FROM 売上履歴; 平均小計 640 集計関数はGROUP BYを使わない時でも使える
39. 副問い合わせ（1/2） 売上日 商品ID 小計 2019-01-28 A 500 2019-02-07 A 1000 2019-02-28 B 700 2019-03-11 B 600 2019-04-01 C 400 テーブル：売上履歴 SELECT * FROM 貸出履歴 WHERE ⼩計 > (SELECT AVG(⼩計) FROM 売上履歴); 別のSQL⽂の結果を⽤いて問い合わせを⾏う 売上日 商品ID 小計 2019-02-07 A 1000 2019-02-28 B 700 SQL 実⾏結果
40. 副問い合わせ（2/2） SELECT * FROM 貸出履歴 WHERE ⼩計 > (SELECT AVG(⼩計) FROM 売上履歴); WITH 平均表 AS ( SELECT AVG(⼩計) AS 値 FROM 売上履歴 ) SELECT * FROM 貸出履歴 WHERE ⼩計 > 平均表.値; ＝ l WITH句を使って可読性をあげることが可能 l ⾼負荷を避けるため副問い合わせの使いすぎは避ける
41. ウィンドウ関数（分析関数）(1/2) l データ分析のための新しいSQL関数 l 結果⾏の集約演算を⾏うことなく集計・分析を⾏う 部門 売上合計 A 1100 B 1400 C 1500 部門 売上合計 全体合計 売上割合 A 1100 4000 0.275 B 1400 4000 0.350 C 1500 4000 0.375 売上テーブル
42. ウィンドウ関数（分析関数）(1/2) l データ分析のための新しいSQL関数 l 結果⾏の集約演算を⾏うことなく集計・分析を⾏う 部門 売上合計 A 1100 B 1400 C 1500 全体合計 4000 SELECT SUM(売上合計) AS 全体合計 FROM 売上テーブル 売上テーブル 単純な集約演算の場合 集約してしまうので， 各⾏の情報が失われる
43. ウィンドウ関数（分析関数）(2/2) l データ分析のための新しいSQL関数 l 結果⾏の集約演算を⾏うことなく集計・分析を⾏う 部門 売上合計 A 1100 B 1400 C 1500 部門 売上合計 全体合計 売上割合 A 1100 4000 0.275 B 1400 4000 0.350 C 1500 4000 0.375 SELECT *, SUM(売上合計) OVER() AS 全体合計, 売上合計 / SUM(売上合計) OVER() AS 売上割合 FROM 売上テーブル 売上テーブル ウィンドウ関数を⽤いた場合
44. ウィンドウ関数のポイント 参考1: https://qiita.com/w-sato-ist/items/63600a3ab84aad38e879 参考2: https://qiita.com/HiromuMasuda0228/items/0b20d461f1a80bd30cfc lPARTITION BY句とORDER BY句の動作理解 l⽬的に応じたウィンドウ関数の使い分け 関数例 意味 AVG 全体の平均、グループごとの平均、累積平均 COUNT 全体の数、グループごとの数、累積数 SUM 全体の合計、グループごとの合計、累積合計 ROW_NUMBER ソートして順位付けする FIRST_VALUE 最初の行の値を利用できる LAG 前の行の値を利用できる
45. ウィンドウ関数におけるORDER BY（1/2） SELECT customer_id, amount, RANK() OVER (ORDER BY amount DESC) AS ranking FROM receipt; receiptテーブル
46. ウィンドウ関数におけるORDER BY（2/2） SELECT customer_id, amount, RANK() OVER (ORDER BY amount DESC) AS ranking FROM receipt; SQLの結果 内部的にamountでソートした結果を 保持しておいて、順位を求める
47. ウィンドウ関数におけるPARTITION BY（1/2） receiptテーブル SELECT DISTINCT sales_ymd, SUM(amount) OVER (PARTITION BY sales_ymd) AS amount, SUM(amount) OVER () AS total_amount FROM receipt;
48. ウィンドウ関数におけるPARTITION BY（2/2） SQLの結果 SELECT DISTINCT sales_ymd, SUM(amount) OVER (PARTITION BY sales_ymd) AS amount, SUM(amount) OVER () AS total_amount FROM receipt; 内部的にsales_ymdごとにまとめた情報を 保持しておいて、各⾏の演算を利⽤する