CODEWITHSUNDEEP
neo4jcypher
Jack Daniel
Jack Daniel

Reputation: 2611

Neo4j - Need to aggregate values on different relationships between same pair of nodes

I have to multiply the properties on two different relationships between same pair of nodes and SUM() it,then ORDER BY the pairs based on the value. Ex:

Let (X:amps)-[:coocr{val:1}]->b
    (X:amps)-[:jacc{dist:2}]->b
    (X:amps)-[:coocr{val:2}]->c
    (X:amps)-[:jacc{dist:3}]->c
    (X:amps)-[:coocr]->d
    (X:amps)-[:jacc{dist:4}]->d
    (Y:amps)-[:coocr{val:2}]->b
    (Y:amps)-[:jacc{dist:3}]->b
    (Y:amps)-[:coocr{val:1}]->c
    (Y:amps)-[:jacc{dist:4}]->c
    (Y:amps)-[:coocr{val:4}]->d
    (Y:amps)-[:jacc{dist:3}]->d

Now, 1) Each node b,c,d has two relationships with X,Y, which are multiplies and added pair wise 2)Return the highest value

My attempt to the prob:

WITH [b,c,d] AS words                                                                
MATCH (i:amps) 
MATCH n where n.word in words
MATCH p=(i-[r:jaccard]->(n)) with i,r.dist as dist UNWIND dist as distances
MATCH q=(i-[s:coocr]->(n)) with distances,i,s.val as co UNWIND co as coocr
WITH i, SUM(distances*coocr) AS agg
RETURN i,agg ORDER BY agg DESC

My aggragate function has to work like:

X-[]->b has[jaccard,coocr] , so, jacc.dist*coocr.val = 1*2 =2
X-[]->c has[jaccard,coocr] , so, jacc.dist*coocr.val = 2*3 =6
X-[]->d has[jaccard,] , so, jacc.dist*null = null*2 =0
                                                    --------------
                                                      sum  = 8
                                                    --------------

Y-[]->b has[jaccard,coocr] , so, jacc.dist*coocr.val = 4*2 =8
Y-[]->c has[jaccard,coocr] , so, jacc.dist*coocr.val = 1*3 =3
Y-[]->d has[jaccard,coocr] , so, jacc.dist*coocr.val = 4*4 =16
                                                    --------------
                                                      sum  = 27
                                                    --------------

Then returning these aggregate orderly.

My query returning the aggregates, which are only products, not the whole sum. Need to achieve the whose sum.

Upvotes: 1

Views: 725

Answers (2)

Jack Daniel
Jack Daniel

Reputation: 2611

WITH ["best", "high", "quality","4k"] AS words                                                                
MATCH (i:amps) 
MATCH n where n.word in words
OPTIONAL MATCH p=(i-[r:jaccard]->(n)) with n,i,COALESCE(r.dist, 0)  as distances
OPTIONAL MATCH q=(i-[s:coocr]->(n)) with distances,n,i,COALESCE(s.val, 0) AS  coocr
WITH i,n,distances,coocr, (distances*coocr) AS agg
WITH i,SUM(agg) AS agg
RETURN i,agg

Aggregation function,in last but one line resolved the problem. Thanks for the suggestion Brain.

Upvotes: 0

Brian Underwood
Brian Underwood

Reputation: 10856

What about this?

MATCH (a:amps)
OPTIONAL MATCH (a)-[coocr:coocr]-(n)
OPTIONAL MATCH (a)-[jacc:jacc]-(n)
WITH a, COALESCE(coocr.val, 0) AS val, COALESCE(jacc.dist, 0) AS dist
RETURN a, SUM(val * dist) AS agg
ORDER BY agg DESC

Upvotes: 2

Related Questions