Cypher:分层排序

Question

我是Cypher的新手。我能够创建一个地理网络(从世界到大陆，从国家到地区)和他们的人口。

​

您可以使用以下命令重现它，或者检查到控制台的链接：http://console.neo4j.org/r/dkh90c

CREATE (n1:Geo {name:'World'}),
(n2:Geo {name:'EMEA'})-[:BELONG_TO]->(n1),
(n4:Geo {name:'NORAM'})-[:BELONG_TO]->(n1),
(n5:Geo {name:'Middle East'})-[:BELONG_TO]->(n2),
(n6:Geo {name:'Africa'})-[:BELONG_TO]->(n2),
(n7:Geo {name:'Europe'})-[:BELONG_TO]->(n2),
(n8:Geo {name:'France'})-[:BELONG_TO]->(n7),
(n9:Geo {name:'Germany'})-[:BELONG_TO]->(n7),
(n10:Geo {name:'Italy'})-[:BELONG_TO]->(n7),
(n11:Geo {name:'United Kingdom'})-[:BELONG_TO]->(n7),
(n12:Geo {name:'England'})-[:BELONG_TO]->(n11),
(n13:Geo {name:'Scotland'})-[:BELONG_TO]->(n11),
(n14:Geo {name:'Wales'})-[:BELONG_TO]->(n11),
(n15:Geo {name:'Northern Ireland'})-[:BELONG_TO]->(n11),
(n16:Geo {name:'United Arab Emirates'})-[:BELONG_TO]->(n5),
(n17:Geo {name:'South Africa'})-[:BELONG_TO]->(n6),
(n18:Geo {name:'Canada'})-[:BELONG_TO]->(n4),
(n19:Geo {name:'United States of America'})-[:BELONG_TO]->(n4),
(n20:Geo {name:'Mexico'})-[:BELONG_TO]->(n4),

(:Population {year:'2014',amount:66.1})-[:LIVE_IN]->(n8),
(:Population {year:'2014',amount:81.2})-[:LIVE_IN]->(n9),
(:Population {year:'2013',amount:59.83})-[:LIVE_IN]->(n10),
(:Population {year:'2011',amount:53.01})-[:LIVE_IN]->(n12),
(:Population {year:'2011',amount:5.295})-[:LIVE_IN]->(n13),
(:Population {year:'2011',amount:3.063})-[:LIVE_IN]->(n14),
(:Population {year:'2011',amount:1.811})-[:LIVE_IN]->(n15),
(:Population {year:'2013',amount:9.346})-[:LIVE_IN]->(n16),
(:Population {year:'2013',amount:52.98})-[:LIVE_IN]->(n17),
(:Population {year:'2013',amount:35.16})-[:LIVE_IN]->(n18),
(:Population {year:'2014',amount:318.9})-[:LIVE_IN]->(n19),
(:Population {year:'2013',amount:122.3})-[:LIVE_IN]->(n20)

我还可以使用以下命令计算每个地理位置的总人口：

MATCH (n:Population)-[r:LIVE_IN]->(g1:Geo)-[:BELONG_TO*0..]->(g2:Geo)
RETURN g2.name AS Geography, SUM(toFloat(n.amount)) AS Population
ORDER BY Population DESC

然而，我对结果的排序并不满意：

​

如你所见，美利坚合众国位于欧洲、中东和非洲地区和欧洲之间。有没有办法在显示结果之前先按“BELONG_TO”层次排序？此外，尽管我的所有数据都只有3位小数，但我不明白为什么SUM()命令返回的小数位数太多了。

谢谢你的帮助。

Answer 1

当然，您应该能够根据路径的长度进行排序：

MATCH path=(n:Population)-[r:LIVE_IN]->(g1:Geo)-[:BELONG_TO*0..]->(g2:Geo)
RETURN g2.name AS Geography, SUM(toFloat(n.amount)) AS Population
ORDER BY length(path) ASC, Population DESC

我认为你会因为floating point math而得到很多小数。我认为你应该能够做到：

ROUND(SUM(toFloat(n.amount)) * 1000.0) / 1000.0

编辑：

您是对的，您需要添加路径长度以按其排序：

MATCH path=(n:Population)-[r:LIVE_IN]->(g1:Geo)-[:BELONG_TO*1..]->(g2:Geo)
RETURN g1.name AS Geography, SUM(toFloat(n.amount)) AS Population, length(path) AS path_length
ORDER BY length(path) ASC, Population DESC 

是的，你绝对是对的，你的道路需要以g2作为世界的终结。你可以通过像我在评论中建议的那样匹配(g2:Geo {name: 'World'})来做到这一点，或者如果你将来碰巧有更多的根节点(也许我们会殖民月球或火星！)，你可以这样做：

MATCH path=(n:Population)-[r:LIVE_IN]->(g1:Geo)-[:BELONG_TO*]->(g2:Geo)
WHERE NOT((g2:Geo)-[:BELONG_TO]->())
RETURN g1.name AS Geography, SUM(toFloat(n.amount)) AS Population, length(path) AS path_length
ORDER BY length(path) ASC, Population DESC 

这意味着我们只需要g2不属于任何内容的路径。否则，例如，g2可以是Europe