R dbplyr mysql列转换

Question

我在mySQL中有一个表，它看起来像这样：

tbl<-tibble(
   Result=c("0.1","<0.0001","1.1"),
   Unit=c("mg/L","ug/L","mg/L"),
   Pref_Unit=c("mg/L","mg/L","mg/L"),
   Conversion=c(1,1000,1)
)

我想使用dbplyr、pool和RMariaDB执行的操作是使用表中的转换因子将Result列转换为首选单位，同时保留"<"，并将结果列拆分为只包含数字和numeric分数的censored，以指示Result是否包含"<“。

对于常规的dplyr，我会这样做：

tbl<-tbl %>%
    mutate(numb_Result=as.numeric(gsub("<","",Result)),
           cen_Result=grepl("<",Result)) %>%
    mutate(new_Result=ifelse(cen_Result,paste0("<",numb_Result*Conversion),paste0(numb_Result*Conversion)))

但这不适用于数据库表。任何帮助都将不胜感激。

Answer 1

挑战很可能是因为dbplyr没有为gsub和grepl定义转换。下面是你可以测试的几种可能性：

library(dplyr)
library(dbplyr)

tbl<-tibble(
  Result=c("0.1","<0.0001","1.1"),
  Unit=c("mg/L","ug/L","mg/L"),
  Pref_Unit=c("mg/L","mg/L","mg/L"),
  Conversion=c(1,1000,1)
)

remote_table = tbl_lazy(tbl, con = simulate_mssql())

remote_table %>%
  mutate(has_sign = ifelse(substr(Result, 1, 1) == "<", 1, 0)) %>%
  mutate(removed_sign = ifelse(has_sign == 1, substr(Result, 2, nchar(Result)), Result)) %>%
  mutate(num_value = as.numeric(removed_sign)) %>%
  mutate(converted = as.character(1.0 * num_value * Conversion)) %>%
  mutate(new_Result = ifelse(has_sign, paste0("<",converted), converted))

有针对ifelse、substr、nchar、as.numeric、as.character和paste0的dbplyr转换。所以我希望这能行得通。但是，我一直收到一个错误，因为转换器要求substr的start和stop参数必须是常量，因此它不喜欢我将nchar(Results)作为参数传递。但这可能会在较新版本的包中修复。

我第二次尝试：

remote_table %>%
  mutate(has_sign = ifelse(substr(Result, 1, 1) == "<", 1, 0),
         character_length = nchar(Result),
         remove_first = sql(REPLACE(Result, "<", ""))) %>%
  mutate(removed_sign = ifelse(has_sign == 1, remove_first, Result)) %>%
  mutate(num_value = as.numeric(removed_sign)) %>%
  mutate(converted = as.character(1.0 * num_value * Conversion)) %>%
  mutate(new_Result = ifelse(has_sign, paste0("<",converted), converted))

这将产生预期的SQL转换。但是，由于我使用的是模拟的数据库连接，因此无法测试它是否返回预期的输出。这种方法的缺点是它直接使用SQL函数REPLACE (它传递未经翻译的SQL代码)，这不如完全翻译的解决方案优雅。

可能有更优雅的方法来做到这一点。但希望在这两者之间你能找到一个合适的解决方案。

Answer 2

谢谢你，西蒙！

我已经找到了一个类似的解决方案，它在实际的SQL数据库环境中工作(注意，我还必须将列名作为变量result_col传递，因此使用!!sym())：

tbl %>%
mutate(numb_res = REGEXP_REPLACE(!!sym(result_col),"<",""), 
       cen_res = !!sym(result_col) %like% "<%") %>%
      mutate(numb_res=numb_res*Conversion) %>%
      mutate(!!result_col:=case_when(
        cen_res==1 ~ paste0("<",numb_res),
        T ~ paste0(numb_res)
      ))

看起来您是对的，as.character()和as.numeric()没有SQL转换，但只需对字符向量进行乘法就足以使其成为数字，类似地，粘贴带有"<“的值就可以使其返回到字符中。

我认为这对我是有效的，但我也会调查你的答案。