以过程方式开发密码查询

Question

我一直在用家谱图。

我想回答一个简单的问题：

送还任何家庭

• 有3个或3个以上的老年痴呆症成员
• 已经去世的人
• 他们来自一个大部分是左撇子成员的家庭(至少50%)。

我模拟了两个家庭，其中一个满足标准，另一个不满足(问题末尾的代码)。

大家庭符合标准。较小的家庭不这样做。(sidenote:我在每个家庭上放置了定义节点，以便于区分它们，并在匹配时只返回一个家庭)。

​

​

我想我已经找到了解决方案的一些部分，但我不知道如何以一种连贯的方式将它们粘合在一起。

例如，(感谢@Tim 这里和这里)，我可以找到符合以下条件的树：

MATCH (f:Family)<-[:FAMILY]-(person:Person) 
WHERE person.Diagnosis = "Alzheimers" 
WITH f, count(person) AS Count 
WHERE Count > 2 

// Then report the family members as a single collection
MATCH (a:Person)-[r1:FAMILY]-(f)
RETURN collect(DISTINCT a) 

我可以计算出总共有多少个节点：

MATCH (n { family_ID: 'A' })--(x)
RETURN  count(x) as Tot

我还能计算出有多少人是左撇子：

MATCH (f:Family)<-[:FAMILY]-(person:Person) 
WHERE person.Handedness = 'Left' 
WITH f, count(person) AS Count
RETURN Count  

但到目前为止，我试图以某种过程的方式开发这个查询，还没有奏效。

我试了一些这样的方法：

MATCH (f:Family)<-[:FAMILY]-(person:Person) 
WHERE person.Diagnosis = "Alzheimers" 
AND person.Handedness = "Left"
WITH f, count(person) AS Count 
WHERE Count > 2 

MATCH (a:Person)-[r1:FAMILY]-(f)
WITH a, 

MATCH (n { family_ID: 'A' })--(x)
RETURN  count(x) as Tot

但这基本上是胡说八道。我得到了一个语法错误，在所有的事情。

在其他查询语言中，通常建议一步一步地进行，也许可以通过一系列临时表来传递结果，直到数据以正确的形式出现为止。我似乎不知道如何在CQL中做类似的事情。也许我不知道如何把结果排入收集中？我不知道该怎么处理这个。

我创建数据库的代码：

// First family:  family_ID = A.  This family has 3 members with Alzheimers who are not alive, and more than half of them are Left handed
CREATE 
    (   a:Person {name: 'a',    id:'1', Gender:'Male',   Diagnosis: 'Alzheimers', `Is Alive?`: 'No',   Handedness: 'Left',  `Risk Score`: 'PURPLE'}),
    ( aSP:Person {name: 'aSP',  id:'2', Gender:'Female', Diagnosis: 'Alzheimers', `Is Alive?`: 'No',   Handedness: 'Left', `Risk Score`: 'GIRAFFE'}),
    (   b:Person {name: 'b',    id:'3', Gender:'Male',   Diagnosis: 'Normal',     `Is Alive?`: 'No',   Handedness: 'Left', `Risk Score`: 'PURPLE'}),
    ( bSP:Person {name: 'bSP',  id:'4', Gender:'Female', Diagnosis: 'Alzheimers', `Is Alive?`: 'No',   Handedness: 'Right', `Risk Score`: 'GIRAFFE'}),
    (bSib:Person {name: 'bSib', id:'5', Gender:'Female', Diagnosis: 'MCI',        `Is Alive?`: 'No',   Handedness: 'Left',  `Risk Score`: 'GIRAFFE'}),
    (   c:Person {name: 'c',    id:'6', Gender:'Male',   Diagnosis: 'MCI',        `Is Alive?`: 'No',   Handedness: 'Right', `Risk Score`: 'PURPLE'}),
    (cSib:Person {name: 'cSib', id:'7', Gender:'Female', Diagnosis: 'Alzheimers',  `Is Alive?`: 'Yes', Handedness: 'Left',  `Risk Score`: 'GIRAFFE'})

CREATE
    (a)-[:SPOUSE]->(aSP),
    (b)-[:CHILD]->(a),
    (b)-[:CHILD]->(aSP),
    (b)-[:SPOUSE]->(bSP),
    (bSib)-[:SIBLING]->(b),
    (bSib)-[:CHILD]->(aSP),
    (c)-[:CHILD]->(b),
    (c)-[:CHILD]->(bSP),
    (cSib)-[:SIBLING]->(c),
    (cSib)-[:CHILD]->(bSP)


// Second family:  family_ID = B.  This family does not meet the criteria
CREATE 
    (   a2:Person {name: 'a2',    id:'8',  Gender:'Male',   Diagnosis: 'Alzheimers', `Is Alive?`: 'Yes',   Handedness: 'Right',  `Risk Score`: 'PURPLE'}),
    ( aSP2:Person {name: 'aSP2',  id:'9',  Gender:'Female', Diagnosis: 'Normal',     `Is Alive?`: 'No',    Handedness: 'Left', `Risk Score`: 'GIRAFFE'}),
    (   b2:Person {name: 'b2',    id:'10', Gender:'Male',   Diagnosis: 'Normal',     `Is Alive?`: 'No',    Handedness: 'Left', `Risk Score`: 'PURPLE'})


CREATE 
    (a2)-[:SPOUSE]->(aSP2),
    (b2)-[:CHILD]->(a2),
    (b2)-[:CHILD]->(aSP2)


// Create the definition node for the first family:
CREATE 
    (famA:Family {family_ID:'A'}) 
    WITH famA
    MATCH (a:Person {name:"a"})-[*]-(b:Person)  
    MERGE (famA:Family)<-[:FAMILY]-(a) 
    MERGE (famA:Family)<-[:FAMILY]-(b)


// Create the definition node for the second family:
CREATE (famB:Family {family_ID:'B'}) 
    WITH famB
    MATCH (a2:Person {name:"a2"})-[*]-(b2:Person)  
    MERGE (famB:Family)<-[:FAMILY]-(a2) 
    MERGE (famB:Family)<-[:FAMILY]-(b2)

Answer 1

我认为过滤器 -这是你需要的：

MATCH (Family:Family)<-[r:FAMILY]-(Person:Person)
WITH
  Family, collect(Person) as F
WITH
  Family, size(F) as sF, F,
  filter(x in F where x.Handedness='Left') as LH,
  filter(x in F where x.Diagnosis="Alzheimers" AND x.`Is Alive?`='No') as AD
WHERE
    (size(LH) >= sF/2) AND (size(AD) >= 3)
RETURN Family, 
       F as wholeFamily,     
       extract(n IN AD | n.name) as whoAD,
       size(LH) as sLH, size(AD) as sAD

Answer 2

您可以通过一系列过滤器链接引用以获得最终答案。要获得字段上不同函数的计数，必须重复查询(我发现这很不幸--应该有一种方法从单个图结果中计数多个项)。

在下面的示例中，我根据老年痴呆症的病例数获得一个家庭引用，然后通过查询的其余部分使用该引用来统计组数，然后在最后报告结论。

最终结果如下：

// Find the families with 2 or more Alzheimers cases
MATCH (fam:Family)<-[:FAMILY]-(person:Person)
WHERE person.Diagnosis = "Alzheimers"
WITH fam, count(person) AS fAlzCount
WHERE fAlzCount > 2
with fam

// Count the # of left-handed family members 
MATCH (fam)-[:FAMILY]-(person:Person)
WHERE person.Handedness = 'Left' 
WITH fam, count(person) AS LeftCount

// Count the total # of  family members 
MATCH (fam)-[:FAMILY]-(person:Person)
WITH fam, LeftCount, count(person) AS AllCount

// and then filter for families where more than 1/2 are left-handed
// tofloat() is used to convert the integer results so we can test 
// against a float at the end
WHERE  tofloat(LeftCount) / tofloat(AllCount) > 0.5
RETURN fam, LeftCount, AllCount