我问了一个问题earlier https://stackoverflow.com/questions/28036055/recursive-query-with-sub-graph-aggreagation关于沿着图表聚合数量。提供的两个答案效果很好,但现在我正在尝试将 Cypher 查询扩展到可变深度的图。
总而言之,我们从一堆叶子商店开始,它们都与特定的供应商相关联,这是Store
节点。然后,库存被转移到其他商店,每个供应商的比例对应于他们对原始商店的贡献。
所以对于节点B02
, S2
贡献了750/1250 = 60%
and S3
贡献了40%
。然后我们移动 600 个单位B02
其中60%
属于S2
and 40%
to S3
等等。
我们想知道最后 700 个单位的百分比是多少D01
属于每个供应商。其中同名供应商为同一供应商。因此对于上图我们期望:
S1, 38.09
S2,27.61
S3,34.28
我已经使用这个 Cypher 脚本准备了一个图表:
CREATE (A01:Store {Name: 'A01', Supplier: 'S1'})
CREATE (A02:Store {Name: 'A02', Supplier: 'S1'})
CREATE (A03:Store {Name: 'A03', Supplier: 'S2'})
CREATE (A04:Store {Name: 'A04', Supplier: 'S3'})
CREATE (A05:Store {Name: 'A05', Supplier: 'S1'})
CREATE (A06:Store {Name: 'A06', Supplier: 'S1'})
CREATE (A07:Store {Name: 'A07', Supplier: 'S2'})
CREATE (A08:Store {Name: 'A08', Supplier: 'S3'})
CREATE (B01:Store {Name: 'B01'})
CREATE (B02:Store {Name: 'B02'})
CREATE (B03:Store {Name: 'B03'})
CREATE (B04:Store {Name: 'B04'})
CREATE (C01:Store {Name: 'C01'})
CREATE (C02:Store {Name: 'C02'})
CREATE (D01:Store {Name: 'D01'})
CREATE (A01)-[:MOVE_TO {Quantity: 750}]->(B01)
CREATE (A02)-[:MOVE_TO {Quantity: 500}]->(B01)
CREATE (A03)-[:MOVE_TO {Quantity: 750}]->(B02)
CREATE (A04)-[:MOVE_TO {Quantity: 500}]->(B02)
CREATE (A05)-[:MOVE_TO {Quantity: 100}]->(B03)
CREATE (A06)-[:MOVE_TO {Quantity: 200}]->(B03)
CREATE (A07)-[:MOVE_TO {Quantity: 50}]->(B04)
CREATE (A08)-[:MOVE_TO {Quantity: 450}]->(B04)
CREATE (B01)-[:MOVE_TO {Quantity: 400}]->(C01)
CREATE (B02)-[:MOVE_TO {Quantity: 600}]->(C01)
CREATE (B03)-[:MOVE_TO {Quantity: 100}]->(C02)
CREATE (B04)-[:MOVE_TO {Quantity: 200}]->(C02)
CREATE (C01)-[:MOVE_TO {Quantity: 500}]->(D01)
CREATE (C02)-[:MOVE_TO {Quantity: 200}]->(D01)
当前的查询是这样的:
MATCH (s:Store { Name:'D01' })
MATCH (s)<-[t:MOVE_TO]-()<-[r:MOVE_TO]-(supp)
WITH t.Quantity as total, collect(r) as movements
WITH total, movements, reduce(totalSupplier = 0, r IN movements | totalSupplier + r.Quantity) as supCount
UNWIND movements as movement
RETURN startNode(movement).Supplier as Supplier, round(100.0*movement.Quantity/supCount) as pct
我正在尝试使用递归关系,大致如下:
MATCH (s)<-[t:MOVE_TO]-()<-[r:MOVE_TO*]-(supp)
然而,这提供了通往终端节点的多条路径,我认为需要聚合每个节点的库存。