如何过滤按日期排序的聚合查询

Question

我需要在两个独立日期created_at和erased_at的基础上检索聚合信息(通过SUM)。

要求如下:

tbody> <<tr>用户在该月或该月之前未被擦除。

列	描述
月	月YYYY-MM
Active_users	当月唯一用户总数。

一种方法生成月份，然后使用JOIN和GROUP BY:

select gs.yyyymm, count(u.id)
from generate_series('2018-01-01'::date, '2018-12-01'::date, interval '1 month') gs(yyyymm) left join
users u
on u.created_at < gs.yyyymm + interval '1 month' and
(u.erased_at >= gs.yyymm or u.erased_at is null)
group by gs.yyyymm;

注意:这是一个月内任何时刻活跃的用户，这就是我对你的问题的理解。如果您的意思是:

，您可以调整查询:

• 整个月的活跃用户数。
• 每月第一天的活跃用户数。
• 当月最后一天的活跃用户数。
编辑:

您可以使用以下命令使用数据中的日期:

select gs.yyyymm, count(u.id)
from (select yyyymm
from (select min(erased_at) as min_ea, max(erased_at) as max_ea
from users
) u cross join lateral
generate_series(date_trunc('month', min_ea), date_trunc('month', max_ea), interval '1 month'
) gs left join
users u
on u.created_at < gs.yyyymm + interval '1 month' and
(u.erased_at >= gs.yyymm or u.erased_at is null)
group by gs.yyyymm;