Блог

Допустим, у меня есть удаленный tbl, открытый с помощью dbplyr, и я хочу использовать к нему SQL-запрос (возможно, потому, что для того, что я хочу сделать, нет перевода dbplyr), как мне задать его таким образом, чтобы он возвращал удаленный объект tbl?

DBI::dbGetQuery() Функция позволяет вам отправлять запрос в базу данных, но она возвращает фрейм данных в памяти, а не удаленный объект tbl.

Допустим, у вас уже con открыто соединение с базой данных, вы можете создать таблицу, подобную этой:

 library(tidyverse)

x_df <- expand.grid(A = c('a','b','c'), B = c('d','e','f', 'g','h')) %>% 
  mutate(C = round(rnorm(15), 2))

DBI::dbWriteTable(conn = con,
                  name = "x_tbl",
                  value = x_df,
                  overwrite = TRUE)

x_tbl = tbl(con, 'x_tbl')

sql_query <- build_sql('SELECT a, b, c, avg(c) OVER (PARTITION BY a) AS mean_c FROM x_tbl')

y_df <- DBI::dbGetQuery(con, sql_query) # This returns a data frame on memory

y_tbl <- x_tbl %>% 
  group_by(a) %>% 
  mutate(mean_c = mean(c))

show_query(y_tbl) # This is a remote tbl object
  

В этом случае я мог бы просто использовать y_tbl . Но бывают случаи, когда функция не была переведена в dbplyr (например, quantile не работает), и мне нужно использовать SQL-код. Но я не хочу собирать результат, я хочу, чтобы он создавал удаленный tbl-объект. Есть ли способ, которым я могу отправить SQL-запрос (например, с dbGetQuery() ), но чтобы он возвращал удаленный tbl?

Спасибо

Что ж, поиграв с тем, как это работает, я думаю, что нашел способ. Вы можете отправить sql-запрос внутри функции mutate:

 y_tbl <- x_tbl %>% 
  group_by(a) %>% 
  mutate(mean_c = sql("avg(c) OVER (PARTITION BY a)"))

show_query(y_tbl) # This is a remote tbl object
  

Это позволит вам дать SQL-определение переменной без необходимости также вычислять таблицу.

Насколько я понимаю, существует набор стандартных переводов, которые dbplyr выполняет из dplyr в SQL. Все, что выходит за рамки этого перевода, остается как есть.

Например, DATEFROMPARTS является функцией SQL, но не R-функцией. Я обычно использую следующий mutate:

 y_tbl <- x_tbl %>% 
    mutate(new_date = DATEFROMPARTS(year_col, month_col, day_col)
  

И поскольку не существует определенного преобразования из R-функции DATEFROMPARTS в SQL-функцию (поскольку R-функция не существует в dplyr), она оставлена как есть.