我在图数据库中创建了3个节点,分别是起点机场、终点机场和运营商。它们通过名为'canceled_by'的属性相关联。
MATCH (origin:origin_airport {name: row.ORIGIN}),
(destination:dest_airport {name: row.DEST}),
(carrier:Carrier {name: row.UNIQUE_CARRIER})
CREATE (origin)-[:cancelled_by {cancellation: row.count}]->(carrier)
CREATE (origin)-[:cancelled_by {cancellation: row.count}]->(destination)
CREATE (origin)-[:operated_by {carrier: row.UNIQUE_CARRIER}]->(carrier)
cancelled_by表示特定承运人被取消的次数。我的输入文件将采用以下格式:
ORIGIN UNIQUE_CARRIER DEST Cancelled
ABE DL ATL 1
ABE EV ATL 1
ABE EV DTW 3
ABE EV ORD 3
ABQ DL DFW 2
ABQ B6 JFK 2
我需要计算每个承运人的取消比例。我期望得到以下结果:
UNIQUE_CARRIER DEST Percentage_Cancelled
DL 25%
EV 58.33%
B6 16.66%
Example: Total number of cancellation = 12
No of cancellation for DL = 3
Percentage of cancellation for DL = (3/12)*100 = 25%
以下查询可以得到每个航空公司取消的总数:
MATCH ()-[ca:cancelled_by]->(c:Carrier)
RETURN c.name AS Carrier,
SUM(toFloat(ca.cancellation)) As sum
ORDER BY sum DESC
LIMIT 10
我尝试了下面的查询来计算百分比:
MATCH ()-[ca:cancelled_by]->(c:Carrier)
WITH SUM(toFloat(ca.cancellation)) As total
MATCH ()-[ca:cancelled_by]->(c:Carrier)
RETURN c.name AS Carrier,
(toFloat(ca.cancellation)/total)*100 AS percent
ORDER BY percent DESC
LIMIT 10
但它没有按组计算百分比,而是单独计算百分比。
Carrier sum
DL 0.36862408915559364
DL 0.34290612944706383
DL 0.3171881697385341
如何使用Neo4j中的cypher查询按group_by计算百分比?