sql - 有没有办法在 SQL 中找到活跃用户？

Question

我正在尝试查找数据库中活跃用户的总数。这里的“活跃”用户定义为在选定日期或选定日期之后注册了事件的用户。因此，如果用户在第 1、2 和 5 天注册了事件，则他们在第 1、2、3、4 和 5 天都被计为“活跃”。

我的原始数据集如下所示(请注意，这是一个示例 - 真实数据集将运行长达 365 天，并且有大约 1000 个用户)。

Day    ID
0      1
0      2
0      3
0      4
0      5
1      1
1      2
2      1
3      1
4      1
4      2

如您所见，所有 5 个 ID 在第 0 天都处于事件状态，并且 2 个 ID(1 和 2)一直处于事件状态直到第4，所以我希望完成的表格看起来像这样:

Day    Count
0      5
1      2
2      2
3      2
4      2

我试过使用以下查询:

select Day as days, sum(case when Day <= days then 1 else 0 end)
from df

但它给出了错误的输出(只计算在每个特定日期活跃的用户)。

我不知道接下来可以尝试什么。有人有什么想法吗？非常感谢!

Answer 1

我想我会使用generate_series():

select gs.d, count(*)
from (select id, min(day) as min_day, max(day) as max_day
      from t
      group by id
     ) t cross join lateral
     generate_series(t.min_day, .max_day, 1) gs(d)
group by gs.d
order by gs.d;

如果你想从第 1 天起将每个人都统计为活跃的——但不是所有的人在第 1 天都有值——那么使用 1 而不是 min_day 。

Here是一个数据库<> fiddle 。