时间戳

首页 > 解决方案 > 获取过去 2 周在任何给定日期发生的事件

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

问题描述

我有类似的数据

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

我想要得到的是在任何给定日期过去 2 周内具有 ID 的用户发生的次数，所以基本上是“日期 - 14 天和日期之间的发生次数。我试图按用户的数量对用户进行分类过去两周的会议，我每天都在关注他们。

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

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

标签： sqlpresto

解决方案

不幸的是，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;

推荐阅读