CODEWITHSUNDEEP
neo4jcyphergraph-databaseswith-clause
zanbri
zanbri

Reputation: 6006

Cypher – How to carry variables through WITH pipe

I have the following query:

START e1=node:event(prop="0")
MATCH e1-[r:rbel]->e2
WITH e1, e2, count(e1) as ecount
MATCH e1-[:redge]->p<-[:redge]-e2
WITH p.element_type as Type, p.label as Label, (count(p)*100./ecount) as percentage
WHERE percentage > 20
RETURN Type, Label, ROUND(percentage) as Percentage

I am trying to calculate the percentage of times the specified pattern occurs in events with prop="0", over all patterns occurring in those events.

I receive the following error: Unknown identifier 'ecount'

So I replaced ecount in the calculation with count(ecount), and that consistently yielded percentages of 100%, which I know not to be true.

Am I going about this wrong? How can I carry the value of ecount to the WITH clause and use it in calculation?

Any help is appreciated!

Upvotes: 3

Views: 3642

Answers (2)

zanbri
zanbri

Reputation: 6006

h3nrik's solution worked perfectly in the console setup example below, however, for some reason it failed to work when applied to my actual data in my localhost data browser. I found the following work-around, despite having a slower query time:

START e1=node:event(prop="0") 
MATCH e1-[:rbel]->e2 
WITH count(e1) as ecount
START e1=node:event(prop="0") 
MATCH e1-[:rbel]->e2, e1-[:redge]->p<-[:redge]-(e2)
WITH p.label AS Label, p.element_type as Type, ecount, count(p)*100./ecount AS percentage 
WHERE percentage > 20 
RETURN Label, Type, ROUND(percentage) as Percentage

Upvotes: 1

Henrik Sachse
Henrik Sachse

Reputation: 54292

Does this query work for you? Whenever I combine e1 and count(e1) in a WITH statement, the count(e1) is always 1. I think that is because the count(e1) aggregation does not work any more when you select e1, too. Either you leave out the e1 or the count(e1).

START e1=node:event(prop="0")
MATCH e1-[r:rbel]->e2
WITH e1, e2
MATCH e1-[:redge]->p<-[:redge]-e2
WITH p.element_type as Type, p.label as Label, (count(p)*100./count(e1)) as percentage
WHERE percentage > 20
RETURN Type, Label, ROUND(percentage) as Percentage

UPDATE After playing around with your provided console setup I got the following query working:

START e1=node:node_auto_index(prop="0") 
MATCH e1-[r:rbel]->e2 
WITH COLLECT(e2) AS e2collection, count(e1) AS cnt 
MATCH e1-[:redge]->p<-[:redge]-(e2) 
WITH p, COLLECT(e1) AS e1collection, cnt, e2collection 
WITH p.name AS Name, cnt, count(p)*100/cnt AS Percentage 
WHERE Percentage > 20 
RETURN Name, Percentage

Upvotes: 4

Related Questions