PostgreSQL，用于通过配方计算成分的复杂查询 [英] PostgreSQL, complex query for calculating ingredients by recipe

问题描述

我必须计算存储在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，用于通过配方计算成分的复杂查询的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！