r - 带有 WHERE 和 HAVING 子句的 sqldf 中的 LEFT OUTER JOIN

Question

我在 R 中有两个数据框，我想在 id 和 day 有条件地合并它们。合并是合并到 left 变量的 right 变量尽可能新/新鲜/最近，但必须至少三天。

但是，如果 right 与 left 中的 id-date 对不匹配我仍然想保留它们。我的研究分为两部分，所以我不想删除 id-day 观察结果，因为它们不完整。

我可以在一个 sqldf 步骤中完成吗？我当前的方法需要一个额外的基础 R merge。

left <- data.frame(id=rep(1:5, each=10),
                   day=rep(1:10, times=5),
                   x=rnorm(5*10))
right <- data.frame(id=rep(1:2, each=21),
                   day=rep(-10:10, times=2),
                   y=rnorm(2*21))
combined <- sqldf("SELECT L.id, L.day, L.x, R.y
                  FROM left L LEFT OUTER JOIN right R
                  ON (L.id = R.id)
                  WHERE ((L.day - R.day) >= 3)
                  GROUP BY L.id, L.day
                  HAVING (R.day = MAX(R.day))")
combined                  

combined.2 <- merge(left, combined, all=TRUE)
combined.2 

Answer 1

尝试像这样嵌套选择语句:

sqldf("SELECT * from left
       LEFT JOIN (SELECT id, L.day, L.x, R.y
                  FROM left L LEFT OUTER JOIN right R
                  USING (id)
                  WHERE ((L.day - R.day) >= 3)
                  GROUP BY L.id, L.day
                  HAVING (R.day = MAX(R.day))) 
       USING (id, day, x)")

这也可以按如下方式完成。它使用了这样一个事实，即如果使用 max，则保证同一结果行上的其他值来自与 max 相同的原始行。这是 SQLite 提供的 SQL 扩展。

sqldf("select max(R.day) as maxRday, L.*, R.y
  from left L left outer join right R
  on L.id = R.id and L.day - R.day >= 3
  group by L.id, L.day")[-1]