使用Neo4j Cypher批量生成多患者就诊NEXT_EVENT关系遇阻

问题背景 #

在单患者单就诊场景下，已成功通过时间戳创建NEXT_EVENT关系，但批量处理多患者多就诊时遇到问题。目标是识别每个就诊的所有相关节点，为同一就诊内的节点按时间戳顺序创建时序化的NEXT_EVENT关系。

单患者场景示例代码 #

// Create patient node
CREATE (p:Patient {patientID: "P001", name: "John Doe", birthdate: "1980-01-01"})

// Create encounter node
CREATE (e:Encounter {encounterID: "E001", patientID: "P001", timestamp: "2023-03-01T00:00:00", startTime: "2023-03-01T00:00:00", endTime: "2023-03-10T00:00:00"})

// Create relationships between patient, encounter, and other nodes
MATCH (p:Patient {patientID: "P001"})
MATCH (e:Encounter {encounterID: "E001"})
CREATE (p)-[:HAS_ENCOUNTER]->(e)

// Create nodes for a single patient and their components
CREATE (a:Admission {patientID: "P001", timestamp: "2023-03-01T01:00:00"})
CREATE (d:Discharge {patientID: "P001", timestamp: "2023-03-10T00:00:00"})
CREATE (dx:Diagnosis {patientID: "P001", diagnosis: "Diabetes", timestamp: "2023-03-01T02:00:00"})
CREATE (vs1:VitalSign {vitalSignID: "VS001", testName: "Blood Pressure", timestamp: "2023-03-01T10:00:00", systolic: 120, diastolic: 80})
CREATE (vs2:VitalSign {vitalSignID: "VS002", testName: "Heart Rate", timestamp: "2023-03-01T10:00:00", value: 75})
CREATE (pr1:Procedure {procedureID: "PR001", procedureName: "ECG", timestamp: "2023-03-01T15:00:00"})
CREATE (mo1:MedicationOrder {orderID: "M001", medicationName: "Lisinopril", dosage: "10mg", timestamp: "2023-03-02T08:00:00"})
CREATE (lo1:LabOrder {orderID: "L001", testName: "CBC", timestamp: "2023-03-02T09:00:00"})
CREATE (hgb:Component {componentID: "C001", testName: "Hemoglobin", timestamp: "2023-03-02T12:00:00", value: 12.5})
CREATE (hct:Component {componentID: "C002", testName: "Hematocrit", timestamp: "2023-03-02T12:00:00", value: 36.0})
CREATE (mo2:MedicationOrder {orderID: "M002", medicationName: "Metformin", dosage: "500mg", timestamp: "2023-03-03T10:00:00"})
CREATE (pr2:Procedure {procedureID: "PR002", procedureName: "IV insertion", timestamp: "2023-03-03T10:00:00"})
CREATE (lo2:LabOrder {orderID: "L002", testName: "BMP", timestamp: "2023-03-04T14:00:00"})
CREATE (na:Component {componentID: "C003", testName: "Sodium", timestamp: "2023-03-04T17:00:00", value: 140})
CREATE (k:Component {componentID: "C004", testName: "Potassium", timestamp: "2023-03-04T17:00:00", value: 4.0})
CREATE (cl:Component {componentID: "C005", testName: "Chloride", timestamp: "2023-03-04T17:00:00", value: 100})

MATCH (e:Encounter {encounterID: "E001"})
MATCH (a:Admission)
CREATE (e)-[:HAS_ADMISSION]->(a)

// Create NEXT_EVENT relationships based on timestamps
MATCH (n)
WHERE n:Encounter OR n:Admission OR n:Diagnosis OR n:MedicationOrder OR n:LabOrder OR n:Procedure OR n:VitalSign OR n:Component OR n:Discharge
WITH n.timestamp AS ts, collect(n) AS nodes_at_timestamp
ORDER BY ts
WITH collect({ts: ts, nodes: nodes_at_timestamp}) AS timestamp_groups
UNWIND range(0, size(timestamp_groups) - 2) AS i
WITH timestamp_groups[i] AS group1, timestamp_groups[i + 1] AS group2
UNWIND group1.nodes AS n1
UNWIND group2.nodes AS n2
CREATE (n1)-[:NEXT_EVENT]->(n2)

多患者示例数据 #

// Create patient nodes
CREATE (p1:Patient {patientID: "P001", name: "John Doe", birthdate: "1980-01-01"})
CREATE (p2:Patient {patientID: "P002", name: "Jane Doe", birthdate: "1985-01-01"})

// Create encounter nodes for each patient
CREATE (e1:Encounter {encounterID: "E001", patientID: "P001", timestamp: "2023-03-01T00:00:00", startTime: "2023-03-01T00:00:00", endTime: "2023-03-10T00:00:00"})
CREATE (e2:Encounter {encounterID: "E002", patientID: "P001", timestamp: "2023-04-01T08:00:00", startTime: "2023-04-01T00:00:00", endTime: "2023-04-10T00:00:00"})
CREATE (e3:Encounter {encounterID: "E003", patientID: "P002", timestamp: "2023-02-01T00:00:00", startTime: "2023-02-01T00:00:00", endTime: "2023-02-10T00:00:00"})
CREATE (e4:Encounter {encounterID: "E004", patientID: "P002", timestamp: "2023-03-01T08:00:00", startTime: "2023-03-01T00:00:00", endTime: "2023-03-10T00:00:00"})

//Encounter 1 - Patient 1
// Create nodes for Encounter 1
CREATE (a1:Admission {patientID: "P001", encounterID: "E001", timestamp: "2023-03-01T01:00:00", location: "ICU"})
CREATE (d1:Discharge {patientID: "P001", encounterID: "E001", timestamp: "2023-03-06T00:00:00", disposition: "Home"})
CREATE (dx1:Diagnosis {patientID: "P001", encounterID: "E001", diagnosis: "Diabetes", timestamp: "2023-03-01T02:00:00"})
CREATE (vs1:VitalSign {patientID: "P001", encounterID: "E001", vitalSignID: "VS001", testName: "Blood Pressure", timestamp: "2023-03-01T10:00:00", systolic: 120, diastolic: 80})
CREATE (vs2:VitalSign {patientID: "P001", encounterID: "E001", vitalSignID: "VS002", testName: "Heart Rate", timestamp: "2023-03-01T10:00:00", value: 75})

尝试过的代码及问题 #

版本1：仅为首个就诊创建关系 #

// Create NEXT_EVENT relationships based on timestamps
MATCH (e:Encounter)
WITH collect(e) AS encounters
UNWIND encounters AS encounter
MATCH (n)
WHERE (n:Encounter OR n:Admission OR n:Diagnosis OR n:MedicationOrder OR n:LabOrder OR n:Procedure OR n:VitalSign OR n:Component OR n:Discharge) 
AND n.encounterID = encounter.encounterID
WITH n.encounterID AS eid, n.timestamp AS ts, collect(n) AS nodes_at_timestamp
ORDER BY eid, ts
WITH eid, collect({ts: ts, nodes: nodes_at_timestamp}) AS timestamp_groups
UNWIND range(0, size(timestamp_groups) - 2) AS i
WITH timestamp_groups[i] AS group1, timestamp_groups[i + 1] AS group2
UNWIND group1.nodes AS n1
UNWIND group2.nodes AS n2
CREATE (n1)-[:NEXT_EVENT]->(n2)

问题：仅能为首个就诊创建NEXT_EVENT关系，其他就诊未处理。

版本2：类似版本1，无改善 #

// Version 2 - Same as above
MATCH (e:Encounter)
WITH collect(e) as encounters
UNWIND encounters as e
MATCH (n)
WHERE (n:Encounter OR n:Admission OR n:Diagnosis OR n:MedicationOrder OR n:LabOrder OR n:Procedure OR n:VitalSign OR n:Component OR n:Discharge) 
AND n.encounterID = e.encounterID
WITH e, n.timestamp AS ts, collect(n) AS nodes_at_timestamp
ORDER BY ts
WITH e, collect({ts: ts, nodes: nodes_at_timestamp}) AS timestamp_groups
UNWIND range(0, size(timestamp_groups) - 2) AS i
WITH e, timestamp_groups[i] AS group1, timestamp_groups[i + 1] AS group2
UNWIND group1.nodes AS n1
UNWIND group2.nodes AS n2
CREATE (n1)-[:NEXT_EVENT]->(n2)

问题：和版本1一样，无法覆盖所有就诊。

版本3：逻辑问题导致关系创建不完整 #

// Version 3
// Collect all encounters
MATCH (e:Encounter)
WITH collect(e) as encounters
// Loop through each encounter
UNWIND encounters as e
WITH e
MATCH (n)
WHERE (n:Encounter OR n:Admission OR n:Diagnosis OR n:MedicationOrder OR n:LabOrder OR n:Procedure OR n:VitalSign OR n:Component OR n:Discharge)
AND n.encounterID = e.encounterID
WITH e, n
ORDER BY n.timestamp
// Create a relationship between each node and the next one within the same encounter
WITH e, collect(n) as nodes
UNWIND range(0, size(nodes)-2) as i
CREATE (nodes[i])-[:NEXT_EVENT]->(nodes[i+1])

问题：未正确按就诊分组处理，导致部分关系缺失。

版本4：线性关系但未处理同时间戳节点 #

/// Version 4 - Works well but creates a list and creates linear relationships// Collect all encounters
MATCH (e:Encounter)
WITH collect(e) as encounters
// Loop through each encounter
UNWIND encounters as e
MATCH (n)
WHERE (n:Encounter OR n:Admission OR n:Diagnosis OR n:MedicationOrder OR n:LabOrder OR n:Procedure OR n:VitalSign OR n:Component OR n:Discharge)
AND n.encounterID = e.encounterID
WITH e.encounterID as encounterID, n
ORDER BY n.timestamp
// Create a list of nodes for each encounter
WITH encounterID, collect(n) as nodes
ORDER BY encounterID
// Loop through each encounter
UNWIND range(0, size(nodes)-2) as i
// Create a relationship between each node and the next one within the same encounter
WITH nodes[i] as n1, nodes[i+1] as n2
MERGE (n1)-[:NEXT_EVENT]->(n2)

问题：仅创建线性的节点间关系，同时间戳的节点未互相连接到下一个时间戳的所有节点，不符合原始单患者场景的逻辑。

解决方案 #

以下Cypher脚本可正确为每个就诊内的所有节点按时间戳创建NEXT_EVENT关系，同时保留同时间戳节点到下一时间戳所有节点的连接逻辑：

// 按就诊分组，收集每个就诊的所有相关节点
MATCH (n)
WHERE (n:Encounter OR n:Admission OR n:Diagnosis OR n:MedicationOrder OR n:LabOrder OR n:Procedure OR n:VitalSign OR n:Component OR n:Discharge)
AND exists(n.encounterID)  // 确保节点关联到具体就诊
WITH n.encounterID AS eid, n.timestamp AS ts, collect(n) AS nodes_at_timestamp
ORDER BY eid, ts  // 先按就诊ID分组，再按时间戳排序
WITH eid, collect({ts: ts, nodes: nodes_at_timestamp}) AS timestamp_groups
// 遍历每个就诊的时间戳组，创建跨时间戳的NEXT_EVENT关系
UNWIND range(0, size(timestamp_groups) - 2) AS i
WITH timestamp_groups[i] AS group1, timestamp_groups[i + 1] AS group2
UNWIND group1.nodes AS n1
UNWIND group2.nodes AS n2
MERGE (n1)-[:NEXT_EVENT]->(n2)  // 使用MERGE避免重复创建关系

脚本说明 #

1. 按就诊分组：通过n.encounterID将节点按就诊归类，确保每个就诊的节点独立处理。
2. 时间戳排序：对每个就诊内的节点按时间戳分组并排序，保证时序正确。
3. 跨时间戳连接：将前一个时间戳的所有节点与后一个时间戳的所有节点创建NEXT_EVENT关系，和单患者场景逻辑一致。
4. 避免重复：使用MERGE替代CREATE，防止重复运行脚本时创建冗余关系。

内容的提问来源于stack exchange，提问作者Gabriel