使用dbplyr删除包含NA行

Question

下面是我如何通过dbplyr运行一些SQL查询

library(tidyverse)
library(dbplyr)
library(DBI)
library(RPostgres)
library(bit64)
library(tidyr)

drv <- dbDriver('Postgres')

con <- dbConnect(drv,dbname='mydb',port=5432,user='postgres')

table1 <- tbl(con,'table1')
table2 <- tbl(con,'table2')
table3 <- tbl(con,'table3')

table1 %>% mutate(year=as.integer64(year)) %>% left_join(table2,by=c('id'='id')) %>%
left_join(table3,by=c('year'='year'))

我想删除一些行，其中包括NA，然后collect我的最后一个表，但是找不到任何有用的东西来处理dbplyr查询。

我尝试从tidyr和其他一些基本函数(complete.cases()等)中导入drop_na()。你能给我一些建议来实现我的目标吗？通过管道将SQL查询(如WHERE FOO IS NOT NULL)转换为dbplyr查询也是受欢迎的。

提前谢谢。

Answer 1

实际上，我仍然没有得到一个令人满意的解决方案。我真正想做的是在R环境中删除包含NA的行，而不是输入SQL查询，我认为dbplyr还不支持这个功能。

然后我写了一些简单的代码来实现我的愿望；

main_query<-table1 %>% mutate(year=as.integer64(year)) %>% left_join(table2,by=c('id'='id')) %>%
left_join(table3,by=c('year'='year'))

colnames <- main_query %>% colnames

query1 <- main_query %>% sql_render %>% paste('WHERE')

query2<-''


for(i in colnames){

    if(i == tail(colnames,1)){query2<-paste(query2,i,'IS NOT NULL')}
    
    else{query2<-paste(query2,i,'IS NOT NULL AND')}

}

desiredTable <- dbGetQuery(con,paste(query1,query2))

是的，我知道这看起来不是很神奇，但也许有人可以利用它。

Answer 2

尝试使用!is.na(col_name)作为筛选器的一部分：

library(dplyr)
library(dbplyr)

df = data.frame(my_num = c(1,2,3))
df = tbl_lazy(df, con = simulate_mssql())

output = df %>% filter(!is.na(my_num))

调用show_query(output)检查生成的sql结果如下：

<SQL>
SELECT *
FROM `df`
WHERE (NOT(((`my_num`) IS NULL)))

额外的括号是dbplyr进行转换的一部分。

如果要对多个列执行此操作，请尝试基于this答案的以下方法：

library(rlang)
library(dplyr)
library(dbplyr)

df = data.frame(c1 = c(1,2,3), c2 = c(9,8,7))
df = tbl_lazy(df, con = simulate_mssql())

colnames = c("c1","c2")
conditions = paste0("!is.na(",colnames,")")

output = df %>%
  filter(!!!parse_exprs(conditions))

调用show_query(output)会在生成的查询中显示这两列：

<SQL>
SELECT *
FROM `df`
WHERE ((NOT(((`c1`) IS NULL))) AND (NOT(((`c2`) IS NULL))))