R sqldf实现不同日期数据集匹配：按最近日期填充B表权重值

前置预处理 #

首先将两个数据集的日期字段从字符型转为Date类型，方便后续计算日期差：

library(sqldf)
# 转换日期格式
A$DATE <- as.Date(A$DATE)
B$DATE <- as.Date(B$DATE)

核心关联代码 #

借助SQLite的日期函数和窗口函数实现按最近日期匹配：

A_B <- sqldf("
WITH date_diff_calc AS (
    SELECT 
        a.ord, a.id, a.Score, a.DATE, a.VISIT, b.Weight,
        -- 计算A表日期与B表日期的差值绝对值
        ABS(JULIANDAY(a.DATE) - JULIANDAY(b.DATE)) AS gap,
        -- 按A表每条记录分组，按日期差值升序排序，差值最小的排名为1
        ROW_NUMBER() OVER (
            PARTITION BY a.ord 
            ORDER BY ABS(JULIANDAY(a.DATE) - JULIANDAY(b.DATE)) ASC
        ) AS rank_num
    FROM A a
    -- 按id关联两表
    LEFT JOIN B b ON a.id = b.id
)
-- 筛选每个A表记录对应的最近B表权重
SELECT ord, id, Score, DATE, VISIT, Weight
FROM date_diff_calc
WHERE rank_num = 1
ORDER BY ord
")

代码说明 #

• sqldf默认使用SQLite引擎，JULIANDAY()函数可以将日期转为儒略日数值，方便计算间隔天数
• 若存在两个B表日期与A表日期的差值完全相同的场景，可以修改ORDER BY规则调整优先级：例如追加, b.DATE DESC会优先取时间更晚的B表记录，追加, b.DATE ASC则优先取更早的记录
• 执行后得到的A_B与预期输出结构一致，可直接使用

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