PostgreSQL生成自定义起止日期序列并按计费周期汇总订阅量

你规划的CTE生成计费周期的方案完全可行。PostgreSQL原生的月份偏移运算已经天然覆盖你提到的两类边界场景，不需要额外写判断逻辑处理平闰月、大小月的适配：

例如'2021-01-29'::date + interval '1 month'会自动返回2021-02-28，'2021-03-31'::date + interval '1 month'会返回2021-04-30，完全符合你要求的边界适配规则。

• 先用generate_series生成从0到「当前月和订阅起始月的月份差」的整数序列，每个整数对应从订阅日开始偏移的月份数
• 每个偏移量n对应的计费周期起始日：subscribe_start_date + n * interval '1 month'，自动适配大小月、2月的特殊情况
• 每个计费周期结束日：(subscribe_start_date + (n+1) * interval '1 month') - interval '1 day'，同时限制最后一个周期不能超过当前日期
• 后续可直接基于生成的周期关联业务数据，按周期聚合sum(subscription_qty)即可

WITH params AS (
    SELECT '2021-10-07'::date AS current_date -- 此处可替换为实际当前日期，也可直接用CURRENT_DATE内置函数
),
-- 此处替换为你的实际订阅表查询逻辑，下方为测试示例数据
raw_subscriptions AS (
    SELECT 
        123 AS account_id,
        '2021-01-29'::timestamp AS subscribe_start_date,
        26 AS subscription_qty
    UNION ALL
    SELECT 
        112 AS account_id,
        '2021-10-27'::timestamp AS subscribe_start_date,
        261 AS subscription_qty
),
month_offset_list AS (
    SELECT 
        s.*,
        generate_series(0, EXTRACT(YEAR FROM AGE(p.current_date, s.subscribe_start_date))::int * 12 + EXTRACT(MONTH FROM AGE(p.current_date, s.subscribe_start_date))::int) AS month_offset
    FROM raw_subscriptions s
    CROSS JOIN params p
    -- 过滤订阅起始日晚于当前日期的记录，不需要可删除
    WHERE s.subscribe_start_date::date <= p.current_date
)
SELECT 
    account_id,
    subscribe_start_date,
    (subscribe_start_date + month_offset * interval '1 month')::date AS start_date,
    LEAST(
        (subscribe_start_date + (month_offset + 1) * interval '1 month')::date - 1,
        p.current_date
    ) AS end_date,
    subscription_qty
FROM month_offset_list mo
CROSS JOIN params p
ORDER BY account_id, start_date;

以测试数据中account_id=123的记录为例，生成的部分周期如下：

内容的提问来源于stack exchange，提问作者newbieitTech