I'd like to know how can I use cypher like SQL function "rank over partition by".
For example, I want to get 1st rank data by subject, class.
I'd appreciate advice.
Ex)
Class Subject Score
1 Math 99 (Y)
1 Math 95
1 English 98 (Y)
1 English 97
2 Math 96 (Y)
2 Math 94
2 English 96 (Y)
2 English 93
Assuming you want to do, for example, a rank over "Class" partition by "Subject",
I think you could execute something like that:
MATCH (node:Test)
WHERE node:Test // filter only node with the wanted label
with apoc.coll.sortNodes(collect(node), 'class') as partition, node.subject as partitionName // sort nodes by partition
call {
with partition
call {
with partition
unwind partition as n
return collect(n.class) as nodes // create list of class
}
unwind partition as n
set n.rank = apoc.coll.indexOf(nodes, n.class) + 1 // set rank
return nodes, n
}
return nodes, n // return nodes