寻求dplyr/SQL中str_extract的SQL兼容替代函数以提升效率

处理大型数据库表时，用stringr::str_extract或stringi::stri_extract_all_regex提取子串必须先collect()把数据拉到本地，导致查询耗时极长（约5分钟）。由于这些函数无法被dbplyr转换为SQL，不提前收集数据就会报错，急需找能直接在数据库端执行、兼容SQL的正则提取替代方案，避免拉取全量数据。

当前需求是从raw_user_key字段提取冒号后的数字内容（比如从MMDHTT:12345567提取12345567），再基于该字段关联另一张表。

针对你使用的Impala数据库，直接用dbplyr支持的**regexp_extract函数**即可，它会被转换为Impala原生的regexp_extract SQL函数，全程无需将数据拉到本地处理。

核心操作：

• 移除rawframe2处理流程中的collect()调用，保留数据库端的tbl对象
• 用regexp_extract替代stri_extract_all_regex，Impala的regexp_extract语法为regexp_extract(字段, 正则表达式, 捕获组索引)

library(DBI)
library(dplyr)
library(dbplyr)
library(dfcr)
library(stringr)
library(lubridate)
library(timeDate)
library(plotly)
library(openxlsx)
library(bit64)
library(cld3)
library(stringi)

doConn("odbc")
sc <- dbConnect(odbc::odbc(),"Impala")
options(scipen = 999)
currentdate <- Sys.Date() 

DF1 <- sc %>% tbl(tblsan("rawframe1")) %>%
  filter(between (as.Date(createtime), as.Date("2022-11-01"), as.Date(currentdate))) %>%
  mutate(id = as.character(id),
         userid = as.character(userid)) %>%
  select(id, userid, body, createtime)
  
 
DF2 <- DF1 %>%
  left_join(
    sc %>% tbl(tblsan("rawframe2")) %>%
      filter(as_of_date == "2023-07-24") %>%
      # 用Impala兼容的regexp_extract在数据库端直接处理
      mutate(user_id = regexp_extract(raw_user_key, "(?<=:)([0-9]*)", 1)) %>%
      select(user_id, name, login) %>%
      distinct(), 
    by = c('userid' = 'user_id')
  )

• 若使用其他数据库，可对应调整正则提取函数：比如PostgreSQL用regexp_match，MySQL用REGEXP_SUBSTR，dbplyr通常会自动适配或提供对应包装函数
• regexp_extract第三个参数是捕获组索引，本次正则里的([0-9]*)是第1个捕获组，所以填1即可提取目标内容
• 全程无需collect()，所有计算在数据库端完成，大幅减少数据传输耗时

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