如何用dbplyr为SQL Server中关联ID生成统一组标识？

问题背景 #

数据集存储于Microsoft SQL Server，包含两列唯一标识符：

• ID：当前版本的唯一标识
• Copied_ID：该ID的上一版本标识
  每行的ID与Copied_ID组合唯一。需将存在迭代关联的ID归为同一组，生成唯一组标识。每组的最新版本ID对应的Copied_ID为NA，实际ID是36位随机UUID，无法通过字符特征分组。优先采用dbplyr将R语法转换为SQL查询的方案，也可接受加载至内存处理的备选方案。

示例数据 #

library(dbplyr)
library(tidyverse)
  
data <- tibble(
     ID = c("1a2b", "3c4d", "5e6f", "7g8h", "9i0j", "1k2l", "3m4n", "5o6p"),
     Copied_ID = c("3c4d", "5e6f", "7g8h", NA, "1k2l", "3m4n", "5o6p", NA)
   )

data

期望输出 #

grouped_data <- tibble(
     ID = c("1a2b", "3c4d", "5e6f", "7g8h", 
            "9i0j", "1k2l", "3m4n", "5o6p"),
     Group_ID = c("Group 1", "Group 1", "Group 1", "Group 1",
                  "Group 2", "Group 2", "Group 2", "Group 2")
   )

grouped_data

解决方案 #

方案1：dbplyr递归查询（推荐，数据库端执行） #

利用SQL Server支持的递归CTE，通过dbplyr语法实现，无需加载全量数据到内存：

# 假设con是已建立的SQL Server连接，your_table_name是目标表名
data <- tbl(con, "your_table_name")

# 递归追溯每个ID的根节点（最新版本，Copied_ID为NA）
grouped_data <- data %>%
  # 初始化递归起点：每个ID的初始根为自身
  mutate(root_id = ID) %>%
  # 递归关联：沿着Copied_ID向上查找父节点
  recursive_join(
    data,
    by = c("root_id" = "ID"),
    suffix = c("", "_parent"),
    join_by = ~ .x$Copied_ID == .y$ID
  ) %>%
  # 筛选出最终根节点（无父节点的最新版本）
  filter(is.na(Copied_ID_parent)) %>%
  select(ID, root_id) %>%
  # 生成格式化的Group ID
  mutate(Group_ID = paste0("Group ", dense_rank(root_id))) %>%
  arrange(ID)

说明：dbplyr会将上述代码转换为SQL递归CTE，直接在数据库端完成分组计算，避免大数据量加载到内存的性能问题。

方案2：内存处理（备选，小数据量适用） #

若数据量较小，可通过collect()加载到内存后，用igraph包处理关联关系：

library(igraph)

# 将数据加载到本地内存
local_data <- data %>% collect()

# 构建关联边列表（排除无父节点的记录）
edges <- local_data %>%
  filter(!is.na(Copied_ID)) %>%
  select(from = Copied_ID, to = ID)

# 创建有向图对象
g <- graph_from_data_frame(edges, directed = TRUE)

# 获取所有根节点（最新版本，Copied_ID为NA）
roots <- local_data %>% filter(is.na(Copied_ID)) %>% pull(ID)

# 为每个根节点匹配所有关联的子节点
group_assignments <- map_dfr(roots, function(root) {
  # 获取所有能追溯到该根节点的ID（包括根节点自身）
  related_nodes <- subcomponent(g, root, mode = "in") %>% names()
  tibble(ID = related_nodes, root_id = root)
})

# 生成最终分组结果
grouped_local <- local_data %>%
  left_join(group_assignments, by = "ID") %>%
  mutate(Group_ID = paste0("Group ", dense_rank(root_id))) %>%
  select(ID, Group_ID) %>%
  arrange(ID)

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