从neo4j联合结果中求出平均值

Question

我在neo4j中有以下查询，它使用UNION

MATCH (u:User {userId:'1'})-[dw:DIRECTOR_WEIGHT]->(d:Person)-[:DIRECTED]->(m:Movie)
WITH m, avg(dw.weight) AS mean_dw, 0 AS mean_aw, 0 AS mean_gw
WHERE m.title = 'Bambi'
RETURN m.title, mean_dw, mean_aw, mean_gw, mean_dw + mean_aw + mean_gw AS total

UNION
MATCH (u:User {userId:'1'})-[aw:ACTOR_WEIGHT]->(a:Person)-[:ACTED_IN]->(m:Movie)
WITH m, 0 AS mean_dw, avg(aw.weight) AS mean_aw, 0 AS mean_gw 
WHERE m.title = 'Bambi'
RETURN m.title, mean_dw, mean_aw, mean_gw, mean_dw + mean_aw + mean_gw AS total

UNION
MATCH (u:User {userId:'1'})-[gw:GENRE_WEIGHT]->(g:Genre)<-[:GENRE]-(m:Movie)
WITH m, 0 AS mean_dw, 0 AS mean_aw, avg(gw.weight) AS mean_gw
WHERE m.title = 'Bambi'
RETURN m.title, mean_dw, mean_aw, mean_gw, mean_dw + mean_aw + mean_gw AS total

产生以下结果：

╒═════════╤═══════════════╤════════════════╤═════════════════╤═════════════════╕
│"m.title"│"mean_dw"      │"mean_aw"       │"mean_gw"        │"total"          │
╞═════════╪═══════════════╪════════════════╪═════════════════╪═════════════════╡
│"Bambi"  │7.2916666666667│"0"             │"0"              │7.2916666666667  │
├─────────┼───────────────┼────────────────┼─────────────────┼─────────────────┤
│"Bambi"  │"0"            │0.45322110715442│"0"              │0.45322110715442 │
├─────────┼───────────────┼────────────────┼─────────────────┼─────────────────┤
│"Bambi"  │"0"            │"0"             │9.289617486338933│9.289617486338933│
└─────────┴───────────────┴────────────────┴─────────────────┴─────────────────┘

我的问题是“总计”没有完成我想做的事情，因为我只想每部电影只需要一个总数(即三个非零权重的之和: 7.29 + 0.45 + 9.28)，但是我无法找到进一步使用这个返回结果的方法。也就是说，我想说点什么

RETURN m.title, sum(total)

或

RETURN m.title, mean_dw + mean_aw + mean_gw

分别得到mean_dw、mean_aw和mean_gw的结合后

Answer 1

虽然目前Cypher不支持联合后处理，但是您可以在APOC过程中使用apoc.cypher.run()来解决这一问题。这将允许您在运行中执行一个联合，并产生统一的结果，允许您完成任何您想要的剩余处理。

通过查看您的查询，您在每个查询中执行相同的操作，唯一的区别是匹配中所遵循的关系。也有一些不必要的工作要做的三个单独的平均栏，因为你感兴趣的唯一的事情是得到每个特定关系的平均重量作为平均值，然后总结所有的手段。

这将使我们能够减少一些冗余操作，并使用一组更窄的变量。

就像这样：

MATCH (u:User {userId:'1'}), (m:Movie{title:'Bambi'})
CALL apoc.cypher.run("
MATCH (u)-[dw:DIRECTOR_WEIGHT]->()-[:DIRECTED]->(m)
RETURN avg(dw.weight) as mean
UNION ALL
MATCH (u)-[aw:ACTOR_WEIGHT]->()-[:ACTED_IN]->(m)
RETURN avg(aw.weight) AS mean
UNION ALL
MATCH (u)-[gw:GENRE_WEIGHT]->()<-[:GENRE]-(m)
RETURN avg(gw.weight) AS mean
", {u:u, m:m}) YIELD value
RETURN m.title, SUM(value.mean) as total

现在，所有这些都表明，你根本不需要使用工会。连接的子查询即可。

MATCH (u:User {userId:'1'}), (m:Movie{title:'Bambi'})
MATCH (u)-[dw:DIRECTOR_WEIGHT]->()-[:DIRECTED]->(m)
WITH u, m, avg(dw.weight) as total
MATCH (u)-[aw:ACTOR_WEIGHT]->()-[:ACTED_IN]->(m)
WITH u, m, total + avg(aw.weight) AS total
MATCH (u)-[gw:GENRE_WEIGHT]->()<-[:GENRE]-(m)
WITH u, m, total + avg(gw.weight) AS total
RETURN m.title, total