月活跃用户分析-如何显示一个月内发生0个事件的情况

Question

我有一个这样的表

User| Action | Date
1     log-in   2021-07-08
1     log-in   2021-09-03
...

等等…

我想要的是能够看到用户1自加入以来每个月使用了多少次，即使是他没有做任何类似这样的事情的那个月:

User|   Cohort    |   Date   | Clicks
1     2021-07-01   2021-07-01    1
1     2021-07-01   2021-08-01    0
1     2021-07-01   2021-09-01    1
...

要做到这一点，我做了这样的事情:

with cte as (
select user, max(date_trunc('month',date)) as this_month
from table
group by 1
having max(date_trunc('month',date)) = date_trunc('month',Current_date)
)
select t.user, cte.this_month, date_trunc('month',t.date), count(*) as clicks
from table t
join cte on cte.user = t.user
group by 1,2,3
order by 1,3 desc

但是省略了8月，只显示了7月和9月。

建议吗?

Answer 1

您的代码看起来像Postgres。如果是这样，可以使用generate_series():

select t.user, t.cohort, gs.date,
count(*) filter (where date_trunc('month', date) = gs.date) as clicks
from (select t.*,
date_trunc('month', min(date) over (partition by user)) as cohort
from t
) t cross join lateral
generate_series(t.cohort, current_date, interval '1 month') as gs(date)
group by t.user, t.cohort, gs.date