SQL实现按月度确认订阅收入（含月中开票、升降级场景）

现有方案的问题 #

当前拆分逻辑默认单张月付发票覆盖完整30天服务周期，未处理客户中途升降级导致旧订阅提前终止、新订阅即时生效的场景，固定按30天做比例拆分在套餐变更时会出现收入多记、漏记。同时硬拆分curr_month_rev、next_month_rev的写法扩展性差，遇到服务期跨2个以上自然月的极端场景（如月末开账+中途套餐变更）容易计算出错。

实现步骤 #

1. 标记相邻发票关联关系 #

通过窗口函数给每张发票关联同客户下一张发票的开具日期，用于判断订阅是否提前终止（升降级），无需在CASE语句中硬写多层判断：

WITH invoice_with_next AS (
    SELECT 
        customer_id,
        date AS invoice_date,
        MonthlyFee AS invoice_amt,
        -- 同客户下一张发票日期，无后续发票则默认按30天月付周期计算到期日
        LEAD(date, 1, DATE_ADD(date, INTERVAL 30 DAY)) 
            OVER (PARTITION BY customer_id ORDER BY date) AS next_invoice_date
    FROM chargeapp.invoices mi
    LEFT JOIN internalapp.accounts a ON a.id = mi.customer_id
)

2. 按有效服务周期按天摊分收入 #

核心规则：

• 单张发票的实际有效服务周期 = 下一张发票开具日期 - 当前发票开具日期，不存在后续发票则按30天计算
• 周期内收入按天均摊，每天的收入对应实际服务日期的所属月份，自动处理递延、升降级场景

, revenue_daily AS (
    SELECT
        customer_id,
        invoice_date,
        invoice_amt,
        -- 生成服务周期内的所有连续日期
        DATE_ADD(invoice_date, INTERVAL seq.day_offset DAY) AS service_date,
        -- 单日应确认收入：总金额/实际有效服务天数
        invoice_amt / DATE_DIFF(next_invoice_date, invoice_date, DAY) AS daily_rev
    FROM invoice_with_next
    -- 生成0-29的偏移量序列，覆盖最长30天的月付周期，无内置序列函数可替换为递归CTE
    CROSS JOIN UNNEST(GENERATE_ARRAY(0, 29)) AS seq(day_offset)
    WHERE DATE_ADD(invoice_date, INTERVAL seq.day_offset DAY) < next_invoice_date
)

3. 按月汇总收入 #

直接按服务日期所属月份聚合单日收入，自动包含两部分：当月开具发票当月确认的收入、历史发票递延到当月的收入，无需额外做跨月关联计算：

SELECT
    DATE_TRUNC(service_date, MONTH) AS revenue_month,
    ROUND(SUM(daily_rev), 2) AS total_monthly_revenue
FROM revenue_daily
GROUP BY revenue_month
ORDER BY revenue_month

逻辑适配说明 #

• 升降级场景下，旧发票的有效服务天数自动取新旧发票的间隔天数，收入摊分仅计算到新发票开具前一日，完全匹配Price * (newInvDate-OldInvDate)/周期天数的计算要求，无需额外写CASE分支判断
• 原逻辑中Month1 Rev = Curr_month1_rev + next_month0_rev的规则和按天聚合的计算结果完全一致，且支持服务期跨2个以上自然月的场景
• 如需兼容年付订阅，仅需将LEAD函数中的默认到期日从发票日期+30天修改为发票日期+365天，同时将序列生成范围扩大到0-364即可，核心摊分逻辑无需调整

尾差处理提示：如果业务要求按自然月实际天数确认收入，可将摊分分母替换为对应月份的实际天数，避免月末出现几分钱的计算尾差。

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