如何用SQL/R循环获取person10的2度邻居（替代igraph）

问题背景 #

给定如下R数据框，表示无向的友谊关系：

friendships <- structure(list(person = c("person3", "person10", "person2", "person6", 
                                         "person4", "person6", "person10", "person5", "person3", "person9", 
                                         "person9", "person9", "person3", "person8", "person10", "person7", 
                                         "person10"), 
                              friend = c("person9", "person4", "person1", "person7", 
                                         "person10", "person7", "person9", "person10", "person7", "person5", 
                                         "person7", "person5", "person6", "person9", "person2", "person5", 
                                         "person8")), 
                         row.names = c(1L, 3L, 4L, 5L, 7L, 8L, 10L, 11L, 12L, 13L, 14L, 15L, 16L, 17L, 18L, 19L, 20L), 
                         class = "data.frame")

需要获取person10的2度邻居（包含自身、1度邻居、2度邻居，与igraph的ego(g,2,"person10")结果一致），且不能使用igraph包，仅通过R函数/循环和SQL实现。

原尝试代码得到的结果缺少person3，需修正。

问题原因 #

原代码仅查询person列等于目标节点的friend值，但友谊是无向的：比如person3和person9是朋友，数据仅存储了person=person3, friend=person9这一条记录，未存储反向关系。原代码查找person9的朋友时，只检索person=person9的行，忽略了friend=person9的行，因此漏掉了person3。

修正方案 #

核心是处理无向关系：查询节点的朋友时，同时包含person=节点对应的friend，以及friend=节点对应的person。此外，2度邻居只需迭代2次即可，无需无限循环，效率更高。

修正后的代码（直接分步实现） #

library(sqldf)

# 第一步：获取person10的1度邻居（包含自身）
degree1 <- sqldf("
    SELECT DISTINCT friend AS neighbor FROM friendships WHERE person = 'person10'
    UNION
    SELECT DISTINCT person AS neighbor FROM friendships WHERE friend = 'person10'
    UNION
    SELECT 'person10' AS neighbor
")

# 第二步：基于1度邻居，获取所有关联节点（即2度邻居）
degree2 <- sqldf(paste0("
    SELECT DISTINCT friend AS neighbor FROM friendships WHERE person IN ('", paste(degree1$neighbor, collapse="','"), "')
    UNION
    SELECT DISTINCT person AS neighbor FROM friendships WHERE friend IN ('", paste(degree1$neighbor, collapse="','"), "')
"))

# 去重得到最终的2度邻居集合
final_neighbors <- unique(degree2$neighbor)

# 输出结果
cat("2度邻居数量：", length(final_neighbors), "\n")
cat("2度邻居列表：", paste(final_neighbors, collapse=", "), "\n")

结果验证 #

运行后结果与igraph完全一致：

2度邻居数量： 9 
2度邻居列表： person4, person9, person2, person8, person10, person1, person5, person7, person3 

贴合原思路的循环实现 #

如果坚持使用循环逻辑，需在每次查询时都处理无向关系：

library(sqldf)

# 初始化：包含person10自身
friends_degree2 <- data.frame(neighbor = "person10")

# 第一次循环：获取1度邻居
current_nodes <- "person10"
new_friends <- sqldf(paste0("
    SELECT DISTINCT friend AS neighbor FROM friendships WHERE person IN ('", current_nodes, "')
    UNION
    SELECT DISTINCT person AS neighbor FROM friendships WHERE friend IN ('", current_nodes, "')
"))
friends_degree2 <- unique(rbind(friends_degree2, new_friends))

# 第二次循环：获取2度邻居（基于1度邻居）
current_nodes <- paste(friends_degree2$neighbor, collapse="','")
new_friends <- sqldf(paste0("
    SELECT DISTINCT friend AS neighbor FROM friendships WHERE person IN ('", current_nodes, "')
    UNION
    SELECT DISTINCT person AS neighbor FROM friendships WHERE friend IN ('", current_nodes, "')
"))
friends_degree2 <- unique(rbind(friends_degree2, new_friends))

# 输出结果
cat("2度邻居数量：", nrow(friends_degree2), "\n")
print(friends_degree2)

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