Cypher中的group by功能实现 return和with都可以

Cypher 语言并没有原生的 GROUP BY 关键字，但聚合函数（例如 COUNT）隐含地引入了分组。

 

https://neo4j.com/docs/cypher-manual/current/functions/aggregating/#grouping-key-examples

聚合函数采用一组值并计算它们的聚合值。可以对所有匹配路径进行聚合计算，也可以通过引入分组键来进一步划分。分组键是非聚合表达式，用于对进入聚合函数的值进行分组。

例如，假设以下查询包含两个返回表达式n和count(*)：

 

RETURN n, count(*)

第一个n不是聚合函数，因此它将是分组键。后者count(*)是一个聚合表达式。根据分组键，匹配的路径将被分为不同的桶。然后，聚合函数将在这些存储桶上运行，计算每个存储桶的聚合值。

用于count(*)对关系类型进行分组和计数

该函数count(*)可用于对匹配关系的类型进行分组并返回类型的数量。

示例 5.count()

询问

MATCH (p:Person {name: 'Keanu Reeves'})-[r]->()
RETURN type(r), count(*)

对匹配的关系类型进行分组，并返回关系类型的组数：

表 5. 结果

类型(r)	数数（*）
"ACTED_IN"	1
"KNOWS"	3
行数：2

 

有重复和无重复的计数

此示例尝试查找 的朋友的所有朋友Keanu Reeves并对其进行计数。

count(DISTINCT friendOfFriend)

只会计算friendOfFriend一次，因为DISTINCT会删除重复项。

count(friendOfFriend)

会friendOfFriend多次考虑同样的事情。

示例 7.count()

询问

MATCH (p:Person)-->(friend:Person)-->(friendOfFriend:Person)
WHERE p.name = 'Keanu Reeves'
RETURN friendOfFriend.name, count(DISTINCT friendOfFriend), count(friendOfFriend)

节点Carrie Anne Moss和Liam Neeson都与 具有传出KNOWS关系Guy Pearce。Guy Pearce因此，当不使用 时，该节点将被计数两次DISTINCT。

表 7. 结果

朋友的朋友.name	计数（不同的朋友的朋友）	计数（朋友的朋友）
"Guy Pearce"	1	2

 

https://stackoverflow.com/questions/52722671/how-to-make-group-by-in-a-cypher-query

 

 https://neo4j.com/docs/cypher-manual/current/clauses/with/  

过滤聚合函数结果

聚合结果必须通过WITH子句才能进行过滤。

询问

MATCH (david {name: 'David'})--(otherPerson)-->()
WITH otherPerson, count(*) AS foaf
WHERE foaf > 1
RETURN otherPerson.name

查询将返回与“David”连接且具有至少一个以上传出关系的人的姓名。

otherPerson.name

"Anders"

Rows: 1

 

Cypher query aggregate sum of values

Ask Question

I have a Cypher query that shows the following output:

+----------------
| usid  | count |
+----------------
| "000" | 1     |
| "000" | 0     |
| "000" | 0     |
| "001" | 1     |
| "001" | 1     |
| "001" | 0     |
| "002" | 2     |
| "002" | 2     |
| "002" | 0     |
| "003" | 4     |
| "003" | 2     |
| "003" | 2     |
| "004" | 4     |
| "004" | 4     |
| "004" | 4     |
+----------------

How can I get the below result with the condition SUM(count) <= 9.

+----------------
| usid  | count |
+----------------
| "000" | 1     |
| "001" | 2     |
| "002" | 4     |
| "003" | 8     |
+----------------

 

I don't know how you get your original data, so I will just use a WITH clause and assume the data is there:

// original data
WITH usid, count
// aggregate and filter
WITH usid, sum(count) as new_count
WHERE new_count <= 9
RETURN usid, new_count

Based on the updated question, the new query would look like:

MATCH (us:USER)
WHERE us.count <= 4
WITH us.id as usid, sum(us.count) as count
WHERE new_count <= 9
RETURN usid, count
˙˙˙


其他补充：

AGGREGATION Functions List

S.No.	AGGREGATION Function	Description
1.	COUNT	It returns the number of rows returned by MATCH command.
2.	MAX	It returns the maximum value from a set of rows returned by MATCH command.
3.	MIN	It returns the minimum value from a set of rows returned by MATCH command.
4.	SUM	It returns the summation value of all rows returned by MATCH command.
5.	AVG	It returns the average value of all rows returned by MATCH command.

Now we will discuss each Neo4j CQL AGGREGATION Functions in detail with examples