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操作

    View full-size slide

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

    View full-size slide

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

    View full-size slide

  4. WINDOW関数に関する補足
    0

    View full-size slide

  5. Window関数の形式
    SUM(列名)
    MAX(列名)
    AVG(列名)
    ROW_NUMBER()
    LAG(列名)
    COUNT(*)

    Window関数は必ずWINDOW句とOVER句がセット
    WINDOW( ) OVER( )
    OVER(PARTION BY 列名)
    OVER(ORDR BY 列名)

    View full-size slide

  6. WINDOW( ) OVER( )
    Window関数の形式
    Window関数は必ずWINDOW句とOVER句がセット
    OVER(PARTITION BY 列名)
    OVER(ORDR BY 列名)

    View full-size slide

  7. WINDOW( ) OVER( )
    Window関数の形式
    SUM(列名)
    MAX(列名)
    AVG(列名)
    ROW_NUMBER()
    LAG(列名)
    COUNT(*)

    Window関数は必ずWINDOW句とOVER句がセット

    View full-size slide

  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%
    … … …

    View full-size slide

  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%
    … … …
    ?
    ?
    ?

    View full-size slide

  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%
    … … …
    ?

    View full-size slide

  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%
    … … …
    ?

    View full-size slide

  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%
    … … …
    ?

    View full-size slide

  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句の関数を適⽤

    View full-size slide

  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
    ?

    View full-size slide

  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
    ?

    View full-size slide

  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
    ?

    View full-size slide

  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句の関数を適⽤

    View full-size slide

  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

    View full-size slide

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

    View full-size slide

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

    View full-size slide

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

    View full-size slide

  22. Day 3 の課題(選択課題3-1)
    2017年から2019年までの期間の売上を把握する
    ために,1ヶ月ごとに以下の情報を集約表示せよ:
    • 年月
    • 購買回数
    • 購買1回あたりの平均購入額
    • 月間売上高
    • 当該月の前年売上高
    • 売上の対前年比

    View full-size slide

  23. ABC分析
    ABC分析は販売戦略を考えるために,
    売上によって商品をランク付けする手法である.
    一般に,ABC分析では売上総額の
    • 上位0〜70%の商品をAランク
    • 上位70〜90%の商品をBランク
    • 上位90〜100%の商品をCランク
    とするランク付けを行う.

    View full-size slide

  24. Day 3 の課題(選択課題3-2)
    商品カテゴリ「菓子に属する商品について,
    小区分ごとに売上を集計し,菓子カテゴリの
    売上総額に占める割合(構成比)を計算せよ.
    売上がN位の小区分の行には売上額上位
    N位までの構成比累計,および構成比累計に
    基づくABC分析のランク付け結果も表示せよ.
    課題3-2-2
    課題3-2-1

    View full-size slide

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

    View full-size slide

  26. 2 データ分析で用いるSQL

    View full-size slide

  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
    … … …

    View full-size slide

  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 共通する値が存在しない時,⾏は無視される

    View full-size slide

  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に関するデータがないので無視される

    View full-size slide

  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

    View full-size slide

  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 共通する値が存在しない時,結合する側の表の⾏は空で結合
    (結合される側(左側)の表の情報のみ残す)

    View full-size slide

  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

    View full-size slide

  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

    View full-size slide

  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 ⾷材, 調味料

    View full-size slide

  35. NULL値: 空データの扱い
    l 「値が空である」 = NULL値
    l テーブルの定義次第でNULL値を許すことが可能
    SELECT * FROM 商品マスタ
    WHERE 単価 IS NOT NULL
    NULL値の判定
    “単価 = NULL”ではダメ.
    NULL値は⽐較演算の対象外

    View full-size slide

  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 高い

    View full-size slide