GraphAcademy

Cypher Aggregations

Understanding Aggregation in Cypher

Working with Lists

Aggregating Functions

Calculating Min/Max Values

How min()/max() works

Unlike the aggregation functions sum() and avg(), the min and max functions evaluate values that could represent mixed types. The types could be numeric, strings, or lists. Like sum() and avg(), null elements are ignored.

Mixed element types

A numeric value is always greater than a string or list value. This query returns 2 because it is the greatest numeric value.

cypher
UNWIND [[2],0,"a",2,[1]] AS x
RETURN max(x)

String values are always greater than a list value. Because strings are always greater than lists, this query returns "b"

cypher
UNWIND [["a"],"a","b",[1]] AS x
RETURN max(x)

List element comparisons

If lists are being compared, the elements are compared in the order in the list. This query returns [1,3,5] because the element comparison of the second element is greatest and no other elements in the list are compared.

cypher
UNWIND [[1,2,12], [1,3,5]] AS x
RETURN max(x)

Using min()/max() effectively

Because min() and max() use eager aggregation in their query plans, you should profile your queries and work to improve their performance.

Here is an example where we determine the maximum top vote value using max() and then use this value to find the movie:

cypher
PROFILE MATCH (m:Movie)
WITH max(m.imdbVotes) as topVotes
MATCH (m:Movie) where m.imdbVotes = topVotes
RETURN m.title, m.imdbVotes

Using max() may not be the best solution. This query can be improved:

cypher
PROFILE MATCH (m:Movie)
WHERE m.imdbVotes IS NOT NULL
RETURN m.title, m.imdbVotes ORDER by m.imdbVotes DESC LIMIT 1

Use of indexes

If the property that you are comparing with min() or max() has an index on it, the index is used to return the value which is very efficient. In our graph the Movie node has an index on imdbRating so this query is very fast because the index is used to determine min/max, rather than aggregating the property values.

cypher
PROFILE MATCH (m:Movie)
RETURN max(m.imdbRating)

Check your understanding

1. Elements used for min() and max()

What types of elements are compared with min() and max()?

  • ✓ Integer

  • ✓ Float

  • ✓ List

  • ✓ String

  • ❏ null

Hint

Null elements are not used in the calculation.

Solution

The correct answers are Integer, Float, List, and String.

2. What does min() return?

Given this list:

[[2,0],0,"a",2,null,[2,1]]

What is the result of calling min() on its elements?

  • ❏ "a"

  • ✓ [2,0]

  • ❏ 0

  • ❏ null

  • ❏ [2,1]

  • ❏ 2

Hint

Null elements are not used in the calculation. List elements are evaluated in element order until a minimum value is found. Numeric values are greater than strings and lists. String values are greater than lists.

Solution

The correct answer is [2,0]

Summary

In this lesson, you reviewed and learned how minimum and maximum values are determined during a query. In the next Challenge, you will create a query that uses min() to find the minimum value.