cancel
Showing results for 
Search instead for 
Did you mean: 

How would you call for the counts of nodes that fall into different ranges?

geronimo4j
Graph Buddy

Hi I have User and Rating nodes where users can leave ratings for different things. I'm interested in graphing the distribution of users' ratings, thinking because ratings can be > 0 and <=5 I can do it in 5 tiers:

0, 1, 2, 3, 4-5 stars

And essentially end up with something like:

0 - .999... = 14 ratings, average rating .58
1 - 1.999 = 11 ratings, average rating: 1.8
2 - 2.999 = 9 ratings, average rating: 2.2
etc.

Curious how you would set up a query to do this? One idea is to just have 5 separate calls where each looks for the count in the 5 ranges, is there another way to return the ratings for a user separated by range? And have some of this aggregate type info

Thank you in advance!

2 REPLIES 2

giuseppe_villan
Graph Fellow

@geronimo4j

You could return all ranges at once with this query:

UNWIND [[0,1], [1,2], [2,3], [3,4], [4,5]] as range // list of lists
MATCH (n:User)-->(r:Rating) // match rating
WHERE r.rating > range[0] and r.rating <= range[1]  // filter rating (from 0 to 1, from 1 to 2, etc.....)
RETURN range, count(r.rating), avg(r.rating) // return range, count and average

where I created a list of ranges, then I UNWIND it and after where I returned average and count.


With a dataset like this:
create (u1:User {id: 1})-[:HAS_RATE]->(:Rating {rating: 1}), (u1)-[:HAS_RATE]->(:Rating {rating: 1.5})
create (:User {id: 2})-[:HAS_RATE]->(:Rating {rating: 2.2})
create (:User {id: 3})-[:HAS_RATE]->(:Rating {rating: 2})
create (:User {id: 4})-[:HAS_RATE]->(:Rating {rating: 3.1})
create (:User {id: 5})-[:HAS_RATE]->(:Rating {rating: 4.4})
create (:User {id: 6})-[:HAS_RATE]->(:Rating {rating: 4.6})
create (:User {id: 7})-[:HAS_RATE]->(:Rating {rating: 5})
create (:User {id: 8})-[:HAS_RATE]->(:Rating {rating: 0.3})

it returns:

range	| count(r.rating)	    | avg(r.rating)
[0, 1]	| 2	                    | 0.65
[1, 2]	| 2	                    | 1.75
[2, 3]	| 1	                    | 2.2
[3, 4]	| 1	                    | 3.1
[4, 5]	| 3	                    | 4.666666666666667

Wow this is really cool, so essentially you're calculating all of it one by one, I just read about UNWIND and it looks very good, have never used it before.

Thank you very much, looks like it does exactly what I'm after!

Happy and healthy holidays!

Nodes 2022
Nodes
NODES 2022, Neo4j Online Education Summit

On November 16 and 17 for 24 hours across all timezones, you’ll learn about best practices for beginners and experts alike.