社内向けに実施した第2回 SQL勉強会の内容を社外公開用に一部加工したものです。
©2021 crassone第2回 SQL勉強会2021/11/25
View Slide
10. はじめに
2この会の趣旨エンジニア / 非エンジニアを問わず、データのさらなる利活用によって業務の幅を広げていくことを目的に知識ゼロからSQLを少しずつ学んでいこうというイベントです。たぶん全4回くらい実施できると一通りのことができるようになりそう。
3今日のゴール1. 表結合についてなんとなく理解できる2. 簡単な結合表現を利用して複数テーブルにまたがるクエリを書けるようになる3. 集計関数の基本的な使い方がわかる
41. 前回のおさらい
5SQLとは ...データベースに対する問い合わせ言語そしてSELCT文は読取のための構文
6基本的なSELECT文SELECTID, 名前, メールアドレス, 作成日時FROMユーザーWHERE名前 LIKE ‘佐藤%’ORDER BY作成日時 DESCLIMIT 10
7基本的なSELECT文SELECTID, 名前, メールアドレス, 作成日時FROMユーザーWHERE名前 LIKE ‘佐藤%’ORDER BY作成日時 DESCLIMIT 10ユーザーの中から「佐藤」という名前で始まる人を最新10名分ください
8基本的なSELECT文SELECTID, 名前, メールアドレス, 作成日時FROMユーザーWHERE名前 LIKE ‘佐藤%’ORDER BY作成日時 DESCLIMIT 10ユーザーの中から(対象のテーブル)「佐藤」から始まる名前の人を(抽出条件)「作成日時」新しい順に(結果の取得順序の指定 )10件まで(結果の最大取得件数 )
9さてじゃあ、次に表結合の話をします
102. 表... 結合...?
11とりあえず表結合とは ...表(テーブル)同士を結合する(つなぐ)こと
12例えばこんなデータがあったとしますとあるECサイトID 名前 メアド1 田中 [email protected]2 佐藤 [email protected]3 高橋 [email protected]ユーザーID 名前 価格1 シャツ 3,5002 ズボン 4,0003 くつ 8,500商品
13要求???「誰に何が売れたのか教えてくれ」
14疑問先ほどの情報から取り出せそうですか?
15注文データの登場ECサイトなのでユーザーの注文もデータとして記録しなければならないのでしたID 名前 メアド1 田中 tanak...2 佐藤 [email protected]3 高橋 takaha...ユーザーID 名前 価格1 シャツ 3,5002 ズボン 4,0003 くつ 8,500商品ユーザーID 商品ID 購入日1 2 2021-10-301 2 2021-10-303 1 2021-11-04注文 New!
16疑問こうじゃないの???ユーザー名 商品名 購入日田中 ズボン 2021-10-30田中 ズボン 2021-10-30高橋 シャツ 2021-11-04注文
17そうじゃないんですこうじゃないの???ユーザー名 商品名 購入日田中 ズボン 2021-10-30田中 ズボン 2021-10-30高橋 シャツ 2021-11-04注文同姓同名の人がいると異なるユーザーが混ざってしまったり、逆に注文時に別名を入力されたら別ユーザーとして扱われてしまったり …「1人のユーザー」として追跡するためには、ひとりひとりのユーザーが独立した1レコードとして存在する必要があるのです。
18テーブル同士の「関連付け」ECサイトなのでユーザーの注文も記録しなければならないのでしたID 名前 メアド1 田中 tanak...2 佐藤 [email protected]3 高橋 takaha...ユーザーID 名前 価格1 シャツ 3,5002 ズボン 4,0003 くつ 8,500商品ユーザーID 商品ID 購入日1 2 2021-10-301 2 2021-10-303 1 2021-11-04注文注文とユーザーの関連付けを表す 注文と商品の関連付けを表す
19では注文データを取り出してみるSELECTユーザーID, 商品ID, 購入日FROM注文ORDER BY購入日 DESCLIMIT 10
20注文データを取り出してみたSELECTユーザーID, 商品ID, 購入日FROM購入ORDER BY購入日 DESCLIMIT 10ユーザーID 商品ID 購入日1 2 2021-11-201 3 2021-11-113 1 2021-11-102 2 2021-11-101 1 2021-11-083 3 2021-11-06
21注文データを取り出してみた、結果SELECTユーザーID, 商品ID, 購入日FROM購入ORDER BY購入日 DESCLIMIT 10ユーザーID 商品ID 購入日1 2 2021-11-201 3 2021-11-113 1 2021-11-102 2 2021-11-101 1 2021-11-083 3 2021-11-06え、誰?え、何?
223. そこで「結合」ですよ
23表結合とは … (再掲)表(テーブル)同士を結合する(つなぐ)こと
24今回やりたいことユーザー、商品、注文のすべてをつなげて一緒に見れればよさそうID 名前 メアド1 田中 tanak...2 佐藤 [email protected]3 高橋 takaha...ユーザーID 名前 価格1 シャツ 3,5002 ズボン 4,0003 くつ 8,500商品ユーザーID 商品ID 購入日1 2 2021-10-301 2 2021-10-303 1 2021-11-04注文
25(内部)結合の基礎SELECT[見たいフィールド]FROM[結合元のテーブル]INNER JOIN [結合先のテーブル]ON [結合先のキー] = [結合元のキー]
26今回のケースでいうと ...SELECTユーザー.名前,商品.名前,注文.購入日FROM注文INNER JOIN ユーザーON ユーザー.ID = 注文.ユーザーIDINNER JOIN 商品ON 商品.ID = 注文.商品ID
27結合させてる箇所SELECTユーザー.名前,商品.名前,注文.購入日FROM注文INNER JOIN ユーザーON ユーザー.ID = 注文.ユーザーIDINNER JOIN 商品ON 商品.ID = 注文.商品IDこの部分が結合のための記述
28注文 - ユーザーの結合SELECTユーザー.名前,商品.名前,注文.購入日FROM注文INNER JOIN ユーザーON ユーザー.ID = 注文.ユーザーIDINNER JOIN 商品ON 商品.ID = 注文.商品IDユーザーテーブルの「 ID」注文テーブルの「ユーザー ID」の二つは、どちらもユーザーに割り当てられた固有の IDを表すフィールドで、同じものなのでこの二つを結び付けてユーザーと注文を結合しますよ
29注文 - 商品の結合SELECTユーザー.名前,商品.名前,注文.購入日FROM注文INNER JOIN ユーザーON ユーザー.ID = 注文.ユーザーIDINNER JOIN 商品ON 商品.ID = 注文.商品ID商品テーブルの「ID」注文テーブルの「商品 ID」は、どちらも商品ごとに割り当てられた固有の IDを表すフィールドで、同じものなのでこの二つを結び付けて商品と注文を結合しますよ
30総じて ...SELECTユーザー.名前,商品.名前,注文.購入日FROM注文INNER JOIN ユーザーON ユーザー.ID = 注文.ユーザーIDINNER JOIN 商品ON 商品.ID = 注文.商品ID注文テーブルを「起点」にユーザーと商品を結び付けて結果を取得しますよ
31取得結果SELECTユーザー.名前,商品.名前,注文.購入日FROM注文INNER JOIN ユーザーON ユーザー.ID = 注文.ユーザーIDINNER JOIN 商品ON 商品.ID = 注文.商品IDユーザー.名前 商品.名前 注文.購入日田中 ズボン 2021-11-20田中 くつ 2021-11-11高橋 シャツ 2021-11-10佐藤 ズボン 2021-11-10田中 シャツ 2021-11-08高橋 くつ 2021-11-06
32結合の結果誰が何を買ったのかわかった!
33SELECTの中身についてSELECTユーザー.名前,商品.名前,注文.購入日FROM注文INNER JOIN ユーザーON ユーザー.ID = 注文.ユーザーIDINNER JOIN 商品ON 商品.ID = 注文.商品IDユーザー.名前 商品.名前 注文.購入日田中 ズボン 2021-11-20田中 くつ 2021-11-11高橋 シャツ 2021-11-10佐藤 ズボン 2021-11-10田中 シャツ 2021-11-08高橋 くつ 2021-11-06この部分について見ていきます
34元のデータベースID 名前 メアド1 田中 tanak...2 佐藤 [email protected]3 高橋 takaha...ユーザーID 名前 価格1 シャツ 3,5002 ズボン 4,0003 くつ 8,500商品ユーザーID 商品ID 購入日1 2 2021-10-301 2 2021-10-303 1 2021-11-04注文
35注文.ユーザーID注文.商品ID注文.購入日ユーザー.IDユーザー.名前ユーザー.メアド商品.ID 商品.名前 商品.価格1 2 2021-10-30 1 田中 [email protected]..2 ズボン 4,0001 2 2021-10-30 1 田中 [email protected]..2 ズボン 4,0003 1 2021-11-04 2 高橋 takahashi@...1 シャツ 3,500JOINされるとこういう形になります注文を起点に結合された擬似テーブル
36注文.ユーザーID注文.商品ID注文.購入日ユーザー.IDユーザー.名前ユーザー.メアド商品.ID 商品.名前 商品.価格1 2 2021-10-30 1 田中 [email protected]..2 ズボン 4,0001 2 2021-10-30 1 田中 [email protected]..2 ズボン 4,0003 1 2021-11-04 2 高橋 takahashi@...1 シャツ 3,500擬似テーブルの内訳注文を起点に結合された擬似テーブル注文テーブルの属性 ユーザーテーブルの属性 商品テーブルの属性
37注文.ユーザーID注文.商品ID注文.購入日ユーザー.IDユーザー.名前ユーザー.メアド商品.ID 商品.名前 商品.価格1 2 2021-10-30 1 田中 [email protected]..2 ズボン 4,0001 2 2021-10-30 1 田中 [email protected]..2 ズボン 4,0003 1 2021-11-04 2 高橋 takahashi@...1 シャツ 3,500先のselect句の中身はつまりこういうことSELECTユーザー.名前,商品.名前,注文.購入日
38(ちなみに) 参照カラムをIDに変えると ...SELECTユーザー.ID,商品.ID,注文.購入日FROM注文INNER JOIN ユーザーON ユーザー.ID = 注文.ユーザーIDINNER JOIN 商品ON 商品.ID = 注文.商品IDユーザー.ID 商品.ID 注文.購入日1 2 2021-11-201 3 2021-11-113 1 2021-11-102 2 2021-11-101 1 2021-11-083 3 2021-11-06
394. ここでワークショップ
405. 集計の基本
41基本的な集計関数COUNT() … 指定フィールドの総件数を取得SUM() … 指定フィールドの合計値を取得AVG() … 指定フィールドの平均値を取得MAX() … 指定フィールドの最大値を取得MIN() … 指定フィールドの最小値を取得
42使うデータ先ほどのECサイトのDBID 名前 メアド1 田中 tanak...2 佐藤 [email protected]3 高橋 takaha...ユーザーID 名前 価格1 シャツ 3,5002 ズボン 4,0003 くつ 8,500商品ユーザーID 商品ID 購入日1 2 2021-10-301 2 2021-10-303 1 2021-11-04注文
43SELECTCOUNT(ID)FROMユーザー集計関数の例 1. - COUNT()COUNT(ID)3
44SELECTCOUNT(ID)FROMユーザー集計関数の例 1. - COUNT()COUNT(ID)3このクエリからわかること : このECサイトのユーザー総数は 3人!
45SELECTAVG(価格)FROM商品集計関数の例 2. - AVG()AVG(価格)53333.3333 ....
46SELECTAVG(価格)FROM商品集計関数の例 2. - AVG()AVG(価格)5,333.3333 ....このクエリからわかること : このECサイトの平均商品価格は 5,3333.3333 ...円
47またも要求???「11月の総売上を教えてくれ」
48SELECTSUM(商品.価格)FROM注文INNER JOIN 商品ON 商品.ID = 注文.商品IDWHRE注文.購入日 BETWEEN ‘2021-11-01’ AND ‘2021-11-31’余裕SUM(商品.価格)32,000
49SELECTSUM(商品.価格)FROM注文INNER JOIN 商品ON 商品.ID = 注文.商品IDWHRE注文.購入日 BETWEEN ‘2021-11-01’ AND ‘2021-11-31’余裕SUM(商品.価格)32,000注文ごとに買われた商品の価格を引っ張り出して合計している感じ(ズボン価格 + ズボン価格 + シャツ価格 + くつ価格 … etc)
50要は ...フィールド(セル)の値を集計するって意味ではだいたいSpreadsheetと一緒
516. ここでワークショップ
527. おわりに
53今日のゴール1. 表結合についてなんとなく理解できる2. 簡単な結合表現を利用して複数テーブルにまたがるクエリを書けるようになる3. 集計関数の基本的な使い方がわかるどうだったでしょうか?
54次回予告1. 結合の種類と原理2. 表結合と集計の組み合わせ3. データの構造を理解する