sql - 如何按动态时间间隔聚合数据

Question

我想使用 psql 计算每个唯一身份访问者。

唯一身份访问者是指在不到一个小时之前没有访问过的访问者。

因此，对于以下几行用户和时间戳，我们会得到 4 个唯一身份访问者的总数，其中 user1 和 user2 分别计为 2。

请注意，我不想在一天 24 小时内按小时汇总。我想在用户第一次访问的时间戳之后的一个小时进行聚合。

我猜直接的 sql 表达式不会这样做。

user1,"2015-07-13 08:28:45.247000" 
user1,"2015-07-13 08:30:17.247000"
user1,"2015-07-13 09:35:00.030000" 
user1,"2015-07-13 09:54:00.652000"
user2,"2015-07-13 08:28:45.247000" 
user2,"2015-07-13 08:30:17.247000"
user2,"2015-07-13 09:35:00.030000" 
user2,"2015-07-13 09:54:00.652000"

所以 user1 在 8:28 到达，这算作一次命中。他在 8:30 回来，这算作零。然后他在 9 点 35 分回来，距离 8 点 30 分还有一个多小时，所以他又受到了一次打击。然后他在 9:35 回来，距离上次 9:30 仅 5 分钟，所以这算作零。用户 1 的总点击次数为 2 次。同样的事情发生在 user2 身上，这意味着每次点击两次，最终总数达到 4。

Answer 1

这是一种方法:

select count(*)
from t
where not exists (select 1
                  from t t2
                  where t2.user = t.user and
                        t2.timestamp < t.timestamp and
                        t2.timestamp > t.timestamp - interval '1 hour'
                 );

编辑:

如果重复的时间戳是一个潜在的问题，您可以使用 count(distinct user, timestamp) .

备注:user和 timestamp都是关键字和user被预定了。希望您的实际列被命名为其他名称。

where子句仅在前一小时内没有用户其他记录的情况下保留记录。这是您对"new"用户的定义，因此汇总计数应该是您要寻找的。