テーブルを一時的に変形する方法

Question

閲覧ありがとうございます！

以前は

id	メニュー	Water	POCARI SWEAT
1	メニュー１	1	1
2	メニュー2	2	2
3	メニュー3	1	1

のようなテーブルを作成していたのですが、これだとDBの保守には相応しく無いと本に書いてあったので、

id	メニュー
1	メニュー１
2	メニュー2

id	外部キー	外部キー	個数
1	メニュー１	ドリンク１	1
2	メニュー１	ドリンク１	1

id	ドリンク
1	Water
2	POCARI SWEAT

といった、中間テーブルを設けて、勉強しています。

問題点

以前のテーブル構造だとwhere(water=1, POCARI SWEAT=1)みたいな書き方をすれば、条件を複数設定することが出来ました。
しかし、中間テーブルの場合だとjoinを使っても

id	メニュー	個数	ドリンク
	メニュー1	1	Water
	メニュー1	1	POCARI SWEAT
	メニュー2	2	Water

というテーブルが作成されてしまうため、以前の様なSQLで検索出来ません。
where(water=1)とwhere(POCARI SWEAT=1)の積集合で求めることは可能なのですが、コードも増え扱いづらいので、いっその事テーブルを一時的に変更する事が可能ならばどうだろうかと思いました。

テーブル構造は中間テーブルを使っているが、検索時には以前のテーブル構造に変換する事は可能ですか？

よろしくお願いいたします！

Answer 1

元のテーブルが何を意味するのかが質問からは把握できませんでした。
売上なのか、セットメニューなのかといったところかと推測しています。
そのため、どのようなテーブル構造がよいのかという判断はできていません。

また、分割後のテーブルは、元のテーブルのデータ構造を正規化したものとなっていません。
つまり同じものとして扱うことはできないので、そのままでは元のテーブルを再現することはできません。
列名を動的に与える方法をすぐには思いつきませんでしたのでドリンク名は固定としていますが、下記のようにすれば元のテーブルの再現は可能かと思います。

-- CREATE TABLE menus (id INT AUTO_INCREMENT PRIMARY KEY, menu VARCHAR(16));
-- INSERT INTO menus (menu) VALUES ('メニュー1'), ('メニュー2'), ('メニュー3');

-- CREATE TABLE drinks (id INT AUTO_INCREMENT PRIMARY KEY, drink VARCHAR(16));
-- INSERT INTO drinks (drink) VALUES ('Water'), ('POCARI SWEAT');

-- CREATE TABLE menudrink (id INT AUTO_INCREMENT PRIMARY KEY, menu_id INT, drink_id INT, number INT);
-- INSERT INTO menudrink (menu_id, drink_id, number) VALUES (1, 1, 1), (1, 2, 1), (2, 1, 2), (2, 2, 2), (3, 1, 1), (3, 2, 1);

SELECT m.id, m.menu, md1.number `Water`, md2.number `POCARI SWEAT` FROM menus m
INNER JOIN menudrink md1 ON m.id = md1.menu_id AND md1.drink_id = 1
INNER JOIN menudrink md2 ON m.id = md2.menu_id AND md2.drink_id = 2
ORDER BY 1;

+----+---------------+-------+--------------+
| id | menu          | Water | POCARI SWEAT |
+----+---------------+-------+--------------+
|  1 | メニュー1     |     1 |            1 |
|  2 | メニュー2     |     2 |            2 |
|  3 | メニュー3     |     1 |            1 |
+----+---------------+-------+--------------+

SELECT * FROM
(SELECT m.id, m.menu, md1.number 'Water', md2.number 'POCARI SWEAT' FROM menus m
INNER JOIN menudrink md1 ON m.id = md1.menu_id AND md1.drink_id = 1
INNER JOIN menudrink md2 ON m.id = md2.menu_id AND md2.drink_id = 2) tmp
WHERE `Water` = 1 AND `POCARI SWEAT` = 1
ORDER BY 1;

+----+---------------+-------+--------------+
| id | menu          | Water | POCARI SWEAT |
+----+---------------+-------+--------------+
|  1 | メニュー1     |     1 |            1 |
|  3 | メニュー3     |     1 |            1 |
+----+---------------+-------+--------------+

ただし、この構造だとドリンクの数だけ列が増えていくことになります。
Water、POCARI SWEATの個数がそれぞれ1のメニューを取得したいということであれば、下記のようになるのではないかと考えられます。

SELECT m.id, m.menu FROM menus m
WHERE EXISTS (SELECT md.id FROM menudrink md WHERE m.id = md.menu_id AND drink_id = 1 AND md.number = 1)
  AND EXISTS (SELECT md.id FROM menudrink md WHERE m.id = md.menu_id AND drink_id = 2 AND md.number = 1)
ORDER BY 1;

+----+---------------+
| id | menu          |
+----+---------------+
|  1 | メニュー1     |
|  3 | メニュー3     |
+----+---------------+

どちらの場合も、プログラム側で動的にSQLを組み立てることになりますので、扱いやすい方を選ばれるとよいかと思います。

---

追記：

今回のデータを考える場合、出発点となる非正規テーブルは下記のような形となるのかと思います。

• セットメニュー

|id|メニュー名|ドリンク1|個数|ドリンク2|個数|
|:--|:--:|--:|
|1|メニュー1|Water|1|POCARI SWEAT|1|
|2|メニュー2|Water|2|POCARI SWEAT|2|
|3|メニュー3|Water|1|POCARI SWEAT|1|

このテーブルをデータの重複や繰り返しをなくす形で分割した場合、質問に記載の3テーブルへの分割は、正規化後の構造として問題ないものと思われます。
正規化の妥当性を検証するためには、分割後のテーブルから上記のテーブルが作成できることを確認されるとよいかと思います。
前述しているSQLにドリンク名が表示されるように組み立てると、再現できるかと思います。

一番最後のSQLが理解できないです。なぜこのSQLで積集合みたいなことが出来るのですか？
仮にメニュ−２のwaterの個数が１だった場合、一つ目のwhereはメニュー123が該当してしまうため、結果としてメニュー１２３がピックアップされると思うのですが。。。。。

メニュー２のWaterの個数が1、POCARI SWEATの個数が2の場合を考えます。
この場合、下記のSQLの結果としてメニュー2も含まれるようになります。

SELECT md.id FROM menudrink md WHERE m.id = md.menu_id AND drink_id = 1 AND md.number = 1

ただし、下記のSQLについては、POCARI SWEATの個数が2であるためにメニュー2のidは結果に含まれません。

SELECT md.id FROM menudrink md WHERE m.id = md.menu_id AND drink_id = 2 AND md.number = 1

EXISTSは結果が1行でも存在すれば真(true)、0件であれば偽(false)となる演算で、上記2つのSELECT文をWHERE句のAND条件で使用しています。
AND条件ですので、どちらか一方が真となっても、もう一方が偽であればWHERE句の抽出条件としては成立しません。
ここではAND条件で積集合を求めていると考えていただいてもよいかと思います。

Comments

[PartyKids]

コメントありがとうございます！

>何を意味するのかが質問からは把握できません
セットメニューを意味しています。命名が適当で申し訳ございませんでした。

>どのようなテーブル構造がよい
本には重複するもの、及び可変するものは外部キーを使った方が良いと書かれていました。
ドリンク名は増えていくであろうと予想し、ドリンクだけのテーブルを作成し、個数を中間テーブルに収めてみました。（個数をどこに納めればいいのか悩みましたが中間が一番いいと考えました。）

すみません。一番最後のSQLが理解できないです。なぜこのSQLで積集合みたいなことが出来るのですか？
仮にメニュ−２のwaterの個数が１だった場合、一つ目のwhereはメニュー123が該当してしまうため、結果としてメニュー１２３がピックアップされると思うのですが。。。。。

勉強不足で申し訳ございませんが、よろしくお願いいたします！

[退会済みユーザー]

回答に追記しました。

[PartyKids]

おはようございます！
追記ありがとうございました！

＞正規化後の構造として問題ないもの
ありがとうございます。この前、本を読んで正規化を学んだので、自分なりに考えて設計してみたのですが、上手くいって良かったです。

＞WHERE句の抽出条件としては成立しません。
多分、select?where?の動きをまだ理解していないため、suyamaさんが仰っている意味を理解できなかったんだと思います。最初のselectで、Menuから１行取り出し、それを次の２つのselec(where)tに当てはめて真偽を求めている。っといったかんじですか？
副問合せは、ネストされた部分から展開すると本に書かれていたので（あやふやですが。。。）、whereの後のselectから進めるものだと思っていました。なので最初は、Waterが１個とPOCARI SWEATが２個のMenuDrink.idをすべて取得した後に、Menuを取得するものだと思っていました。

[退会済みユーザー]

> 副問合せは、ネストされた部分から展開する
> 最初のselectで、Menuから１行取り出し
上記の認識で間違っていません。
副問合せは、その前に主となる問い合わせの1行を取り出していることを意識する必要があります。
Menuの1行についての、それぞれの副問い合わせ（サブクエリ）が実行されます。
上に記載しているSQLでは、サブクエリで参照している m.id は主となる問い合わせのデータを参照しています。
1行ごとにさらに2回のSQLを実行しているということなので、10行だと+20回、100行あれば+200回となり、副問い合わせは一般的にコストの大きいものになります。
不用意に使用すると不必要に重い処理となってしまうので扱いには注意が必要です。

[PartyKids]

返信ありがとうございます！

＞主となる問い合わせの1行を取り出していることを意識する
そういう事だったんですね。サブクエリがselect内にある場合と、where内にある場合とでは、考え方が異なってきますね。

助けて頂き、本当に有難うございました！
今後ともよろしくお願いいたします！