How to calculate a unit price with a recursive Tree structure in a database

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

Related Post