如何通过动态时间间隔聚合数据
与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
这里有一个方法:
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
子句只保留在前一小时没有其他用户记录的记录。这是您对“新”用户的定义,因此汇总计数应该是您要查找的内容。
非常感谢。你能解释它在做什么吗?这将是非常有益的。 –
我误解了这个定义。它改变了定义。我提高了你的意思,并重新定义了这个定义,但是现在答案也是不正确的。 –
@BrianYeh。 。 。我第一次明白了这个问题。这应该得到你想要的数量。 –
角的情况下在你的定义一边(see comment),这个查询比我的本地测试提供了一个@Gordon上的Postgres 9.4快许多倍,带或不带指数:
SELECT count(ct)
FROM (
SELECT user_id, CASE WHEN lead(created_at, 1, 'infinity')
OVER (PARTITION BY user_id ORDER BY created_at)
> created_at + interval '1h' THEN true ELSE NULL END AS ct
FROM tbl
) sub;
的核心特性是窗口功能lead()
查找当前用户的“下一个”行。如果下一行是更多比一个小时的路程,这一行很重要。
lead()
经常被忽视的功能是为缺少的行提供默认值。填写'infinity'
覆盖角落案例。
此查询适用于timestamp
列created_at
中的NULL值(未计数)或重复值(仅1行计数)。
关键最高性能是多列索引:
CREATE INDEX tbl(user_id, created_at);
对于“独立访问者”防弹定义不同的查询方式可能更好。
如果user1在9:00访问,那么在9:30访问然后在11:00访问,那么这应该算作2次访问。我严格地表述了这个定义。我会提高你们两个人并编辑定义。 –
'现在想'应该是'不想'我想?请始终声明您的Postgres版本。你有单独的用户表吗?实际的表定义将有助于查看数据类型和约束:CREATE TABLE脚本或psql中的\ d tbl'的输出。对于每60分钟访问一次的假设用户呢?计数为** 0次访问总数,即使在较长时间内也是如此?此外,严格来说,您的定义将在不到一小时前(从现在开始)排除访问。 –
该版本是亚马逊上的Redshift。所以它缺少某些postgres功能。如果用户每60分钟访问一次,则每次访问计数一次。如果用户在60分钟内访问超过一次,那么他只会计算一次。 –
红移不是Postgres,也非常有限。我不是粉丝。请记住提供您的版本,以问题开头。 –