SQL Recursive sum-CodePudding

I have a Table that looks like this :

It's a video game craft recipes :

(item_id = 1) retail price is 10000, in order to craft it you need 10 (resources_id = 5) which costs 50 each and 20 (resource_id = 6) which costs 50 each
(item_id = 2) retail price is 150, in order to craft it you need 1 (item_id = 1) which costs 10000 and 5 (resource_id = 8) which costs 50 each
(item_id = 3) retail price is 5500, in order to craft it you need 50 (item_id = 1) which costs 10000 each, 1 (item_id = 2) which costs 150 and 3 (resource_id = 9) which costs 50 each

My goal is to figure out if it is profitable to buy the item directly (retail price), to craft it (by buying the necessary resources and items) or to craft it (by buying the necessary resources and crafting the necessary items) .

So I'd want an output that looks likes :

Sample data in Source DATA screenshot :

CREATE TABLE store(
    item_id integer,
    item_price real,
    quantity integer,
    uses_item_id integer,
    uses_item_price real,
    resource_id integer,
    resource_price real
);
INSERT INTO store (item_id, item_price, quantity, uses_item_id, uses_item_price, resource_id, resource_price)
VALUES (1, 10000, 10, null, null, 5, 50 ),
(1, 10000, 20, null, null, 6, 50 ),
(2, 150, 1, 1, 10000, null, null),
(2, 150, 5, null, null, 8, 50 ),
(3, 500, 3, null, null, 9, 50 ),
(3, 5500, 50, 1, 10000, null, null ),
(3, 5500, 1, 2, 150, null, null );

SQL fiddle link

The partial_craft_price column is quite simple to do :

select
    item_id,
    item_price as retail_price,
    sum(coalesce(uses_item_price, 0) * quantity)   sum(coalesce(resource_price*quantity, 0))  as partial_craft_price
FROM store
GROUP BY item_id, retail_price;

But, I can't really figure out of a way to compute full_craft_price using SQL only. I thought about using Recursive CTEs, but I'm not experiences enough with them :/

Can anyone help me up please ?

Thanks alot

CodePudding user response：

That's a good use case of recursive CTEs.

You can compute the full craft price by appending the required resources (id, price and quantity) of each item that's needed for a craft to the iteam you're crafting.

This is achievable with something like :


with RECURSIVE cte as (
  SELECT item_id, item_price, quantity,
  resource_id, resource_price
  FROM store
  
  UNION ALL
  
  SELECT s.item_id, s.item_price, c.quantity,
  c.resource_id, c.resource_price
  FROM store s
  INNER JOIN cte c ON c.item_id = s.uses_item_id
) SELECT 
        item_id, 
        sum(coalesce(resource_price*quantity, 0))  as full_craft_price
FROM cte
GROUP BY item_id;