postgresql - Postgres 随着时间的推移累积计数

Question

我正在尝试查找某个时间范围内一个月内的活跃订阅数量。

表:订阅字段:

• ID
• “创建日期”
• “删除日期”

在以下情况下，订阅在特定时间戳被视为活跃:

1. 删除日期为空
2. 特定时间戳介于creationDate和deletionDate之间

示例:

• 订阅 A 的创建日期为“2014-06-27 11:37:34.205+00”，删除日期为“2014-08-01 04:16:34.435+00”。订阅 A 在 2014 年 6 月、2014 年 7 月和 2014 年 8 月被视为活跃。
• 订阅 B 的创建日期为“2014-06-27 11:37:34.205+00”，删除日期为“2014-06-28 11:37:34.205+00”。订阅 B 仅在 2014 年 6 月有效。
• 订阅 C 的创建日期为“2014-06-27 11:37:34.205+00”，没有删除日期。订阅 C 在 2014 年 6 月至今的月份内被视为有效。

这是我尝试过的:

select "Month", sum(sub) over (order by "Month" asc) as "Active subscriptions"
from
(select to_char(subscriptions."creationDate" at time zone '-7', 'YYYY-MM') as "Month", 
    count(distinct subscriptions.id) as sub
    from subscriptions
    where (to_char(subscriptions."deletionDate" at time zone '-7', 'YYYY-MM') is null 
        or to_char(subscriptions."deletionDate" at time zone '-7', 'YYYY-MM') >= to_char(subscriptions."creationDate" at time zone '-7', 'YYYY-MM') )
    group by "Month") as foo

但是，问题在于它包含了上个月的非活跃订阅数量。为了说明我的意思，我上面的查询似乎包括订阅 B(在上面的示例中)作为 2014 年 7 月的有效订阅。

我不确定如何获取特定月份的“活跃订阅”，以删除前几个月不再活跃的订阅计数。

谢谢! :)

Answer 1

SELECT m, count(subscriptions.*) 
FROM subscriptions 
JOIN generate_series('2010-01-01'::date, now(), interval '1 mon') AS m
ON m >= subscriptions.creationDate AND 
       (subscriptions.deletionDate IS NULL OR m <= subscriptions.deletionDate)
/* You may get better indexed performance if you use a date 
   far in the future for current
  accounts, instead of NULL. Then you can use BETWEEN */
GROUP BY m ORDER BY m;