CODEWITHSUNDEEP
recursionneo4jcypher
NeddySpaghetti
NeddySpaghetti

Reputation: 13495

Recursive query with sub-graph aggregation

I am trying to use Neo4j to write a query that aggregates quantities along a particular sub-graph.

We have two stores Store1 and Store2 one with supplier S1 the other with supplier S2. We move 100 units from Store1 into Store3 and 200 units from Store2 to Store3.

We then move 100 units from Store3 to Store4. So now Store4 has 100 units and approximately 33 originated from supplier S1 and 66 from supplier S2.

enter image description here

I need the query to effectively return this information, E.g.

S1, 33
S2, 66

I have a recursive query to aggregate all the movements along each path 

MATCH p=(store1:Store)-[m:MOVE_TO*]->(store2:Store { Name: 'Store4'}) 
RETURN store1.Supplier, reduce(amount = 0, n IN relationships(p) | amount + n.Quantity) AS reduction

Returns:

| store1.Supplier | reduction|
|-------------------- |-------------|
| S1 | 200 |
| S2 | 300 |
| null | 100 |

Desired:

| store1.Supplier | reduction|
|---------------------|-------------|
| S1 | 33.33 |
| S2 | 66.67 |

Upvotes: 1

Views: 214

Answers (2)

Christophe Willemsen
Christophe Willemsen

Reputation: 20175

What about this one :

MATCH (s:Store) WHERE s.name = 'Store4'
MATCH (s)<-[t:MOVE_TO]-()<-[r:MOVE_TO]-(supp)
WITH t.qty as total, collect(r) as movements
WITH total, movements, reduce(totalSupplier = 0, r IN movements | totalSupplier + r.qty) as supCount
UNWIND movements as movement
RETURN startNode(movement).name as supplier, round(100.0*movement.qty/supCount) as pct

Which returns :

supplier    pct
Store1  33
Store2  67
Returned 2 rows in 151 ms

Upvotes: 4

Pat
Pat

Reputation: 409

So the following is pretty ugly, but it works for the example you've given.

MATCH (s4:Store { Name:'Store4' })<-[r1:MOVE_TO]-(s3:Store)<-[r2:MOVE_TO*]-(s:Store) 
WITH s3, r1.Quantity as Factor, SUM(REDUCE(amount = 0, r IN r2 | amount + r.Quantity)) AS Total
MATCH (s3)<-[r1:MOVE_TO*]-(s:Store) 
WITH s.Supplier as Supplier, REDUCE(amount = 0, r IN r1 | amount + r.Quantity) AS Quantity, Factor, Total 
RETURN Supplier, Quantity, Total, toFloat(Quantity) / toFloat(Total) * Factor as Proportion

I'm sure it can be improved.

Upvotes: 3

Related Questions