Neo4J - How to sum up relationship values over different hop distances

user_8365493 0
Viewed 160

after playing a little in Neo4J I encountered following problem:

I have a Neo4J Database, where I store only one node type and 1 relationship type. The nodes and the relations have different properties, but they will not be relevant for my questions:

To understand better, I modeled a small version of my Problem:

enter image description here

Note: The dashed transaction value are excluded in the respective queries, because they do not originate from a new node.

Is there a way, to sum up all the relationship values for different hop distances in 1 query and group them by property?

I know how to do this in 3 different queries, 1 query for each distance, but can I also combine these 3 queries into 1 efficient query?

MATCH (a:Node {is_capital_letter: 'True'})
<-[t1]-
(b:Node {is_new: 'True'})
RETURN a.capital_letter, sum(t1.value)


MATCH (a:Node {is_capital_letter: 'True'})
<-[t1]-()
<-[t2]-
(b:Node {is_new: 'True'})
RETURN a.capital_letter, sum(t2.value)

MATCH (a:Node{is_capital_letter: 'True'})
<-[t1]-()
<-[t2]-()
<-[t3]-
(b:Node {is_new: 'True'})
RETURN a.capital_letter, sum(t3.value)

Is it possible to get sum(t1.value), sum(t2.value) and sum(t3.value) all in one query (grouped by capital_letter)?

Edit: The CREATE statements to reproduce the above example:

CREATE (a:Node {is_capital_letter: "True", capital_letter: "A"})
CREATE (b:Node {is_capital_letter: "True", capital_letter: "B"})
CREATE (n0:Node {is_new: "True"})
CREATE (n1:Node {is_new: "True"})
CREATE (n2:Node {is_new: "True"})
CREATE (n3:Node {is_new: "True"})
CREATE (n4:Node {is_new: "True"})
CREATE (n5:Node {is_new: "False"})
CREATE (n6:Node {is_new: "True"})
CREATE (n7:Node {is_new: "True"})
CREATE (n8:Node {is_new: "False"})
CREATE (n9:Node {is_new: "True"})
CREATE (n10:Node {is_new: "True"})
CREATE (n11:Node {is_new: "True"})
CREATE (n0)-[:TRANSACTIONS {value: 0}]->(a)
CREATE (n1)-[:TRANSACTIONS {value: 1}]->(a)
CREATE (n2)-[:TRANSACTIONS {value: 2}]->(a)
CREATE (n3)-[:TRANSACTIONS {value: 3}]->(a)
CREATE (n4)-[:TRANSACTIONS {value: 4}]->(a)
CREATE (n5)-[:TRANSACTIONS {value: 5}]->(a)
CREATE (n6)-[:TRANSACTIONS {value: 6}]->(n5)
CREATE (n7)-[:TRANSACTIONS {value: 7}]->(n4)
CREATE (n7)-[:TRANSACTIONS {value: 4}]->(n5)
CREATE (n8)-[:TRANSACTIONS {value: 8}]->(n3)
CREATE (n9)-[:TRANSACTIONS {value: 9}]->(n8)
CREATE (n10)-[:TRANSACTIONS {value: 1}]->(b)
CREATE (n11)-[:TRANSACTIONS {value: 2}]->(b)
neo4j
2 Answers
MATCH p=(a:Node {is_capital_letter:"True"})<-[:TRANSACTIONS*0..2]-(:Node)<-[r:TRANSACTIONS]-(:Node {is_new:"True"}) 
RETURN sum(r.value), a.capital_letter, length(p)

Should yield:

╒══════════════╤══════════════════╤═══════════╕
│"sum(r.value)"│"a.capital_letter"│"length(p)"│
╞══════════════╪══════════════════╪═══════════╡
│10            │"A"               │1          │
├──────────────┼──────────────────┼───────────┤
│9             │"A"               │3          │
├──────────────┼──────────────────┼───────────┤
│17            │"A"               │2          │
├──────────────┼──────────────────┼───────────┤
│3             │"B"               │1          │
└──────────────┴──────────────────┴───────────┘
user_2807240

If all you want is a single query (and not improved performance) you could use WITH to combine the queries.

MATCH (a:Node {is_capital_letter:"True"})<-[t1:TRANSACTIONS]-(b:Node {is_new:"True"})
WITH a, sum(t1.value) AS oneHopSum
OPTIONAL MATCH (a)<-[]-()<-[t2]-(:Node {is_new:"True"})
WITH a, oneHopSum, sum(t2.value) AS twoHopsSum
OPTIONAL MATCH (a)<-[]-()<-[]-()<-[t3]-(:Node {is_new:"True"})
RETURN a, oneHopSum, twoHopsSum, sum(t3.value) AS threeHopsSum

This produces:

╒═════════════════════════════════════════════════╤═══════════╤════════════╤══════════════╕
│"a"                                              │"oneHopSum"│"twoHopsSum"│"threeHopsSum"│
╞═════════════════════════════════════════════════╪═══════════╪════════════╪══════════════╡
│{"is_capital_letter":"True","capital_letter":"A"}│10         │17          │9             │
├─────────────────────────────────────────────────┼───────────┼────────────┼──────────────┤
│{"is_capital_letter":"True","capital_letter":"B"}│3          │0           │0             │
└─────────────────────────────────────────────────┴───────────┴────────────┴──────────────┘
user_2807240
Related