Tidyverse下结合关联表与时间区间用mutate新增key2列的方法

核心思路是先按公共字段Key1关联两张表，再用时间运算符做过滤，最终补全未匹配的记录，实现代码如下：

library(tidyverse)
library(lubridate)

# 示例数据（已修正原代码末尾多余括号的语法问题）
set.seed(123)
df1 <- data.frame(
  Key1 = c('01','01','01','02','02','02','02','02','02','03','03','03'),
  type = c('R','R','R','C','C','R','C','R','R','C','C','R'),
  value = dnorm(1:12,mean=5,sd=10),
  hour = c(now(),now()+hours(1),now()+hours(2),now()-hours(338),now()-hours(337),
           now()-hours(336), now()-hours(48),now()-hours(47),now()-hours(46),
           now(),now()+hours(20),now()+hours(30))
)

df2 <- data.frame(
  key2 = c('A','B','C','D'),
  Key1 = c('02','02','01','03'),
  time = c(interval(now()-hours(340),now()-hours(299)),
           interval(now()-hours(60),now()-hours(46)),
           interval(now()-hours(10),now()+hours(10)),
           interval(now()-hours(5),now()+hours(50)))
)

# 核心匹配逻辑
result <- df1 %>%
  # 按客户标识Key1关联映射表，拉取对应所有key2和时间区间
  left_join(df2, by = "Key1") %>%
  # 过滤出监测时间落在对应区间的匹配项
  filter(hour %within% time) %>%
  # 保留原df1所有字段 + 匹配到的key2
  select(all_of(names(df1)), key2) %>%
  # 补回所有未匹配到key2的原始df1记录，对应key2为NA
  right_join(df1, by = names(df1))

适配不同业务场景的调整说明 #

• 如果业务允许一条监测记录匹配多个key2，可删除最后的right_join步骤，直接保留所有匹配结果
• 如果单条监测记录命中多个key2需要指定优先级，可在filter后按业务规则排序，再用distinct(Key1, hour, .keep_all = TRUE)保留唯一匹配项
• 数据量较大时可改用powerjoin包的非等值连接简化写法：

# 需提前安装powerjoin包
library(powerjoin)
result <- df1 %>%
  power_left_join(
    df2, 
    by = c("Key1", ~ .x$hour %within% .y$time),
    keep = "left"
  )

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