使用DuckDB、Arrow与dbplyr查询行数及连接匹配诊断

注：你的代码中存在笔误，inner_join(bd1, bd2, by = "id")里的bd1/bd2应为db1/db2，执行时需要修正。

1. 获取CSV文件对应的DuckDB数据集行数 #

不管是Arrow数据集还是已转换的DuckDB数据集，都可以在不加载全量数据到本地的情况下统计行数：

方法1：针对已转换的DuckDB数据集（db1/db2） #

用dbplyr的tally()或summarize()在数据库端计算：

# 统计db1的行数
db1 %>% tally() %>% pull()

# 或用SQL风格的汇总
db1 %>% summarize(total_rows = n()) %>% pull(total_rows)

方法2：直接针对Arrow格式的CSV数据集 #

如果还没转换到DuckDB，Arrow支持更高效的行数统计（无需读取全量数据）：

arrow::open_dataset(paste0(path, "data1.csv"), format = "csv") %>% 
  summarize(total_rows = n()) %>% 
  collect() %>% 
  pull(total_rows)

2. 大型数据集连接不匹配的诊断（无需加载到本地） #

针对内连接不匹配的问题，可以从数量统计和原因排查两方面入手：

第一步：统计不匹配的数量 #

• 统计db1中在db2无匹配的ID数量：

db1 %>% 
  anti_join(db2, by = "id") %>% 
  tally() %>% 
  pull()

• 统计db2中在db1无匹配的ID数量：

db2 %>% 
  anti_join(db1, by = "id") %>% 
  tally() %>% 
  pull()

• 检查两边是否存在重复ID（重复ID会导致连接结果行数异常）：

# 查看db1中重复的ID及出现次数
db1 %>% 
  count(id, sort = TRUE) %>% 
  filter(n > 1) %>% 
  collect()

# 查看db2中重复的ID及出现次数
db2 %>% 
  count(id, sort = TRUE) %>% 
  filter(n > 1) %>% 
  collect()

第二步：排查不匹配的原因 #

• 检查ID的数据类型：如果两边ID类型不一致（比如一边是数值型，一边是字符型），会导致匹配失败：

# 查看db1中ID的数据类型
db1 %>% select(id) %>% head(0) %>% collect() %>% str()

# 查看db2中ID的数据类型
db2 %>% select(id) %>% head(0) %>% collect() %>% str()

• 检查ID格式问题（字符型ID常见）：比如空格、大小写差异、特殊字符等：

# 检查db1中含空格的ID示例
db1 %>% 
  filter(grepl("\\s", id)) %>% 
  select(id) %>% 
  head(10) %>% 
  collect()

# 检查大小写差异导致的不匹配
db1 %>% 
  mutate(id_lower = tolower(id)) %>% 
  anti_join(db2 %>% mutate(id_lower = tolower(id)), by = "id_lower") %>% 
  select(id) %>% 
  head(10) %>% 
  collect()

• 抽样查看异常ID：直接抽取部分不匹配的ID，直观观察问题：

# 抽样db1中无匹配的ID
db1 %>% 
  anti_join(db2, by = "id") %>% 
  select(id) %>% 
  sample_n(10) %>% 
  collect()

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