Aggregation Functions
Functions that activate auto aggregation.
Data Setup
LOAD 'age';
SET search_path TO ag_catalog;
SELECT create_graph('graph_name');
SELECT * FROM cypher('graph_name', $$
CREATE (a:Person {name: 'A', age: 13}),
(b:Person {name: 'B', age: 33, eyes: "blue"}),
(c:Person {name: 'C', age: 44, eyes: "blue"}),
(d1:Person {name: 'D', eyes: "brown"}),
(d2:Person {name: 'D'}),
(a)-[:KNOWS]->(b),
(a)-[:KNOWS]->(c),
(a)-[:KNOWS]->(d1),
(b)-[:KNOWS]->(d2),
(c)-[:KNOWS]->(d2)
$$) as (a agtype);
min
min() returns the minimum value in a set of values.
Syntax: min(expression)
Returns:
A property type, or a list, depending on the values returned by expression.
Arguments:
| Name | Description |
| expression | An expression returning a set containing any combination of property types and lists thereof. |
Considerations:
Any null values are excluded from the calculation.
In a mixed set, any string value is always considered to be lower than any numeric value, and anylist is always considered to be lower than any string.
Lists are compared in dictionary order, i.e. list elements are compared pairwise in ascending order from the start of the list to the end.
min(null) returns null.
Query
SELECT *
FROM cypher('graph_name', $$
MATCH (v:Person)
RETURN min(v.age)
$$) as (min_age agtype);
Result:
| min_age |
| 2123e1af756543542064ae0d07792be90176b311be |
| 1 row(s) returned |
Using min() with Lists
Data Setup:
To clarify the following example, assume the next three commands are run first:
SELECT * FROM cypher('graph_name', $$
CREATE (:min_test {val:'d'})
$$) as (result agtype);
SELECT * FROM cypher('graph_name', $$
CREATE (:min_test {val:['a', 'b', 23]})
$$) as (result agtype);
SELECT * FROM cypher('graph_name', $$
CREATE (:min_test {val:['a', 'b', 23]})
$$) as (result agtype);
Query
SELECT *
FROM cypher('graph_name', $$
MATCH (v:min_test)
RETURN min(v.val)
$$) as (min_val agtype);
The lowest of all the values in the set—in this case, the list [‘a’, ‘c’, 23]—is returned, as (i) the two lists are considered to be lower values than the string “d”, and (ii) the string “a” is considered tobe a lower value than the numerical value 1.
Result:
| min_age |
| ["a", "b", 23] |
| 1 row(s) returned |
max
max() returns the maximum value in a set of values.
Syntax: max(expression)
Returns:
A property type, or a list, depending on the values returned by expression.
Arguments:
| Name | Description |
| expression | An expression returning a set containing any combination of property types and lists thereof. |
Considerations:
Any null values are excluded from the calculation.
In a mixed set, any numeric value is always considered to be higher than any string value, and anystring value is always considered to be higher than any list.
Lists are compared in dictionary order, i.e. list elements are compared pairwise in ascending order from the start of the list to the end.
max(null) returns null.
Query:
SELECT *
FROM cypher('graph_name', $$
MATCH (n:Person)
RETURN max(n.age)
$$) as (max_age agtype);
The highest of all the values in the property age is returned.
Result:
| min_age |
| 44 |
| 1 row(s) returned |
stDev
stDev() returns the standard deviation for the given value over a group. It uses a standard two-pass method, with N - 1 as the denominator, and should be used when taking a sample of the population for an unbiased estimate. When the standard variation of the entire population is being calculated, stdDevP should be used.
Syntax: stDev(expression)
Returns:
An agtype Float.
Arguments:
| Name | Description |
| expression | An agtype number expression |
Considerations:
Any null values are excluded from the calculation.
stDev(null) returns 0.
Query
SELECT *
FROM cypher('graph_name', $$
MATCH (n:Person)
RETURN stDev(n.age)
$$) as (stdev_age agtype);
The standard deviation of the values in the property age is returned.
Result:
| stdev_age |
| 15.716233645501712 |
| 1 row(s) returned |
stDevP
stDevP() returns the standard deviation for the given value over a group. It uses a standard two-pass method, with N as the denominator, and should be used when calculating the standard deviation for an entire population. When the standard variation of only a sample of the population is being calculated, stDev should be used.
Syntax: stDevP(expression)
Returns:
An agtype Float.
Arguments:
| Name | Description |
| expression | An agtype number expression |
Considerations:
Any null values are excluded from the calculation.
stDevP(null) returns 0.
Query
SELECT *
FROM cypher('graph_name', $$
MATCH (n:Person)
RETURN stDevP(n.age)
$$ as (stdevp_age agtype);
The population standard deviation of the values in the property age is returned.
Result:
| stdevp_age |
| 12.832251036613439 |
| 1 row(s) returned |
percentileCont
percentileCont() returns the percentile of the given value over a group, with a percentile from 0.0 to 1.0. It uses a linear interpolation method, calculating a weighted average between two values if the desired percentile lies between them. For nearest values using a rounding method, see percentileDisc.
Syntax: percentileCont(expression, percentile)
Returns:
An agtype Float.
Arguments:
| Name | Description |
| expression | An agtype number expression |
| percentile | An agtype number value between 0.0 and 1.0 |
Considerations:
Any null values are excluded from the calculation.
percentileCont(null, percentile) returns null.
Query
SELECT *
FROM cypher('graph_name', $$
MATCH (n:Person)
RETURN percentileCont(n.age, 0.4)
$$ as (percentile_cont_age agtype);
The 40th percentile of the values in the property age is returned, calculated with a weighted average. In this case, 0.4 is the median, or 40th percentile.
Result:
| percentile_cont_age |
| 29.0 |
| 1 row(s) returned |
percentileDisc
percentileDisc() returns the percentile of the given value over a group, with a percentile from 0.0to 1.0. It uses a rounding method and calculates the nearest value to the percentile. For interpolated values, see percentileCont.
Syntax: percentileDisc(expression, percentile)
Returns:
An agtype Float.
Arguments:
| Name | Description |
| expression | An agtype number expression |
| percentile | An agtype number value between 0.0 and 1.0 |
Considerations:
Any null values are excluded from the calculation.
percentileDisc(null, percentile) returns null.
Query
SELECT *
FROM cypher('graph_name', $$
MATCH (n:Person)
RETURN percentileDisc(n.age, 0.5)
$$ as (percentile_disc_age agtype);
The 50th percentile of the values in the property age is returned.
Result:
| percentile_cont_age |
| 33 |
| 1 row(s) returned |
count
count() returns the number of values or records, and appears in two variants:
count(*) returns the number of matching records
count(expr) returns the number of non-null values returned by an expression.
Syntax: count(expression)
Returns:
An agtype Integer.
Arguments:
| Name | Description |
| expression | An expression |
Considerations:
count(*) includes records returning null.
count(expr) ignores null values.
count(null) returns 0.
Using count(*) to return the number of nodes
count(*) can be used to return the number of nodes; for example, the number of nodes connected to some node n.
Query
SELECT *
FROM cypher('graph_name', $$
MATCH (n {name: 'A'})-[]->(x)
RETURN n.age, count(*)
$$ as (age agtype, number_of_people agtype);
The labels and age property of the start node n and the number of nodes related to n are returned.
Result:
| age | number_of_people |
| 13 | 3 |
| 1 row(s) returned | |
Using count() to group and count relationship typescount() can be used to group relationship types and return the number.
Query
SELECT *
FROM cypher('graph_name', $$
MATCH (n {name: 'A'})-[r]->()
RETURN type(r), count(*)
$$ as (label agtype, count agtype);
The relationship types and their group count are returned.
Result:
| label | count |
| “KNOWS” | 3 |
| 1 row(s) returned | |
Using count(expression) to return the number of values
Instead of simply returning the number of records with count(*), it may be more useful to return the actual number of values returned by an expression.
Query
SELECT *
FROM cypher('graph_name', $$
MATCH (n {name: 'A'})-[]->(x)
RETURN count(x)
$$) as (count agtype);
The number of nodes connected to the start node is returned.
Result:
| count |
| 3 |
| 1 row(s) returned |
Counting non-null values
count(expression) can be used to return the number of non-null values returned by the expression.
Query
SELECT *
FROM cypher('graph_name', $$
MATCH (n:Person)
RETURN count(n.age)
$$) as (count agtype);
The number of :Person nodes having an age property is returned.
Result:
| count |
| 3 |
| 1 row(s) returned |
Counting with and without duplicates
In this example we are trying to find all our friends of friends, and count them:
The first aggregate function, count(DISTINCT friend_of_friend), will only count a friend_of_friend once, as DISTINCT removes the duplicates.
The second aggregate function, count(friend_of_friend), will consider the same friend_of_friend multiple times.
Query
SELECT *
FROM cypher('graph_name', $$
MATCH (me:Person)-[]->(friend:Person)-[]->(friend_of_friend:Person)
WHERE me.name = 'A'
RETURN count(DISTINCT friend_of_friend), count(friend_of_friend)
$$) as (friend_of_friends_distinct agtype, friend_of_friends agtype);
Both B and C know D and thus D will get counted twice when not using DISTINCT
Result:
| friend_of_friends_distinct | friend_of_friends |
| 1 | 2 |
| 1 row |
avg
avg() returns the average of a set of numeric values.
Syntax: avg(expression)
Returns:
An agtype Integer
Arguments:
| Name | Description |
| expression | An expression returning a set of numeric values. |
Considerations:
Any null values are excluded from the calculation.
avg(null) returns null.
Query
SELECT *
FROM cypher('graph_name', $$
MATCH (n:Person)
RETURN avg(n.age)
$$) as (avg_age agtype);
The average of all the values in the property age is returned.
Result:
| avg_age |
| 30.0 |
| 1 row(s) returned |
sum
sum() returns the sum of a set of numeric values.
Syntax: sum(expression)
Returns:
An agtype Float
Arguments:
| Name | Description |
| expression | An expression returning a set of numeric values. |
Considerations:
Any null values are excluded from the calculation.
sum(null) returns 0.
Query
SELECT *
FROM cypher('graph_name', $$
MATCH (n:Person)
RETURN sum(n.age)
$$ as (total_age agtype);
The sum of all the values in the property age is returned.
Result:
| total_age |
| 90 |
| 1 row(s) returned |