我必须计算存储在PostgreSQL表中的食物和用过的食物的成分:
I have to calculate food and ingredients of used food stored in PostgreSQL tables:
table1 'usedfood' food food used used code name qty meas ---------------------------------------------- 10 spaghetti 3 pcs 156 mayonnaise 2 pcs 173 ketchup 1 pcs 172 bolognese sauce 2 pcs 173 ketchup 1 pcs 10 spaghetti 2 pcs 156 mayonnaise 1 pcs table2 'ingredients' food ingr. ingredient qty meas code code name /1 in 1 ---------------------------------------------- 10 1256 spaghetti rinf 75 gramm 156 1144 salt 0.3 gramm 10 1144 salt 0.5 gramm 156 1140 fresh egg 50 gramm 172 1138 tomato 80 gramm 156 1139 mustard 5 gramm 172 1136 clove 1 gramm 156 1258 oil 120 gramm 172 1135 laurel 0.4 gramm 10 1258 oil 0.4 gramm 172 1130 corned beef 40 gramm结果:
used times code food/ingredient qty meas ---------------------------------------------- 5 1256 spaghetti rinf 375 gramm 8 1258 oil 362 gramm 2 1138 tomato 160 gramm 3 1140 fresh egg 150 gramm 2 1130 corned beef 80 gramm 3 1139 mustard 15 gramm 8 1144 salt 3.4 gramm 2 1136 clove 2 gramm 2 1135 laurel 0.8 gramm 2 173 ketchup 2 pcs //haven't any ingredients现在,我通过遍历table1并查询每一行的table2,然后添加结果,以此类推(使用C)来处理大型数据,这可能非常慢。
For now I do this by looping through table1 and queriying table2 for each row then adding results and so on (with C) what may be very slow on larger data.
表1包含食品代码,食品名称和使用量。 表2包含成分(乱序),其成分和代码表示一种食品的和平度,并且其中也出现了食品代码。 表1中的使用量应乘以表2中的数量,根据每个食谱中都应添加到成分代码的结果中。 因此,进入食物意大利面的所有成分行均以食物意大利面代码(10)开头。 没有任何食物的食物成分应使用表1中的数量计算并显示相同的名称。这实际上意味着它是最终产品(如啤酒瓶)。 这可能更复杂,但是我很想问。 例如,在ingredinets列表中,可能是他自己配制的成分。例如芥末中含有醋,盐,种子等。然后呢? 在所示示例芥末的表2中,将其用作成品(组件)。
Table1 contains food code, food name and used quantity. Table2 contains ingredients (in messy order) with code and used quantity for one peace of food and also code of food in which appears. Used quantity from table1 should be multiplied with quantity from table2 according to every recipe and should be added to result of ingredients code. So all ingredient rows which goes to food "spaghetti" starts with food spaghetti code (10). Food without any ingredient should be calculated with quantity from table1 and showed with same name. That actually mean it is a final product (like beer bottle). Here may be more complication but I'm, affraid to ask. For example in ingredinets list may be ingredient which is recipe by himself. For example mustard which contains from vinegar, salt, seed, etc... What then? In table2 of showed example mustard is used as ready product (component).
是否可以通过仅使用PostgreSQL即可快速进行计算并获得结果的任何方法?PostgreSQL可以为C程序提供现成的结果吗? 也许不是似乎对我来说很复杂?该查询的外观如何?
Is here any way for do such calculation and getting results fast with using just PostgreSQL which will give ready results to C program? Maybe not too complex as seem's for me? How would that query look like?
推荐答案尝试
SELECT SUM(f.qty) used_times, COALESCE(i.ingr_code, f.food_code) code, COALESCE(i.name, f.name) name, SUM(COALESCE(i.qty, 1) * f.qty) qty, COALESCE(i.meas, f.meas) meas FROM usedfood f LEFT JOIN ingredients i ON f.food_code = i.food_code GROUP BY i.ingr_code, i.name输出:
| USED_TIMES | CODE | NAME | QTY | MEAS | ---------------------------------------------------- | 2 | 173 | ketchup | 2 | pcs | | 2 | 1130 | corned beef | 80 | gramm | | 2 | 1135 | laurel | 0.8 | gramm | | 2 | 1136 | clove | 2 | gramm | | 2 | 1138 | tomato | 160 | gramm | | 3 | 1139 | mustard | 15 | gramm | | 3 | 1140 | fresh egg | 150 | gramm | | 8 | 1144 | salt | 3.4 | gramm | | 5 | 1256 | spaghetti rinf | 375 | gramm | | 8 | 1258 | oil | 362 | gramm |这里是 SQLFiddle 演示
Here is SQLFiddle demo
更多推荐
PostgreSQL,用于通过配方计算成分的复杂查询
发布评论