This question already has an answer for SQL, and I was able to implement that solution in R using sqldf. However, I've been unable to find a way to implement it using data.table.
The problem is to count the distinct values of one column within a rolling date range, e.g. (and quoting directly from the linked question) if the data looked like this:
Date | email
-------+----------------
1/1/12 | test@test.com
1/1/12 | test1@test.com
1/1/12 | test2@test.com
1/2/12 | test1@test.com
1/2/12 | test2@test.com
1/3/12 | test@test.com
1/4/12 | test@test.com
1/5/12 | test@test.com
1/5/12 | test@test.com
1/6/12 | test@test.com
1/6/12 | test@test.com
1/6/12 | test1@test.com
Then the result set would look something like this if we used a date period of 3 days
date | count(distinct email)
-------+------
1/1/12 | 3
1/2/12 | 3
1/3/12 | 3
1/4/12 | 3
1/5/12 | 2
1/6/12 | 2
Here's the code to create the same data in R using data.table:
date <- as.Date(c('2012-01-01','2012-01-01','2012-01-01',
'2012-01-02','2012-01-02','2012-01-03',
'2012-01-04','2012-01-05','2012-01-05',
'2012-01-06','2012-01-06','2012-01-06'))
email <- c('test@test.com', 'test1@test.com','test2@test.com',
'test1@test.com', 'test2@test.com','test@test.com',
'test@test.com','test@test.com','test@test.com',
'test@test.com','test@test.com','test1@test.com')
dt <- data.table(date, email)
Any help on this would be much appreciated. Thanks!
Edit 1:
This is a toy problem that I want to apply to a much larger data set, so use of Cartesian products is problematic. Instead, I'd like something equivalent to a correlated subquery in SQL, e.g. the solution from the question that I originally linked was:
SELECT day
,(SELECT count(DISTINCT email)
FROM tbl
WHERE day BETWEEN t.day - 2 AND t.day -- period of 3 days
) AS dist_emails
FROM tbl t
WHERE day BETWEEN '2012-01-01' AND '2012-01-06'
GROUP BY 1
ORDER BY 1;
Edit 2: Here is some timing based on @MichaelChirico's solution, as requested by @jangorecki:
# The data
> dim(temp)
[1] 2627785 4
> head(temp)
date category1 category2 itemId
1: 2013-11-08 0 2 1713
2: 2013-11-08 0 2 90485
3: 2013-11-08 0 2 74249
4: 2013-11-08 0 2 2592
5: 2013-11-08 0 2 2592
6: 2013-11-08 0 2 765
> uniqueN(temp$itemId)
[1] 13510
> uniqueN(temp$date)
[1] 127
# Timing for data.table
> system.time(dtTime <- temp[,
+ .(count = temp[.(seq.Date(.BY$date - 6L, .BY$date, "day"),
+ .BY$category1, .BY$category2 ), uniqueN(itemId), nomatch = 0L]),
+ by = c("date","category1","category2")])
user system elapsed
6.913 0.130 6.940
>
# Time for sqldf
> system.time(sqlDfTime <-
+ sqldf(c("create index ldx on temp(date, category1, category2)",
+ "SELECT date, category1, category2,
+ (SELECT count(DISTINCT itemId)
+ FROM temp
+ WHERE category1 = t.category1 AND category2 = t.category2 AND
+ date BETWEEN t.date - 6 AND t.date
+ ) AS numItems
+ FROM temp t
+ GROUP BY date, category1, category2
+ ORDER BY 1;"))
user system elapsed
87.225 0.098 87.295
The outputs are eqivalent, but using data.table rather than sqldf resulted in a 12.5x speedup. Pretty substantial!
