Question 88931 in PostgreSQL Question posted in
The official documentation can be found here.

How to make calculations with properties from different edges – Postgresql

I was making a graph for a recommendation system and added vertices for users, categories and products and edges to represent the connections between them. One product may have connections to categories and a rating as a property for them. Users can also have a rating for each category. So, it is something like this:

-- User preferences.
SELECT * FROM cypher('RecommenderSystem', $$
    MATCH (a:Person {name: 'Abigail'}), (A:Category), (C:Category), (H:Category)
    WHERE A.name = 'A' AND C.name = 'C' AND H.name = 'H' 
    CREATE (a)-[:RATING {rating: 3}]->(C),
           (a)-[:RATING {rating: 1}]->(A),
           (a)-[:RATING {rating: 0}]->(H)
$$) AS (a agtype);

-- Products rating.
SELECT * FROM cypher('RecommenderSystem', $$
    MATCH (product:Product {title: 'Product_Name'}), (A:Category), (C:Category), (H:Category)
    WHERE A.name = 'A' AND C.name = 'C' AND H.name = 'H' 
    CREATE (product)-[:RATING {rating: 0}]->(C),
           (product)-[:RATING {rating: 4}]->(A),
           (product)-[:RATING {rating: 0}]->(H)
$$) AS (a agtype);

graph

My recommendation system is based on Content Filtering, which uses information we know about people and products as connective tissue for recommendations. So for this, it would be necessary to do a calculation like: [(user_rating_C x product_rating_C) + (user_rating_A x product_rating_A) + (user_rating_H x product_rating_H)] / (num_categories x max_rating). For example, the likelihood of Abigail liking the product from the cypher query above would be:

[(3 x 0) + (1 x 4) + (0 x 0)] / (3 x 4) = 0.333
which in a range from 0 to 4, she is likely going to hate the product. And the closer to 4, the more likely becomes for the user to buy or consume the product.

But then, how could I retrieve every edge rating that is connected to a person and a product and do this type of calculation with it?

Tags:

3

Answers


  1. 0

    The following query should work for this situation

    SELECT e1/(ct*4) AS factor FROM cypher('RecommenderSystem', $$
MATCH (u: Person)-[e1: RATING]->(v: Category)<-[e2: RATING]-(w:      
Product), (c: Category) WITH e1, e2, COUNT(DISTINCT c) AS ct
RETURN SUM(e1.rating* e2.rating)::float, ct  
$$) AS (e1  float, ct agtype);

    This outputs:

          factor       
-------------------
0.333333333333333
(1 row)

    Explanation

    You need to find the category for which the person and product both have set the rating using the MATCH clause. Once you get these ratings, the sum of the product of these ratings would give

    [(user_rating_C x product_rating_C) + (user_rating_A x product_rating_A) + (user_rating_H x product_rating_H)]

    Now to divide it by the product of

    (num_categories x max_rating)

    You get num_categories using COUNT(DISTINCT c) and I assume that you already know the max_rating.

    Hope it helps

    Edit

    I assumed that by num_categories, you meant the total number of categories in the system and not the only ones that are associated with the person and product in common. In case, num_categories is the count of categories associated with product and person in common, then modify your WITH clause as

    WITH e1, e2, COUNT(*) AS ct

    Else is fine

    Login or Signup to reply.
  2. 0

    Something like this may work for you:

    WITH
  'Abigail' AS perName,
  [{c: 'A', p: 'prod_1'}, {c: 'C', p: 'prod_9'}, {c: 'H', p: 'prod_4'}] AS x
MATCH (per:Person)-[perRating:RATING]->(cat:Category)<-[prodRating:RATING]-(prod:Product)
WHERE per.name = perName AND ANY(i IN x WHERE cat.name = i.c AND prod.name = i.p)
WITH *, SUM(perRating.rating*prodRating.rating) AS total, MAX(prodRating.rating) AS maxProdRating
RETURN per, total/(SIZE(x) * maxProdRating) AS affinity

    perName is the person’s name, x is a list of the desired category/product name pairs, and affinity will be the calculated result.

    NOTE: Even if not all desired pairs in x are found in the data, this query uses the size of x in the denominator. Adjust the query if this is not wanted.

    [UPDATE]

    Unfortunately, the ANY predicate function is not part of openCypher, so it is not supported by Apache AGE.

    Even more unfortunately, even though list comprehension is a part of openCypher, AGE does not yet support that either.

    But, on an openCypher system that does support list comprehension, we could replace this:

    ANY(i IN x WHERE cat.name = i.c AND prod.name = i.p)

    with something like this (we don’t care about the generated list’s contents, so we just use arbitrary 1 elements):

    SIZE([i IN x WHERE cat.name = i.c AND prod.name = i.p | 1]) > 0
    Login or Signup to reply.
  3. 0

    If I understand correctly, you want to calculate the rating of each product for a user based on the given formula: [(user_rating_C x product_rating_C) + (user_rating_A x product_rating_A) + (user_rating_H x product_rating_H)] / (num_categories x max_rating). According to your model, max_rating is set to 4 (range from 0 to 4). To perform this calculation, you can use the following query:

    SELECT * FROM cypher('RecommenderSystem', $$
    MATCH (a: Person {name: 'Abigail'})-[r1: RATING]->(c: Category)<-[r2: RATING]-(p:Product)
    WITH a.name AS person, p.title AS product, 
         SUM(r1.rating * r2.rating)/(count(c) * 4)::float AS rate
    RETURN person AS a, product AS p, rate AS r
$$) AS (a agtype, p agtype, r float);

    I added another product (rating 0 with category C, rating 1 with category A and rating 3 with category H) and this query gave me these results:
    Query results. Person: Abigail, product: Product_Name, rating: 0.33 and Person: Abigail, product: Other_Product, rating: 0.083

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search