Bill of Materials / Hierarchical SQL to find lowest cost

Viewed 37

Here I have a BOM problem. The data in a tree format:

1 (value:100) -> 2 (value: 2)  -> 4 (value: 1)
                               -> 5 (value: 10) -> 6 (value: 1) 
              -> 3 (value: 1)

Each item's value can be obtained by following either of two rules:

  1. get the item by its value e.g. here item 5 has value 10
  2. get the item by the total sum of its children (instead of by its value) e.g. here item 5 will be the value of its child (item 5 will get the value of item 6, i.e. value 1).

So how would I build the value of item 1 based on the above rules? Item 1 can cost just 3.

I am familiar with recursion in SQL, however I am not sure how to extend my answer to my specific case. I can also change the data format, however I cannot think of another data format which would help.

The following is an example of traversal with my data, still this does not give me what I want i.e. cheapest cost for item 1.

with recursive tree_data as
(
    select 1 parent_id, 2 child_id, 2 child_cost
    union
    select 1 parent_id, 3 child_id, 1 child_cost
    union 
    select 2 parent_id, 4 child_id, 1 child_cost
    union
    select 2 parent_id, 4 child_id, 1 child_cost
    union
    select 2 parent_id, 5 child_id, 10 child_cost
    union
    select 5 parent_id, 6 child_id, 1 child_cost
), traversal as
(
    select *, 0 node_cost
    from tree_data
    where parent_id = 1
    union
    select tree_data.*, traversal.node_cost + tree_data.child_cost
    from tree_data
    inner join traversal on tree_data.parent_id = traversal.child_id
)
select * from traversal

To be clear, a variant of this output would be of interest. Even just solving this for the root would be interesting (i.e. show only line 1).

parent_id cheapest_value actual_value
1 3 100
2 2 2
3 1 1
4 1 1
5 1 10
6 1 1
0 Answers
Related