Neo4j Cypher如何按vacancyId分组聚合计算总加权投票权重

现有查询 #

当前使用的明细查询语句如下：

MATCH (v:Vacancy {deleted: false})-[vv:HAS_VOTE_ON]->(c:Criterion)<-[vp:HAS_VOTE_ON]-(p:Profile {id: 703, deleted: false})
WHERE vv.avgVotesWeight > 0 AND vv.avgVotesWeight <= vp.avgVotesWeight
WITH v, p
MATCH (v)-[vv1:HAS_VOTE_ON]->(cv:Criterion)
OPTIONAL MATCH (p)-[vp1:HAS_VOTE_ON]->(cv)
WITH v.id as vacancyId, cv.id as criterionId, coalesce(vv1.`properties.skillCoefficient`, 1.0) as vacancyCriterionCoefficient, coalesce(vp1.avgVotesWeight, 0) as profileCriterionVoteWeight, coalesce(vp1.totalVotes, 0) as profileCriterionTotalVotes
RETURN vacancyId, criterionId, vacancyCriterionCoefficient, profileCriterionVoteWeight, profileCriterionTotalVotes

该查询会返回每个Vacancy关联的所有Criterion维度的明细行，示例结果：

需求说明 #

需要按vacancyId对结果分组，对每个Vacancy下所有关联Criterion，先计算单条记录的vacancyCriterionCoefficient * profileCriterionVoteWeight乘积，再通过SUM聚合得到每个Vacancy对应的totalProfileCriterionVoteWeight总值。

实现代码 #

直接在原有查询的最后增加分组聚合逻辑即可，完整语句如下：

MATCH (v:Vacancy {deleted: false})-[vv:HAS_VOTE_ON]->(c:Criterion)<-[vp:HAS_VOTE_ON]-(p:Profile {id: 703, deleted: false})
WHERE vv.avgVotesWeight > 0 AND vv.avgVotesWeight <= vp.avgVotesWeight
WITH v, p
MATCH (v)-[vv1:HAS_VOTE_ON]->(cv:Criterion)
OPTIONAL MATCH (p)-[vp1:HAS_VOTE_ON]->(cv)
WITH v.id as vacancyId, 
     cv.id as criterionId, 
     coalesce(vv1.`properties.skillCoefficient`, 1.0) as vacancyCriterionCoefficient, 
     coalesce(vp1.avgVotesWeight, 0) as profileCriterionVoteWeight, 
     coalesce(vp1.totalVotes, 0) as profileCriterionTotalVotes
// 按vacancyId分组做聚合计算
WITH vacancyId,
     SUM(vacancyCriterionCoefficient * profileCriterionVoteWeight) AS totalProfileCriterionVoteWeight,
     // 若不需要保留维度明细，可删除下面collect相关代码
     collect({
         criterionId: criterionId,
         vacancyCriterionCoefficient: vacancyCriterionCoefficient,
         profileCriterionVoteWeight: profileCriterionVoteWeight,
         profileCriterionTotalVotes: profileCriterionTotalVotes
     }) AS criterionDetailList
RETURN vacancyId, totalProfileCriterionVoteWeight, criterionDetailList

• 若只需要返回每个Vacancy的聚合总分，不需要关联的Criterion明细，直接删除collect包裹的明细字段部分，最终RETURN仅返回vacancyId和totalProfileCriterionVoteWeight即可。
• 聚合逻辑保留了原查询的coalesce空值处理逻辑，不会出现空值导致乘积计算为null的问题。

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