我真的很感谢对这个问题的一些帮助,我找不到足够接近的例子。
我有两个 data.tables,第一个称为customer.table
,包含特定于时间戳(AsOfDate
),第二个表称为activity.table
描述发送给该客户的营销活动ActivityDate
.
我想找到客户数据表中每条记录的 AsOfDate 之前或当天发送给会员的最新 ActivityDate(即最大日期)。
我查看了几个问题(一个接近的问题是:处理 ID 重复的表 https://stackoverflow.com/questions/29988283/handle-a-table-with-id-repetition/29988428#29988428),但我不确定如何将条件 (ActivityDate
#libraries
library(lubridate)
library(data.table)
#data
customer.table = structure(list(CustomerID = c(1, 2, 3, 4, 5, 6, 7, 8, 9, 10,
4), AsOfDate = structure(c(1435622400, 1435622400, 1435622400,
1435622400, 1435622400, 1435622400, 1435622400, 1435622400, 1435622400,
1435622400, 1394150400), tzone = "UTC", class = c("POSIXct",
"POSIXt")), distance = c(2.17380476584343, 29.4024827688224,
3.01353310956009, 18.4923143452557, 294.878606580665, 11.8870209430565,
9.54438580030996, 24.2192034858273, 15.0069335290262, 10.4513664447137,
18.4923143452557)), .Names = c("CustomerID", "AsOfDate", "distance"
), row.names = c("1", "5", "8", "10", "18", "28", "33", "37",
"45", "47", "101"), class = "data.frame")
activity.table = structure(list(CustomerID = c(3, 5, 8, 10, 4, 10, 2, 2, 5, 7,
5, 8, 4, 6, 10, 6, 5, 4, 2, 5, 5, 6, 5, 5, 10, 8, 6, 4, 5, 8,
7, 1, 8, 10, 7, 8, 4, 1, 1, 10, 9, 7, 4, 6, 9, 10, 8, 3, 5, 8,
1, 4, 4), ActivityDate = structure(c(1330560000, 1368144000,
1332855900, 1337817600, 1370822400, 1365984000, 1337817600, 1368144000,
1331164800, 1331164800, 1394150400, 1394150400, 1396224000, 1393891200,
1393891200, 1398643200, 1396310400, 1399334400, 1399939200, 1403222400,
1402358400, 1404086400, 1425254400, 1426464000, 1426464000, 1426464000,
1427155200, 1429056000, 1429056000, 1429056000, 1363737600, 1332201600,
1330560000, 1433116800, 1433289600, 1433289600, 1338462000, 1366628400,
1335885300, 1427241600, 1427241600, 1427241600, 1430265600, 1430265600,
1430265600, 1430265600, 1365503400, 1338394200, 1430265600, 1430265600,
1432598400, 1433894400, 1426723200), tzone = "UTC", class = c("POSIXct",
"POSIXt")), row.index = 1:53), .Names = c("CustomerID", "ActivityDate",
"row.index"), row.names = c(NA, -53L), class = "data.frame")
# what does the data look like
> head(activity.table)
CustomerID ActivityDate row.index
1 3 2012-03-01 00:00:00 1
2 5 2013-05-10 00:00:00 2
3 8 2012-03-27 13:45:00 3
4 10 2012-05-24 00:00:00 4
5 4 2013-06-10 00:00:00 5
6 10 2013-04-15 00:00:00 6
> head(customer.table)
CustomerID AsOfDate distance
1 1 2015-06-30 2.173805
5 2 2015-06-30 29.402483
8 3 2015-06-30 3.013533
10 4 2015-06-30 18.492314
18 5 2015-06-30 294.878607
28 6 2015-06-30 11.887021
感谢你的协助。