Neo4j - 不能在聚合函数中使用聚合函数

Question

大家!我有一个 Neo4j 数据库，其中包含有关在特定时间访问多个检查站的访问者的信息。访问者、访问和检查点表示为节点。

每个访问者都由他/她的设备 MAC 地址标识，每个检查点都有一个唯一的 ID。

每个访问节点都包含一个 EnterTime 属性，它是在相应检查点附近首次注意到具有特定 MAC 地址的访问者的时间(自 1970 年以来的毫秒数)。

注意:一次访问的时长应计算为下一次访问的 EnterTime 与本次访问的 EnterTime 之间的差值。

我的目标是找到一些有关访问的统计信息，例如，1) 特定访问者的平均访问时间，2)访问特定商店的平均时间，3)最受欢迎的商店(即总访问时间最长的商店4)最受欢迎的商店(即平均访问时间最长的商店)等。

数据库是用这个密码查询创建的:

//Create visitors:
CREATE (Visitor1: Visitor {MAC:'00:0a:95:9d:68:16'})
CREATE (Visitor2: Visitor {MAC:'00:0a:95:9d:68:17'})
CREATE (Visitor3: Visitor {MAC:'00:0a:95:9d:68:18'})

//Create CheckPoints:
CREATE (CheckPoint1: CheckPoint {CheckPointId: 1})
CREATE (CheckPoint2: CheckPoint {CheckPointId: 2})
CREATE (CheckPoint3: CheckPoint {CheckPointId: 3})
CREATE (CheckPoint4: CheckPoint {CheckPointId: 4})
CREATE (CheckPoint5: CheckPoint {CheckPointId: 5})


//Create visits:
//by visitor 1:
CREATE  (Visitor1)-[:MAKES]->(Visit1: Visit {EnterTime: 1488358800000})-[:TO]->(CheckPoint1)
CREATE  (Visitor1)-[:MAKES]->(Visit2: Visit {EnterTime: 1488359400000})-[:TO]->(CheckPoint2)
CREATE  (Visitor1)-[:MAKES]->(Visit3: Visit {EnterTime: 1488361200000})-[:TO]->(CheckPoint3)
CREATE  (Visitor1)-[:MAKES]->(Visit4: Visit {EnterTime: 1488363600000})-[:TO]->(CheckPoint4)
CREATE  (Visitor1)-[:MAKES]->(Visit5: Visit {EnterTime: 1488364800000})-[:TO]->(CheckPoint5)
CREATE  (Visitor1)-[:MAKES]->(Visit6: Visit {EnterTime: 1488365400000})-[:TO]->(CheckPoint1)

//by visitor 2:
CREATE  (Visitor2)-[:MAKES]->(Visit7: Visit {EnterTime: 1488358800000})-[:TO]->(CheckPoint1)
CREATE  (Visitor2)-[:MAKES]->(Visit8: Visit {EnterTime: 1488360300000})-[:TO]->(CheckPoint4)
CREATE  (Visitor2)-[:MAKES]->(Visit9: Visit {EnterTime: 1488362400000})-[:TO]->(CheckPoint2)
CREATE  (Visitor2)-[:MAKES]->(Visit10: Visit {EnterTime: 1488363000000})-[:TO]->(CheckPoint1)

//by visitor 3:
CREATE  (Visitor3)-[:MAKES]->(Visit11: Visit {EnterTime: 1488353820000})-[:TO]->(CheckPoint1)
CREATE  (Visitor3)-[:MAKES]->(Visit12: Visit {EnterTime: 1488354600000})-[:TO]->(CheckPoint4)
CREATE  (Visitor3)-[:MAKES]->(Visit13: Visit {EnterTime: 1488358200000})-[:TO]->(CheckPoint3)
CREATE  (Visitor3)-[:MAKES]->(Visit14: Visit {EnterTime: 1488359700000})-[:TO]->(CheckPoint1)  

请帮我正确写出问题 1 - 4。到目前为止，我有这个查询，它输出特定访问者对每个检查点的访问持续时间(它工作正常):

match (vr: Visitor {MAC: '00:0a:95:9d:68:16'})-->(v1: Visit)-->(cp1: CheckPoint)
optional match (vr: Visitor {MAC: '00:0a:95:9d:68:16'})-->(v2: Visit)-->(cp2: CheckPoint)
where v2.EnterTime > v1.EnterTime
return cp1.CheckPointId, v1.EnterTime, min((v2.EnterTime - v1.EnterTime) / 60000) as visit_duration
order by v1.EnterTime;

但是当我试图平均访问持续时间时:

match (vr: Visitor {MAC: '00:0a:95:9d:68:16'})-->(v1: Visit)-->(cp1: CheckPoint)
optional match (vr: Visitor {MAC: '00:0a:95:9d:68:16'})-->(v2: Visit)-->(cp2: CheckPoint)
where v2.EnterTime > v1.EnterTime
return avg(min((v2.EnterTime - v1.EnterTime) / 60000)) as avg_visit_duration;

返回错误信息:

Error occurred: Can't use aggregate functions inside of aggregate functions.

可能，我需要使用 WITH 子句，但到目前为止，我一直无法使用 WITH 子句编写查询，从而给出正确的结果(110 分钟)。

有什么想法吗？您的帮助将不胜感激!谢谢!

Answer 1

如果需要传递另一个聚合函数的聚合函数的结果，通过WITH来实现。像这样的:

match (vr: Visitor {MAC: '00:0a:95:9d:68:16'})-->(v1: Visit)-->(cp1: CheckPoint)
optional match (vr)-->(v2: Visit)-->(cp2: CheckPoint)
where v2.EnterTime > v1.EnterTime
with cp1.CheckPointId as CheckPointId, 
     v1.EnterTime as EnterTime, 
     min((v2.EnterTime - v1.EnterTime) / 60000) as visit_duration
return sum(visit_duration)