Aggregate functions-PolarDB(PolarDB)-阿里云帮助中心

Aggregate functions operate on a set of values and return a single result. This page covers the aggregate functions supported by the auto aggregation feature.

Function summary

Function	Syntax	Returns	Null handling
`min()`	`min(expression)`	Minimum value in the set	Nulls excluded; `min(null)` returns `null`
`max()`	`max(expression)`	Maximum value in the set	Nulls excluded; `max(null)` returns `null`
`count()`	`count(*)` or `count(expression)`	Number of records or non-null values	`count(*)` includes nulls; `count(null)` returns `0`
`avg()`	`avg(expression)`	Average of numeric values	Nulls excluded; `avg(null)` returns `null`
`sum()`	`sum(expression)`	Sum of numeric values	Nulls excluded; `sum(null)` returns `null`
`stDev()`	`stDev(expression)`	Sample standard deviation	Nulls excluded; `stDev(null)` returns `0.0`
`stDevP()`	`stDevP(expression)`	Population standard deviation	Nulls excluded; `stDevP(null)` returns `0.0`
`percentileCont()`	`percentileCont(expression, percentile)`	Percentile via linear interpolation	Nulls excluded; `percentileCont(null, percentile)` returns `null`
`percentileDisc()`	`percentileDisc(expression, percentile)`	Percentile via rounding	Nulls excluded; `percentileDisc(null, percentile)` returns `null`

Prepare data

The examples on this page use the following graph. Run these statements to set it up:

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)

Return value

A property type or a list, depending on the values returned by expression.

Parameters

Parameter	Description
`expression`	An expression that returns a set containing any combination of property types and lists.

Usage notes

Null values are excluded from the calculation.
In a mixed set, strings are always less than numeric values, and lists are always less than strings.
Lists are compared in dictionary order: elements are compared pair by pair from the start of the list.
min(null) returns null.

Example

SELECT *
FROM cypher('graph_name', $$
    MATCH (v:Person)
    RETURN min(v.age)
$$) as (min_age agtype);

Returns the minimum age across all Person nodes:

 min_age
---------
 13
(1 row)

Use min() with lists

Run the following statements to prepare data for this example:

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:[1, 'b', 23]})
$$) as (result agtype);

Find the minimum value across a mixed set:
```
SELECT *
FROM cypher('graph_name', $$
    MATCH (v:min_test)
    RETURN min(v.val)
$$) as (min_val agtype);
```
Result:
```
 min_val
----------------
 ["a", "b", 23]
(1 row)
```
The result is ["a", "b", 23] because: (i) both lists are considered less than the string 'd', and (ii) among the two lists, ['a', 'b', 23] is less than [1, 'b', 23] because the string 'a' is less than the numeric value 1.

max

max() returns the maximum value in a set of values.

Syntax

max(expression)

Return value

A property type or a list, depending on the values returned by expression.

Parameters

Parameter	Description
`expression`	An expression that returns a set containing any combination of property types and lists.

Usage notes

Null values are excluded from the calculation.
In a mixed set, strings are always less than numeric values, and lists are always less than strings.
Lists are compared in dictionary order: elements are compared pair by pair from the start of the list.
max(null) returns null.

Example

SELECT *
FROM cypher('graph_name', $$
    MATCH (n:Person)
    RETURN max(n.age)
$$) as (max_age agtype);

Returns the maximum age across all Person nodes:

 max_age
---------
 44
(1 row)

stDev

stDev() returns the sample standard deviation of a numeric expression within a group. It uses a standard two-pass method with N - 1 as the denominator, which gives an unbiased estimate when working with a sample of a population. To calculate the standard deviation of an entire population, use stDevP().

Syntax

stDev(expression)

Return value

An agtype floating-point number.

Parameters

Parameter	Description
`expression`	An agtype numeric expression.

Usage notes

Null values are excluded from the calculation.
stDev(null) returns 0.0.

Example

SELECT *
FROM cypher('graph_name', $$
   MATCH (n:Person)
   RETURN stDev(n.age)
$$) as (stdev_age agtype);

Returns the sample standard deviation of age across all Person nodes:

    stdev_age
--------------------
 15.716233645501712
(1 row)

stDevP

stDevP() returns the population standard deviation of a numeric expression within a group. It uses the two-pass method with N as the denominator. Use this function when your data represents the entire population. To calculate an unbiased estimate from a sample, use stDev().

Syntax

stDevP(expression)

Return value

An agtype floating-point number.

Parameters

Parameter	Description
`expression`	An agtype numeric expression.

Usage notes

Null values are excluded from the calculation.
stDevP(null) returns 0.0.

Example

SELECT *
FROM cypher('graph_name', $$
    MATCH (n:Person)
    RETURN stDevP(n.age)
$$) as (stdevp_age agtype);

Returns the population standard deviation of age across all Person nodes:

    stdevp_age
--------------------
 12.832251036613439
(1 row)

percentileCont

percentileCont() returns the value at a given percentile within a group using linear interpolation. If the target percentile falls between two values, the function returns a weighted average of those two values. Percentile values range from 0.0 to 1.0. To get the nearest actual value using rounding instead, use percentileDisc().

Syntax

percentileCont(expression, percentile)

Return value

An agtype floating-point number.

Parameters

Parameter	Description
`expression`	An agtype numeric expression.
`percentile`	The target percentile. An agtype number between `0.0` and `1.0`.

Usage notes

Null values are excluded from the calculation.
percentileCont(null, percentile) returns null.

Example

SELECT *
FROM cypher('graph_name', $$
    MATCH (n:Person)
    RETURN percentileCont(n.age, 0.4)
$$) as (percentile_cont_age agtype);

Returns the 40th percentile of age across all Person nodes, calculated using linear interpolation:

 percentile_cont_age
---------------------
 29.0
(1 row)

percentileDisc

percentileDisc() returns the value at a given percentile within a group using a rounding method, selecting the nearest actual value in the dataset. Percentile values range from 0.0 to 1.0. To get an interpolated value instead, use percentileCont().

Syntax

percentileDisc(expression, percentile)

Return value

An agtype floating-point number.

Parameters

Parameter	Description
`expression`	An agtype numeric expression.
`percentile`	The target percentile. An agtype number between `0.0` and `1.0`.

Usage notes

Null values are excluded from the calculation.
percentileDisc(null, percentile) returns null.

Example

SELECT *
FROM cypher('graph_name', $$
    MATCH (n:Person)
    RETURN percentileDisc(n.age, 0.5)
$$) as (percentile_disc_age agtype);

Returns the 50th percentile of age across all Person nodes:

 percentile_disc_age
---------------------
 33.0
(1 row)

count

count() returns the number of values or records. It has two variants:

count(*) — counts all matching records, including those with null values.
count(expression) — counts only the non-null values returned by the expression.

Syntax

count(expression)

Return value

An agtype integer.

Parameters

Parameter	Description
`expression`	An expression whose non-null results are counted. Use `*` to count all records.

Usage notes

count(*) includes records where the expression evaluates to null.
count(expression) ignores null values.
count(null) returns 0.

Examples

Count related nodes and group by relationship type

The following two queries use count(*). All examples use MATCH against the graph created in Prepare data.

-- Count the nodes that A knows
SELECT *
FROM cypher('graph_name', $$
    MATCH (n {name: 'A'})-[]->(x)
    RETURN n.age, count(*)
$$) as (age agtype, number_of_people agtype);

 age | number_of_people
-----+------------------
 13  | 3
(1 row)

-- Group by relationship type
SELECT *
FROM cypher('graph_name', $$
    MATCH (n {name: 'A'})-[r]->()
    RETURN type(r), count(*)
$$) as (label agtype, count agtype);

   label  | count
----------+-------
 "KNOWS"  | 3
(1 row)

Count non-null values with count(expression)

count(expression) counts only the non-null values returned by the expression, unlike count(*) which counts all records.

-- Count nodes connected to A
SELECT *
FROM cypher('graph_name', $$
    MATCH (n {name: 'A'})-[]->(x)
    RETURN count(x)
$$) as (count agtype);

 count
-------
 3
(1 row)

-- Count Person nodes with a non-null age value
SELECT *
FROM cypher('graph_name', $$
    MATCH (n:Person)
    RETURN count(n.age)
$$) as (count agtype);

 count
-------
 3
(1 row)

Count with and without DISTINCT

DISTINCT removes duplicates before counting. The following example finds friends-of-friends of node A and compares the count with and without deduplication:

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);

 friend_of_friends_distinct | friend_of_friends
----------------------------+-------------------
 1                          | 2
(1 row)

Both B and C know D. Without DISTINCT, D is counted twice.

avg

avg() returns the average of a set of numeric values.

Syntax

avg(expression)

Return value

An agtype floating-point number.

Parameters

Parameter	Description
`expression`	An expression that returns a set of numeric values.

Usage notes

Null values are excluded from the calculation.
avg(null) returns null.

Example

SELECT *
FROM cypher('graph_name', $$
MATCH (n:Person)
RETURN avg(n.age)
$$) as (avg_age agtype);

Returns the average age across all Person nodes:

 avg_age
---------
 30.0
(1 row)

sum

sum() returns the sum of a set of numeric values.

Syntax

sum(expression)

Return value

An agtype floating-point number.

Parameters

Parameter	Description
`expression`	An expression that returns a set of numeric values.

Usage notes

Null values are excluded from the calculation.
sum(null) returns null.

Example

SELECT *
FROM cypher('graph_name', $$
MATCH (n:Person)
RETURN sum(n.age)
$$) as (total_age agtype);

Returns the sum of age across all Person nodes:

 total_age
-----------
 90
(1 row)