密码联盟按总和分组 [英] cypher union group by sum

问题描述

我想根据图遍历的一些规则为用户增加优势.基本上在mysql中，我会这样做:

I would like to boost edges for a user depending of some rules base on graph traversal. Basically in mysql I would do that :

select id, sum(weight) as total
from
(
     select id, 10 as weight
     from user
     inner join userRel1 ON user.id = userRel1.userId
     where userRel1.attr1 in (1, 2)
  union
     select id, 5 as weight
     from user
     inner join userRel2 ON user.id = userRel2.userId
     inner join userRel3 ON user.id = userRel3.userId
     where userRel2.attr2 = 'a' and userRel3.attr2 = 'z'
  union
     ...
)
group by id 
order by total desc

此外，我已经在gremlin 3的帮助下编写了此查询，但是我想比较使用cypher的性能.但我在这篇帖子中读到，按工会进行分组是还不可能，这意味着密码功能不如gremlin强大吗?要实现此功能，是否需要将权重设置为边缘的属性?

Also, I have already writed this query with some help in gremlin 3 but I would like to compare performance with cypher. But I read in this post that group by on union are not possible yet, it mean that cypher is less powerful than gremlin ? Would I have to set weight as properties on edges to achieve it ?

谢谢

推荐答案

尽管UNION后处理确实是

While it is true that post-UNION processing is still an open feature request, you do not need to use UNION to perform your use case.

此查询应与您的SQL等效(忽略不完整的部分):

This query should do the equivalent of your SQL (ignoring the incomplete part):

WITH [] AS res

OPTIONAL MATCH (user1:User), (userRel1:UserRel1) 
WHERE user1.id = userRel1.userId AND userRel1.attr1 IN [1, 2] 
WITH res, (CASE WHEN userRel1 IS NOT NULL THEN COLLECT({id: user1.id, weight: 10}) ELSE [] END) AS data 
WITH res + data AS res

OPTIONAL MATCH (user2:User), (userRel2:UserRel2)
WHERE user2.id = userRel2.userId AND userRel2.attr2 = 'a'
OPTIONAL MATCH (userRel3:UserRel3)
WHERE user2.id = userRel3.userId AND userRel3.attr2 = 'z' 
WITH res, (CASE WHEN userRel3 IS NOT NULL THEN COLLECT({id: user2.id, weight: 5}) ELSE [] END) AS data 
WITH res + data AS res 

UNWIND res AS result
RETURN result.id, SUM(result.weight) AS weight;

我在视觉上将此查询分解为Cypher的单独块，以使其更易于阅读.

I visually broke up this query into separate blocks of Cypher, to make it easier to read.

查询将使用适当的id/weight对继续扩展(并替换)res集合，然后在末尾进行聚合.

The query keeps extending (and replacing) the res collection with appropriate id/weight pairs, and then aggregates at the end.

带有2个OPTIONAL MATCH子句的块本可以使用单个OPTIONAL MATCH编写的，但我认为执行相同的工作更加高效，并且允许一个OPTIONAL MATCH的失败潜在地通知Cypher以甚至不理会另一个人.该块的第二个WHERE子句依赖于第一个OPTIONAL MATCH所找到的user2节点.如果第一个OPTIONAL MATCH失败，则user2的值将为NULL，而这样的NULL值也将导致第二个WHERE子句失败(从而使userRel3 NULL).

The block with 2 OPTIONAL MATCH clauses could have been written using a single OPTIONAL MATCH, but I thought it was more performant do the same work piecemeal, and to allow the failure of one OPTIONAL MATCH to potentially inform Cypher to not even bother with the other one. The block's second WHERE clause relies on the user2 node that is found by the first OPTIONAL MATCH. user2 would have the value NULL if the first OPTIONAL MATCH failed, and such a NULL value would also cause the second WHERE clause to fail (which would in turn make userRel3 NULL).

这篇关于密码联盟按总和分组的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！