diff --git a/astro.config.mjs b/astro.config.mjs
index b0c19ec..0d6f759 100644
--- a/astro.config.mjs
+++ b/astro.config.mjs
@@ -51,6 +51,7 @@
},
securityLevel: "loose",
+ strategy: "img-png",
}
]
],
diff --git a/src/pages/sql/040-join.mdx b/src/pages/sql/040-join.mdx
index 2795d49..80c0eda 100644
--- a/src/pages/sql/040-join.mdx
+++ b/src/pages/sql/040-join.mdx
@@ -4,11 +4,14 @@
export const title = "テーブルを分ける、繋げる";
+import Details from "@/components/Details.astro";
import DockerLink from "@/components/DockerLink.astro";
import OuterLink from "@/components/OuterLink.astro";
# {title}
+## TOC
+
## テーブルを分ける
例えば、なにか観た映画の率直な感想をデータベースで管理したいとします。
@@ -17,14 +20,33 @@
| 作品名 | ラベル |
| :----- | :---- |
-| インセプション | おもしろい、感動的 |
-| マトリックス | おもしろい、難しい |
-| 君の名は。 | 感動的、絵がきれい |
-| 天空の城ラピュタ | 感動的、面白い |
+| インセプション | `おもしろい、感動的` |
+| マトリックス | `おもしろい、難しい` |
+| 君の名は。 | `感動的、絵がきれい` |
+| 天空の城ラピュタ | `感動的、面白い` |
+
+
+
+```sql
+DROP TABLE IF EXISTS movies;
+CREATE TABLE movies (
+ title VARCHAR(255),
+ label VARCHAR(255)
+);
+INSERT INTO movies (title, label) VALUES
+('インセプション', 'おもしろい、感動的'),
+('マトリックス', 'おもしろい、難しい'),
+('君の名は。', '感動的、絵がきれい'),
+('天空の城ラピュタ', '感動的、面白い');
+```
+
+
このテーブルには、いくつかの問題点があります。
1つずつ見ていきましょう。
+### 同義語の扱いが面倒
+
さてこの中で、面白いと評価された映画を抽出したいとします。
```sql
@@ -36,7 +58,7 @@
| 作品名 | ラベル |
| :----- | :---- |
-| 天空の城ラピュタ | 感動的、面白い |
+| 天空の城ラピュタ | `感動的、面白い` |
`インセプション`と`マトリックス`が抽出されません。それもそのはず、`おもしろい`と`面白い`は別の文字列だからです。
@@ -50,9 +72,9 @@
| 作品名 | ラベル |
| :----- | :---- |
-| インセプション | おもしろい、感動的 |
-| マトリックス | おもしろい、難しい |
-| 天空の城ラピュタ | 感動的、面白い |
+| インセプション | `おもしろい、感動的` |
+| マトリックス | `おもしろい、難しい` |
+| 天空の城ラピュタ | `感動的、面白い` |
確かに見た目上は、すべて抽出できました。
しかし、この面白いという評価は、他にも`おもろい`など、様々な表現があるかもしれません。
@@ -67,21 +89,58 @@
| ID | ラベル |
| --: | :---- |
-| 1 | おもしろい |
-| 2 | 感動的 |
-| 3 | 難しい |
-| 4 | 絵がきれい |
+| 1 | `おもしろい` |
+| 2 | `感動的` |
+| 3 | `難しい` |
+| 4 | `絵がきれい` |
| ~ | \~中略\~ |
-| 10 | 自分の好み |
+| 10 | `自分の好み` |
+| 11 | `儚い` |
+
+
+
+```sql
+DROP TABLE IF EXISTS movie_labels;
+CREATE TABLE movie_labels (
+ id INT AUTO_INCREMENT PRIMARY KEY,
+ label VARCHAR(255)
+);
+INSERT INTO movie_labels (id, label) VALUES
+(1, 'おもしろい'),
+(2, '感動的'),
+(3, '難しい'),
+(4, '絵がきれい'),
+(10, '自分の好み'),
+(11, '儚い');
+```
+
+
`movies`テーブルのラベル列を、この`movie_labels`テーブルのIDを使うようにしてみます。
| 作品名 | ラベルID |
| :----- | :---- |
-| インセプション | 1,2 |
-| マトリックス | 1,3 |
-| 君の名は。 | 2,4 |
-| 天空の城ラピュタ | 2,1 |
+| インセプション | `1,2` |
+| マトリックス | `1,3` |
+| 君の名は。 | `2,4` |
+| 天空の城ラピュタ | `2,1` |
+
+
+
+```sql
+DROP TABLE IF EXISTS movies;
+CREATE TABLE movies (
+ title VARCHAR(255),
+ label_id VARCHAR(255)
+);
+INSERT INTO movies (title, label_id) VALUES
+('インセプション', '1,2'),
+('マトリックス', '1,3'),
+('君の名は。', '2,4'),
+('天空の城ラピュタ', '2,1');
+```
+
+
これで、おもしろいと評価する映画を抽出するSQLは、以下のようになります。
@@ -93,6 +152,406 @@
| 作品名 | ラベルID |
| :----- | :---- |
-| インセプション | 1,2 |
-| マトリックス | 1,3 |
-| 天空の城ラピュタ | 2,1 |
+| インセプション | `1,2` |
+| マトリックス | `1,3` |
+| 天空の城ラピュタ | `2,1` |
+
+これで、`1`という番号さえ知っていれば、同義語を気にせずにおもしろいと評価された映画を抽出できるようになりました。
+
+### 中間テーブル
+
+以下のSQLには、まだ問題があります。どのような問題でしょうか？
+
+```sql
+SELECT * FROM movies WHERE label_id LIKE '%1%';
+```
+
+わかりますか？
+もう少し具体的に、以下のような評価を追加してみましょう。
+
+| 作品名 | ラベルID |
+| :----- | :---- |
+| インセプション | `1,2` |
+| マトリックス | `1,3` |
+| 天空の城ラピュタ | `2,1` |
+| ~~~ | ~ |
+| 風の谷のナウシカ | `10` |
+| 崖の上のポニョ | `11,10` |
+
+
+
+```sql
+DROP TABLE IF EXISTS movies;
+CREATE TABLE movies (
+ title VARCHAR(255),
+ label_id VARCHAR(255)
+);
+INSERT INTO movies (title, label_id) VALUES
+('インセプション', '1,2'),
+('マトリックス', '1,3'),
+('天空の城ラピュタ', '2,1'),
+('風の谷のナウシカ', '10'),
+('崖の上のポニョ', '11,10');
+```
+
+
+
+このとき、先ほどのSQLを実行するとどのような結果になるでしょうか？
+
+```sql
+SELECT * FROM movies WHERE label_id LIKE '%1%';
+```
+
+
+
+
+
+このとき、本来意図しない`風の谷のナウシカ`と`崖の上のポニョ`も抽出されてしまいます。
+
+| 作品名 | ラベルID |
+| :----- | :---- |
+| インセプション | `1,2` |
+| マトリックス | `1,3` |
+| 天空の城ラピュタ | `2,1` |
+| 風の谷のナウシカ | `10` |
+| 崖の上のポニョ | `11,10` |
+
+それもそのはず、実行しているSQLは、`label_id`列に`1`という文字が含まれているかどうかを調べているだけだからです。
+`10`や`11`にも`1`という文字は含まれています。
+
+本来、おもしろいと評価された映画だけを抽出したい場合は、`1`を含む、ではなく、`1`だけ、を調べる必要があります。
+どこがこれを不可能にしているのでしょうか？
+
+
+
+
+
+これを不可能にしているのは、`movies`テーブルの`label_id`列に複数のラベルIDをカンマ区切りで格納していることです。
+これを解決するのに、どのようなアプローチがあるでしょうか？
+
+カンマ区切りで複数のラベルIDを格納するのではなく、1つのラベルIDにつき1行を割り当てればいいのでしょうか？
+
+テーブル名: **movies**
+
+| 作品名 | ラベルID |
+| :----- | :---- |
+| インセプション | `1` |
+| インセプション | `2` |
+| マトリックス | `1` |
+| マトリックス | `3` |
+| 天空の城ラピュタ | `2` |
+| 天空の城ラピュタ | `1` |
+| 風の谷のナウシカ | `10` |
+| 崖の上のポニョ | `11` |
+| 崖の上のポニョ | `10` |
+
+
+
+```sql
+DROP TABLE IF EXISTS movies;
+CREATE TABLE movies (
+ title VARCHAR(255),
+ label_id INT
+);
+INSERT INTO movies (title, label_id) VALUES
+('インセプション', 1),
+('インセプション', 2),
+('マトリックス', 1),
+('マトリックス', 3),
+('天空の城ラピュタ', 2),
+('天空の城ラピュタ', 1),
+('風の谷のナウシカ', 10),
+('崖の上のポニョ', 11),
+('崖の上のポニョ', 10);
+```
+
+
+
+これも1つの解決策です。
+この状態であれば、`おもしろい`のみを確実に抽出することができますね。
+
+```sql
+SELECT * FROM movies WHERE label_id = 1;
+```
+
+しかしこの方法では、1つの映画を評価するたびに、複数行を追加しなければならなくなります。
+
+
+
+
+もう1つあります。
+ラベルの列を複数持てばいいのでしょうか？
+
+テーブル名: **movies**
+
+| 作品名 | ラベルID1 | ラベルID2 | ラベルID3 |
+| :----- | --: | --: | --: |
+| インセプション | `1` | `2` | ~ |
+| マトリックス | `1` | `3` | ~ |
+| 天空の城ラピュタ | `2` | `1` | ~ |
+| 風の谷のナウシカ | `10` | ~ | ~ |
+| 崖の上のポニョ | `11` | `10` | ~ |
+
+
+
+```sql
+DROP TABLE IF EXISTS movies;
+CREATE TABLE movies (
+ title VARCHAR(255),
+ label_id1 INT,
+ label_id2 INT,
+ label_id3 INT
+);
+INSERT INTO movies (title, label_id1, label_id2, label_id3) VALUES
+('インセプション', 1, 2, NULL),
+('マトリックス', 1, 3, NULL),
+('天空の城ラピュタ', 2, 1, NULL),
+('風の谷のナウシカ', 10, NULL, NULL),
+('崖の上のポニョ', 11, 10, NULL);
+```
+
+
+
+この方法でも、`おもしろい`のみを確実に抽出することができますね。
+
+```sql
+SELECT * FROM movies WHERE label_id1 = 1 OR label_id2 = 1;
+```
+
+また、1つの映画を評価するたびに、複数行を追加する必要もありません。
+ラベルが足りなくなったら、列を追加すればよいからです。
+
+まだ完璧ではないですが、実際に使われている方法でもあります。
+
+
+
+では、この問題すらも解決する方法があります。
+どのようにするか思いつくでしょうか？
+
+
+
+
+
+
+ここで登場するのが**中間テーブル**です。
+まず準備として、`movies`テーブルにID列を追加しておきます。
+
+テーブル名: **movies**
+
+| ID | 作品名 | ラベルID (削除されます) |
+| --: | :----- | :---- |
+| 1 | インセプション | ~ |
+| 2 | マトリックス | ~ |
+| 3 | 天空の城ラピュタ | ~ |
+| 4 | 風の谷のナウシカ | ~ |
+| 5 | 崖の上のポニョ | ~ |
+
+
+
+```sql
+DROP TABLE IF EXISTS movies;
+CREATE TABLE movies (
+ id INT AUTO_INCREMENT PRIMARY KEY,
+ title VARCHAR(255)
+);
+INSERT INTO movies (title) VALUES
+('インセプション'),
+('マトリックス'),
+('天空の城ラピュタ'),
+('風の谷のナウシカ'),
+('崖の上のポニョ');
+```
+
+
+
+`movie_labels`テーブルはそのまま使います。
+
+さて、中間テーブルを作成します。
+中間テーブルは、以下のような見た目をしています。
+
+テーブル名: **movie_label_mappings**
+
+| movie_id | label_id |
+| --: | ----: |
+| 1 | 1 |
+| 1 | 2 |
+| 2 | 1 |
+| 2 | 3 |
+| 3 | 2 |
+| 3 | 1 |
+| 4 | 10 |
+| 5 | 11 |
+| 5 | 10 |
+
+
+
+```sql
+DROP TABLE IF EXISTS movie_label_mappings;
+CREATE TABLE movie_label_mappings (
+ movie_id INT,
+ label_id INT
+);
+INSERT INTO movie_label_mappings (movie_id, label_id) VALUES
+(1, 1),
+(1, 2),
+(2, 1),
+(2, 3),
+(3, 2),
+(3, 1),
+(4, 10),
+(5, 11),
+(5, 10);
+```
+
+
+
+なにやら数字だけで、人の目には非常にわかりづらいですね。
+
+しかし、映画をラベルで評価するための情報は、すべてこの中間テーブルに集約されています。
+
+例えば、`インセプション`はIDが`1`です。
+この`1`に対応する情報を`movie_label_mappings`テーブルから探してみると、ラベルIDが`1`と`2`で評価されていることがわかります。
+
+これにさらに別の`自分の好み`というラベルを追加したい場合は、以下のように中間テーブルに行を追加すればよいのです。
+
+```sql
+INSERT INTO movie_label_mappings (movie_id, label_id) VALUES (1, 10);
+```
+
+
+
+| movie_id | label_id |
+| --: | ----: |
+| 1 | 1 |
+| 1 | 2 |
+| 1 | 10 (追加) |
+
+## テーブルをつなげて表示する
+
+ここまでは、情報を適切に管理するために、IDを使ってテーブルを分ける方法を見てきました。
+しかし実際問題、このIDはだけを見ても、それがどの映画で、どのラベルなのかはわかりません。
+
+これを解決するために、`JOIN`でテーブルを繋げて表示する方法があります。
+
+`JOIN`で連携するテーブルを指定し、`ON`でどのように繋げるかの条件を指定します。
+
+```sql {3,4}
+SELECT movies.title, movie_labels.label
+FROM movies
+JOIN movie_label_mappings ON movies.id = movie_label_mappings.movie_id
+JOIN movie_labels ON movie_label_mappings.label_id = movie_labels.id;
+```
+
+
+
+```sql
+DROP TABLE IF EXISTS movies;
+DROP TABLE IF EXISTS movie_labels;
+DROP TABLE IF EXISTS movie_label_mappings;
+
+CREATE TABLE movies (
+ id INT AUTO_INCREMENT PRIMARY KEY,
+ title VARCHAR(255)
+);
+
+CREATE TABLE movie_labels (
+ id INT AUTO_INCREMENT PRIMARY KEY,
+ label VARCHAR(255)
+);
+
+CREATE TABLE movie_label_mappings (
+ movie_id INT,
+ label_id INT
+);
+
+INSERT INTO movies (title) VALUES
+('インセプション'),
+('マトリックス'),
+('天空の城ラピュタ'),
+('風の谷のナウシカ'),
+('崖の上のポニョ');
+
+INSERT INTO movie_labels (id, label) VALUES
+(1, 'おもしろい'),
+(2, '感動的'),
+(3, '難しい'),
+(4, '絵がきれい'),
+(10, '自分の好み'),
+(11, '儚い');
+
+INSERT INTO movie_label_mappings (movie_id, label_id) VALUES
+(1, 1),
+(1, 2),
+(2, 1),
+(2, 3),
+(3, 2),
+(3, 1),
+(4, 10),
+(5, 11),
+(5, 10);
+```
+
+
+
+
+
+
+
+| 作品名 | ラベル |
+| :----- | :---- |
+| インセプション | おもしろい |
+| インセプション | 感動的 |
+| マトリックス | おもしろい |
+| マトリックス | 難しい |
+| 天空の城ラピュタ | 感動的 |
+| 天空の城ラピュタ | おもしろい |
+| 風の谷のナウシカ | 自分の好み |
+| 崖の上のポニョ | 儚い |
+| 崖の上のポニョ | 自分の好み |
+
+
+
+
+
+IDを使ってテーブルを分けて管理しつつ、`JOIN`で繋げて表示することで、人間にもわかりやすい形で情報を扱うことができるようになります。
+
+ここから派生して、例えば、おもしろいと評価された映画だけを抽出するSQLは、以下のようになります。
+素直に`WHERE`で条件を指定するだけです。
+
+```sql add={5}
+SELECT movies.title, movie_labels.label
+FROM movies
+JOIN movie_label_mappings ON movies.id = movie_label_mappings.movie_id
+JOIN movie_labels ON movie_label_mappings.label_id = movie_labels.id
+WHERE movie_labels.id = 1
+```
+
+また、IDで情報を管理しているからこそ、情報をまとめやすくなっています。
+グループ化(`GROUP BY`)の機能を使って、ラベルごとに映画の数を集計することも簡単にできるようになります。
+
+ラベル毎に、紐づいている映画の数を集計するSQLは、以下のようになります。
+
+```sql add={5-7}
+SELECT movie_labels.label, COUNT(movies.id) AS movie_count
+FROM movies
+JOIN movie_label_mappings ON movies.id = movie_label_mappings.movie_id
+JOIN movie_labels ON movie_label_mappings.label_id = movie_labels.id
+GROUP BY movie_labels.id
+```
+
+
+
+| ラベル | movie_count |
+| :---- | -----------: |
+| おもしろい | 3 |
+| 感動的 | 3 |
+| 難しい | 1 |
+| 絵がきれい | 1 |
+| 自分の好み | 2 |
+| 儚い | 1 |
+
+
+
+
+
+IDを使ってテーブルを適切に分けることは、情報を効率用管理するだけでなく、情報を調査・分析する際にも非常に有用なのです。