如何在R中使用DuckDB读取多份年度CSV并添加年份字段

针对你用R的duckdb包合并年度CSV时缺少年份字段的问题，这里提供两种实用方案：

方案一：读取时直接添加年份字段（推荐） #

无需将大型CSV加载到R内存，直接通过DuckDB的SQL语法在读取过程中为每个文件追加对应年份字段，再合并到单表中。这种方式高效适配百万级数据：

示例修改代码 #

library(duckdb)
con <- dbConnect(duckdb())

# 示例年份1数据
data <- data.frame(id = 1:3, b = letters[1:3]) # year = 1
path <- tempfile(fileext = ".csv")
write.csv(data, path, row.names = FALSE)

# 示例年份2数据
data2 <- data.frame(id = 1:3, b = letters[4:6]) # year = 2
path2 <- tempfile(fileext = ".csv")
write.csv(data2, path2, row.names = FALSE)

# 初始化总表，同时添加年份字段
dbExecute(con, sprintf("CREATE TABLE data AS SELECT *, 1 AS year FROM read_csv('%s')", path))
# 追加第二个文件并对应年份
dbExecute(con, sprintf("INSERT INTO data SELECT *, 2 AS year FROM read_csv('%s')", path2))

# 查看最终结果
dbReadTable(con, "data")

运行后得到带年份的表：

id b year
1  1 a    1
2  2 b    1
3  3 c    1
4  1 d    2
5  2 e    2
6  3 f    2

批量处理8个文件的写法 #

将文件路径与对应年份做成映射表，循环处理所有文件：

# 构建文件-年份映射（替换成你的实际文件路径和年份）
file_year_map <- list(
  "/path/to/year2016.csv" = 2016,
  "/path/to/year2017.csv" = 2017,
  "/path/to/year2018.csv" = 2018,
  "/path/to/year2019.csv" = 2019,
  "/path/to/year2020.csv" = 2020,
  "/path/to/year2021.csv" = 2021,
  "/path/to/year2022.csv" = 2022,
  "/path/to/year2023.csv" = 2023
)

# 初始化总表（处理第一个文件）
first_file <- names(file_year_map)[1]
first_year <- file_year_map[[first_file]]
dbExecute(con, sprintf(
  "CREATE TABLE combined_data AS SELECT *, %d AS year FROM read_csv('%s')",
  first_year,
  first_file
))

# 循环追加剩余文件
for (file_path in names(file_year_map)[-1]) {
  current_year <- file_year_map[[file_path]]
  dbExecute(con, sprintf(
    "INSERT INTO combined_data SELECT *, %d AS year FROM read_csv('%s')",
    current_year,
    file_path
  ))
}

方案二：分表存储（按需选择） #

如果需要频繁单独查询某一年的数据，可以给每个年份创建单独的表（比如data_2016、data_2017），后续跨年份分析时再通过UNION ALL合并查询：

分表创建示例 #

# 循环创建年份表
for (file_path in names(file_year_map)) {
  current_year <- file_year_map[[file_path]]
  table_name <- sprintf("data_%d", current_year)
  dbExecute(con, sprintf(
    "CREATE TABLE %s AS SELECT * FROM read_csv('%s')",
    table_name,
    file_path
  ))
}

# 跨年份查询（带年份标识）
result <- dbGetQuery(con, "
  SELECT *, 2016 AS year FROM data_2016
  UNION ALL
  SELECT *, 2017 AS year FROM data_2017
  -- 继续添加剩余年份的UNION ALL语句
")

方案对比 #

• 单表加年份：适合跨年份趋势分析，查询直接高效，是多数场景的优先选择。
• 分表存储：适合单独年份查询需求多的场景，但跨年份分析需要额外编写UNION语句，灵活性稍差。

内容的提问来源于stack exchange，提问作者Matt L.