如何在SQL中将年度费用分摊到各月并按月累加计算月度收入

场景说明 #

你当前的原始交易表结构如下（假设表名为membership_revenue）：

需求为将每笔年度会员费总额平分为12份，从收费当月开始分摊到后续12个自然月，最终统计每个月的累计确认收入。

实现思路 #

1. 用递归CTE生成0到11的月份偏移量，对应每笔收费需要分摊的12个周期
2. 将原始交易表与偏移量做笛卡尔积，把每笔收费拆成12条分摊记录，计算出每条记录对应的确认月份和单月分摊金额
3. 按确认月份分组聚合，得到每月的总确认收入

通用SQL代码（兼容PostgreSQL/MySQL 8.0+） #

WITH RECURSIVE month_offsets AS (
    -- 生成0-11的偏移量，对应收费当月到之后11个月，共12个分摊周期
    SELECT 0 AS offset
    UNION ALL
    SELECT offset + 1 FROM month_offsets WHERE offset < 11
),
revenue_allocation AS (
    SELECT
        -- 计算分摊对应的自然月
        DATE_ADD(m.Month, INTERVAL mo.offset MONTH) AS recognized_month,
        -- 计算单月分摊金额
        m.amount / 12 AS monthly_revenue
    FROM membership_revenue m
    CROSS JOIN month_offsets mo
    WHERE m.Transaction = 'Annual Membership Fee'
)
-- 按月聚合得到累计确认收入
SELECT
    recognized_month,
    SUM(monthly_revenue) AS total_recognized_revenue
FROM revenue_allocation
GROUP BY recognized_month
ORDER BY recognized_month;

如果你使用SQL Server，需要将日期计算逻辑替换为DATEADD(MONTH, mo.offset, m.Month)即可正常运行。

结果校验逻辑 #

以你给出的示例数据计算：

• 2021-09月的2940元分摊到每月为245元，分摊周期为2021-09到2022-08
• 2021-10月总确认收入 = 7月分摊的3.75元 + 8月分摊的≈12.08元 + 9月分摊的245元 + 10月自身分摊的128.75元 ≈ 389.58元
  所有分摊记录都会自动叠加到对应的月份，满足你的业务需求。

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