我正在寻找按日期范围连接两个表的简单方法。一个表包含确切的日期,另一个表包含两个标识时间段开始和结束的变量。如果第一个表中的日期在第二个表的范围内,我需要连接表。
data1 <- data.table(date = c('2010-01-21', '2010-01-25', '2010-02-02', '2010-02-09'),
name = c('id1','id2','id3','id4'))
data2 <- data.table(beginning=c('2010-01-15', '2010-01-23', '2010-01-30', '2010-02-05'),
ending = c('2010-01-22','2010-01-29','2010-02-04','2010-02-13'),
class = c(1,2,3,4))
result <- data.table(date = c('2010-01-21', '2010-01-25', '2010-02-02', '2010-02-09'),
beginning=c('2010-01-15', '2010-01-23', '2010-01-30', '2010-02-05'),
ending = c('2010-01-22','2010-01-29','2010-02-04','2010-02-13'),
name = c('id1','id2','id3','id4'),
class = c(1,2,3,4))
有什么帮助吗?我发现了一些困难的例子,但由于格式原因,它们甚至无法处理我的数据。我需要类似的东西:
select * from data1
left join
select * from data2
where data2.beginning <= data1.date <= data2.ending
Thanks