sql - 如何在 Amazon Redshift 中按小时汇总唯一身份用户？

Question

借助 Amazon Redshift，我想统计每个唯一身份访问者。

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

因此，对于以下几行用户和时间戳，我们会得到 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

您可以使用lag 来完成此操作。但是，您还必须通过在 day 上进行分区来处理一天结束的情况。下面的查询将是一个起点。

with prev as (
select user_id,
datecol,
coalesce(lag(datecol) over(partition by user_id order by datecol),0) as prev
from tablename
)
select user_id,
sum(case when datediff(minutes, datecol, prev) >=60 then 1 else 0 end) as totalvisits
from prev
group by user_id