PAYATHON 2026

如何使用 SQL 生成订阅月度付款与 YTD 汇总表

支付老李

结论

这项计算可以完全用 SQL 完成。先求出每条订阅与各月份的有效日期交集，再用下面的公式计算月费：

当月费用 = AnnualFee / 365 × 当月实际生效天数

之后按 AccountNumber 和月份汇总，并使用窗口函数 SUM() OVER (...) 计算 YTD 累计金额。

以下查询以 PostgreSQL 为例，并基于这些假设：

  • StartDate 和 EndDate 当天都计费。
  • 日期字段使用真正的 DATE 类型，不是字符串。
  • 日费率固定为 AnnualFee / 365。
  • 输出时间为 2022 年 1 月 1 日至 2022 年 7 月 31 日。
  • 同一订阅的不同记录代表不同的有效期和年费。记录的有效期如果重叠，会分别计费。

PostgreSQL 查询

为了便于阅读，查询使用 snake_case 字段名。如果实际字段名是 AccountNumber、StartDate 这类大小写混合名称，在 PostgreSQL 中可能需要加双引号。

WITH months AS (
    SELECT
        month_start,
        month_end,
        COUNT(*) AS num_days_in_month
    FROM calendar
    WHERE date >= DATE '2022-01-01'
      AND date <  DATE '2022-08-01'
    GROUP BY
        month_start,
        month_end
),
accounts AS (
    SELECT DISTINCT
        account_number
    FROM subscriptions
),
monthly_raw AS (
    SELECT
        a.account_number,
        m.month_start,
        m.month_end,
        m.num_days_in_month,
        COALESCE(
            SUM(
                s.annual_fee / 365.0
                * (
                    LEAST(
                        COALESCE(s.end_date, m.month_end),
                        m.month_end
                    )
                    - GREATEST(s.start_date, m.month_start)
                    + 1
                )
            ),
            0
        ) AS fees_paid_raw
    FROM accounts AS a
    CROSS JOIN months AS m
    LEFT JOIN subscriptions AS s
        ON s.account_number = a.account_number
       AND s.start_date <= m.month_end
       AND COALESCE(s.end_date, DATE 'infinity') >= m.month_start
    GROUP BY
        a.account_number,
        m.month_start,
        m.month_end,
        m.num_days_in_month
),
monthly_rounded AS (
    SELECT
        account_number,
        month_start,
        month_end,
        num_days_in_month,
        ROUND(fees_paid_raw, 2) AS fees_paid
    FROM monthly_raw
)
SELECT
    account_number,
    month_start,
    month_end,
    num_days_in_month,
    fees_paid,
    ROUND(
        SUM(fees_paid) OVER (
            PARTITION BY account_number
            ORDER BY month_start
            ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
        ),
        2
    ) AS ytd_paid
FROM monthly_rounded
ORDER BY
    account_number,
    month_start;

日期交集的计算

一条订阅在某个月内的实际生效日期，从下面这个日期开始：

GREATEST(start_date, month_start)

到下面这个日期结束：

LEAST(end_date, month_end)

开始日和结束日都要计费，所以计算天数时需要加 1：

LEAST(end_date, month_end)
- GREATEST(start_date, month_start)
+ 1

例如，Subscription C 从 2022 年 2 月 4 日开始，它与 2 月的交集是：

2022-02-04 至 2022-02-28

首尾都包含在内，共 25 天，费用为：

450 / 365 × 25 = 30.82

Subscription A 和 B 在整个 2 月都有效：

(600 + 280) / 365 × 28 = 67.51

因此，2 月的总费用是：

67.51 + 30.82 = 98.33

先算月费，再算 YTD

monthly_rounded 会先把每个月的费用四舍五入到两位小数：

ROUND(fees_paid_raw, 2)

窗口函数再累计这些月度金额：

SUM(fees_paid) OVER (
    PARTITION BY account_number
    ORDER BY month_start
)

这样算出的 YTD，相当于逐月累加账单上已经四舍五入的月费。例如：

January:  74.74
February: 98.33
YTD:      173.07

如果账务规则要求先累计未舍入的金额，最后再保留两位小数，可以直接累计 fees_paid_raw：

ROUND(
    SUM(fees_paid_raw) OVER (
        PARTITION BY account_number
        ORDER BY month_start
    ),
    2
) AS ytd_paid

这两种算法在部分月份可能相差一两分钱，应按实际账务规则选择。

按天计算的写法

calendar 表已经有每日记录，因此也可以把每天连接到当天有效的订阅，再按月汇总。这种写法更直观，但数据量较大时，通常不如直接计算日期交集高效。

WITH months AS (
    SELECT
        month_start,
        month_end,
        COUNT(*) AS num_days_in_month
    FROM calendar
    WHERE date >= DATE '2022-01-01'
      AND date <  DATE '2022-08-01'
    GROUP BY
        month_start,
        month_end
),
daily_fees AS (
    SELECT
        s.account_number,
        c.month_start,
        c.month_end,
        c.date,
        SUM(s.annual_fee / 365.0) AS daily_fee
    FROM calendar AS c
    JOIN subscriptions AS s
      ON c.date >= s.start_date
     AND c.date <= s.end_date
    WHERE c.date >= DATE '2022-01-01'
      AND c.date <  DATE '2022-08-01'
    GROUP BY
        s.account_number,
        c.month_start,
        c.month_end,
        c.date
),
monthly AS (
    SELECT
        d.account_number,
        d.month_start,
        d.month_end,
        m.num_days_in_month,
        ROUND(SUM(d.daily_fee), 2) AS fees_paid
    FROM daily_fees AS d
    JOIN months AS m
      ON m.month_start = d.month_start
     AND m.month_end = d.month_end
    GROUP BY
        d.account_number,
        d.month_start,
        d.month_end,
        m.num_days_in_month
)
SELECT
    account_number,
    month_start,
    month_end,
    num_days_in_month,
    fees_paid,
    ROUND(
        SUM(fees_paid) OVER (
            PARTITION BY account_number
            ORDER BY month_start
            ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
        ),
        2
    ) AS ytd_paid
FROM monthly
ORDER BY
    account_number,
    month_start;

注意事项

闰年计费

上面的查询严格按题目给出的 AnnualFee / 365 计算。如果业务规定闰年按 366 天计费，分母需要动态计算。PostgreSQL 可以这样写：

EXTRACT(
    DAY FROM (
        make_date(EXTRACT(YEAR FROM charge_date)::int + 1, 1, 1)
        - make_date(EXTRACT(YEAR FROM charge_date)::int, 1, 1)
    )
)

订阅跨年时，每个自然年的日费率可能不同。遇到这种情况，按天计算通常更合适，也可以先按年份拆分有效区间。

EndDate 是否包含当天

当前查询采用闭区间：

StartDate <= 计费日期 <= EndDate

如果系统将 EndDate 定义为“不再生效的第一天”，它就是排他结束日期。此时需要做三处调整：

  • 把重叠条件中的 >= 改为 >。
  • 计算天数时去掉 + 1。
  • 将 EndDate - 1 day 作为最后计费日，或者统一使用半开区间 [StartDate, EndDate)。

防止重复计费

如果同一账户、同一 Subscription 的两条记录在日期上重叠，查询会同时计入这两条记录。这既可能是正确的独立收费，也可能是数据问题。

如果同一 Subscription 在任何一天只能对应一个有效价格，应增加唯一性约束或排除约束，也可以在查询前使用 ROW_NUMBER() 明确价格的生效优先级。

不完整月份

示例中的 calendar 表只包含到 8 月 1 日，不能直接把表中的行数当作完整 8 月的天数。查询使用下面的条件，只输出截至 7 月 31 日的完整月份：

date < DATE '2022-08-01'

如果结束日期通过参数传入，最好确保它始终落在完整月末，或者明确允许按截止日期计算不完整月份。

索引

数据量较大时，建议至少创建以下索引：

CREATE INDEX idx_subscriptions_account_dates
    ON subscriptions (
        account_number,
        start_date,
        end_date
    );

CREATE INDEX idx_calendar_date
    ON calendar (date);

直接计算月份交集不会把每条订阅展开为每日记录，通常更适合生产查询。

备注：内容仅供参考。