如何在R语言中对数据库表执行滚动连接？

dplyr开发版的join_by(closest(...))语法目前暂不支持dbplyr，要实现类似的滚动连接（匹配同person下b.time <= a.time的最近/最大time记录），可以通过以下两种方式实现：

方法一：用dbplyr结合关联子查询 #

利用数据库的关联子查询能力，先为a表的每条记录找到对应b表中符合条件的最大time，再关联获取pref字段：

library(dbplyr)
library(dplyr)

a <- memdb_frame(
  person = rep("a", 10),
  time = 1:10
) 

b <- memdb_frame(
  person = rep("a", 3),
  time = c(1, 8, 5),
  pref = c("good", "terrible", "not so good")
)

# 为a表每条记录匹配对应b表的最大符合条件time
a_with_max_b_time <- a %>%
  mutate(
    max_b_time = sql("(SELECT MAX(time) FROM b WHERE b.person = a.person AND b.time <= a.time)")
  )

# 关联b表获取对应的pref
result <- a_with_max_b_time %>%
  left_join(b, by = c("person", "max_b_time" = "time")) %>%
  select(person, time.x, time.y = max_b_time, pref)

# 提取结果
collect(result)

方法二：直接执行原生SQL #

如果dbplyr的语法限制较多，直接编写原生SQL语句执行，逻辑更直观：

result <- tbl(a$src, sql("
  SELECT 
    a.person,
    a.time AS time.x,
    b.time AS time.y,
    b.pref
  FROM a
  LEFT JOIN b 
    ON a.person = b.person
    AND b.time = (
      SELECT MAX(time) 
      FROM b AS b_sub
      WHERE b_sub.person = a.person 
        AND b_sub.time <= a.time
    )
")) %>%
  collect()

关键说明 #

• 两种方法核心逻辑一致：为a表的每条记录，找到同分组（person）下b表中time小于等于当前a表time的最大值，再关联获取对应的pref，和closest(time >= time)的行为完全匹配。
• 若数据量较大，建议给b表的person和time字段建立联合索引，大幅提升查询效率。
• 该方案适用于支持关联子查询的现代数据库（如PostgreSQL、MySQL 8.0+、SQL Server等）。

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