Я хочу подсчитать скользящее количество уникальных пользователей с переменными временными окнами. Вот пример того, что у меня есть, и желаемого результата.
have <- data.frame(user = c(1, 2,
2, 3,
1, 2, 3,
4,
3, 4,
4),
when = lubridate::ymd("2020-01-01",
"2020-01-01",
"2020-01-02",
"2020-01-02",
"2020-01-03",
"2020-01-03",
"2020-01-03",
"2020-01-05",
"2020-01-06",
"2020-01-06",
"2020-01-07"))
have
# user when
#1 1 2020-01-01
#2 2 2020-01-01
#3 2 2020-01-02
#4 3 2020-01-02
#5 1 2020-01-03
#6 2 2020-01-03
#7 3 2020-01-03 # note that Jan 4 is missing
#8 4 2020-01-05
#9 3 2020-01-06
#10 4 2020-01-06
#11 4 2020-01-07
want <- data.frame(when=c("2020-01-01",
"2020-01-02",
"2020-01-03",
"2020-01-04",
"2020-01-05",
"2020-01-06",
"2020-01-07"),
twoDayCount=c(2, # Jan 1: 1, 2
3, # Jan 1-2: 1, 2, 3
3, # Jan 2-3: 1, 2, 3
3, # Jan 3-4: 1, 2, 3
1, # Jan 4-5: 4
2, # Jan 5-6: 3, 4
2 # Jan 6-7: 3, 4
)
)
want
# when twoDayCount
#1 2020-01-01 2 # users: 1, 2
#2 2020-01-02 3 # users: 1, 2, 3
#3 2020-01-03 3 # users: 1, 2, 3
#4 2020-01-04 3 # users: 1, 2, 3
#5 2020-01-05 1 # users: 4
#6 2020-01-06 2 # users: 3, 4
#7 2020-01-07 2 # users: 3, 4
Я пробовал несколько подходов, но они заставляют меня подсчитывать все строки в окне, а не отдельных пользователей в окне. Например, желаемое двухдневное количество уникальных пользователей 3 января составляет 3 (пользователи 1, 2, 3), а не 5 строк (при этом пользователи 2 и 3 появляются дважды).
В моем фактическом варианте использования в качестве входных данных требуется период скользящего окна (в данном примере 2 дня).
В идеале решение работает с функциями, которые {dbplyr}
можно переводить в sql или через собственный sql, который можно запускать с {dbplyr}
.
Этот ответ дает представление о том, как решить с помощью sql:
SELECT when, count(DISTINCT user) AS dist_users
FROM (SELECT generate_series('2020-01-01'::date, '2020-01-07'::date, '1d')::date) AS g(when)
LEFT JOIN tbl t ON t.when BETWEEN g.when - 2 AND g.when
GROUP BY 1
ORDER BY 1;