PAYATHON 2026

PostgreSQL 生成自定义账期并汇总 subscription_qty

支付阿杰

结论

不要在上一个账单日期上反复加 interval '1 month'。日期经过 2 月后，可能会一直停留在 28 日。

可以按下面的方式计算：

  1. 用 generate_series() 生成连续的月份起点；
  2. 保留 subscribe_start_date 的原始日号；
  3. 每个月取原始日号与当月最后一天日号中的较小值；
  4. 用相邻两个账单日期组成左闭右开的周期：[start_date, end_date)；
  5. 按 usage_date >= start_date AND usage_date < end_date 汇总用量。

计算结果如下：

2021-01-29
2021-02-28
2021-03-29
2021-04-29
...

如果订阅从 31 日开始，结果是：

2021-01-31
2021-02-28
2021-03-31
2021-04-30
2021-05-31
...

短月份只影响当月，不会改变后续月份的账单日。

生成每个月的账单日期

假设订阅表名为 subscriptions：

CREATE TABLE subscriptions (
    account_id bigint,
    subscribe_start_date timestamp,
    subscription_qty numeric
);

先为每个账户生成账单日期：

WITH billing_dates AS (
    SELECT
        s.account_id,
        s.subscribe_start_date,
        s.subscription_qty,
        gs.month_start,
        gs.month_start
            + (
                LEAST(
                    EXTRACT(DAY FROM s.subscribe_start_date)::int,
                    EXTRACT(
                        DAY FROM (
                            gs.month_start
                            + interval '1 month'
                            - interval '1 day'
                        )
                    )::int
                ) - 1
            ) * interval '1 day'
            + s.subscribe_start_date::time AS billing_date
    FROM subscriptions AS s
    CROSS JOIN LATERAL generate_series(
        date_trunc('month', s.subscribe_start_date),
        date_trunc('month', current_date),
        interval '1 month'
    ) AS gs(month_start)
)
SELECT
    account_id,
    billing_date
FROM billing_dates
WHERE billing_date >= subscribe_start_date
ORDER BY account_id, billing_date;

其中，LEAST() 用来取下面两个值中的较小值：

LEAST(原始订阅日号, 当月最后一天的日号)

假设原始订阅日号是 31：

  • 1 月最后一天是 31 日，结果为 31 日；
  • 2 月最后一天是 28 日，结果为 28 日；
  • 3 月最后一天是 31 日，结果恢复为 31 日；
  • 4 月最后一天是 30 日，结果为 30 日。

每个月都以原始订阅日号为基准单独计算，所以不会出现经过 2 月后一直停留在 28 日的情况。

生成 start_date 和 end_date

使用 lead()，可以把下一次账单日期作为当前周期的结束时间：

WITH billing_dates AS (
    SELECT
        s.account_id,
        s.subscribe_start_date,
        gs.month_start
            + (
                LEAST(
                    EXTRACT(DAY FROM s.subscribe_start_date)::int,
                    EXTRACT(
                        DAY FROM (
                            gs.month_start
                            + interval '1 month'
                            - interval '1 day'
                        )
                    )::int
                ) - 1
            ) * interval '1 day'
            + s.subscribe_start_date::time AS billing_date
    FROM subscriptions AS s
    CROSS JOIN LATERAL generate_series(
        date_trunc('month', s.subscribe_start_date),
        date_trunc('month', current_date) + interval '1 month',
        interval '1 month'
    ) AS gs(month_start)
),
billing_periods AS (
    SELECT
        account_id,
        billing_date AS start_date,
        lead(billing_date) OVER (
            PARTITION BY account_id
            ORDER BY billing_date
        ) AS end_date
    FROM billing_dates
    WHERE billing_date >= subscribe_start_date
)
SELECT
    account_id,
    start_date,
    end_date
FROM billing_periods
WHERE end_date IS NOT NULL
  AND start_date <= current_timestamp
ORDER BY account_id, start_date;

这里的 generate_series() 会多生成一个月，因为当前月份的周期也需要下一次账单日期作为 end_date。

假设订阅开始时间为 2021-01-29 00:00:00，部分结果如下：

start_date           end_date
2021-01-29 00:00:00  2021-02-28 00:00:00
2021-02-28 00:00:00  2021-03-29 00:00:00
2021-03-29 00:00:00  2021-04-29 00:00:00

按账单周期汇总 subscription_qty

问题中没有提供实际的用量表结构。这里假设用量记录保存在 subscription_usage 中：

subscription_usage (
    account_id,
    usage_date,
    subscription_qty
)

完整查询如下：

WITH billing_dates AS (
    SELECT
        s.account_id,
        s.subscribe_start_date,
        gs.month_start
            + (
                LEAST(
                    EXTRACT(DAY FROM s.subscribe_start_date)::int,
                    EXTRACT(
                        DAY FROM (
                            gs.month_start
                            + interval '1 month'
                            - interval '1 day'
                        )
                    )::int
                ) - 1
            ) * interval '1 day'
            + s.subscribe_start_date::time AS billing_date
    FROM subscriptions AS s
    CROSS JOIN LATERAL generate_series(
        date_trunc('month', s.subscribe_start_date),
        date_trunc('month', current_date) + interval '1 month',
        interval '1 month'
    ) AS gs(month_start)
),
billing_periods AS (
    SELECT
        account_id,
        billing_date AS start_date,
        lead(billing_date) OVER (
            PARTITION BY account_id
            ORDER BY billing_date
        ) AS end_date
    FROM billing_dates
    WHERE billing_date >= subscribe_start_date
)
SELECT
    bp.account_id,
    bp.start_date,
    bp.end_date,
    COALESCE(SUM(u.subscription_qty), 0) AS total_subscription_qty
FROM billing_periods AS bp
LEFT JOIN subscription_usage AS u
    ON u.account_id = bp.account_id
   AND u.usage_date >= bp.start_date
   AND u.usage_date < bp.end_date
WHERE bp.end_date IS NOT NULL
  AND bp.start_date <= current_timestamp
GROUP BY
    bp.account_id,
    bp.start_date,
    bp.end_date
ORDER BY
    bp.account_id,
    bp.start_date;

这里要使用左闭右开的区间：

usage_date >= start_date
AND usage_date < end_date

如果改用 BETWEEN start_date AND end_date，恰好发生在 end_date 的记录可能同时进入前后两个周期，导致重复统计。

只统计截至当前时间的用量

上面的查询会生成完整账单周期，因此当前周期的 end_date 可能在未来。这适合展示账期，但实际用量只能统计到当前时间。

可以将连接条件改为：

AND u.usage_date < LEAST(bp.end_date, current_timestamp)

再单独列出实际的统计截止时间：

LEAST(bp.end_date, current_timestamp) AS usage_cutoff

不要直接把周期的 end_date 改成当前时间，否则账期边界和实际统计截止时间会混在一起。

注意事项

  • 如果 subscribe_start_date 晚于当前时间，就不应生成已经开始的账期。查询中的 bp.start_date <= current_timestamp 会排除这类记录。
  • 如果一个账户可能有多条订阅，只按 account_id 分区会混合不同订阅。此时应增加 subscription_id，并在 PARTITION BY、关联条件和分组条件中使用它。
  • 如果字段类型是 timestamptz，月份边界会受会话时区影响。账期应按业务时区计算，例如先用 AT TIME ZONE 'Asia/Shanghai' 转换为业务本地时间。
  • 如果只关心日期，不需要保留时分秒，可以把账单边界统一转换为 date，查询会更简单。
  • 10/07/2021 存在日期格式歧义，既可能表示 10 月 7 日，也可能表示 7 月 10 日。SQL 中应使用 ISO 格式，例如 DATE '2021-10-07'，避免依赖数据库的 DateStyle 设置。
  • 数据量较大时，应为用量表建立与关联条件匹配的索引：
CREATE INDEX ON subscription_usage (account_id, usage_date);

备注：内容仅供参考。