How to sum up different property weights?

问题:

I am trying to get two properties sum (with weight) and combine them finally into a combined weight.

So, first the separate queries, so you can see the correct sum weights

1. Language = French, Proficiency over 6 (scale 1-10):

   MATCH (User)-[Speaks]->(Language) 
   WHERE Language.language = 'French' AND Speaks.proficiency >=6 
   RETURN User.name, SUM(
   CASE
       WHEN Language.language='French'  THEN Speaks.proficiency*3  
       END )*0.1 AS TOTAL_Language
   ORDER BY TOTAL_Language DESC
   

   User.name TOTAL_Language David 3 Marion 1.8

2. Skill = Finance,Banking,IT,Marketing Weight =1 (of 1):

   MATCH (User)-[IsSkilledIn]->(Skill)
   WHERE  Skill.skill IN ['Finance','Banking','IT','Marketing']
   RETURN User.name, SUM(
   CASE
       WHEN Skill.skill='Finance' THEN IsSkilledIn.weight*10
       WHEN Skill.skill='Banking' THEN IsSkilledIn.weight*5        
       WHEN Skill.skill='IT' THEN IsSkilledIn.weight*8
   WHEN Skill.skill='Marketing' THEN IsSkilledIn.weight*1
   END )*0.1 AS TOTAL_Skill
   ORDER BY TOTAL_Skill DESC
   
   User.name   TOTAL_Skill
   Eric            1.5
   Marion          0.9
   David           0.8
   Hans            0.8
   

So now, I tried to combine these Queries into this:

3. French >=6 AND Skills Finance,Banking,IT,Marketing:

   MATCH (User)-[Speaks]->(Language) , (User)-[IsSkilledIn]->(Skill)
   WHERE Language.language = 'French' AND Speaks.proficiency >=6 AND Skill.skill IN ['Finance','Banking','IT','Marketing']
   RETURN User.name, SUM(
   CASE
       WHEN Language.language='French'  THEN Speaks.proficiency*3   
       END )*0.1 AS TOTAL_Language,
   SUM(
   CASE
       WHEN Skill.skill='Finance' THEN IsSkilledIn.weight*10
       WHEN Skill.skill='Banking' THEN IsSkilledIn.weight*5        
       WHEN Skill.skill='IT' THEN IsSkilledIn.weight*8
       WHEN Skill.skill='Marketing' THEN IsSkilledIn.weight*1
   END )*0.1 AS TOTAL_Skill
   ORDER BY TOTAL_Skill DESC
   

Now I got these results:

 User.name  TOTAL_Language  TOTAL_Skill
 Marion          3.6            0.9
 David       3                  0.8

The TOTAL_Language of Marion is twice as high as the first answer.

So I checked what could be wrong and it must be in the WHERE-Clause, But I don't know what's wrong.

How can I sum up the two results in the end?

回答1:

Ah, I see the problem. You're ending up with two results instead of one, because your match matches two skill records. What you'll need to do is WITH the two queries together, so the sums are calculated separately.

MATCH (User)-[Speaks]->(Language) 
WHERE Language.language = 'French' AND Speaks.proficiency >=6 
WITH User, SUM(
CASE
    WHEN Language.language='French'  THEN Speaks.proficiency*3  
    END )*0.1 AS TOTAL_Language
MATCH (User)-[IsSkilledIn]->(Skill)
WHERE  Skill.skill IN ['Finance','Banking','IT','Marketing']
RETURN User.name, TOTAL_Language, SUM(
CASE
    WHEN Skill.skill='Finance' THEN IsSkilledIn.weight*10
    WHEN Skill.skill='Banking' THEN IsSkilledIn.weight*5        
    WHEN Skill.skill='IT' THEN IsSkilledIn.weight*8
    WHEN Skill.skill='Marketing' THEN IsSkilledIn.weight*1
END )*0.1 AS TOTAL_Skill
ORDER BY TOTAL_Skill DESC

回答2:

Another option is to do it still with one query, but collect the skills for a person, and sum the collection of skill weight as opposed to sum the rows,

MATCH (User)-[Speaks]->(Language), (User)-[IsSkilledIn]->(Skill) 
WHERE Language.language = 'French' 
 AND Speaks.proficiency >=6 
 AND Skill.skill IN ['Finance','Banking','IT','Marketing']
WITH User.name as userName, 
     (CASE WHEN Language.language='French' 
           THEN Speaks.proficiency*3 END) as languageProficiency, 
      collect(CASE 
              WHEN Skill.skill='Finance' THEN IsSkilledIn.weight*10 
              WHEN Skill.skill='Banking' THEN IsSkilledIn.weight*5 
              WHEN Skill.skill='IT' THEN IsSkilledIn.weight*8 
              WHEN Skill.skill='Marketing' THEN IsSkilledIn.weight*1 
              END) as skillWeightCollection
RETURN userName, 
       sum(languageProficiency)*0.1 as TOTAL_Language, 
       reduce(totalSkill = 0, skillWeight in skillWeightCollection | 
              totalSkill + skillWeight) * 0.1 as TOTAL_SKILL