質問をすることでしか得られない、回答やアドバイスがある。

15分調べてもわからないことは、質問しよう!

ただいまの
回答率

90.50%

  • Ruby on Rails

    7276questions

    Ruby on Railsは、オープンソースのWebアプリケーションフレームワークです。「同じことを繰り返さない」というRailsの基本理念のもと、他のフレームワークより少ないコードで簡単に開発できるよう設計されています。

  • SQL

    2394questions

    SQL(Structured Query Language)は、リレーショナルデータベース管理システム (RDBMS)のデータベース言語です。大きく分けて、データ定義言語(DDL)、データ操作言語(DML)、データ制御言語(DCL)の3つで構成されており、プログラム上でSQL文を生成して、RDBMSに命令を出し、RDBに必要なデータを格納できます。また、格納したデータを引き出すことも可能です。

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

解決済

回答 1

投稿

  • 評価
  • クリップ 0
  • VIEW 263

PartyKids

score 57

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

以前は

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

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

id メニュー
1 メニュー1
2 メニュー2
id 外部キー 外部キー 個数
1 メニュー1 ドリンク1 1
2 メニュー1 ドリンク1 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)の積集合で求めることは可能なのですが、コードも増え扱いづらいので、いっその事テーブルを一時的に変更する事が可能ならばどうだろうかと思いました。

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

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

  • 気になる質問をクリップする

    クリップした質問は、後からいつでもマイページで確認できます。

    またクリップした質問に回答があった際、通知やメールを受け取ることができます。

    クリップを取り消します

  • 良い質問の評価を上げる

    以下のような質問は評価を上げましょう

    • 質問内容が明確
    • 自分も答えを知りたい
    • 質問者以外のユーザにも役立つ

    評価が高い質問は、TOPページの「注目」タブのフィードに表示されやすくなります。

    質問の評価を上げたことを取り消します

  • 評価を下げられる数の上限に達しました

    評価を下げることができません

    • 1日5回まで評価を下げられます
    • 1日に1ユーザに対して2回まで評価を下げられます

    質問の評価を下げる

    teratailでは下記のような質問を「具体的に困っていることがない質問」、「サイトポリシーに違反する質問」と定義し、推奨していません。

    • プログラミングに関係のない質問
    • やってほしいことだけを記載した丸投げの質問
    • 問題・課題が含まれていない質問
    • 意図的に内容が抹消された質問
    • 広告と受け取られるような投稿

    評価が下がると、TOPページの「アクティブ」「注目」タブのフィードに表示されにくくなります。

    質問の評価を下げたことを取り消します

    この機能は開放されていません

    評価を下げる条件を満たしてません

    評価を下げる理由を選択してください

    詳細な説明はこちら

    上記に当てはまらず、質問内容が明確になっていない質問には「情報の追加・修正依頼」機能からコメントをしてください。

    質問の評価を下げる機能の利用条件

    この機能を利用するためには、以下の事項を行う必要があります。

回答 1

checkベストアンサー

0

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

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

-- 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で積集合みたいなことが出来るのですか?
仮にメニュ−2のwaterの個数が1だった場合、一つ目のwhereはメニュー123が該当してしまうため、結果としてメニュー123がピックアップされると思うのですが。。。。。

メニュー2の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条件で積集合を求めていると考えていただいてもよいかと思います。

投稿

編集

  • 回答の評価を上げる

    以下のような回答は評価を上げましょう

    • 正しい回答
    • わかりやすい回答
    • ためになる回答

    評価が高い回答ほどページの上位に表示されます。

  • 回答の評価を下げる

    下記のような回答は推奨されていません。

    • 間違っている回答
    • 質問の回答になっていない投稿
    • スパムや攻撃的な表現を用いた投稿

    評価を下げる際はその理由を明確に伝え、適切な回答に修正してもらいましょう。

  • 2017/10/17 18:40

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

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

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

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

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

    キャンセル

  • 2017/10/18 00:34

    回答に追記しました。

    キャンセル

  • 2017/10/18 07:31

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

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

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

    キャンセル

  • 2017/10/18 07:44 編集

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

    キャンセル

  • 2017/10/18 18:42

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

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

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

    キャンセル

15分調べてもわからないことは、teratailで質問しよう!

  • ただいまの回答率 90.50%
  • 質問をまとめることで、思考を整理して素早く解決
  • テンプレート機能で、簡単に質問をまとめられる

関連した質問

同じタグがついた質問を見る

  • Ruby on Rails

    7276questions

    Ruby on Railsは、オープンソースのWebアプリケーションフレームワークです。「同じことを繰り返さない」というRailsの基本理念のもと、他のフレームワークより少ないコードで簡単に開発できるよう設計されています。

  • SQL

    2394questions

    SQL(Structured Query Language)は、リレーショナルデータベース管理システム (RDBMS)のデータベース言語です。大きく分けて、データ定義言語(DDL)、データ操作言語(DML)、データ制御言語(DCL)の3つで構成されており、プログラム上でSQL文を生成して、RDBMSに命令を出し、RDBに必要なデータを格納できます。また、格納したデータを引き出すことも可能です。