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

第2回 SQL勉強会 (社外公開用)

第2回 SQL勉強会 (社外公開用)

社内向けに実施した第2回 SQL勉強会の内容を社外公開用に一部加工したものです。

Saiid Al-Halawi

December 06, 2021
Tweet

More Decks by Saiid Al-Halawi

Other Decks in Technology

Transcript

  1. ©2021 crassone
    第2回 SQL勉強会
    2021/11/25

    View Slide

  2. 1
    0. はじめに

    View Slide

  3. 2
    この会の趣旨
    エンジニア / 非エンジニアを問わず、データのさらなる利活
    用によって業務の幅を広げていくことを目的に知識ゼロから
    SQLを少しずつ学んでいこうというイベントです。
    たぶん全4回くらい実施できると一通りのことができるように
    なりそう。

    View Slide

  4. 3
    今日のゴール
    1. 表結合についてなんとなく理解できる
    2. 簡単な結合表現を利用して複数テーブルにまたがるク
    エリを書けるようになる
    3. 集計関数の基本的な使い方がわかる

    View Slide

  5. 4
    1. 前回のおさらい

    View Slide

  6. 5
    SQLとは ...
    データベースに対する問い合わせ言語
    そしてSELCT文は読取のための構文

    View Slide

  7. 6
    基本的なSELECT文
    SELECT
    ID, 名前, メールアドレス, 作成日時
    FROM
    ユーザー
    WHERE
    名前 LIKE ‘佐藤%’
    ORDER BY
    作成日時 DESC
    LIMIT 10

    View Slide

  8. 7
    基本的なSELECT文
    SELECT
    ID, 名前, メールアドレス, 作成日時
    FROM
    ユーザー
    WHERE
    名前 LIKE ‘佐藤%’
    ORDER BY
    作成日時 DESC
    LIMIT 10
    ユーザーの中から「佐藤」という名前で始まる
    人を最新10名分ください

    View Slide

  9. 8
    基本的なSELECT文
    SELECT
    ID, 名前, メールアドレス, 作成日時
    FROM
    ユーザー
    WHERE
    名前 LIKE ‘佐藤%’
    ORDER BY
    作成日時 DESC
    LIMIT 10
    ユーザーの中から
    (対象のテーブル)
    「佐藤」から始まる名前の人を
    (抽出条件)
    「作成日時」新しい順に
    (結果の取得順序の指定 )
    10件まで
    (結果の最大取得件数 )

    View Slide

  10. 9
    さて
    じゃあ、次に表結合の話をします

    View Slide

  11. 10
    2. 表... 結合...?

    View Slide

  12. 11
    とりあえず表結合とは ...
    表(テーブル)同士を結合する(つなぐ)こと

    View Slide

  13. 12
    例えばこんなデータがあったとします
    とあるECサイト
    ID 名前 メアド
    1 田中 [email protected]
    2 佐藤 [email protected]
    3 高橋 [email protected]
    ユーザー
    ID 名前 価格
    1 シャツ 3,500
    2 ズボン 4,000
    3 くつ 8,500
    商品

    View Slide

  14. 13
    要求
    ???「誰に何が売れたのか教えてくれ」

    View Slide

  15. 14
    疑問
    先ほどの情報から取り出せそうですか?

    View Slide

  16. 15
    注文データの登場
    ECサイトなのでユーザーの注文もデータとして記録しなければならないのでした
    ID 名前 メアド
    1 田中 tanak...
    2 佐藤 [email protected]
    3 高橋 takaha...
    ユーザー
    ID 名前 価格
    1 シャツ 3,500
    2 ズボン 4,000
    3 くつ 8,500
    商品
    ユーザーID 商品ID 購入日
    1 2 2021-10-30
    1 2 2021-10-30
    3 1 2021-11-04
    注文 New!

    View Slide

  17. 16
    疑問
    こうじゃないの???
    ユーザー名 商品名 購入日
    田中 ズボン 2021-10-30
    田中 ズボン 2021-10-30
    高橋 シャツ 2021-11-04
    注文

    View Slide

  18. 17
    そうじゃないんです
    こうじゃないの???
    ユーザー名 商品名 購入日
    田中 ズボン 2021-10-30
    田中 ズボン 2021-10-30
    高橋 シャツ 2021-11-04
    注文
    同姓同名の人がいると異なるユーザーが混ざってしまったり、逆に注文時に別名を入
    力されたら別ユーザーとして扱われてしまったり …
    「1人のユーザー」として追跡するためには、ひとりひとりのユーザーが独立した1レ
    コードとして存在する必要があるのです。

    View Slide

  19. 18
    テーブル同士の「関連付け」
    ECサイトなのでユーザーの注文も記録しなければならないのでした
    ID 名前 メアド
    1 田中 tanak...
    2 佐藤 [email protected]
    3 高橋 takaha...
    ユーザー
    ID 名前 価格
    1 シャツ 3,500
    2 ズボン 4,000
    3 くつ 8,500
    商品
    ユーザーID 商品ID 購入日
    1 2 2021-10-30
    1 2 2021-10-30
    3 1 2021-11-04
    注文
    注文とユーザーの関連付けを表す 注文と商品の関連付けを表す

    View Slide

  20. 19
    では注文データを取り出してみる
    SELECT
    ユーザーID, 商品ID, 購入日
    FROM
    注文
    ORDER BY
    購入日 DESC
    LIMIT 10

    View Slide

  21. 20
    注文データを取り出してみた
    SELECT
    ユーザーID, 商品ID, 購入日
    FROM
    購入
    ORDER BY
    購入日 DESC
    LIMIT 10
    ユーザーID 商品ID 購入日
    1 2 2021-11-20
    1 3 2021-11-11
    3 1 2021-11-10
    2 2 2021-11-10
    1 1 2021-11-08
    3 3 2021-11-06

    View Slide

  22. 21
    注文データを取り出してみた、結果
    SELECT
    ユーザーID, 商品ID, 購入日
    FROM
    購入
    ORDER BY
    購入日 DESC
    LIMIT 10
    ユーザーID 商品ID 購入日
    1 2 2021-11-20
    1 3 2021-11-11
    3 1 2021-11-10
    2 2 2021-11-10
    1 1 2021-11-08
    3 3 2021-11-06
    え、誰?
    え、何?

    View Slide

  23. 22
    3. そこで「結合」ですよ

    View Slide

  24. 23
    表結合とは … (再掲)
    表(テーブル)同士を結合する(つなぐ)こと

    View Slide

  25. 24
    今回やりたいこと
    ユーザー、商品、注文のすべてをつなげて一緒に見れればよさそう
    ID 名前 メアド
    1 田中 tanak...
    2 佐藤 [email protected]
    3 高橋 takaha...
    ユーザー
    ID 名前 価格
    1 シャツ 3,500
    2 ズボン 4,000
    3 くつ 8,500
    商品
    ユーザーID 商品ID 購入日
    1 2 2021-10-30
    1 2 2021-10-30
    3 1 2021-11-04
    注文

    View Slide

  26. 25
    (内部)結合の基礎
    SELECT
    [見たいフィールド]
    FROM
    [結合元のテーブル]
    INNER JOIN [結合先のテーブル]
    ON [結合先のキー] = [結合元のキー]

    View Slide

  27. 26
    今回のケースでいうと ...
    SELECT
    ユーザー.名前,
    商品.名前,
    注文.購入日
    FROM
    注文
    INNER JOIN ユーザー
    ON ユーザー.ID = 注文.ユーザーID
    INNER JOIN 商品
    ON 商品.ID = 注文.商品ID

    View Slide

  28. 27
    結合させてる箇所
    SELECT
    ユーザー.名前,
    商品.名前,
    注文.購入日
    FROM
    注文
    INNER JOIN ユーザー
    ON ユーザー.ID = 注文.ユーザーID
    INNER JOIN 商品
    ON 商品.ID = 注文.商品ID
    この部分が結合のための記述

    View Slide

  29. 28
    注文 - ユーザーの結合
    SELECT
    ユーザー.名前,
    商品.名前,
    注文.購入日
    FROM
    注文
    INNER JOIN ユーザー
    ON ユーザー.ID = 注文.ユーザーID
    INNER JOIN 商品
    ON 商品.ID = 注文.商品ID
    ユーザーテーブルの「 ID」
    注文テーブルの「ユーザー ID」
    の二つは、どちらもユーザーに割り当てられた固有の ID
    を表すフィールドで、同じものなのでこの二つを結び付
    けてユーザーと注文を結合しますよ

    View Slide

  30. 29
    注文 - 商品の結合
    SELECT
    ユーザー.名前,
    商品.名前,
    注文.購入日
    FROM
    注文
    INNER JOIN ユーザー
    ON ユーザー.ID = 注文.ユーザーID
    INNER JOIN 商品
    ON 商品.ID = 注文.商品ID
    商品テーブルの「ID」
    注文テーブルの「商品 ID」
    は、どちらも商品ごとに割り当てられた固有の IDを
    表すフィールドで、同じものなのでこの二つを結び
    付けて商品と注文を結合しますよ

    View Slide

  31. 30
    総じて ...
    SELECT
    ユーザー.名前,
    商品.名前,
    注文.購入日
    FROM
    注文
    INNER JOIN ユーザー
    ON ユーザー.ID = 注文.ユーザーID
    INNER JOIN 商品
    ON 商品.ID = 注文.商品ID
    注文テーブルを「起点」に
    ユーザーと商品を結び付けて
    結果を取得しますよ

    View Slide

  32. 31
    取得結果
    SELECT
    ユーザー.名前,
    商品.名前,
    注文.購入日
    FROM
    注文
    INNER JOIN ユーザー
    ON ユーザー.ID = 注文.ユーザーID
    INNER JOIN 商品
    ON 商品.ID = 注文.商品ID
    ユーザー.名前 商品.名前 注文.購入日
    田中 ズボン 2021-11-20
    田中 くつ 2021-11-11
    高橋 シャツ 2021-11-10
    佐藤 ズボン 2021-11-10
    田中 シャツ 2021-11-08
    高橋 くつ 2021-11-06

    View Slide

  33. 32
    結合の結果
    誰が何を買ったのかわかった!

    View Slide

  34. 33
    SELECTの中身について
    SELECT
    ユーザー.名前,
    商品.名前,
    注文.購入日
    FROM
    注文
    INNER JOIN ユーザー
    ON ユーザー.ID = 注文.ユーザーID
    INNER JOIN 商品
    ON 商品.ID = 注文.商品ID
    ユーザー.名前 商品.名前 注文.購入日
    田中 ズボン 2021-11-20
    田中 くつ 2021-11-11
    高橋 シャツ 2021-11-10
    佐藤 ズボン 2021-11-10
    田中 シャツ 2021-11-08
    高橋 くつ 2021-11-06
    この部分について見ていきます

    View Slide

  35. 34
    元のデータベース
    ID 名前 メアド
    1 田中 tanak...
    2 佐藤 [email protected]
    3 高橋 takaha...
    ユーザー
    ID 名前 価格
    1 シャツ 3,500
    2 ズボン 4,000
    3 くつ 8,500
    商品
    ユーザーID 商品ID 購入日
    1 2 2021-10-30
    1 2 2021-10-30
    3 1 2021-11-04
    注文

    View Slide

  36. 35
    注文.
    ユー
    ザーID
    注文.
    商品ID
    注文.
    購入日
    ユーザー
    .ID
    ユーザー.
    名前
    ユーザー.
    メアド
    商品.ID 商品.名前 商品.価格
    1 2 2021-10-30 1 田中 [email protected]
    ..
    2 ズボン 4,000
    1 2 2021-10-30 1 田中 [email protected]
    ..
    2 ズボン 4,000
    3 1 2021-11-04 2 高橋 takahashi
    @...
    1 シャツ 3,500
    JOINされるとこういう形になります
    注文を起点に結合された擬似テーブル

    View Slide

  37. 36
    注文.
    ユー
    ザーID
    注文.
    商品ID
    注文.
    購入日
    ユーザー
    .ID
    ユーザー.
    名前
    ユーザー.
    メアド
    商品.ID 商品.名前 商品.価格
    1 2 2021-10-30 1 田中 [email protected]
    ..
    2 ズボン 4,000
    1 2 2021-10-30 1 田中 [email protected]
    ..
    2 ズボン 4,000
    3 1 2021-11-04 2 高橋 takahashi
    @...
    1 シャツ 3,500
    擬似テーブルの内訳
    注文を起点に結合された擬似テーブル
    注文テーブルの属性 ユーザーテーブルの属性 商品テーブルの属性

    View Slide

  38. 37
    注文.
    ユー
    ザーID
    注文.
    商品ID
    注文.
    購入日
    ユーザー
    .ID
    ユーザー.
    名前
    ユーザー.
    メアド
    商品.ID 商品.名前 商品.価格
    1 2 2021-10-30 1 田中 [email protected]
    ..
    2 ズボン 4,000
    1 2 2021-10-30 1 田中 [email protected]
    ..
    2 ズボン 4,000
    3 1 2021-11-04 2 高橋 takahashi
    @...
    1 シャツ 3,500
    先のselect句の中身はつまりこういうこと
    SELECT
    ユーザー.名前,
    商品.名前,
    注文.購入日

    View Slide

  39. 38
    (ちなみに) 参照カラムをIDに変えると ...
    SELECT
    ユーザー.ID,
    商品.ID,
    注文.購入日
    FROM
    注文
    INNER JOIN ユーザー
    ON ユーザー.ID = 注文.ユーザーID
    INNER JOIN 商品
    ON 商品.ID = 注文.商品ID
    ユーザー.ID 商品.ID 注文.購入日
    1 2 2021-11-20
    1 3 2021-11-11
    3 1 2021-11-10
    2 2 2021-11-10
    1 1 2021-11-08
    3 3 2021-11-06

    View Slide

  40. 39
    4. ここでワークショップ

    View Slide

  41. 40
    5. 集計の基本

    View Slide

  42. 41
    基本的な集計関数
    COUNT() … 指定フィールドの総件数を取得
    SUM() … 指定フィールドの合計値を取得
    AVG() … 指定フィールドの平均値を取得
    MAX() … 指定フィールドの最大値を取得
    MIN() … 指定フィールドの最小値を取得

    View Slide

  43. 42
    使うデータ
    先ほどのECサイトのDB
    ID 名前 メアド
    1 田中 tanak...
    2 佐藤 [email protected]
    3 高橋 takaha...
    ユーザー
    ID 名前 価格
    1 シャツ 3,500
    2 ズボン 4,000
    3 くつ 8,500
    商品
    ユーザーID 商品ID 購入日
    1 2 2021-10-30
    1 2 2021-10-30
    3 1 2021-11-04
    注文

    View Slide

  44. 43
    SELECT
    COUNT(ID)
    FROM
    ユーザー
    集計関数の例 1. - COUNT()
    COUNT(ID)
    3

    View Slide

  45. 44
    SELECT
    COUNT(ID)
    FROM
    ユーザー
    集計関数の例 1. - COUNT()
    COUNT(ID)
    3
    このクエリからわかること : このECサイトのユーザー総数は 3人!

    View Slide

  46. 45
    SELECT
    AVG(価格)
    FROM
    商品
    集計関数の例 2. - AVG()
    AVG(価格)
    53333.3333 ....

    View Slide

  47. 46
    SELECT
    AVG(価格)
    FROM
    商品
    集計関数の例 2. - AVG()
    AVG(価格)
    5,333.3333 ....
    このクエリからわかること : このECサイトの平均商品価格は 5,3333.3333 ...円

    View Slide

  48. 47
    またも要求
    ???「11月の総売上を教えてくれ」

    View Slide

  49. 48
    SELECT
    SUM(商品.価格)
    FROM
    注文
    INNER JOIN 商品
    ON 商品.ID = 注文.商品ID
    WHRE
    注文.購入日 BETWEEN ‘2021-11-01’ AND ‘2021-11-31’
    余裕
    SUM(商品.価格)
    32,000

    View Slide

  50. 49
    SELECT
    SUM(商品.価格)
    FROM
    注文
    INNER JOIN 商品
    ON 商品.ID = 注文.商品ID
    WHRE
    注文.購入日 BETWEEN ‘2021-11-01’ AND ‘2021-11-31’
    余裕
    SUM(商品.価格)
    32,000
    注文ごとに買われた商品の価格を引っ張り出して合計している感じ
    (ズボン価格 + ズボン価格 + シャツ価格 + くつ価格 … etc)

    View Slide

  51. 50
    要は ...
    フィールド(セル)の値を集計するって意味では
    だいたいSpreadsheetと一緒

    View Slide

  52. 51
    6. ここでワークショップ

    View Slide

  53. 52
    7. おわりに

    View Slide

  54. 53
    今日のゴール
    1. 表結合についてなんとなく理解できる
    2. 簡単な結合表現を利用して複数テーブルにまたがるク
    エリを書けるようになる
    3. 集計関数の基本的な使い方がわかる
    どうだったでしょうか?

    View Slide

  55. 54
    次回予告
    1. 結合の種類と原理
    2. 表結合と集計の組み合わせ
    3. データの構造を理解する

    View Slide