R语言按时间戳匹配所属时间段实现两表关联及长表转宽表

以下方案基于data.table实现，性能优异，无硬编码逻辑，可直接支持大表、多颜色、多同色分段的扩展场景。

1 数据预处理 #

首先需要将读入的字符串格式时间字段转换为R标准时间格式，否则无法进行时间大小比较：

library(data.table)

# 读取输入数据，实际使用时替换为你自己的数据读取路径即可
instance <- read.table(header=TRUE, text="
ID      UTC_DateTime            Value
1       2022-06-15 15:00:00     8
2       2022-06-15 15:05:00     3
3       2022-06-15 15:10:00     2
4       2022-06-15 15:15:00     4
")

periods <- read.table(header=TRUE, text="
COLOR           UTC_DateTime          
Start_Green     2022-06-15 14:56:22
End_Green       2022-06-15 15:03:08
Start_Red       2022-06-15 15:03:11
End_Red         2022-06-15 15:58:48
")

# 转换为data.table格式，统一将时间字段转为UTC时区的POSIXct时间类型
setDT(instance)
instance[, UTC_DateTime := as.POSIXct(UTC_DateTime, tz = "UTC")]

setDT(periods)
periods[, UTC_DateTime := as.POSIXct(UTC_DateTime, tz = "UTC")]

2 长表转宽表（periods转periods_wide） #

自动拆分长表中的开始/结束标记和颜色名，支持任意数量颜色、任意数量同色不连续时间段，不需要手动指定颜色名：

# 将COLOR列拆分为区间类型（Start/End）、真实颜色名两部分
periods[, c("period_type", "color_name") := tstrsplit(COLOR, "_", fixed = TRUE, keep = 1:2)]
# 给同颜色的多个区间自动加序号，适配多段同色的场景
periods[, segment_id := rowid(color_name, period_type)]

# 变形为宽表，得到每个颜色区间的开始、结束时间
periods_wide <- dcast(
  periods,
  color_name + segment_id ~ period_type,
  value.var = "UTC_DateTime"
)
# 整理列名和表结构，和示例输出对齐
setnames(periods_wide, c("Start", "End"), c("UTC_DateTime_Start", "UTC_DateTime_End"))
periods_wide[, segment_id := NULL]
setnames(periods_wide, "color_name", "COLOR")

运行后得到的periods_wide结构和要求完全一致，新增颜色时不需要修改代码。

3 时间区间关联匹配 #

使用data.table内置的foverlaps函数做区间重叠匹配，底层为C实现，相比逐行遍历、模糊连接的写法性能高1~2个数量级，可支持千万级记录的instance表快速匹配：

# 给instance表加区间辅助列，单点时间的开始、结束时间取同一个值即可
instance[, `:=`(start_t = UTC_DateTime, end_t = UTC_DateTime)]

# 设置匹配键，顺序固定为开始时间、结束时间
setkey(periods_wide, UTC_DateTime_Start, UTC_DateTime_End)

# 执行区间匹配：时间点完全落在颜色区间内则关联对应颜色
joined <- foverlaps(
  instance,
  periods_wide,
  by.x = c("start_t", "end_t"),
  by.y = c("UTC_DateTime_Start", "UTC_DateTime_End"),
  type = "within",
  nomatch = NA # 未落在任何区间的记录Color返回NA，可按需替换为默认值
)

# 清理辅助列，整理输出表结构
joined[, `:=`(start_t = NULL, end_t = NULL, UTC_DateTime_Start = NULL, UTC_DateTime_End = NULL)]
setnames(joined, "COLOR", "Color")
setcolorder(joined, c("ID", "UTC_DateTime", "Value", "Color"))

注意事项 #

• 你提供的joined示例中ID=2的记录时间为2022-06-15 15:05:00，按给出的periods时间边界实际属于Red区间，示例标注为Green属于笔误，代码严格按时间区间边界做匹配，替换为你的真实业务时间数据即可得到正确结果
• 区间匹配规则可灵活调整，需要修改开闭规则（如左开右闭、不包含结束端点等）时，调整foverlaps的type参数或对时间字段做微偏移即可
• 若存在时间点落在两个颜色区间的空隙段，对应记录的Color字段会返回NA，可根据业务规则自行填充

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