sql - Postgresql 基于日期聚合值

Question

我有一个包含以下列的表格:

**name** | **start_date** | **termination_date** |  
X1 | 2019-01-01 | NULL  
X2 | 2018-05-01 | 2019-03-01 

我想知道每个月有多少人订阅了。这意味着 start_date 在 X 月之前，而 termination_date 要么在之后，要么为空。
例如。对于“2019-01-01”，我想要 start_date 在“2019-01-01”之前或之后的每一行，而 termination_date 在“2019-01-01”之后或 Null

我用这个查询试过:

SELECT start_date,
       sum(count(distinct name)) OVER (order by start_date)
from Table
where
    is_active = TRUE
    and (termination_date >= start_date or termination_date is null)
Group by start_date
Order by start_date;

但是，当我手动检查一些结果时，我发现它没有返回正确的值，而是 sum() 太高了。我不知道为什么会这样。

谁能指出我正确的方向？

Answer 1

demo:db<>fiddle

SELECT
    gs::date,
    COUNT(*)
FROM
    subscription,
    generate_series(                                             -- 1
        date_trunc('month', start_date),                         -- 2
        date_trunc('month', COALESCE(termination_date, now())),  -- 3
        interval '1 month'
    ) gs
GROUP BY gs::date                                                -- 4
ORDER BY 1

1. generate_series() 生成特定时间间隔内的日期。在这种情况下，每个月。
2. 开始是开始月份的第一天(由date_trunc()计算)。
3. 结束是终止月份的第一天。如果没有，则取当天 (COALESCE())
4. 此计算是针对每条记录分别进行的。因此之后可以对日期进行分组。

当然，您可以使用 to_char() 格式化您的月份: demo