如何使用dbplyr无需collect即可将字符串按空格拆分为多行

核心逻辑是直接调用数据库原生的字符串拆分与行展开函数，通过dbplyr的sql()函数嵌入原生SQL片段，完全不需要将数据拉取到本地。以下是不同主流数据库的可运行实现：

方案1：针对支持REGEXP_SPLIT_TO_TABLE的数据库（PostgreSQL、MySQL 8.0.19+、Snowflake等） #

library(dbplyr)
library(dplyr)

# 直接调用数据库原生拆分函数即可得到拆分后多行的结果
res <- df %>%
  mutate(WORD = sql("REGEXP_SPLIT_TO_TABLE(TEXT, ' ')"))

# 验证生成的SQL是否正确
show_query(res)

# 小批量测试结果正确性
head(res, 20) %>% collect()

方案2：Oracle数据库适配 #

res <- df %>%
  mutate(WORD = sql("REGEXP_SUBSTR(TEXT, '[^ ]+', 1, LEVEL)")) %>%
  filter(sql("CONNECT BY LEVEL <= REGEXP_COUNT(TEXT, ' ') + 1 
              AND PRIOR ID = ID 
              AND PRIOR SYS_GUID() IS NOT NULL"))

方案3：低版本数据库通用兼容方案 #

如果你的数据库版本不支持拆分后直接转多行，可以先提前设定文本的最大单词数，逐个位置提取后过滤空值：

# 按需调整最大单词数，覆盖你的文本最长场景即可
max_word_num <- 20

res <- df %>%
  # 生成单词位置序列
  crossing(tibble(pos = seq_len(max_word_num))) %>%
  # 提取对应位置的单词
  mutate(WORD = sql(paste0("REGEXP_SUBSTR(TEXT, '[^ ]+', 1, ", pos, ")"))) %>%
  # 过滤掉超出文本实际单词数的空值
  filter(!is.na(WORD)) %>%
  select(-pos)

如果需要更贴合dplyr的写法，不需要直接写SQL片段，可以通过dbplyr::sql_translator()注册自定义函数的SQL翻译规则，后续就可以像本地函数一样直接调用。

内容的提问来源于stack exchange，提问作者user10022403