基于Cypher查询计算BOM层级中各组件的成本占比

Got it, let's break down how to calculate each component's cost contribution percentage to the finished good (fg:part) in your Neo4j BOM structure. I'll cover two approaches that align with your logic, plus edge cases to watch out for.

Approach 1: Direct Cumulative Contribution Calculation #

This method calculates the total cost a component contributes to the finished good directly, then derives the percentage relative to the finished good's total cost. It's efficient and aligns with the end goal of seeing each part's impact on the final product.

// Match all paths from any part to a finished good (fg:part)
MATCH path = (part:part)-[:component_of*]->(fg:part)
// Optional: Uncomment below to only include lowest-level components (no children)
// WHERE NOT EXISTS((part)-[:component_of]->())
// Calculate total quantity of the part needed for one finished good
WITH part, fg, 
     reduce(total_qty = 1, rel IN relationships(path) | total_qty * rel.quantity) AS total_quantity,
     fg.cost AS fg_total_cost
// Skip finished goods with zero cost to avoid division errors
WHERE fg_total_cost > 0
// Compute total contribution and percentage
WITH part, fg, 
     (part.cost * total_quantity) AS total_contribution_cost,
     fg_total_cost
RETURN 
  part.id AS part_id,
  part.name AS part_name,
  fg.id AS finished_good_id,
  fg.name AS finished_good_name,
  total_contribution_cost,
  fg_total_cost,
  round((total_contribution_cost / fg_total_cost) * 100, 2) AS contribution_pct_to_fg
ORDER BY finished_good_id, contribution_pct_to_fg DESC

How it works: #

• reduce() iterates through all relationships in the path to multiply together the quantities required at each level, giving the total number of the part needed for one finished good.
• Multiply that total quantity by the part's unit cost to get the total cost contribution to the finished good.
• Divide by the finished good's total cost and multiply by 100 to get the percentage, rounded to two decimal places for readability.

Approach 2: Step-by-Step Parent-to-Final Contribution #

If you specifically want to follow the logic of first calculating each part's contribution to its direct parent, then accumulating those percentages up to the finished good, this recursive approach does just that.

// Step 1: Calculate each part's contribution percentage to its direct parent
MATCH (child:part)-[rel:component_of]->(parent:part)
WHERE parent.cost > 0
WITH child, parent, rel,
     ((child.cost * rel.quantity) / parent.cost) AS parent_contribution_ratio

// Step 2: Recursively accumulate ratios up to the finished good
MATCH path = (child)-[:component_of*]->(fg:part)
// Ensure we're only targeting finished goods (no parent components)
WHERE NOT EXISTS((fg)-[:component_of]->())
WITH child, fg,
     reduce(cumulative_ratio = 1.0, rel_step IN relationships(path) | 
            cumulative_ratio * ((startNode(rel_step).cost * rel_step.quantity) / endNode(rel_step).cost)
           ) AS total_contribution_ratio
WHERE fg.cost > 0

// Format results as percentages
RETURN 
  child.id AS part_id,
  child.name AS part_name,
  fg.id AS finished_good_id,
  fg.name AS finished_good_name,
  round(total_contribution_ratio * 100, 2) AS contribution_pct_to_fg
ORDER BY finished_good_id, contribution_pct_to_fg DESC

How it works: #

• First, we calculate the ratio of each child part's cost (times quantity) to its parent's total cost.
• Then, we use reduce() on the full path from the part to the finished good to multiply all these parent-level ratios together. This gives the cumulative ratio of the part's cost to the finished good's cost.
• Convert the ratio to a rounded percentage for the final output.

Key Notes & Edge Cases #

• Avoid division by zero: Always include WHERE parent.cost > 0 and WHERE fg.cost > 0 to prevent runtime errors from zero-cost parts or finished goods.
• Path limits: If your BOM has deep hierarchies or potential cycles (uncommon in real BOMs, but possible), add a length limit to the relationship path like [:component_of*1..10] to prevent infinite loops.
• Include intermediate components: If you want to see how sub-assemblies contribute to the finished good, remove the WHERE NOT EXISTS((part)-[:component_of]->()) filter in Approach 1. This will return contributions for every level of the BOM.

内容的提问来源于stack exchange，提问作者Chad