如何用SQL计算复杂MRR？附示例数据与预期结果

原始数据 #

计算规则 #

• Annual套餐：订阅有效期内（CreatedAt至ActualEndDate），每月按年价的月均值（Price / 12）计入MRR
• Mensual套餐：订阅有效期内，每月按订阅价格（Price）全额计入MRR

预期结果 #

解决方案（基于CTE的SQL实现） #

以下以PostgreSQL为例，核心逻辑适配多数支持CTE和日期函数的关系型数据库（如MySQL 8.0+、SQL Server等，仅日期生成逻辑需微调）：

WITH date_range AS (
    -- 生成覆盖所有订阅周期的月份序列
    SELECT generate_series(
        date_trunc('month', MIN(CreatedAt))::date,
        date_trunc('month', MAX(ActualEndDate))::date,
        INTERVAL '1 month'
    ) AS month_start
    FROM subscriptions
),
subscription_monthly_contributions AS (
    -- 拆分每个订阅到对应生效月份，计算单订阅当月MRR贡献
    SELECT
        TO_CHAR(dr.month_start, 'YYYY-MM') AS month,
        CASE
            WHEN s.PlanType = 'Annual' THEN s.Price / 12
            ELSE s.Price
        END AS contribution
    FROM subscriptions s
    JOIN date_range dr
        -- 判断当前月份是否在订阅有效期内
        ON dr.month_start <= s.ActualEndDate
        AND (dr.month_start + INTERVAL '1 month' - INTERVAL '1 day') >= s.CreatedAt
)
-- 按月汇总MRR
SELECT
    month AS Month,
    ROUND(SUM(contribution)) AS MRR
FROM subscription_monthly_contributions
GROUP BY month
ORDER BY month;

逻辑说明 #

1. date_range CTE：自动生成从最早订阅开始月到最晚订阅结束月的所有月份起始日期，确保覆盖所有需要统计的时间范围。
2. subscription_monthly_contributions CTE：将每个订阅与生效月份关联，通过日期判断筛选出订阅有效的月份，再根据套餐类型计算当月贡献额（Annual套餐取年价月均，Mensual取原价）。
3. 最终汇总：按月份分组求和并取整，得到与预期匹配的整数MRR结果。

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