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

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

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

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

059fb717431a8cd2b509ffebc57d905a?s=128

Y. Yamamoto

April 18, 2022
Tweet

More Decks by Y. Yamamoto

Other Decks in Education

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ごとにまとめた情報を 保持しておいて、各⾏の演算を利⽤する