如何在Neo4j中使用密码查询为多列聚合函数 [英] How to do aggregate functions for multiple columns using cypher query in Neo4j
问题描述
我在图数据库中创建了2个节点,始发机场和目的地机场.它与名为"delayed_by"的属性相关.
I have 2 nodes created in graph database, origin airport and destination airport. It is related by a property named 'delayed_by'.
MATCH (origin:origin_airport {name: row.ORIGIN}),
(destination:dest_airport {name: row.DEST})
CREATE (origin)-[:delayed_by {carr_delay: row.carr_delay}]->(destination)
CREATE (origin)-[:delayed_by {weather_delay: row.weather_delay}]->
(destination)
CREATE (origin)-[:delayed_by {nas_delay: row.nas_delay}]->(destination)
delayed_by保留由于载波延迟,天气延迟和nas延迟引起的值延迟.在这里,我需要对起点和目的地进行分组,并使用Neo4j中的密码查询来计算所有3个延迟之和的平均值.我的输入文件以表格格式表示,其格式如下:
delayed_by holds the value delays caused due to carrier delay, weather delay and nas delay. Here I need to group_by origin and destination and calculate the average value of sum of all 3 delays using cypher query in Neo4j. Representing in tabular format, my input file will of the format as described below:
ORIGIN DEST carr_delay weather_delay nas_delay
ABE ATL 492 56 56
ABE DTW 412 0 47
ABQ ATL 181 0 218
我希望结果采用以下格式.
I am expecting the result in the below format.
ORIGIN DEST Avg_delay
ABE ATL 201.33
ABE DTW 153
ABQ ATL 133
我正在使用以下查询:
MATCH (oa:origin_airport)-[d:delayed_by]->(da:dest_airport)
RETURN oa.name AS Origin, da.name AS Destination,
AVG((toFloat(d.carr_delay))+(toFloat(d.weather_delay))+
(toFloat(d.nas_delay))) As avg_delay
ORDER BY avg_delay DESC
LIMIT 10
但是获取Avg_delay的空值.
But getting null values for Avg_delay.
ORIGIN DEST Avg_delay
ABE ATL NULL
ABE DTW NULL
ABQ ATL NULL
推荐答案
1)我建议更改模型:为延迟关系添加delay的属性类型,并将delay值保存在属性值中.
1) I recommend to change the model: for the delay relationship add the property type of delay, and the delay value save in the property value.
MATCH (origin:origin_airport {name: row.ORIGIN}),
(destination:dest_airport {name: row.DEST})
CREATE (origin)
-[:delayed_by {type: "carr_delay", value: row.carr_delay}]->
(destination)
CREATE (origin)
-[:delayed_by {type: "weather_delay", value: row.weather_delay}]->
(destination)
CREATE (origin)
-[:delayed_by {type: "nas_delay", value: row.nas_delay}]->
(destination)
2)和所需的查询:
MATCH (oa:origin_airport)-[d:delayed_by]->(da:dest_airport)
RETURN oa.name AS Origin,
da.name AS Destination,
AVG( toFloat(d.value) ) As avg_delay
ORDER BY avg_delay DESC
LIMIT 10
这篇关于如何在Neo4j中使用密码查询为多列聚合函数的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!