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

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

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

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

Y. Yamamoto

April 18, 2022
Tweet

More Decks by Y. Yamamoto

Other Decks in Education

Transcript

  1. ⼭本 祐輔
    静岡⼤学 情報学部
    [email protected]
    2022年度前期 データアナリティクスII – 山本担当モジュール2
    2022年4月18日
    ⼭本祐輔
    クリエイティブコモンズライセンス (CC BY-NC-SA 4.0)
    データ選択・集約のための
    基礎的なSQL操作

    View full-size slide

  2. 前回のおさらい:シナリオ
    あなたは新⽶データサイエンティスト.
    初めての案件が、⼩売店「杏森堂」からやってきました.
    杏森堂のセールス担当者
    杏森堂では,顧客台帳をオリジナルのExcelを使っ
    て管理しています.おかげさまで,売り上げも安定し
    ています.Excelのデータも結構な量になってきた
    ので,データ分析できると,いろんな知見が分かると
    思うんです.試しに少し分析していただけますか?

    View full-size slide

  3. 前回のおさらい:課題
    授業サポートWebsiteに掲載されたファイルと
    Excelを用いて、以下の3つの表を作成せよ
    商品A … 商品Z
    2019年1月
    2019年2月

    2019年7月
    商品ごとの年⽉別売上情報
    A市 … H市
    2019年1月
    2019年2月

    2019年7月
    各市ごとの年⽉別売上情報
    顧客名
    山本祐輔

    静岡花子
    集計期間中に買い物を
    ⾏っていない顧客のリスト

    View full-size slide

  4. 前回のおさらい: 課題に⽤いるExcelファイル
    顧客台帳データ(2ファイル:kokyaku_daicho_X.csv)
    購買記録に関するデータ( 2ファイル: uriage_X.csv)

    View full-size slide

  5. 世に存在する様々なバッドデータ
    フォーマットが統一されていない…
    1つのセルに複数データが入っている…
    データが空…
    データに重複がある…
    データが構造化されていない…
    ファイルが文字化けしている…
    入力ルールに反している…

    View full-size slide

  6. 人間が読みやすいデータ
    計算機が処理しやすいデータ
    変換・加⼯

    View full-size slide

  7. データの前処理のパターン(1/2)
    ⽣データ
    絞り込みデータ
    結合データ
    変換データ
    条件にマッチするデータを検索
    複数のデータを統合
    データ内容の変換(加工,欠損値処理)
    集約データ
    ある基準でデータを集約し,新データを作成
    データセットの特徴の把握

    View full-size slide

  8. データの前処理のパターン(2/2)
    ⽣データ
    変換データ
    集約データ
    機械学習⽤データ
    機械学習が可能な形式に変換(例:ダミー変数化)
    学習/テストデータ
    データ分割
    教師なし学習/統計モデリング
    教師あり学習

    View full-size slide

  9. ⽣データの重要性(1/2)
    店舗 日時 金額
    A店 2019年1月 480万
    A店 … …
    A店 2022年3月 600万
    B店 2019年1月 400万
    B店 … …
    店舗別の売上
    商品 日時 金額
    商品い 2019年1月 480万
    商品ろ … …
    商品は 2022年3月 600万
    … … …
    商品別の売上
    集約済みのデータは再利用が難しい

    View full-size slide

  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万
    … … …
    商品別の売上
    データの統合と集約

    View full-size slide

  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
    購買履歴(⽣データ)
    生データであるビッグデータを
    うまくデータ管理・処理する仕組みが必要

    View full-size slide

  12. 関係データベース(RDB: Relational Database)
    l データ管理者が指定したデータの整合性を担保
    l ⼤規模データを効率よく検索・集計できる
    Wikipediaを扱う関係データベースのER図
    (画像出典:https://ja.wikipedia.org/)

    View full-size slide

  13. SQL
    関係データベース(RDB)に対する問い合わせ言語
    ー データの定義,作成,更新,削除,検索,集約に使う
    ー サイズが⼤きいデータセットに対して効率よく検索/抽出処理できる
    SELECT
    *
    FROM
    購買履歴
    WHERE
    店舗 = “A店” AND ⽇時 BETWEEN X AND Y
    ORDER BY
    顧客ID
    !"#$%&'()*+,-./0123456789:

    View full-size slide

  14. データサイエンスにおける主要⾔語の⽐較
    SQL Python R
    扱える
    データ量
    DBのサイズ
    (メモリにのらない
    データ量でもOK)
    メモリにのりきる
    量に限定
    メモリにのりきる
    量に限定
    処理速度 速い 普通 遅い
    分散処理 自動最適化 記述コスト大 記述コスト大
    計算機能
    ・基本的な統計処理
    ・集約演算に長ける
    あらゆる分野で充実
    (言語処理,深層学習
    はPython一択?)
    統計分野に強み
    データ
    可視化
    なし あり あり
    必要なデータをSQLで引っ張ってきて,Python/Rで変換・分析

    View full-size slide

  15. 今回のお題
    関係データベース上に格納された
    架空の小売店の購買データを題材に、
    SQLでデータの抽出・集約を行う
    15

    View full-size slide

  16. 様々な関係データベースのマネジメントシステム(RDBMS)
    商⽤
    オープンソース
    本番環境
    テスト環境/組込
    本演習で使うのはコレ

    View full-size slide

  17. 課題: 購買データ × SQL
    1

    View full-size slide

  18. シナリオ(1/2)
    あなたは新⽶データサイエンティスト.
    前回の案件でデータ分析の可能性を感じた「杏森堂」から
    新たな案件がやってきました.
    杏森堂のセールス担当者
    データ分析を試しにやっていただいて,データ分析
    の可能性を強く感じました.同時に,何も考えずに
    Excelでデータ管理をしていては、有意義なデータ
    分析ができないことも痛いほど分かりました…

    View full-size slide

  19. シナリオ(2/2)
    あなたは新⽶データサイエンティスト.
    前回の案件でデータ分析の可能性を感じた「杏森堂」から
    新たな案件がやってきました.
    杏森堂のセールス担当者
    あの後,弊社では関係データベースを導入し,
    購買データをきっちり管理し,生データを収集して
    います.その結果,Excelでは扱えないくらいの量の
    データが集まりつつあります.
    弊社の手に負えないので,データサイエンティストの
    力をお借りして,データの傾向を把握したいです!

    View full-size slide

  20. Day 2 (& Day 3) の課題
    授業サポートWebsiteで入手できるデータとSQL
    を用いて、顧客の購買頻度を分析し,以下の項目
    について分析結果を得なさい.
    1. 顧客ごとの購買頻度
    2. 購買頻度に対応する顧客の数
    3. 該当する購買頻度以下の顧客数の累積値

    View full-size slide

  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本ノック」

    View full-size slide

  22. 授業サポートWebsite
    https://data-analytics2022.hontolab.org/

    View full-size slide

  23. 2 Google Colaboratoryの使い方

    View full-size slide

  24. Google Colaboratoryとは?
    l ブラウザで実⾏できるPython(& R)の実⾏環境
    l Googleのインフラ上で対話的にPythonを実⾏できる

    View full-size slide

  25. セル:コードを書く箇所
    セル
    セル

    View full-size slide

  26. コードの実⾏
    セル左端の再⽣ボタンをクリックすると,
    そのセルのコードを実⾏できる

    View full-size slide

  27. 新規セルの作り⽅
    1. 画⾯左上の「+コード」をクリック
    2. 空のセルが追加される

    View full-size slide

  28. SQLの実⾏
    1. 読み込むデータベースを設定
    2. セルの冒頭に“%%sql”と書く.
    1⾏空けて、その下にSQL⽂を書いて実⾏

    View full-size slide

  29. 3 データ分析で用いるSQL

    View full-size slide

  30. 射影(1/2)
    SELECT * FROM 顧客リスト;
    顧客ID 姓 名 誕生年 誕生日 居住県
    1 青木 葵 1982 9月25日 A県
    2 伊藤 博 1966 7月7日 B県

    100 山本 五十八 2002 8月3日 Z県
    テーブル:顧客リスト
    指定したテーブルから抽出する属性(列)を指定し
    レコードを抽出
    アスタリスクはすべての属性

    View full-size slide

  31. 射影(2/2)
    SELECT 顧客ID, 居住県 FROM 顧客リスト;
    顧客ID 居住県
    1 A県
    2 B県

    100 Z県
    テーブル:顧客リスト
    指定したテーブルから抽出する属性(列)を指定し
    レコードを抽出

    View full-size slide

  32. 選択
    SELECT * FROM 顧客リスト
    WHERE 居住県 = “A県”;
    顧客ID 姓 名 誕生年 誕生日 居住県
    1 青木 葵 1982 9月25日 A県
    17 鈴木 次郎 1988 1月4日 A県

    50 浜松 マッコイ 1997 2月29日 A県
    テーブル:顧客リスト
    テーブルから条件にマッチするレコードを抽出

    View full-size slide

  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

    View full-size slide

  34. 整列(1/2)
    SELECT * FROM 顧客リスト
    ORDER BY 利⽤歴 DESC;
    顧客ID 姓 名 利用歴 購買額
    1 青木 葵 10 300万
    2 伊藤 博 10 400万

    100 山本 五十八 1 20万
    テーブル:顧客リスト
    指定した列情報を⽤いてレコードを並び替る
    DESCは降順.ASCだと昇順

    View full-size slide

  35. 整列(2/2)
    SELECT * FROM 顧客リスト
    ORDER BY 利⽤歴, 購買額 DESC;
    顧客ID 姓 名 利用歴 購買額
    2 伊藤 博 10 400万
    1 青木 葵 10 300万

    100 山本 五十八 1 20万
    テーブル:顧客リスト
    整列基準を複数設けることも可能
    利⽤歴が同⼀の場合、購買額で整列

    View full-size slide

  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

    View full-size slide

  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;

    View full-size slide

  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を使わない時でも使える

    View full-size slide

  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
    実⾏結果

    View full-size slide

  40. 副問い合わせ(2/2)
    SELECT
    *
    FROM
    貸出履歴
    WHERE
    ⼩計 >
    (SELECT
    AVG(⼩計)
    FROM
    売上履歴);
    WITH 平均表 AS (
    SELECT
    AVG(⼩計) AS 値
    FROM
    売上履歴
    ) SELECT
    *
    FROM
    貸出履歴
    WHERE
    ⼩計 > 平均表.値;

    l WITH句を使って可読性をあげることが可能
    l ⾼負荷を避けるため副問い合わせの使いすぎは避ける

    View full-size slide

  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
    売上テーブル

    View full-size slide

  42. ウィンドウ関数(分析関数)(1/2)
    l データ分析のための新しいSQL関数
    l 結果⾏の集約演算を⾏うことなく集計・分析を⾏う
    部門 売上合計
    A 1100
    B 1400
    C 1500
    全体合計
    4000
    SELECT
    SUM(売上合計) AS 全体合計
    FROM 売上テーブル
    売上テーブル
    単純な集約演算の場合
    集約してしまうので,
    各⾏の情報が失われる

    View full-size slide

  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 売上テーブル
    売上テーブル
    ウィンドウ関数を⽤いた場合

    View full-size slide

  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 前の行の値を利用できる

    View full-size slide

  45. ウィンドウ関数におけるORDER BY(1/2)
    SELECT
    customer_id,
    amount,
    RANK() OVER (ORDER BY amount DESC) AS ranking
    FROM
    receipt;
    receiptテーブル

    View full-size slide

  46. ウィンドウ関数におけるORDER BY(2/2)
    SELECT
    customer_id,
    amount,
    RANK() OVER (ORDER BY amount DESC) AS ranking
    FROM
    receipt;
    SQLの結果
    内部的にamountでソートした結果を
    保持しておいて、順位を求める

    View full-size slide

  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;

    View full-size slide

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

    View full-size slide