概述

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 the AS keyword.
  • Using LET: Alternatively, you can use the LET statement to define a new variable for the grouping key before the RETURN statement, ensuring it is available for grouping.

• 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

根据列别名分组

本条查询中，分组键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

结果：

以下查询会引发语法错误，这是因为分组键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.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

结果：

Grouping by Multiple Keys

MATCH ({_id: "c1"})<-[e:Take]-()
RETURN e.year AS Y, e.term AS T GROUP BY Y, T

结果：