Neo4j Aura图谱数据模型问题：查询返回非目标区域犯罪数据

一、问题背景 #

构建犯罪数据图谱时，查询特定AreaUnit（如Glamorgan）会错误返回其他区域（如Glen Innes North）的Victimisations数据，同时需要优化数值数据建模以支持后续聚合与对比分析。

二、CSV样本数据 #

ANZSOC Group,YYYY,Territorial Authority,Area Unit,Victimisations
Aggravated Robbery,2024,Auckland,Glen Innes North,4
Aggravated Sexual Assault,2024,Auckland,Glamorgan,1
Blackmail and Extortion,2024,Auckland,Glen Innes North,4
Common Assault,2024,Auckland,Glen Innes North,2
Illegal Use of a Motor Vehicle,2024,Auckland,Glamorgan,5
Illegal Use of a Motor Vehicle,2024,Auckland,Glen Innes North,17
Serious Assault Not Resulting in Injury,2024,Auckland,Glen Innes North,3
Serious Assault Resulting in Injury,2024,Auckland,Glen Innes North,7
"Theft (Except Motor Vehicles), N.E.C.",2024,Auckland,Glamorgan,1
"Theft (Except Motor Vehicles), N.E.C.",2024,Auckland,Glen Innes North,7
Theft From a Person (Excluding By Force),2024,Auckland,Glen Innes North,1
Theft of Motor Vehicle Parts or Contents,2024,Auckland,Glamorgan,4
Theft of Motor Vehicle Parts or Contents,2024,Auckland,Glen Innes North,17
"Unlawful Entry With Intent/Burglary, Break and Enter",2024,Auckland,Glamorgan,7
"Unlawful Entry With Intent/Burglary, Break and Enter",2024,Auckland,Glen Innes North,34

三、原数据模型创建脚本 #

pre_ingest:
  - CREATE CONSTRAINT crimetype_anzsocgroup IF NOT EXISTS FOR (n:CrimeType) REQUIRE n.anzsocGroup IS UNIQUE;
  - CREATE CONSTRAINT territorialauthority_cityname IF NOT EXISTS FOR (n:TerritorialAuthority) REQUIRE n.cityName IS UNIQUE;
  - CREATE CONSTRAINT areaunit_areaname IF NOT EXISTS FOR (n:AreaUnit) REQUIRE n.areaName IS UNIQUE;
files:
- chunk_size: 100
  cql: |
    WITH $dict.rows AS rows
    UNWIND rows AS row
    MERGE (n:CrimeType {anzsocGroup: row.`ANZSOC Group`})
  url: $BASE/
- chunk_size: 100
  cql: |
    WITH $dict.rows AS rows
    UNWIND rows AS row
    MERGE (n:TerritorialAuthority {cityName: row.`Territorial Authority`})
  url: $BASE/
- chunk_size: 100
  cql: |
    WITH $dict.rows AS rows
    UNWIND rows AS row
    MERGE (n:AreaUnit {areaName: row.`Area Unit`})
  url: $BASE/
- chunk_size: 100
  cql: |-
    WITH $dict.rows AS rows
    UNWIND rows AS row
    MERGE (n:CrimeEvent {year:toIntegerOrNull(row.YYYY), victimisation:toIntegerOrNull(row.Victimisations)})
  url: $BASE/
- chunk_size: 100
  cql: |
    WITH $dict.rows AS rows
    UNWIND rows as row
    MATCH (source:TerritorialAuthority {cityName: row.`Territorial Authority`})
    MATCH (target:AreaUnit {areaName: row.`Area Unit`})
    MERGE (source)-[n:HAS_SUB_REGION]->(target)
  url: $BASE/
- chunk_size: 100
  cql: |
    WITH $dict.rows AS rows
    UNWIND rows as row
    MATCH (areaUnit:AreaUnit {areaName: row.`Area Unit`})
    MATCH (crimeType:CrimeType {anzsocGroup: row.`ANZSOC Group`})
    MATCH (crimeEvent:CrimeEvent {year:toIntegerOrNull(row.YYYY), victimisation:toIntegerOrNull(row.Victimisations)})
    MERGE (areaUnit)-[c:HAS_CRIME]->(crimeType)-[t:HAS_CRIME_COUNT]->(crimeEvent)
  url: $BASE/

四、查询异常情况 #

原查询语句 #

MATCH path = (n:AreaUnit {areaName:"Glamorgan"})-[c:HAS_CRIME]->(t:CrimeType)-[:HAS_CRIME_COUNT]->(e:CrimeEvent)
RETURN path

错误结果 #

返回了Glamorgan区域不存在的Victimisations数值（如17、7、34等，实际属于Glen Innes North），导致数据混淆。

五、问题解决与优化方案 #

1. 修复查询异常：调整数据模型关系 #

原模型核心问题是CrimeEvent仅用year和victimisation作为唯一标识，不同区域的同数值事件被合并，进而错误关联到所有同类型犯罪的区域。

修正后的建模脚本（推荐方案：关系属性存储统计值）

删除原CrimeEvent节点创建步骤，将统计数据存储在AreaUnit与CrimeType的关联关系中：

pre_ingest:
  - CREATE CONSTRAINT crimetype_anzsocgroup IF NOT EXISTS FOR (n:CrimeType) REQUIRE n.anzsocGroup IS UNIQUE;
  - CREATE CONSTRAINT territorialauthority_cityname IF NOT EXISTS FOR (n:TerritorialAuthority) REQUIRE n.cityName IS UNIQUE;
  - CREATE CONSTRAINT areaunit_areaname IF NOT EXISTS FOR (n:AreaUnit) REQUIRE n.areaName IS UNIQUE;
files:
- chunk_size: 100
  cql: |
    WITH $dict.rows AS rows
    UNWIND rows AS row
    MERGE (n:CrimeType {anzsocGroup: row.`ANZSOC Group`})
  url: $BASE/
- chunk_size: 100
  cql: |
    WITH $dict.rows AS rows
    UNWIND rows AS row
    MERGE (n:TerritorialAuthority {cityName: row.`Territorial Authority`})
  url: $BASE/
- chunk_size: 100
  cql: |
    WITH $dict.rows AS rows
    UNWIND rows AS row
    MERGE (n:AreaUnit {areaName: row.`Area Unit`})
  url: $BASE/
- chunk_size: 100
  cql: |
    WITH $dict.rows AS rows
    UNWIND rows as row
    MATCH (source:TerritorialAuthority {cityName: row.`Territorial Authority`})
    MATCH (target:AreaUnit {areaName: row.`Area Unit`})
    MERGE (source)-[n:HAS_SUB_REGION]->(target)
  url: $BASE/
- chunk_size: 100
  cql: |
    WITH $dict.rows AS rows
    UNWIND rows as row
    MATCH (areaUnit:AreaUnit {areaName: row.`Area Unit`})
    MATCH (crimeType:CrimeType {anzsocGroup: row.`ANZSOC Group`})
    MERGE (areaUnit)-[r:HAS_CRIME_STATS {year: toIntegerOrNull(row.YYYY), victimisations: toIntegerOrNull(row.Victimisations)}]->(crimeType)
  url: $BASE/

修正后的查询语句

MATCH (n:AreaUnit {areaName:"Glamorgan"})-[r:HAS_CRIME_STATS]->(t:CrimeType)
RETURN n.areaName, t.anzsocGroup, r.year, r.victimisations

2. 适合聚合与对比的数值数据建模方法 #

方法一：关系属性存储统计值（推荐）

将year、victimisations存储在关联关系中，便于直接按维度聚合：

• 按区域统计年度总受害数：

  MATCH (a:AreaUnit)-[r:HAS_CRIME_STATS]->()
  WHERE r.year = 2024
  RETURN a.areaName, SUM(r.victimisations) AS total_victimisations
  ORDER BY total_victimisations DESC
  

• 对比同一犯罪类型在不同区域的数值：

  MATCH (a:AreaUnit)-[r:HAS_CRIME_STATS]->(t:CrimeType {anzsocGroup:"Illegal Use of a Motor Vehicle"})
  WHERE r.year = 2024
  RETURN a.areaName, r.victimisations
  ORDER BY r.victimisations DESC
  

方法二：节点存储预聚合数据

如果需要存储多维度聚合结果（如月度、季度统计），创建CrimeAggregation节点：

MERGE (a:AreaUnit {areaName:"Glamorgan"})
MERGE (t:CrimeType {anzsocGroup:"Illegal Use of a Motor Vehicle"})
MERGE (agg:CrimeAggregation {
    areaName: a.areaName,
    crimeType: t.anzsocGroup,
    year: 2024,
    totalVictimisations: 5,
    avgMonthly: 0.42
})
MERGE (a)-[:HAS_AGGREGATE]->(agg)
MERGE (t)-[:AGGREGATED_FOR]->(agg)

该方式适合预计算聚合结果，提升查询效率。

方法三：图算法动态聚合分析

对于复杂对比（如区域犯罪相似度），使用Neo4j GDS库计算：

CALL gds.nodeSimilarity.stream({
    nodeProjection: 'AreaUnit',
    relationshipProjection: {
        HAS_CRIME_STATS: {
            type: 'HAS_CRIME_STATS',
            properties: 'victimisations',
            orientation: 'REVERSE'
        }
    }
})
YIELD node1, node2, similarity
RETURN gds.util.asNode(node1).areaName AS area1, gds.util.asNode(node2).areaName AS area2, similarity
ORDER BY similarity DESC

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