使用密码获取具有不同值的最大记录 [英] Getting the max record with different values using cypher

问题描述

我有一个使用空间插件的包含空间数据的图形.

I have a graph with spatial data using the spatial plugin.

此图的威胁区域"(多边形)可以位于另一个图的顶部，因此它们还具有z-index属性.

This graph has "Threat Zones" (Polygons) which can be on top of the other so they also have a z-index property.

每个威胁区"都附加到1到N个威胁方案，有时多个威胁区"都附加到具有不同属性的同一威胁方案.

Each "Threat Zone" is attached to 1 to N threat scenarios, sometimes multiple "Threat Zones" are attached to the same threat scenario, with different properties.

我正在尝试基于z索引针对特定位置针对每种威胁情况获取最高威胁区域.

I'm trying to get the top threat zone for each threat scenario, based on the z-index, for a specific location.

这是我当前的查询，非常完美:

This is my current query which is almost perfect:

MATCH (asset:Asset{name:'Asset Name'})-[]-(ara:AssetRiskAssessment)
WITH asset, ara
CALL spatial.intersects('threat_zones',asset.wkt) YIELD node 
WITH node, asset, ara
MATCH (node)<-[:FOR]-(tss:ThreatScenarioScore)-[]-(ts:ThreatScenario)
RETURN ts.name, max(node.zindex) AS zindex, tss.intention, tss.capability
ORDER BY ts.name, zindex

我的问题-如果删除tss.intenion，tss.capability，我将得到所需的内容(正确区域的每个相关威胁情况)，但我需要的是tss.intention和.由于区域之间的值不同，因此max函数会将它们视为不同的记录.

My problem - if I remove tss.intenion, tss.capability I'm getting what I'm looking for (each relevant threat scenario of the right zone) but what I need from that is the tss.intention and tss.capability. Since their values is different between zones the max function consider them as different records.

是否有更好的方法来使用max函数来获取我想要的东西和/或使用嵌套查询来提取意图/功能(这就是我所追求的)?

Is there a better way to use the max function to get what I want and / or use a nested query to extract the intention / capability (which is what I'm after)?

推荐答案

我认为您正在寻找 "arg max" 样式查询.在这种情况下，使用 collect 是必经之路:

I think you are looking for an "arg max" style query. In this case, using collect is the way to go:

MATCH (asset:Asset {name:'Asset Name'})-[]-(ara:AssetRiskAssessment)
WITH asset, ara
CALL spatial.intersects('threat_zones',asset.wkt) YIELD node 
WITH node, asset, ara
MATCH (node)<-[:FOR]-(tss:ThreatScenarioScore)-[]-(ts:ThreatScenario)
WITH node, tss, ts
ORDER BY ts.name ASC, node.zindex DESC
WITH
  ts.name AS name,
  collect({
    zindex: node.zindex, intention: tss.intention, capability: tss.capability
  })[0] AS max
RETURN
  name,
  max.zindex AS zindex,
  max.intention AS intention,
  max.capability AS capability

这将根据元组的name(升序)对元组进行排序，但更重要的是，根据元组的zindex降序对其进行排序.因此，当zindex和tss属性收集到列表中时，第一项(索引[0])将保存具有最大zindex值的元素.

This sorts the tuples according to their name (ascending), but more importantly, according to their zindex in a descending order. So when the zindex and tss properties are collected to a list, the first item (index [0]) will hold the elements with the maximum zindex value.

这篇关于使用密码获取具有不同值的最大记录的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！