概述
GROUP BY
从句可用来指定对最终表分组时使用的分组键。
The GROUP BY
clause allows you to specify the set of grouping keys to be used during grouping on the final table.
<return statement> ::=
"RETURN" [ "DISTINCT" ] { <"*"> | <return item list> }
[ <group by clause> ]
<group by clause> ::= "GROUP BY" <grouping key list>
<grouping key list> ::=
<grouping key> [ { "," <grouping key> }... ]
<grouping key> ::= <binding variable>
详情
-
每个分组键必须为绑定变量。如果分组键并非已有绑定变量,有以下两种选择:
- 使用
AS
重命名:如果分组键是最终结果表的一部分,可以使用关键词AS
重命名对应列。 - 使用
LET
:也可在RETURN
语句前使用LET
语句,为分组键定义一个新变量,确保其能在分组时使用。
- 使用
-
分完组后,每组仅返回一条记录。
-
有分组操作时,
RETURN
语句中的任何聚合操作都应用于分完的组。 -
Each grouping key must be a binding variable. If the desired grouping key is not already an existing binding variable, you have two options:
- Renaming using
AS
: If the grouping key is part of the final result table, you can rename the corresponding column using theAS
keyword. - Using
LET
: Alternatively, you can use theLET
statement to define a new variable for the grouping key before theRETURN
statement, ensuring it is available for grouping.
- Renaming using
-
After grouping, only one record in each group will be returned.
-
When grouping is involved, any aggregation operation in the
RETURN
statement is applied to each group after the grouping is done.
示例图集
以下示例根据该图集运行:
在空图集中运行以下语句创建示例图集:
INSERT (alex:Student {_id: 's1', name: 'Alex', gender: 'male'}),
(susan:Student {_id: 's2', name: 'Susan', gender: 'female'}),
(alex2:Student {_id: 's3', name: 'Alex', gender: 'female'}),
(art:Course {_id: 'c1', name: 'Art', credit: 13}),
(literature:Course {_id: 'c2', name: 'Literature', credit: 15}),
(alex)-[:Take {year: 2024, term: 'Spring'}]->(art),
(alex2)-[:Take {year: 2023, term: 'Fall'}]->(art),
(susan)-[:Take {year: 2023, term: 'Fall'}]->(art),
(susan)-[:Take {year: 2023, term: 'Spring'}]->(literature)
根据单个分组键分组
MATCH (:Student)->(c:Course)
RETURN c GROUP BY c
结果:c
_id | _uuid | schema | values |
---|---|---|---|
c1 | Sys-gen | Course | {name: "Art", credit: 13} |
c2 | Sys-gen | Course | {name: "Literature", credit: 15} |
根据列别名分组
本条查询中,分组键Name
是n.name
的列别名:
In this query, the grouping key Name
is the column alias of n.name
:
MATCH (n:Student)
RETURN n.name AS Name GROUP BY Name
结果:
Name |
---|
Alex |
Susan |
以下查询会引发语法错误,这是因为分组键n.name
并非绑定变量:
MATCH (n:Student)
RETURN n.name GROUP BY n.name
根据LET定义的变量分组
本条查询中,分组键Gender
由LET
语句定义:
MATCH (n:Student)
LET Gender = n.gender
RETURN n GROUP BY Gender
结果:
n |
---|
(:Student {_id: "s1", gender: "male", name: "Alex"}) |
(:Student {_id: "s3", gender: "female", name: "Alex"}) |
一下查询会引发语法错误,这是因为分组键n.gender
并非绑定变量:
MATCH (n:Student)
RETURN n GROUP BY n.gender
分组Grouping with Aggregation
MATCH (n:Student)
RETURN n.name AS Name, count(n) GROUP BY Name
结果:
Name | count(n) |
---|---|
Alex | 2 |
Susan | 1 |
Grouping by Multiple Keys
MATCH ({_id: "c1"})<-[e:Take]-()
RETURN e.year AS Y, e.term AS T GROUP BY Y, T
结果:
Year | Term |
---|---|
2023 | Fall |
2024 | Spring |