Neo4j Cypher聚合查询报错：隐式分组表达式问题求助

原查询及问题 #

我复制了Neo4j Cypher聚合课程中的示例查询：

Match (a:Actor)
Where a.born is not null
And a.name starts with 'Tom'
with count(a) as NumActors, collect(duration.between(date(a.born), date())) as Ages
Unwind Ages AS x
Return sum(x), sum(x)/NumActors

在Neo4j Web控制台执行时出现以下错误：

Aggregation column contains implicit grouping expressions. For example, in 'RETURN n.a, n.a + n.b + count()' the aggregation expression 'n.a + n.b + count()' includes the implicit grouping key 'n.b'. It may be possible to rewrite the query by extracting these grouping/aggregation expressions into a preceding WITH clause. Illegal expression(s): NumActors (line 6, column 8 (offset: 183))
"Return sum(x), sum(x)/NumActors"
^

单独返回sum(x)的查询可以正常执行：

Match (a:Actor)
Where a.born is not null
And a.name starts with 'Tom'
with count(a) as NumActors, collect(duration.between(date(a.born), date())) as Ages
Unwind Ages AS x
Return sum(x)

报错原因是sum(x)与NumActors作为隐式聚合键冲突，需修改查询以实现求和与平均值的计算。

修正方案 #

方案1：前置WITH子句分离聚合计算 #

将sum(x)的计算移到UNWIND之后的WITH子句中，同时保留NumActors，后续即可直接使用两个聚合值：

Match (a:Actor)
Where a.born is not null
And a.name starts with 'Tom'
with count(a) as NumActors, collect(duration.between(date(a.born), date())) as Ages
Unwind Ages AS x
with NumActors, sum(x) as totalAge
Return totalAge, totalAge/NumActors as avgAge

方案2：直接对集合聚合（无UNWIND，性能更优） #

无需展开集合，直接对Ages集合做聚合计算，省略UNWIND步骤：

Match (a:Actor)
Where a.born is not null
And a.name starts with 'Tom'
with count(a) as NumActors, collect(duration.between(date(a.born), date())) as Ages
with NumActors, reduce(total = duration({years:0}), age in Ages | total + age) as totalAge
Return totalAge, totalAge/NumActors as avgAge

方案3：初始聚合直接计算总和（最优方案） #

跳过集合收集步骤，首次聚合时直接计算总年龄，全程无冗余操作：

Match (a:Actor)
Where a.born is not null
And a.name starts with 'Tom'
with count(a) as NumActors, sum(duration.between(date(a.born), date())) as totalAge
Return totalAge, totalAge/NumActors as avgAge

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