ここまで、「SQLの基礎① 〜 簡単なSQL文のご紹介（SELECT句、FROM句、WHERE句） 〜」、「SQLの基礎② 〜 簡単なSQL文のご紹介（GROUP BY句、HAVING句） 〜」とSQLの基礎に関してのご紹介をしてきましたが、今回も基礎編として重要となるJOIN句をご紹介していきます。そして、「データ統合」という言葉を最近は色んな場面でも聞くことがあると思いますが、データ統合を担っているのは、SQLにおけるJOINだったりしますので、しっかりと覚えておいてください！
www.digitaldolphin.jp
www.digitaldolphin.jp
目次
データ統合が必要な場面
前回、マーケティング部や経営企画の上司から受注データをもとに、店舗毎の売上や受注単価・顧客単価を出すという指示をされて、担当者が実現するまでの流れを簡単にご紹介していきましたが、その上司から追加の要望をもらうことになりました。「前回出したデータをより詳しくみたいから、顧客や性別毎、また商品のカテゴリ毎に店舗毎の売上を出すことは出来ないか」と担当者に指示を出しました。ただ一方で、担当者は「受注データにそもそも年代とか、性別という項目がないのに、どのようにして実現するのだろう」と思い悩んでました。担当者としては、「確かに、受注データを集計すれば、店舗毎の売上を出せるし、顧客データには年代を出せそうな生年月日や性別という項目があるし、商品データも使えば、頑張って出来そうだけど、2つとか3つのテーブルを利用したFROM句というのは、ここまでやってきていないし、そもそもできるのか…？」という状況です。
今回は、この担当者の悩みを解消する方法として、JOIN句というテーブル同士の統合を実現できるクエリを紹介していきます。
今回使うデータ
まずは今回利用するテーブルを以下に出します。
今回使うデータ一覧
表1：顧客データ
| 顧客ID | 名前 | 性別 | 生年月日 | 都道府県 |
|---|---|---|---|---|
| c001 | 田中 | 男 | 1990-03-10 | 東京都 |
| c002 | 佐藤 | 女 | 1995-02-05 | 東京都 |
| c003 | 加藤 | 男 | 2001-08-08 | 大阪府 |
| c004 | 伊藤 | 男 | 1993-09-20 | 東京都 |
| c005 | 近藤 | 女 | 1997-12-02 | 神奈川県 |
| c006 | 山田 | 男 | 2002-11-24 | 神奈川県 |
| c007 | 山口 | 男 | 1984-02-26 | 神奈川県 |
| c008 | 山崎 | 女 | 1999-07-30 | 埼玉県 |
| c009 | 藤原 | 男 | 2005-01-10 | 千葉県 |
| c010 | 藤堂 | 男 | 1988-03-13 | 東京都 |
表2：受注データ
| 受注ID | 受注日時 | 顧客ID | 購入金額 | 購入店舗 | キャンセル日時 | 返品日時 |
|---|---|---|---|---|---|---|
| o001 | 2022-06-23 11:00:00 | c001 | 16000 | 横浜店 | - | 2022-06-23 11:41:43 |
| o002 | 2022-06-24 19:46:01 | c001 | 32000 | 横浜店 | - | - |
| o003 | 2022-06-27 00:59:09 | c005 | 9000 | オンラインショップ | - | - |
| o004 | 2022-06-29 08:46:59 | c004 | 14000 | 横浜店 | - | - |
| o005 | 2022-06-30 13:04:19 | c007 | 24000 | オンラインショップ | - | - |
| o006 | 2022-07-01 02:21:41 | c007 | 9000 | オンラインショップ | 2022-07-01 04:10:29 | - |
| o007 | 2022-07-03 01:39:28 | c005 | 7000 | オンラインショップ | - | - |
| o008 | 2022-07-04 03:13:40 | c001 | 7000 | 東京店 | - | - |
| o009 | 2022-07-04 23:25:35 | c005 | 11000 | オンラインショップ | - | - |
| o010 | 2022-07-07 08:29:30 | c006 | 32000 | 横浜店 | - | - |
| o011 | 2022-07-10 02:45:14 | c006 | 4000 | オンラインショップ | 2022-07-10 04:11:31 | - |
| o012 | 2022-07-11 21:01:41 | c007 | 9000 | 東京店 | - | - |
表3：受注明細データ
| 受注ID | 明細番号 | sku | 数量 | 金額 | キャンセル日時 | 返品日時 |
|---|---|---|---|---|---|---|
| o001 | 1 | s004-2-1 | 2 | 18000 | - | 2022-06-23 11:41:43 |
| o001 | 2 | s002-1-1 | 2 | 16000 | - | 2022-06-23 11:41:43 |
| o002 | 1 | s003-1-1 | 2 | 14000 | - | - |
| o002 | 2 | s004-2-1 | 2 | 18000 | - | - |
| o003 | 1 | s001-2-2 | 2 | 8000 | - | - |
| o003 | 2 | s004-1-2 | 1 | 9000 | - | - |
| o004 | 1 | s003-1-2 | 2 | 14000 | - | - |
| o005 | 1 | s002-1-1 | 2 | 16000 | - | - |
| o005 | 2 | s002-2-1 | 1 | 8000 | - | - |
| o006 | 1 | s004-1-2 | 1 | 9000 | 2022-07-01 04:10:29 | - |
| o007 | 1 | s003-1-1 | 1 | 7000 | - | - |
| o007 | 2 | s001-2-2 | 1 | 4000 | - | - |
| o008 | 1 | s003-2-2 | 1 | 7000 | - | - |
| o009 | 1 | s001-2-2 | 1 | 4000 | - | - |
| o009 | 2 | s003-2-1 | 1 | 7000 | - | - |
| o010 | 1 | s004-1-2 | 2 | 18000 | - | - |
| o010 | 2 | s003-1-1 | 2 | 14000 | - | - |
| o011 | 1 | s004-1-2 | 1 | 9000 | 2022-07-10 04:11:31 | - |
| o011 | 2 | s001-1-2 | 1 | 4000 | 2022-07-10 04:11:31 | - |
| o012 | 1 | s004-1-2 | 1 | 9000 | - | - |
表4：商品データ
| sku | 品番 | 商品名 | 価格 | サイズ | カラー | 在庫数 | カテゴリ |
|---|---|---|---|---|---|---|---|
| s001-1-1 | s001 | シューズA | 4000 | 1 | 1 | 2 | シューズ |
| s001-2-1 | s001 | シューズA | 4000 | 2 | 1 | 5 | シューズ |
| s001-1-2 | s001 | シューズA | 4000 | 1 | 2 | 7 | シューズ |
| s001-2-2 | s001 | シューズA | 4000 | 2 | 2 | 7 | シューズ |
| s002-1-1 | s002 | シューズB | 8000 | 1 | 1 | 1 | シューズ |
| s002-2-1 | s002 | シューズB | 8000 | 2 | 1 | 9 | シューズ |
| s002-1-2 | s002 | シューズB | 8000 | 1 | 2 | 5 | シューズ |
| s002-2-2 | s002 | シューズB | 8000 | 2 | 2 | 6 | シューズ |
| s003-1-1 | s003 | T-シャツA | 7000 | 1 | 1 | 9 | T-シャツ |
| s003-2-1 | s003 | T-シャツA | 7000 | 2 | 1 | 9 | T-シャツ |
| s003-1-2 | s003 | T-シャツA | 7000 | 1 | 2 | 9 | T-シャツ |
| s003-2-2 | s003 | T-シャツA | 7000 | 2 | 2 | 6 | T-シャツ |
| s004-1-1 | s004 | T-シャツB | 9000 | 1 | 1 | 2 | T-シャツ |
| s004-2-1 | s004 | T-シャツB | 9000 | 2 | 1 | 9 | T-シャツ |
| s004-1-2 | s004 | T-シャツB | 9000 | 1 | 2 | 10 | T-シャツ |
| s004-2-2 | s004 | T-シャツB | 9000 | 2 | 2 | 3 | T-シャツ |
正直このくらいのデータ量ならExcelを駆使して頑張れば最悪なんとかなりますが、受注データも受注明細も数十万、数百万行もあると、ほとんどのケースでExcelは真っ白になり、計算が完了しないという事態に陥ります。そして、上司の要望を実現しようとするとやっぱりSQLでクエリを書くことが一番手っ取り早かったりするのも事実です。ということで、今回は複数のデータを組み合わせるデータ統合をSQLにて実現していきましょう！
今回使うデータの全体像
ただ、その前にもう一個、複数のデータを扱う際にはデータの全体像を把握することが非常に重要となり、その際に利用する図があります。
それが、ER図（Entity Relationship Diagram）になります。実際に上記にあげた各データの関係性をER図で表すと以下のようになります。
このER図を見ることで、テーブル同士がどのように紐づいているのかを把握することが出来るようになり、データ統合をする上でも重要な情報となります。また、折角なのでこのER図の記号も覚えておきましょう！（とはいっても、実際にはこの表見ながら見ればいいと思います…笑）
表5：ER図に使われる記号
| # | 記号 | 意味 |
|---|---|---|
| 1 |  |
結合先が"1"つもないことを示します。 |
| 2 |  |
結合先が"1"つであることを示します。 |
| 3 |  |
結合先が0、若しくは"1"つであることを示します。 |
| 4 |  |
結合先が"1"つ以上であることを示します。 |
| 5 |  |
結合先が"1"つしかないことを示します。（#2をよく使う） |
| 6 |  |
結合先が"0"、若しくは"1"つ以上であることを示します。 |
| 7 |  |
結合先が"1"つ、若しくは"1"つ以上であることを示します。（#4をよく使う） |
| 8 |  |
テーブルを示し、PK（Primary Key）は主キー、FK（Foreign Key）は外部キーを示す。枠が角のものは親を示す |
| 9 |  |
テーブルを示し、PK（Primary Key）は主キー、FK（Foreign Key）は外部キーを示す。枠が丸のものは子を示す |
そして、概念として重要となるのは、結合先が1つである状態と"1"つである状態の時には「1対1」、結合先が"1"つである状態と"1"つ以上である状態の時には「1対N」、結合先が"1"つ以上である状態と"1"つ以上である状態の時には「N対N」とそれぞれ言います。その上で、データ統合をする場合には、「1対1」や「N対1」の時にはいいのですが、「1対N」や「N対N」の時にはデータが多くなるため、Amazon Web Services（AWS）でデータを保存しているときに大きなデータになってしまい、AWSの請求が多くなってしまったというケースが多発します。（ちなみに、自分も経験したことがありますが、AWSは全くディスカウントしてくれませんので、本当に注意してください……）そのため、基本的に、初心者は必ず「1対1」や「N対1」を利用してください。
では、ここまでは今回使うデータに関しての詳細を共有してきましたが、ここからデータを実際に作ってみましょう！
データ統合のSQL例
担当者は必要なインプットデータを準備することが出来、やっと上司から指示された「顧客の年代や性別毎、また商品のカテゴリ毎に店舗毎の売上を出す」ことに取り組み始められます。そして、データを組み合わせる（データ統合）をする際には、JOIN句を利用し、複数テーブルを統合したテーブルを作れば良いということが分かりました。
実際にまずは受注明細データと受注データを組み合わせたデータを以下のSQLで作っていきます。
サンプル1
SELECT
a.受注ID
,a.明細番号
,a.sku
,a.数量
,a.金額
,b.受注日時
,b.顧客ID
,b.購入店舗
FROM
受注明細データ AS a
LEFT JOIN
受注データ AS b
ON
a.受注ID = b.受注ID
WHERE
b.キャンセル日時 IS NULL
AND b.返品日時 IS NULL
AND b.購入店舗 LIKE 'オンラインショップ'
;表6：サンプル1の実行結果
| 受注ID | 明細番号 | sku | 数量 | 金額 | 受注日時 | 顧客ID | 購入店舗 |
|---|---|---|---|---|---|---|---|
| o003 | 1 | s001-2-2 | 2 | 8000 | 2022-06-27 00:59:09 | c005 | オンラインショップ |
| o003 | 2 | s004-1-2 | 1 | 9000 | 2022-06-27 00:59:09 | c005 | オンラインショップ |
| o005 | 1 | s002-1-1 | 2 | 16000 | 2022-06-30 13:04:19 | c007 | オンラインショップ |
| o005 | 2 | s002-2-1 | 1 | 8000 | 2022-06-30 13:04:19 | c007 | オンラインショップ |
| o007 | 1 | s003-1-1 | 1 | 7000 | 2022-07-03 01:39:28 | c005 | オンラインショップ |
| o007 | 2 | s001-2-2 | 1 | 4000 | 2022-07-03 01:39:28 | c005 | オンラインショップ |
| o009 | 1 | s001-2-2 | 1 | 4000 | 2022-07-04 23:25:35 | c005 | オンラインショップ |
| o009 | 2 | s003-2-1 | 1 | 7000 | 2022-07-04 23:25:35 | c005 | オンラインショップ |
受注明細データと受注データを利用したテーブルの統合をしてみましたが、今回クエリ中に出てきたものを補足していきます。
まず、AS句に関しては以前もご紹介しましたが、テーブル名やカラム名を区別するためのクエリとなっております。特に、複数のテーブルを利用する場合は、カラム名が被るとどのテーブルのカラムかがわからなくなり、処理エラーとなるため、今回のように命名してみてください。また、命名したものを利用して、aと命名したテーブルのカラムを選択する場合は、「a.カラム名」のような形でどのテーブルのものかを指定することが出来ます。
次にJOINの前にある"LEFT"という部分に注目してほしいですが、JOINの方法にも実は種類があります。よく使われるものも含めて、図でも意識してもらいたいため、下の表でまとめます。ちなみに、JOIN句と一緒に出てくるON句は結合キーを指定するクエリになります。
表7：JOINの種類
| # | JOINの種類 | イメージ | 意味 |
|---|---|---|---|
| 1 | INNER JOIN |  |
2つのテーブルに共通するレコードを統合。 |
| 2 | LEFT (OUTER) JOIN |  |
結合元に紐づくもののみを統合。結合先に値があっても統合対象外としてレコードを除外。 |
| 3 | RIGHT (OUTER) JOIN |  |
結合先に紐づくもののみを統合。結合元に値があっても統合対象外としてレコードを除外。 |
| 4 | FULL (OUTER) JOIN |  |
結合元でも結合先でも紐づくものを統合。 |
| 5 | CROSS JOIN | イメージ無し | 結合元と結合先のレコードの全てのペアを残すように統合。 |
この中でよく利用するのは、#1のINNER JOINと#2のLEFT JOINのため、この2つを利用する形でデータを作ることを意識してください。RIGHT JOINやFULL JOINを利用してもいいのですが、初心者の方は利用しないようにしてください。
そして、今回担当者は4つのテーブル全てを統合する必要が有るため、以下のようにしてみてください。（※簡易的にするため、項目は一旦同じ項目にします。）
サンプル2
SELECT
a.受注ID
,a.明細番号
,a.sku
,a.数量
,a.金額
,b.受注日時
,b.顧客ID
,b.購入店舗
FROM
受注明細データ AS a
LEFT JOIN
受注データ AS b
ON
a.受注ID = b.受注ID
LEFT JOIN
商品データ AS c
ON
a.sku = c.sku
LEFT JOIN
顧客データ AS d
ON
b.顧客ID = d.顧客ID
WHERE
b.キャンセル日時 IS NULL
AND b.返品日時 IS NULL
AND b.購入店舗 LIKE 'オンラインショップ'
;表8：サンプル2の実行結果
| 受注ID | 明細番号 | sku | 数量 | 金額 | 受注日時 | 顧客ID | 購入店舗 |
|---|---|---|---|---|---|---|---|
| o003 | 1 | s001-2-2 | 2 | 8000 | 2022-06-27 00:59:09 | c005 | オンラインショップ |
| o003 | 2 | s004-1-2 | 1 | 9000 | 2022-06-27 00:59:09 | c005 | オンラインショップ |
| o005 | 1 | s002-1-1 | 2 | 16000 | 2022-06-30 13:04:19 | c007 | オンラインショップ |
| o005 | 2 | s002-2-1 | 1 | 8000 | 2022-06-30 13:04:19 | c007 | オンラインショップ |
| o007 | 1 | s003-1-1 | 1 | 7000 | 2022-07-03 01:39:28 | c005 | オンラインショップ |
| o007 | 2 | s001-2-2 | 1 | 4000 | 2022-07-03 01:39:28 | c005 | オンラインショップ |
| o009 | 1 | s001-2-2 | 1 | 4000 | 2022-07-04 23:25:35 | c005 | オンラインショップ |
| o009 | 2 | s003-2-1 | 1 | 7000 | 2022-07-04 23:25:35 | c005 | オンラインショップ |
複数テーブルを紐づける時には、上記のようにJOINのあとにJOINを組み合わせるような形で実現することが出来ます。
そして、今回の目的のデータに関しては、全てのデータを上記のような形で組み合わせた上で、以前利用したGROUP BY句などを利用することで実現することが出来ます。
じゃあ、ちょっとここまでやってきたことを生かして、オンラインショップにおける、性別ごと、商品カテゴリ毎の売上、受注数、受注単価、購入顧客数、顧客単価を出してみようということで、担当者がSQLを書いてみました。
サンプル3
SELECT
b.購入店舗
,d.性別
,c.カテゴリ
,SUM(a.数量 * a.金額) AS 売上
,COUNT(a.受注ID) AS 受注数
,SUM(a.数量 * a.金額) / NULLIF(COUNT(a.受注ID),0) AS 受注単価
,COUNT(DISTINCT b.顧客ID) AS 購入顧客数
,SUM(a.数量 * a.金額) / NULLIF(COUNT(DISTINCT b.顧客ID),0) AS 顧客単価
FROM
受注明細データ AS a
LEFT JOIN
受注データ AS b
ON
a.受注ID = b.受注ID
LEFT JOIN
商品データ AS c
ON
a.sku = c.sku
LEFT JOIN
顧客データ AS d
ON
b.顧客ID = d.顧客ID
WHERE
b.キャンセル日時 IS NULL
AND b.返品日時 IS NULL
AND b.購入店舗 LIKE 'オンラインショップ'
GROUP BY
b.購入店舗
AND d.性別
AND c.カテゴリ
;表9：サンプル3の実行結果
| 購入店舗 | 性別 | カテゴリ | 売上 | 受注数 | 受注単価 | 購入顧客数 | 顧客単価 |
|---|---|---|---|---|---|---|---|
| オンラインショップ | 女 | T-シャツ | 23000 | 3 | 7667 | 1 | 23000 |
| オンラインショップ | 女 | シューズ | 24000 | 3 | 8000 | 1 | 24000 |
| オンラインショップ | 男 | シューズ | 40000 | 2 | 20000 | 1 | 40000 |
ということで、担当者は「データの作成が出来ました！」と声高々に上司に報告をしました。正直、どの組み合わせが来ても担当者としては、同じ要領でやれば出来るでしょという感覚で、上司からこれ以上何が来ても大丈夫だという感じでした。上司も「都道府県ごとにも出して」や「商品毎に出してみて」と色々と指示を出しましたが、上記のSQLのクエリを少しだけ変えればいいので、ちょっと変えて、「出来ました！」と報告をするだけです…笑
さいごに
ただ、上司から「生年月日はあるけど、年代別って出せって言ったら出来るの？」と聞かれたときに担当者はドキっとしました。「（確かに…、元の項目をそのまま使うことなら出来るけど、変換したりは出来るのか…？）」と悩んだ挙句、上司に対して、「ちょっと考えてみます…！」と一度持ち帰るしかありません。
そんな担当者の悩みを次回では解消できるように、基礎編の最後として、SQLの関数をご紹介していきます！
今回、ご紹介したJOIN句はしっかりと覚えておいてください！