如何在dplyr的left_join中标记多对多、一对多、多对一关系

要完整区分这三种关系，核心是先统计每个关联ID在两个表中的记录数量，再根据数量组合判断关系类型：

• one-to-many：左表（df1）中单个ID仅对应1条记录，右表（df2）中对应多条记录
• many-to-one：左表中单个ID对应多条记录，右表中仅对应1条记录
• many-to-many：左右表中单个ID都对应多条记录

具体实现步骤如下：

library(dplyr)

df1 <- data.frame(
  id1 = c(1, 1, 3, 4),
  value1 = c("A", "B", "C", "D")
)

df2 <- data.frame(
  id2 = c(1, 1, 3, 3, 4),
  value2 = c("X", "Y", "Z1", "Z2", "W")
)

# 统计每个ID在两个表中的记录数
count_df1 <- df1 %>% group_by(id1) %>% summarise(count_df1 = n(), .groups = "drop")
count_df2 <- df2 %>% group_by(id2) %>% summarise(count_df2 = n(), .groups = "drop")

# 关联数据并标记关系类型
result <- df1 %>%
  left_join(df2, by = c("id1" = "id2")) %>%
  left_join(count_df1, by = "id1") %>%
  left_join(count_df2, by = c("id1" = "id2")) %>%
  mutate(
    tag = case_when(
      count_df1 == 1 & count_df2 > 1 ~ "one-to-many",
      count_df1 > 1 & count_df2 == 1 ~ "many-to-one",
      count_df1 > 1 & count_df2 > 1 ~ "many-to-many",
      TRUE ~ "one-to-one" # 补充one-to-one的边界情况
    )
  ) %>%
  select(-count_df1, -count_df2) # 可选：移除中间计数列

result

运行后输出结果：

# A tibble: 7 × 5
    id1 value1   id2 value2 tag        
  <dbl> <chr>  <dbl> <chr>  <chr>      
1     1 A          1 X      many-to-many
2     1 A          1 Y      many-to-many
3     1 B          1 X      many-to-many
4     1 B          1 Y      many-to-many
5     3 C          3 Z1     many-to-many
6     3 C          3 Z2     many-to-many
7     4 D          4 W      one-to-many

如果需要验证many-to-one的标记效果，可以修改测试数据：

# 构造包含many-to-one关系的测试数据
df1_test <- df1 %>% add_row(id1=5, value1="E") %>% add_row(id1=5, value1="F")
df2_test <- df2 %>% add_row(id2=5, value2="V")

# 重新执行统计和关联逻辑
count_df1_test <- df1_test %>% group_by(id1) %>% summarise(count_df1 = n(), .groups = "drop")
count_df2_test <- df2_test %>% group_by(id2) %>% summarise(count_df2 = n(), .groups = "drop")

result_test <- df1_test %>%
  left_join(df2_test, by = c("id1" = "id2")) %>%
  left_join(count_df1_test, by = "id1") %>%
  left_join(count_df2_test, by = c("id1" = "id2")) %>%
  mutate(
    tag = case_when(
      count_df1 == 1 & count_df2 > 1 ~ "one-to-many",
      count_df1 > 1 & count_df2 == 1 ~ "many-to-one",
      count_df1 > 1 & count_df2 > 1 ~ "many-to-many",
      TRUE ~ "one-to-one"
    )
  ) %>%
  select(-count_df1, -count_df2)

result_test

此时输出会包含many-to-one标签的记录：

# A tibble: 9 × 5
    id1 value1   id2 value2 tag        
  <dbl> <chr>  <dbl> <chr>  <chr>      
1     1 A          1 X      many-to-many
2     1 A          1 Y      many-to-many
3     1 B          1 X      many-to-many
4     1 B          1 Y      many-to-many
5     3 C          3 Z1     many-to-many
6     3 C          3 Z2     many-to-many
7     4 D          4 W      one-to-many
8     5 E          5 V      many-to-one
9     5 F          5 V      many-to-one

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