Cypher嵌套收集查询不能正确聚合

Question

我想执行这个查询

MATCH (p:Person)-->(s:Startup)
WITH DISTINCT p
MATCH (p)-->(s:Startup)-->(m:Market)
WITH DISTINCT p, s, m, COLLECT(m) as markets
ORDER BY id(s)
RETURN DISTINCT p, COUNT(DISTINCT s) as inv, 
                COLLECT({id: id(s), markets: markets}) as startups, 
                count (m) as mctotal
ORDER BY inv DESC
LIMIT 10

但我不明白为什么它没有正确地将初创企业与它们的市场聚集在一起。这是我的查询结果：

+-----+-----+------------+---------+
|  p  | inv | startups   | mctotal |
+-----+-----+------------+---------+
|  p  |  11 | id: 1      |    7    |
|     |     | markets: 1 |         |
|     |     |            |         |
|     |     | id: 1      |         |
|     |     | markets: 2 |         |
|     |     |            |         |
|     |     | id: 2      |         |
|     |     | markets: 3 |         |
|     |     |            |         |
|     |     | id: 2      |         |
|     |     | markets: 4 |         |

..。诸若此类。

我想得到的结果如下：

+-----+-----+---------------+---------+
|  p  | inv | startups      | mctotal |
+-----+-----+---------------+---------+
|  p  |  11 | id: 1         |    7    |
|     |     | markets:[1,2] |         |
|     |     |               |         |
|     |     | id: 2         |         |
|     |     | markets:[3,4] |         |

我希望我的要求是清楚的。

Answer 1

1. 不要在聚合中使用distinct
2. 不要将返回的值聚合为简单值(这是您的主要问题)
3. 不匹配两次

下面是一个应该更好工作的变体，您没有共享示例图，所以我无法尝试它。

 MATCH (p)-->(s:Startup)-->(m:Market)
 WITH p, s, COLLECT(m) as markets, count(m) as mcount
 ORDER BY id(s)
 RETURN p, COUNT(DISTINCT s) as inv, 
           COLLECT({id: id(s), markets: markets}) as startups, 
           sum(mcount) as mctotal
 ORDER BY inv DESC
 LIMIT 10