如何根据与物品价格的关系将”expensive_meter”属性添加到图数据库中的顶点?

huangapple go评论57阅读模式
英文:

How to add "expensive_meter" property to vertices in a graph database based on their relation to item prices?

问题

我有一个图数据库,用于存储餐厅及其菜单项的数据。数据库中有标记为 "Restaurant" 和 "Item" 的顶点,而 "Item" 顶点与 "Restaurant" 顶点之间通过 "OFFERS" 边相连接。

数据库中的每个项都有价格、名称和描述等属性。一些餐厅与它们关联了一个名为 "expensive_meter" 的属性,而其他餐厅则没有。

现在,我想要根据餐厅的项目价格与其他餐厅的 "expensive_meter" 值的关系,将 "expensive_meter" 属性添加到当前没有该属性的 "Restaurant" 顶点中。

例如,如果一个餐厅主要提供高价位的项目,它应该被分配一个高的 "expensive_meter" 值。相反,如果一个餐厅主要提供价格较实惠的项目,它应该具有较低的 "expensive_meter" 值。

我正在寻求关于如何在我的图数据库中实现这一逻辑的指导。我可以使用哪些步骤或算法来根据与项目价格的关系计算并分配 "expensive_meter" 属性给 "Restaurant" 顶点?

非常感谢您提供的任何建议、代码片段或相关文档的引用。提前感谢您的帮助!

英文:

I have a graph database that stores data for restaurants and their menu items. The database has vertices labeled "Restaurant" and "Item", and an "Item" vertex is connected to a "Restaurant" vertex by an "OFFERS" edge.

Each item in the database has properties such as price, name, and description. Some restaurants have an "expensive_meter" property associated with them, while others don't.

-- Restaurants (vertex)
SELECT * FROM cypher('DeliveryGraph', $$
    MATCH (v:Restaurant)
    RETURN v.name, v.expensive_meter, v.type, v.address
$$) AS (name agtype, expensive_meter agtype, type agtype, address agtype);

             name             | expensive_meter |         type          |                       address                       
------------------------------+-----------------+-----------------------+-----------------------------------------------------
 "Pagliacci Pizza"            |                 | "Pizzeria"            | "10600 Main St, Bellevue, WA 98004, U.S"
 "MOD Pizza"                  | 1               | "Pizzeria"            | "317 Bellevue Way NE, Bellevue, WA 98004, U.S"
 "Ascend Prime Steak & Sushi" | 4               | "Steakhouse/Japanese" | "10400 NE 4th St Ste 3100, Bellevue, WA 98004, U.S"
(3 rows)


-- Items (vertex)
SELECT * FROM cypher('DeliveryGraph', $$
    MATCH (v:Item)
    RETURN v.name, v.type, v.price
$$) AS (name agtype, type agtype, price agtype);

                name                 |   type    | price 
-------------------------------------+-----------+-------
 "Diavola"                           | "Pizza"   | 25.99
 "Chicken Primo"                     | "Pizza"   | 30.49
 "The Rocket"                        | "Pizza"   | 25.99
 "Mad Dog"                           | "Pizza"   | 10.27
 "Tristan"                           | "Pizza"   | 10.27
 "Jasper"                            | "Pizza"   | 10.27
 "PAT LAFRIEDA PRIME AMERICAN STEAK" | "Beef"    | 87.0
 "PREMIUM OMAKASE"                   | "Sushi"   | 80.0
 "HIVE"                              | "Dessert" | 68.0
(9 rows)


-- OFFERS (edge)
SELECT * FROM cypher('DeliveryGraph', $$
    MATCH (i)<-[:OFFERS]-(r)
    RETURN r.name, i.name
$$) AS (restaurant agtype, item agtype);

          restaurant          |                item                 
------------------------------+-------------------------------------
 "Pagliacci Pizza"            | "Diavola"
 "Pagliacci Pizza"            | "Chicken Primo"
 "Pagliacci Pizza"            | "The Rocket"
 "MOD Pizza"                  | "Mad Dog"
 "MOD Pizza"                  | "Tristan"
 "MOD Pizza"                  | "Jasper"
 "Ascend Prime Steak & Sushi" | "PAT LAFRIEDA PRIME AMERICAN STEAK"
 "Ascend Prime Steak & Sushi" | "PREMIUM OMAKASE"
 "Ascend Prime Steak & Sushi" | "HIVE"
(9 rows)

Now, I want to add the "expensive_meter" property to the "Restaurant" vertices that currently do not have it, based on the relation of their item prices to the "expensive_meter" values of other restaurants.

For example, if a restaurant has mostly high-priced items, it should be assigned a high "expensive_meter" value. Conversely, if a restaurant offers mostly affordable items, it should have a lower "expensive_meter" value.

I'm looking for guidance on how to implement this logic in my graph. What steps or algorithms can I use to calculate and assign the "expensive_meter" property to the "Restaurant" vertices based on the relation to item prices?

Any suggestions, code snippets, or references to relevant documentation would be greatly appreciated. Thank you in advance for your help!

答案1

得分: 1

确定"expensive_meter"用于比萨的方法是将价格分成四分位并计算每个四分位内的平均价格。尽管Postgres中的ntile函数对于这个任务可能很有用,但目前在Apache AGE中无法使用它与属性一起使用。然而,您可以通过使用Apache AGE中的percentileCont函数来实现所需的结果。

首先,让我们考虑一个例子,我们想要创建5个价格组。我们可以使用以下查询确定每个组的分隔值:

SELECT *
FROM cypher('DeliveryGraph', $$
MATCH (n:Item)
RETURN percentileDisc(n.price, 0.2), percentileDisc(n.price, 0.4), percentileDisc(n.price, 0.6), percentileDisc(n.price, 0.8), percentileDisc(n.price, 1)
$$) as (group_1 agtype, group_2 agtype, group_3 agtype, group_4 agtype, group_5 agtype);

查询提供以下结果:

 group_1 | group_2 | group_3 | group_4 | group_5
---------+---------+---------+---------+---------
10.27   | 25.99   | 30.49   | 80.0    | 87.0

基于这个结果,我们可以确定每个组的价格范围。例如,价格从0到10.27的属于第一组,价格大于10.27但不超过25.99的属于第二组,依此类推。

现在,要为特定餐厅分配"expensive_meter"值,您可以计算该餐厅的平均价格并确定相应的价格组。然后,"expensive_meter"可以设置为组号。

SELECT *
FROM cypher('DeliveryGraph', $$
MATCH (r:Restaurant)-[:OFFERS]->(i)
RETURN r.name, avg(i.price)
$$) as (average_price agtype);
英文:

To determine the "expensive_meter" for pizzas, you can divide the prices into quartiles and calculate the average price within each quartile. Although the ntile function in Postgres could be useful for this task, it is currently not possible to use it with properties in Apache AGE. However, you can achieve the desired result by utilizing the percentileCont function in Apache AGE.

To begin, let's consider an example where we want to create 5 price groups. We can determine the dividing values for each group using the following query:

SELECT *
FROM cypher('DeliveryGraph', $$
MATCH (n:Item)
RETURN percentileDisc(n.price, 0.2), percentileDisc(n.price, 0.4), percentileDisc(n.price, 0.6), percentileDisc(n.price, 0.8), percentileDisc(n.price, 1)
$$) as (group_1 agtype, group_2 agtype, group_3 agtype, group_4 agtype, group_5 agtype);

The query provides the following result:

 group_1 | group_2 | group_3 | group_4 | group_5
---------+---------+---------+---------+---------
10.27   | 25.99   | 30.49   | 80.0    | 87.0

Based on this result, we can determine the price ranges for each group. For instance, prices ranging from 0 to 10.27 would fall into the first group, prices greater than 10.27 up to 25.99 would belong to the second group, and so on.

Now, to assign the "expensive_meter" value for a specific restaurant, you can calculate the average price for that restaurant and determine the corresponding price group. The "expensive_meter" can then be set as the group number.

SELECT *
FROM cypher('DeliveryGraph', $$
MATCH (r:Restaurant)-[:OFFERS]->(i)
RETURN r.name, avg(i.price)
$$) as (average_price agtype);

huangapple
  • 本文由 发表于 2023年6月15日 03:08:00
  • 转载请务必保留本文链接:https://go.coder-hub.com/76476828.html
匿名

发表评论

匿名网友

:?: :razz: :sad: :evil: :!: :smile: :oops: :grin: :eek: :shock: :???: :cool: :lol: :mad: :twisted: :roll: :wink: :idea: :arrow: :neutral: :cry: :mrgreen:

确定