SQL查询，以获取在检查站点的瞬间之前访问过该站点2次以上的userCount。

Question

我有一个表，每次用户访问该页面时，我们都会保存日志。我们希望得到已经访问网站超过1次的用户数量。假设用户在8月1日和3日访问了网站。现在，在8月2日，用户将不会被计算，因为他只有一次访问，直到现在。因此，访问超过1次的用户总数仅在8月2日为0，但在8月4日，由于用户已经访问了网站1次以上，用户数量将为1。

我实际上是想把这作为Grafana上的每日用户计数趋势来完成。

我编写的SQL查询提供了：

数据库查询错误: ERROR:还不支持这种关联子查询模式

SQL查询：

select date_trunc('day', timestamp) as day_value, 
   count(distinct distinct_id) 
   from posthog_event where 
   timestamp BETWEEN '2022-08-09T22:15:43Z' AND '2022-08-10T22:15:43Z'  and 
   distinct_id IN 
   (select distinct_id from(select distinct_id, count(*) as event_count from 
   posthog_event innertable 
   where innertable.timestamp < day_value and event = 'visited' group by 1) 
   where event_count>2) 
   group by day_value order by day_value

显示错误和查询的格拉法纳图像

数据源是Redshift。

Answer 1

我已经整理了一个简单的案例来检测第二次(或更多)的访问。我希望您可以修改以满足您的特定用例。

设立：

create table mytable (
  id int,   
  visit_date date);
  
insert into mytable values
(1, '2022-01-01'),
(2, '2022-01-02'),
(3, '2022-01-03'),
(1, '2022-01-04'),
(1, '2022-01-09'),
(2, '2022-01-10'),
(3, '2022-01-11')
;

按身份证统计访问次数：

select id, visit_date,
      count(1) over(partition by id order by visit_date) as num_visits
from mytable
order by visit_date;

这将产生访问id的次数。(是的，我更改为COUNT() OVER()，因为这是最简单的。)在执行窗口功能之前，您可能需要按日期进行一些数据分组。