CODEWITHSUNDEEP
neo4j
Avinash
Avinash

Reputation: 801

How to calculate a rank in Neo4j

enter image description here

I am having two types of nodes (game & player) and one relationship (PLAYED). PLAYED relationship is having a property 'points'.
Sample Data:
Player (309274) scored 10 points
Player (309275) scored 20 points
Player (309276) scored 30 points
Player (309277) scored 40 points
Player (309278) scored 50 points

I want to calculate a rank of a Player 309278 i.e. 5 from the cypher query. Can anybody help me here to generate cypher query?

Upvotes: 4

Views: 2959

Answers (3)

call-in-co
call-in-co

Reputation: 281

I am answering this years later because I found myself needing a similar query and solved it with one of the functions in the fantastic APOC plugin— apoc.coll.indexOf.

MATCH (p:Player)-[pl:PLAYED]->(g:Game)
WITH p, pl.score AS score
ORDER BY score DESC
WITH collect(p) AS players
UNWIND players AS player
RETURN player, apoc.coll.indexOf(players, player) + 1 AS rank  // zero-indexed
;

If you have a large collection, you can append LIMIT N to the end of the query to get just the top N. I just ran this query on a collection of ~172k nodes and it only took just over 3 minutes to run (with Neo4j 3.5.7 and APOC 3.5.0.4 on a MacBook running MacOS version 10.14.6 with 3.1 GHz i7 and 16GB of RAM); obviously YMMV.

I hope that this helps someone looking for how to calculate ranks in Neo4j!

Upvotes: 2

Goujon
Goujon

Reputation: 186

Based on Michael Hunger's answer, the following query computes the rank and returns the player and their rank on the same row instead of on alternating rows:

MATCH (p:Player)-[pl:PLAYED]->(:Game {id:{game-id}})
WITH p, pl
ORDER BY pl.score desc
WITH collect(p) as players
MATCH (p2:Player)-[pl2:PLAYED]->(:Game {id:{game-id}})
RETURN p2, size(filter(x in players where pl2.score < x.score)) + 1

The way it works is that after ordering the players by their score, then - for each player p2 - it counts the number of players that scored higher, which equals p2's 0-based rank.

The caveat is that, with a complexity of O(n²), it's very inefficient if the number of players is large. The advantage is that it allows for further processing of the rank in relation to the player, e.g. store their rank on the relationship:

SET pl2.rank = 1 + size(filter(x in players where pl2.score < x.score))

which can be queried later on without the need for futher calculations:

MATCH (p:Player)-[pl:PLAYED]->(:Game {id:{game-id}})
RETURN pl.rank

Upvotes: 2

Michael Hunger
Michael Hunger

Reputation: 41676

MATCH (p:Player)-[pl:PLAYED]->(:Game {id:{game-id}})
RETURN p.name
ORDER BY pl.score desc

then the row-number is your rank, which your client can compute easily

to access a certain rank:

MATCH (p:Player)-[pl:PLAYED]->(:Game {id:{game-id}})
RETURN p.name
ORDER BY pl.score desc
SKIP 4 LIMIT 1

to compute the rank, you'd probably, do something like this (not efficient):

MATCH (p:Player)-[pl:PLAYED]->(:Game {id:{game-id}})
WITH p,pl
ORDER BY pl.score desc
// create a a collection
WITH collect(p) as players
UNWIND reduce(acc=[],idx in range(0,size(players)-1) | 
                     acc + [idx+1,players[idx]]) as player_rank
RETURN player_rank

Upvotes: 6

Related Questions