在来自两个不同数据库的两个表上使用来自 dplyr 的 anti_join() [英] Using anti_join() from the dplyr on two tables from two different databases

问题描述

我正在从事一个 ETL 测试项目，我需要在该项目中比较来自两个不同数据库的两个表之间的数据.为此，我首先使用如下查询下载了整个表.

I am working on a ETL testing project, where my need is to compare data between two tables from two different databases. to do this, I first downloaded entire tables using query like below.

  query_table_a <- paste0("SELECT * FROM   MBR_MEAS (NOLOCK)")
  table_a <- as.data.frame(sqlQuery(cn, query_table_a))

然后，我使用了 dplyr 中的 anti_join().如果两个数据框中的列名相同，那么我的结果很好.例如(这会返回良好的预期结果)

Then, I used anti_join() from the dplyr. If the column name is same in both data frames, then my result is good. for example(this returns good and expected results)

mismatch_records <- anti_join(table_a, table_b, by="client_id")

但在另一种情况下，列名已更改(表 'c' 的列名为 client_id，表 'd' 具有 clientid，我不知道该怎么做.我尝试使用合并功能，但没有看起来很有前途.

But in another scenario, column name is changed (table 'c' has column name as client_id and table 'd' has clientid, I couldn't figure out what to do. I tried using merge function but that doesn't seems to be very promising.

merge(x = table_c, y = table_d, by.x ="CLIENT_ID", by.y = "ClientId", all.x = "TRUE")

请问有什么建议吗?

推荐答案

试试这个:

mismatch_records <- anti_join(table_c, table_d, by = c("CLIENT_ID" = "ClientId"))

这篇关于在来自两个不同数据库的两个表上使用来自 dplyr 的 anti_join()的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！