Aggregation-PolarDB(PolarDB)-阿里云帮助中心

The aggregate function aggr(expr) processes all rows that share the same aggregation key. Keys are compared using equivalence. In a regular aggregation, null values are excluded from the list of aggregated values before the function runs.

Prepare data

Run the following query to create the sample graph used in the examples below:

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

Auto group by

Cypher aggregation works like GROUP BY in SQL: non-aggregate expressions in a RETURN or WITH clause automatically become grouping keys, and aggregate functions compute one result per group.

For example, avg calculates the average of a set of numeric values, and min finds the smallest value in a set of numeric or string values. Both operate on the values produced by applying the inner expression (such as n.age) to all records within the same group.

Example: Group vertices by name and count matches

SELECT * FROM cypher('graph_name', $$
    MATCH (v:Person)
    RETURN v.name, count(*)
$$) as (grouping_key agtype, count agtype);

Result:

 grouping_key | count
--------------+-------
 "A"          | 1
 "D"          | 2
 "Joan"       | 1
 "B"          | 1
 "John"       | 1
 "Bill"       | 1
 "C"          | 1
 "Jeff"       | 1
(8 rows)

In this query, v.name is a non-aggregate expression, so AGE treats it as the grouping key. count(*) is the aggregate expression. AGE divides the matched vertices into buckets by v.name and computes count(*) for each bucket.

Sort results by an aggregate value

To sort by an aggregated value, include it in the RETURN clause and reference it in ORDER BY.

SELECT *
FROM cypher('graph_name', $$
    MATCH (me:Person)-[]->(friend:Person)
    RETURN count(friend), me
    ORDER BY count(friend)
$$) as (friends agtype, me agtype);

DISTINCT aggregation

aggr(DISTINCT expr) removes null values and duplicate equivalent values before running the aggregate function. Only one value from each equivalence class is kept.

The following example compares counting behaviors:

Behavior	Description
`count(v.eyes)`	Counts all non-null values, including duplicates
`count(DISTINCT v.eyes)`	Counts only unique values after deduplication

Example: Compare `count(DISTINCT ...)` and `count(...)`

SELECT *
FROM cypher('graph_name', $$
 MATCH (v:Person)
 RETURN count(DISTINCT v.eyes), count(v.eyes)
$$) as (distinct_eyes agtype, eyes agtype);

Result:

 distinct_eyes | eyes
---------------+------
 2             | 3
(1 row)

Three vertices have a non-null eyes property ("blue", "blue", "brown"), so count(v.eyes) returns 3. count(DISTINCT v.eyes) deduplicates equivalent values, leaving only two distinct colors ("blue" and "brown"), so it returns 2.

Ambiguous grouping statements

How implicit grouping keys work

Cypher infers grouping keys implicitly from the expressions in a RETURN or WITH clause: any expression that is not inside an aggregate function becomes a grouping key. When aggregate and non-aggregate expressions are mixed in a single column expression, it can be unclear which sub-expressions AGE should treat as grouping keys.

The root cause of ambiguity: if a non-aggregated variable appears in the same column expression as an aggregate function, Cypher must decide whether that variable is a grouping key or just part of the expression. Two queries that look similar can return different row counts depending on how the variables are positioned. For a detailed discussion of the underlying semantics, see the openCypher community documentation.

AGE resolves the ambiguity with a strict rule: a WITH or RETURN column cannot mix aggregate functions with variables that are not explicitly listed as a separate column in the same clause.

Prepare data

SELECT * FROM cypher('graph_name', $$
CREATE (:L {a: 1, b: 2, c: 3}),
       (:L {a: 2, b: 3, c: 1}),
       (:L {a: 3, b: 1, c: 2})
$$) as (a agtype);

Invalid queries in AGE

Mixing aggregate functions with unlisted variables in a single expression is not allowed.

Example: Invalid — variables `x.a`, `x.b`, and `x.c` are not listed separately

SELECT * FROM cypher('graph_name', $$
    MATCH (x:L)
    RETURN x.a + count(*) + x.b + count(*) + x.c
$$) as (a agtype);

Result:

ERROR:  "x" must be either part of an explicitly listed key or used inside an aggregate function
LINE 3:     RETURN x.a + count(*) + x.b + count(*) + x.c

Valid queries in AGE

AGE treats any column that contains no aggregate function as a grouping key. The following two rewrites make the grouping keys explicit.

Example 1: Combine non-aggregate variables into one expression and list it as a separate column

SELECT * FROM cypher('graph_name', $$
    MATCH (x:L)
    RETURN (x.a + x.b + x.c) + count(*) + count(*), x.a + x.b + x.c
$$) as (count agtype, key agtype);

x.a + x.b + x.c is the grouping key. Grouping key expressions created this way must be wrapped in parentheses.

 count | key
-------+-----
 12    | 6
(1 row)

Example 2: List each variable as a separate column

SELECT * FROM cypher('graph_name', $$
    MATCH (x:L)
    RETURN x.a + count(*) + x.b + count(*) + x.c, x.a, x.b, x.c
$$) as (count agtype, a agtype, b agtype, c agtype);

x.a, x.b, and x.c are each treated as a separate grouping key, producing one row per unique combination.

 count | a | b | c
-------+---+---+---
 8     | 3 | 1 | 2
 8     | 2 | 3 | 1
 8     | 1 | 2 | 3
(3 rows)

Use a vertex or edge as a grouping key

A vertex or edge can serve as the grouping key. When the grouping key is a vertex or edge, any of its properties can appear in the same expression without being listed as a separate column.

Example: Group by vertex `x`

SELECT * FROM cypher('graph_name', $$
    MATCH (x:L)
    RETURN count(*) + count(*) + x.a + x.b + x.c, x
$$) as (count agtype, key agtype);

AGE groups results by x. Because x is listed as a separate column, its properties (x.a, x.b, x.c) are considered part of the grouping key and do not need to be listed individually.

 count |                                          key
-------+----------------------------------------------------------------------------------------
 8     | {"id": 1407374883553283, "label": "L", "properties": {"a": 3, "b": 1, "c": 2}}::vertex
 8     | {"id": 1407374883553281, "label": "L", "properties": {"a": 1, "b": 2, "c": 3}}::vertex
 8     | {"id": 1407374883553282, "label": "L", "properties": {"a": 2, "b": 3, "c": 1}}::vertex
(3 rows)

Hide unwanted grouping keys from output

If the grouping key is needed for computation but not for the final output, compute the aggregation in a WITH clause and pass only the result column to RETURN.

Example: Aggregate in `WITH`, return only the computed value

SELECT * FROM cypher('graph_name', $$
    MATCH (x:L)
    WITH count(*) + count(*) + x.a + x.b + x.c as column, x
    RETURN column
$$) as (a agtype);

Result:

 a
---
 8
 8
 8
(3 rows)