按id和最近的时间戳合并2个数据帧

Question

我有两个数据帧：

其中一个(名为t1(由应用程序id、用户id和应用程序日期组成

第二个(名为t2(由客户id、评分日期和分数组成

我想使用客户端id和时间戳将表t2连接到表t1，其中我们从t2获取最新值，其中t2日期不大于t1日期字段。

我可以使用SQL语法合并这些表。不幸的是，我在每个表中都有成千上万的行和数百个要连接的变量。当前的SQL方法可以工作，但速度太慢。有没有更快的方法来完成这项任务？

以下是R代码的示例：

t1 <- data.frame(
application_id = c(1:5),
application_time = c('2020-09-20', 
'2020-09-22',
'2020-09-22',
'2020-09-23',
'2020-09-25'),
client_id = c(1, 2, 3, 1, 1)
)
t2 <- data.frame(
client_id = c(1, 1, 1, 1, 2, 3),
date_ = c('2020-09-19',
'2020-09-20',
'2020-09-25',
'2020-09-26',
'2020-09-26',
'2020-09-20'),
score = c(1:6)
)
## Merging the tables using SQL
library(sqldf)
df <- sqldf(
"SELECT 
t1.*,
(SELECT t2.score FROM t2
WHERE t1.client_id = t2.client_id
AND t1.application_time >= t2.date_
ORDER BY t2.date_ DESC LIMIT 1) AS scorexxx
FROM t1"
)

结果：

application_id application_time client_id scorexxx
1              1       2020-09-20         1        2
2              2       2020-09-22         2       NA
3              3       2020-09-22         3        6
4              4       2020-09-23         1        2
5              5       2020-09-25         1        3

Answer 1

我希望您能找到有帮助的解决方案注意：也请查看评论以获得有用的材料。

感谢您在问题中提供创建查询。cte保存表的JOIN的临时视图，参数与client_id和日期比较相同。之后，MAX丢弃已经在cte中创建的不需要的行。分数不包括在内，因为这会导致表格中有更多的行。为此，执行最终的JOIN，以从t2获得分数信息。

df1 <- sqldf(
"WITH cte AS (SELECT t1.*, t2.*
FROM t1
LEFT JOIN t2
ON t1.client_id = t2.client_id AND t1.application_time >= t2.date_)
SELECT A.*, B.score
FROM (SELECT cte.application_id , cte.application_time , cte.client_id , MAX(cte.date_) AS my_Date
FROM cte
GROUP BY cte.application_id , cte.application_time , cte.client_id)A
LEFT JOIN t2 AS B
ON A.client_id = B.client_id AND A.my_Date = B.date_"
)