料理レシピテーブルと原料テーブルから料理のカロリーを計算する方法（内積計算のSQLでの実現）

Question

以下表のように、レシピテーブルと原料テーブルがあったとします。

この表を元に、例えばカレーのカロリーであったり、豚汁の原価を計算したいと思っています。このような要素の掛け算の和（内積計算）をMySQL実現するには、どのような関数や構文を使ったらいいでしょうか？

カレーのカロリー=Σ(各材料の使用量)*(各材料の単位重量あたりのカロリー)、という感じです。

なお、実際のテーブルは、レシピの列と原料の行は百程度あります。
（本題から逸れますが、そもそもこのレシピテーブルだと、原料追加によってカラムが際限なく増えていくので、ベターな方法があれば指摘いただけたらとも思います）

---

レシピテーブル（単位はすべてg）
|レシピ名|人参|じゃがいも|大根|豚肉|牛肉|
|:--|:--:|--:|
|カレー|50|100|0|0|100|
|豚汁|30|20|20|20|0|

---

原料テーブル

材料名	カロリー(cal/g)	単価(円/g)
人参	30	20
じゃがいも	50	10
大根	10	20
豚肉	40	40
牛肉	100	100
鶏肉	50	30

Answer 1

レシピテーブルは正規化が必要ですね。
料理

料理名
カレー
豚汁

レシピ

料理名	材料名	量
カレー	人参	50
カレー	じゃがいも	100
カレー	牛肉	100
豚汁	人参	30
豚汁	じゃがいも	20
豚汁	大根	20
豚汁	豚肉	20

後は原料テーブルと材料で結合して集計すれば良いだけです。

Comments

[MagMag]

なるほど。SQL関数の問題ではなく、正規化の問題だったんですね！テーブル構造をアドバイス通りに変更したら実現できました。ありがとうございました。