如何通过中间节点关系查找高内聚节点组（附Neo4j示例）

有一些猫喜欢攀爬不同种类的树，需要识别出**喜欢攀爬树木偏好重叠度至少50%**的猫组。

示例规则：

• Lily和Bella的偏好重叠度为67%，应归为同一组
• Luna会攀爬所有树木，不加入该组
• Cleo与Lily、Bella的偏好完全无交集，重叠度为0%，不加入该组

CREATE (:Cat { name: 'Luna' });
CREATE (:Cat { name: 'Lily' });
CREATE (:Cat { name: 'Bella' });
CREATE (:Cat { name: 'Lucy' });
CREATE (:Cat { name: 'Nala' });
CREATE (:Cat { name: 'Callie' });
CREATE (:Cat { name: 'Kitty' });
CREATE (:Cat { name: 'Cleo' });

CREATE (:Tree { type: 'Red_maple' });
CREATE (:Tree { type: 'Loblolly_pine' });
CREATE (:Tree { type: 'American_sweetgum' });
CREATE (:Tree { type: 'Douglas_fir' });
CREATE (:Tree { type: 'Quaking_aspen' });
CREATE (:Tree { type: 'Sugar_maple' });
CREATE (:Tree { type: 'Balsam_fir' });
CREATE (:Tree { type: 'Flowering_dogwood' });

MATCH (c:Cat), (t:Tree) WHERE c.name = 'Lily' AND t.type = 'Red_maple' CREATE (c)-[:LIKES_TO_CLIMB]->(t);
MATCH (c:Cat), (t:Tree) WHERE c.name = 'Lily' AND t.type = 'Loblolly_pine' CREATE (c)-[:LIKES_TO_CLIMB]->(t);
MATCH (c:Cat), (t:Tree) WHERE c.name = 'Lily' AND t.type = 'American_sweetgum' CREATE (c)-[:LIKES_TO_CLIMB]->(t);

MATCH (c:Cat), (t:Tree) WHERE c.name = 'Bella' AND t.type = 'Red_maple' CREATE (c)-[:LIKES_TO_CLIMB]->(t);
MATCH (c:Cat), (t:Tree) WHERE c.name = 'Bella' AND t.type = 'Loblolly_pine' CREATE (c)-[:LIKES_TO_CLIMB]->(t);
MATCH (c:Cat), (t:Tree) WHERE c.name = 'Bella' AND t.type = 'Douglas_fir' CREATE (c)-[:LIKES_TO_CLIMB]->(t);

MATCH (c:Cat), (t:Tree) WHERE c.name = 'Luna' AND t.type = 'Red_maple' CREATE (c)-[:LIKES_TO_CLIMB]->(t);
MATCH (c:Cat), (t:Tree) WHERE c.name = 'Luna' AND t.type = 'Loblolly_pine' CREATE (c)-[:LIKES_TO_CLIMB]->(t);
MATCH (c:Cat), (t:Tree) WHERE c.name = 'Luna' AND t.type = 'American_sweetgum' CREATE (c)-[:LIKES_TO_CLIMB]->(t);
MATCH (c:Cat), (t:Tree) WHERE c.name = 'Luna' AND t.type = 'Douglas_fir' CREATE (c)-[:LIKES_TO_CLIMB]->(t);
MATCH (c:Cat), (t:Tree) WHERE c.name = 'Luna' AND t.type = 'Quaking_aspen' CREATE (c)-[:LIKES_TO_CLIMB]->(t);
MATCH (c:Cat), (t:Tree) WHERE c.name = 'Luna' AND t.type = 'Sugar_maple' CREATE (c)-[:LIKES_TO_CLIMB]->(t);
MATCH (c:Cat), (t:Tree) WHERE c.name = 'Luna' AND t.type = 'Balsam_fir' CREATE (c)-[:LIKES_TO_CLIMB]->(t);
MATCH (c:Cat), (t:Tree) WHERE c.name = 'Luna' AND t.type = 'Flowering_dogwood' CREATE (c)-[:LIKES_TO_CLIMB]->(t);

MATCH (c:Cat), (t:Tree) WHERE c.name = 'Cleo' AND t.type = 'Sugar_maple' CREATE (c)-[:LIKES_TO_CLIMB]->(t);
MATCH (c:Cat), (t:Tree) WHERE c.name = 'Cleo' AND t.type = 'Balsam_fir' CREATE (c)-[:LIKES_TO_CLIMB]->(t);
MATCH (c:Cat), (t:Tree) WHERE c.name = 'Cleo' AND t.type = 'Flowering_dogwood' CREATE (c)-[:LIKES_TO_CLIMB]->(t);

方案1：使用APOC库（推荐，语法简洁） #

MATCH (c1:Cat)-[:LIKES_TO_CLIMB]->(t1:Tree)
WITH c1, collect(t1.type) AS trees1
MATCH (c2:Cat)-[:LIKES_TO_CLIMB]->(t2:Tree)
WHERE c1.name < c2.name  // 避免重复配对（如Lily-Bella和Bella-Lily）
WITH c1, trees1, c2, collect(t2.type) AS trees2
// 计算交集和并集的大小
WITH c1, c2,
     size([t IN trees1 WHERE t IN trees2]) AS intersection,
     size(apoc.coll.union(trees1, trees2)) AS union
// 计算重叠百分比，过滤至少50%的组
WHERE (toFloat(intersection) / union) * 100 >= 50
RETURN c1.name AS cat1, c2.name AS cat2,
       round((toFloat(intersection)/union)*100) AS overlap_percent

方案2：不依赖APOC库（通用兼容） #

MATCH (c1:Cat)-[:LIKES_TO_CLIMB]->(t1:Tree)
WITH c1, collect(t1.type) AS trees1
MATCH (c2:Cat)-[:LIKES_TO_CLIMB]->(t2:Tree)
WHERE c1.name < c2.name
WITH c1, trees1, c2, collect(t2.type) AS trees2
WITH c1, c2,
     size([t IN trees1 WHERE t IN trees2]) AS intersection,
     size(trees1) + size(trees2) - intersection AS union
WHERE (toFloat(intersection) / union) * 100 >= 50
RETURN c1.name AS cat1, c2.name AS cat2,
       round((toFloat(intersection)/union)*100) AS overlap_percent

1. 先为每只猫收集其喜欢攀爬的所有树的类型集合
2. 通过c1.name < c2.name过滤掉重复的猫对，避免重复计算同一组
3. 计算两只猫偏好树的交集大小，再通过并集公式计算总覆盖的树数量
4. 计算重叠度（交集/并集*100），筛选出重叠度≥50%的结果
5. 返回配对的猫名和对应的重叠百分比

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