r - 在 SQL Server 中组合 dbplyr 和 case_when

Question

我正在使用 dbplyr 在 SQL Server 中编写和运行查询，并且想要应用条件变异。这可以使用 ifelse 或使用 case_when 来完成。该查询在使用 ifelse 时有效，但在使用 case_when 时抛出异常。

问题似乎是这两个命令转换成的 SQL 语法。 case_when 语法似乎不是有效的 SQL。你能告诉我为什么以及如何解决它吗？或者这是一个错误？

# libraries
library(DBI)
library(dplyr)
library(dbplyr)

# establish connection to database table
connection_string = "database.specific.string"
# mine looks something like "DRIVER=...; Trusted_Connection=...; DATABASE=...' SERVER=..."
db_connection = dbConnect(odbc::odbc(), .connection_string = connection_string)
my_table = tbl(db_connection, from = my_table_name)

# attempted query
tmp = my_table %>%
    mutate(new_col = case_when(col1 == col2 ~ "a",
                               col1 != col2 ~ "b"))

# check SQL code for query
show_query(tmp)

生成的 SQL 查询是:

SELECT 
    col1, col2,
    CASE
       WHEN CONVERT(BIT, IIF(col1 = col2, 1.0, 0.0))) THEN ('a')
       WHEN CONVERT(BIT, IIF(col1 <> col2, 1.0, 0.0))) THEN ('b')
    END AS new_col
FROM my_database.my_table_name

运行此代码会引发错误

An expression of non-boolean type specified in a context where a condition is expected, near 'THEN'

但是 ifelse 查询按预期工作:

# attempted query
tmp = my_table %>%
    mutate(new_col = ifelse(col1 == col2, "a", "b"))

# check SQL code for query
show_query(tmp)

生成的 SQL 查询是:

SELECT 
    col1, col2,
    CASE
       WHEN (CONVERT(BIT, IIF(col1 = col2, 1.0, 0.0))) = TRUE) THEN ('a')
       WHEN (CONVERT(BIT, IIF(col1 = col2, 1.0, 0.0))) = FALSE) THEN ('b')
    END AS new_col
FROM my_database.my_table_name

请注意，在这两种情况下，SQL 语法都是使用 show_query 生成的。使用 translate_sql 生成 SQL 代码始终会生成更清晰的 SQL 语法，但这不是在服务器上运行的语法。

还有其他人收到这些 SQL 查询吗？关于问题所在以及如何解决这个问题有什么建议吗？

已更新

发布为issue on the tidyverse并获悉已经开发了一个解决方案，可将 case_when(..., TRUE ~ "b") 转换为 ELSE 'b' ( here )。

但是，因为这并没有解决导致此异常的语法。问题已编辑，以重点关注导致问题的语法。

更新2

发布为issue on dbplyr 。 Christophe Dervieux (cderv) 的回复指出，原因似乎是 SQL Server 需要对 case_when 进行特殊翻译，就像对 ifelse 进行特殊翻译一样。

同时，用户可以使用多个 ifelse 或 if_else 语句。

Answer 1

只是你的 dplyr 语法有点错误吗？

试试这个

# attempted query
tmp = my_table %>%
    mutate(new_col = case_when(col1 == col2 ~ "a",
                               col1 == 'TRUE' ~ "b"
              # alternatively  col1 == 1 ~ "b"
))