Удаление строк, содержащих NA, с помощью dbplyr

вот как я выполнил несколько SQL-запросов с помощью dbplyr

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.

Я попытался передать drop_na() из tidyr и некоторых других базовых функций (complete.cases() и т. Д.). Не могли бы вы предложить мне что-нибудь для достижения моей цели? Также приветствуется передача запроса SQL (например, WHERE FOO IS NOT NULL) в запрос dbplyr.

Заранее спасибо.


r sql dplyr dbplyr
person Samet Sökel    schedule 24.03.2021    source источник
comment
А что насчет na.omit(table)?   -  person PaulG    schedule 24.03.2021
comment
@PaulG Привет, что означает table? Если вы предложите мне передать na.omit() в конце моего запроса dbplyr, я попробовал это, и это одно из разочарований. Я хочу опустить NA перед сбором, потому что это создает огромный размер временного файла на моем диске.   -  person Samet Sökel    schedule 24.03.2021
comment
table должен быть фреймом данных или тиблом. К какому классу относится ваша результирующая таблица? Может, сначала попробуй преобразовать.   -  person PaulG    schedule 24.03.2021
comment
@PaulG ну, на самом деле это еще не то, что вы сказали. это был ленивый запрос с классами «tbl_lazy», «tbl_sql» и «tbl». проблема начинается там, мне нужно сделать все, что я должен сделать, прежде чем сделать его фреймом данных или тибблом и т. д.   -  person Samet Sökel    schedule 24.03.2021
comment
SQL-запрос в этом ответе кажется, делает то, что вам нужно.   -  person PaulG    schedule 24.03.2021

Ответы (2)


arrow_upward
1
arrow_downward

Попробуйте использовать !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 выполняет свой перевод.

Если вы хотите сделать это для нескольких столбцов, попробуйте следующий подход, основанный на этом ответе:

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))))
person Simon.S.A.    schedule 26.03.2021
comment
да, кажется более общим и понятным. спасибо за эту драгоценную работу! - person Samet Sökel; 26.03.2021

arrow_upward
0
arrow_downward

Что ж, на самом деле я все еще не нашел удовлетворительного решения. Что я точно хотел сделать, так это удалить содержащие строки NA в среде R без ввода 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))

Да, я знаю, что это не кажется волшебным, но, возможно, кому-то это пригодится.

person Samet Sökel    schedule 25.03.2021