Neo4j多OPTIONAL MATCH查询超时 Pattern Comprehension计数方案

我查阅了大量关于OPTIONAL MATCH与Pattern Comprehension的相关讨论，但未找到适配自身业务场景的解决方案。
我的Neo4j数据库中存在Account节点，需要统计每个Account节点关联的各类从属节点数量。当查询仅使用1到2个OPTIONAL MATCH时可正常运行，但使用多个OPTIONAL MATCH时会产生笛卡尔积，最终导致查询超时，原有查询代码如下：

// Account
MATCH (a:Account{billingCountry: "DE", isDeleted: false})
WHERE a.id IS NOT NULL

// User
MATCH (a)<-[:CREATED]-(u:User)

// Contact
OPTIONAL MATCH (a) <-[:CONTACT_OF]- (c:Contact{isDeleted: false})

// Opportunity
OPTIONAL MATCH (a) <-[:OPPORTUNITY_OF]- (o:Opportunity{isDeleted: false, s4sMarked_For_Deletion__C: false})

// Open Opportunity
OPTIONAL MATCH (a)<-[:OPPORTUNITY_OF]-(open:Opportunity{isClosed: false, isDeleted: false})

// Attribute
OPTIONAL MATCH (a) <-[:ATTRIBUTE_OF]- (aa:Attribute_Assignment{isDeleted: false})

// Sales Planning
OPTIONAL MATCH (a) <-[:SALESPLAN_OF]- (s:Sales_Planning)

// Task
OPTIONAL MATCH (a) <-[:TASK_OF]- (t:Task{isDeleted: false})

// Event
OPTIONAL MATCH (a) <-[:EVENT_OF]- (e:Event{isDeleted: false})

// Contract
OPTIONAL MATCH (a) <-[:CONTRACT_OF]- (ct:Contract{isDeleted: false})

RETURN
a.id,
u.name AS User_Name,
u.department AS User_Department,

COUNT(DISTINCT c.id) AS Contact_Count,
COUNT(DISTINCT o.id) AS Opportunity_Count,
COUNT(DISTINCT open.id) AS OpenOpp_Count,
COUNT(DISTINCT aa.id) AS Attribute_Count,
COUNT(DISTINCT s.timeYear) AS Sales_Plan_Count,
COUNT(DISTINCT t.id) AS Task_Count,
COUNT(DISTINCT e.id) AS Event_Count,
COUNT(DISTINCT ct.id) AS Contract_Count

我尝试使用Pattern Comprehension改写查询，但改写后仅能返回存储非去重ID的数组。请问是否存在方法可对Pattern Comprehension返回数组内的值做去重计数，或是有其他基于Pattern Comprehension的统计计数方案？我改写后的代码如下：

MATCH (a:Account{billingCountry: "DE", isDeleted: false})
WHERE a.id IS NOT NULL

RETURN a.id,
[
[(a)<-[:CONTACT_OF]- (c:Contact{isDeleted: false}) | c.id],
[(a)<-[:OPPORTUNITY_OF]- (o:Opportunity{isDeleted: false, s4sMarked_For_Deletion__C: false}) | o.id],
[(a)<-[:OPPORTUNITY_OF]-(open:Opportunity{isClosed: false, isDeleted: false}) | open.id],
[(a) <-[:ATTRIBUTE_OF]- (aa:Attribute_Assignment{isDeleted: false}) | aa.id],
[(a) <-[:SALESPLAN_OF]- (s:Sales_Planning) | s.timeYear],
[(a) <-[:TASK_OF]- (t:Task{isDeleted: false}) | t.id],
[(a) <-[:EVENT_OF]- (e:Event{isDeleted: false}) | e.id],
[(a) <-[:CONTRACT_OF]- (ct:Contract{isDeleted: false}) | ct.id]
]

如果我的提问存在格式问题，也欢迎大家指正。

多个OPTIONAL MATCH串联出现笛卡尔积是Cypher的常见问题，用Pattern Comprehension完全可以实现去重计数，不需要额外嵌套复杂逻辑，直接对模式匹配返回的列表做去重后取长度即可，核心用到两个能力：

• 列表去重：可以用APOC函数apoc.coll.toSet()，无APOC环境也可以用原生DISTINCT配合列表推导实现
• 长度统计：用原生函数size()统计去重后列表的长度，就是需要的去重计数结果

优化后可直接运行的查询代码 #

代码保留了关联创建用户的逻辑，所有计数都用模式推导单独计算，完全不会产生跨关系的笛卡尔积，执行效率远高于多OPTIONAL MATCH写法：

MATCH (a:Account{billingCountry: "DE", isDeleted: false})
WHERE a.id IS NOT NULL
MATCH (a)<-[:CREATED]-(u:User)
RETURN
  a.id,
  u.name AS User_Name,
  u.department AS User_Department,
  size([(a)<-[:CONTACT_OF]- (c:Contact{isDeleted: false}) | c.id]) AS Contact_Count,
  size([(a)<-[:OPPORTUNITY_OF]- (o:Opportunity{isDeleted: false, s4sMarked_For_Deletion__C: false}) | o.id]) AS Opportunity_Count,
  size([(a)<-[:OPPORTUNITY_OF]-(open:Opportunity{isClosed: false, isDeleted: false}) | open.id]) AS OpenOpp_Count,
  size([(a)<-[:ATTRIBUTE_OF]- (aa:Attribute_Assignment{isDeleted: false}) | aa.id]) AS Attribute_Count,
  size(apoc.coll.toSet([(a)<-[:SALESPLAN_OF]- (s:Sales_Planning) | s.timeYear])) AS Sales_Plan_Count,
  size([(a)<-[:TASK_OF]- (t:Task{isDeleted: false}) | t.id]) AS Task_Count,
  size([(a)<-[:EVENT_OF]- (e:Event{isDeleted: false}) | e.id]) AS Event_Count,
  size([(a)<-[:CONTRACT_OF]- (ct:Contract{isDeleted: false}) | ct.id]) AS Contract_Count

补充说明 #

• 除了销售计划是按timeYear去重计数需要调用去重逻辑，其余关联节点因为是按节点唯一标识统计，模式匹配到的节点本身不会重复，直接取size()就等价于之前的COUNT(DISTINCT 节点id)，不需要额外去重。
• 如果环境没有安装APOC插件，销售计划的计数可以替换为原生Cypher写法：

  size([ DISTINCT year IN [(a)<-[:SALESPLAN_OF]- (s:Sales_Planning) | s.timeYear] | year ]) AS Sales_Plan_Count
  

• 这种写法的本质是每个计数维度都单独做一次局部模式匹配，匹配过程中不会和其他维度的匹配结果做乘积，从根源上避免了笛卡尔积导致的查询超时问题。

内容的提问来源于stack exchange，提问作者Axel