获取过去两周在任何给定日期发生的事件

Question

我有类似的数据

id | date   |
-------------
1 | 1.1.20 |
3 | 4.1.20 |
2 | 4.1.20 |
1 | 5.1.20 |
6 | 2.1.20 |

我想得到的是获得ID用户在过去2周内在任何给定日期发生的次数，所以基本上"；发生在日期-14天和日期之间。我试图根据用户过去两周的会话量对他们进行分类，并按照每天的队列对他们进行跟踪。

此查询不起作用，因为用户可能有几天没有登录，也就是没有行：

COUNT (distinct id) OVER (PARTITION BY id ORDER BY date ROWS BETWEEN 14 PRECEDING AND  0 FOLLOWING)

Answer 1

遗憾的是，Presto不支持range()窗口函数。一种方法是自联接/聚合或相关子查询：

select t.id, count(tprev.id)
from t left join
t tprev
on tprev.id = t.id and
tprev.date > t.date - interval '13' day and
tprev.date <= t.date
group by t.id;

这会将您的请求解释为需要14天的数据，包括当天的数据。

另一种更详细但可能更快的方法是使用lag()。和lag()再次：

select t.id,
(1 +   -- current date
(case when lag(date, 1) over (partition by id order by date) > date - interval '14' day then 1 else 0 end) +
(case when lag(date, 2) over (partition by id order by date) > date - interval '14' day then 1 else 0 end) +
. . .
(case when lag(date, 13) over (partition by id order by date) > date - interval '14' day then 1 else 0 end) +
) as cnt_14
from t;