如何加速将一列拆分至千余列？（R/SQLite环境）

R端方案（推荐，适合已导入数据的场景） #

1. 使用data.table的tstrsplit #

data.table的字符串拆分基于底层实现，比dplyr的separate效率高很多，尤其适配大数据集。你的数据已是data.table，直接原地添加列即可，无需复制整个数据集：

library(data.table)
# 假设要拆分出1000列
d[, paste0("S", 1:1000) := tstrsplit(column_to_separate, "\\$", fixed = TRUE)]

• fixed = TRUE：指定分隔符为固定字符串而非正则表达式，进一步提速；
• 原地修改（:=）：避免创建新数据框，节省内存与时间。

2. 使用stringi的stri_split_fixed #

stringi基于C++实现字符串操作，是R中最快的字符串处理库之一，适合超大规模向量拆分：

library(stringi)
# 直接拆分得到矩阵（simplify=TRUE避免生成列表）
split_matrix <- stri_split_fixed(d$column_to_separate, "$", simplify = TRUE)
# 命名列名
colnames(split_matrix) <- paste0("S", 1:ncol(split_matrix))
# 合并到原数据
d2 <- cbind(d, split_matrix)

• 矩阵操作比列表更高效，cbind对data.table的兼容性也很好。

SQLite端方案（适合未导入R、数据量极大的场景） #

如果数据集过大，导入完整字符串列到R会占用大量内存，建议直接在数据库端拆分后再读取结果，避免不必要的数据传输。

动态生成SQL拆分语句 #

利用SQLite的substr和instr函数，循环生成拆分每一列的SQL片段，再执行查询：

library(RSQLite)
# 连接数据库
con <- dbConnect(SQLite(), "your_database.db")

# 生成1000列的拆分SQL片段
split_clauses <- sapply(1:1000, function(i) {
  if (i == 1) {
    # 第一列：取第一个$前的内容，处理无$的情况
    "CASE WHEN instr(col_to_split, '$') > 0 THEN substr(col_to_split, 1, instr(col_to_split, '$')-1) ELSE col_to_split END AS S1"
  } else {
    # 第i列：先截取前i-1个$之后的剩余字符串，再取第一个$前的内容
    prev_expr <- paste0(rep("substr(", i-1), collapse = "")
    prev_expr <- paste0(prev_expr, "col_to_split", paste0(", instr(", 1:(i-1), ", '$')+1)", collapse = ""))
    # 处理最后一列无$的情况
    paste0("CASE WHEN instr(", prev_expr, ", '$') > 0 THEN substr(", prev_expr, ", 1, instr(", prev_expr, ", '$')-1) ELSE ", prev_expr, " END AS S", i)
  }
})

# 拼接完整查询语句
full_query <- paste0(
  "SELECT *, ", paste(split_clauses, collapse = ", "), " ",
  "FROM your_table_name"
)

# 读取拆分后的数据
d <- dbGetQuery(con, full_query)
# 断开连接
dbDisconnect(con)

利用SQLite的CSV虚拟表（SQLite 3.33.0+） #

如果你的SQLite版本足够新，可以借助CSV虚拟表将字符串视为$分隔的CSV，再转置成列：

WITH split_rows AS (
  SELECT 
    t.rowid,
    s.value AS S,
    -- 生成列序号
    row_number() OVER (PARTITION BY t.rowid) AS col_idx
  FROM your_table_name t,
  csv(
    'data:text/csv,' || t.col_to_split,
    'sep=$',
    'header=0'
  ) s
)
SELECT 
  -- 原表所有列 + 拆分后的1000列
  t.*,
  MAX(CASE WHEN col_idx = 1 THEN S END) AS S1,
  MAX(CASE WHEN col_idx = 2 THEN S END) AS S2,
  -- ... 手动补充或用R生成到S1000
  MAX(CASE WHEN col_idx = 1000 THEN S END) AS S1000
FROM your_table_name t
LEFT JOIN split_rows sr ON t.rowid = sr.rowid
GROUP BY t.rowid;

你可以用R自动生成上述SQL中的MAX(CASE...)部分，避免手动编写1000行代码。

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