r - 将函数与 dbplyr 中的现有 sql 翻译组合在一起的函数

Question

出现这个问题是因为我希望为方便起见创建一个函数:

as.numeric_psql <- function(x) {

   return(as.numeric(as.integer(x)))
}

将远程 postgres 表中的 bool 值转换为数字。需要转换为整数的步骤如下:

There is no direct cast defined between numeric and boolean. You can use integer as middle-ground. (https://stackoverflow.com/a/19290671/2109289)

当然，此功能在本地按预期工作:

copy_to(con_psql, cars, 'tmp_cars')

tmp_cars_sdf <-
    tbl(con_psql, 'tmp_cars')


tmp_cars_sdf %>%
    mutate(low_dist = dist < 5) %>%
    mutate(low_dist = as.numeric(as.integer(low_dist)))

# # Source:   lazy query [?? x 3]
# # Database: postgres 9.5.3
#     speed  dist low_dist
#     <dbl> <dbl>    <dbl>
# 1     4     2        1
# 2     4    10        0
# 3     7     4        1
# 4     7    22        0
# 5     8    16        0

cars %>%
    mutate(low_dist = dist < 5) %>%
    mutate(low_dist = as.numeric_psql(low_dist)) %>%
    head(5)

#   speed dist low_dist
# 1     4    2        1
# 2     4   10        0
# 3     7    4        1
# 4     7   22        0
# 5     8   16        0

但是，它在远程数据帧上使用时不起作用，因为 as.numeric_psql不在 sql 翻译列表中，因此被逐字传递给查询:

> tmp_cars_sdf %>%
+     mutate(low_dist = dist < 5) %>%
+     mutate(low_dist = as.numeric_psql(low_dist))
Error in postgresqlExecStatement(conn, statement, ...) : 
  RS-DBI driver: (could not Retrieve the result : ERROR:  syntax error at or near "as"
LINE 1: SELECT "speed", "dist", as.numeric_psql("low_dist") AS "low_...
                                ^
)

我的问题是是否存在一种简单的方法(即不定义自定义 sql 翻译)让 dplyr 理解函数 as.numeric_psql是具有现有 sql 翻译并使用这些翻译的函数的组合。

Answer 1

避免错误的一种方法是将函数设置为对数据帧进行操作，而不是在 mutate 内部进行操作。例如:

copy_to(con_psql, cars, 'tmp_cars')

tmp_cars_sdf <- tbl(con_psql, 'tmp_cars')

as.numeric_psql <- function(data, x) {
  return(data %>% mutate({{x}} := as.numeric(as.integer({{x}}))))
}

tmp_cars_sdf %>%
  mutate(low_dist = dist < 5) %>%
  as.numeric_psql(low_dist)

#> # Source:   lazy query [?? x 3]
#> # Database: sqlite 3.30.1 [:memory:]
#>    speed  dist low_dist
#>    <dbl> <dbl>    <dbl>
#>  1     4     2        1
#>  2     4    10        0
#>  3     7     4        1
#>  4     7    22        0
#>  5     8    16        0
#>  6     9    10        0
#>  7    10    18        0
#>  8    10    26        0
#>  9    10    34        0
#> 10    11    17        0
#> # … with more rows

请注意，在您的示例中，在数据库版本 low_dist 中在创建时已经被编码为整数，而不是像在标准 R 数据帧中那样合乎逻辑:

tmp_cars_sdf %>%
  mutate(low_dist = dist < 5) 
#> # Source:   lazy query [?? x 3]
#> # Database: sqlite 3.30.1 [:memory:]
#>    speed  dist low_dist
#>    <dbl> <dbl>    <int>
#>  1     4     2        1
#>  2     4    10        0
#>  3     7     4        1
#>  4     7    22        0
#>  5     8    16        0
#>  6     9    10        0
#>  7    10    18        0
#>  8    10    26        0
#>  9    10    34        0
#> 10    11    17        0
#> # … with more rows

cars %>%
  mutate(low_dist = dist < 5) %>% head
#>   speed dist low_dist
#> 1     4    2     TRUE
#> 2     4   10    FALSE
#> 3     7    4     TRUE
#> 4     7   22    FALSE
#> 5     8   16    FALSE
#> 6     9   10    FALSE