I’m working with a database that has two key columns:
recipe
: lists all recipes and sub-recipes,name_article
: contains the articles (ingredients) associated with each recipe or sub-recipe.
The issue I’m facing is calculating the unit price for these articles. Sometimes, the unit price for a name_article
is NULL
. In such cases, I need to find its price by looking at the recipe
column, where the recipe
value matches the name_article
. Then, I need to sum up the prices of the articles related to that name_article
.
The complication is that this recipe
can itself contain sub-recipes, which creates a recursive structure. I need to handle this recursion to compute the correct unit price at all levels of the sub-recipes.
Here’s an example of the data I’m working with:
recipe | name_article | id_establishment | quantity | price_unit_ht_2 |
---|---|---|---|---|
X | Z | 2 | 0.3 | NULL |
Z | W | 2 | 0.02 | 2.4275 |
Z | F | 2 | 0.01 | NULL |
F | A | 2 | 0.08 | 4.2 |
F | B | 2 | 0.1 | NULL |
F | C | 2 | 0.2 | NULL |
C | J | 2 | 0.4 | 2.3 |
B | K | 2 | 0.2 | 1.56 |
For example, the unit price for Z
is NULL
, so I need to calculate it by summing the prices of its ingredients, W
and F
. However, the price for F
is also NULL
, which means I need to look at F
‘s ingredients (A
, B
, and C
). Since B
and C
also have NULL
prices, I continue the process: B
is linked to K
and C
is linked to J
, both of which have unit prices.
In this case, the unit price for Z
would be the sum of the unit prices of W
, A
, K
, and J
.
Here’s the expected output:
Here is the updated table with the calculated unit prices:
recipe | name_article | id_establishment | quantity | price_unit_ht_2 |
---|---|---|---|---|
X | Z | 2 | 0.30 | 10.4875 |
Z | W | 2 | 0.02 | 2.4275 |
Z | F | 2 | 0.01 | 8.0600 |
F | A | 2 | 0.08 | 4.2000 |
F | B | 2 | 0.10 | 1.5600 |
F | C | 2 | 0.20 | 2.3000 |
C | J | 2 | 0.40 | 2.3000 |
B | K | 2 | 0.20 | 1.5600 |
How can I handle this recursive structure to calculate the correct unit price for each article using BQuery or SQL ?
Any help would be greatly appreciated 🙏🥺
You need to sign in to view this answers
Leave feedback about this