sql - 如何使用SQL在可变时间段(每月，每年等)分配收入？

其背景是，我想计算基于订阅的业务的MRR（每月经常性收入），其中收入是在销售时记录的，以及合同的时间段，由有效结束日期表示。
然后，任务是将收入分成合同期限，并聚合多个合同，以便显示每月收入。如果能同时显示客户流失，也就是说，当合同到期而未续签时，所发生的美元损失，将是一个利好。
数据的格式如下（忽略计费周期）：

╔══════════════════════════════════╦══════════════════════════╦══════════════════════════╦════════════════╦═══════╗
║            account_id            ║        start_date        ║    effective_end_date    ║ billing_period ║ price ║
╠══════════════════════════════════╬══════════════════════════╬══════════════════════════╬════════════════╬═══════╣
║ 2c92a0fd5286d62801528d6578230fa7 ║ 2015-10-01T00:00:00.000Z ║ 2017-10-15T00:00:00.000Z ║ Annual         ║ 1440  ║
║ 2c92a0fd5286d62801528d6578230fa8 ║ 2015-10-01T00:00:00.000Z ║ 2016-10-15T00:00:00.000Z ║ Annual         ║ 3500  ║
║ 2c92a0fd5286d62801528d6578230fa9 ║ 2015-10-01T00:00:00.000Z ║ 2015-12-31T00:00:00.000Z ║ Annual         ║ 700  ║
╚══════════════════════════════════╩══════════════════════════╩══════════════════════════╩════════════════╩═══════╝

预期结果如下：
2c92a0fd5286d62801528d6578230fa7在2015年10月至2017年10月的24个月内利差1440美元。
2c92a0fd5286d62801528d6578230fa8在2015年10月至2016年10月的12个月内利差3500美元。
2c92a0fd5286d62801528d6578230fa9从2015年10月到2015年12月，3个月内利差700美元。
我意识到我需要使用日期表来交叉连接，因为否则所有日期都不会被表示出来。我可以用CTE做这个。但我更困惑的是如何分配收入。任何帮助都将不胜感激！
到目前为止，我已经做到了：

SELECT account_id, date_trunc('month',effective_start_date) as start_date, effective_end_date, mrr as price,
EXTRACT(YEAR FROM age(date_trunc('month',effective_end_date)::date,date_trunc('month', effective_start_date)::date))*12 + EXTRACT(month from age(date_trunc('month',effective_end_date)::date,date_trunc('month', effective_start_date)::date)) as contract_length_months,
mrr/NULLIF(EXTRACT(YEAR FROM age(date_trunc('month',effective_end_date)::date,date_trunc('month', effective_start_date)::date))*12 + EXTRACT(month from age(date_trunc('month',effective_end_date)::date,date_trunc('month', effective_start_date)::date)),0) as divided_price
FROM "public"."zuora_rate_plan_charge" where mrr <> 0 and mrr is not null
order by date_trunc('month',effective_start_date)

结果：

╔══════════════════════════════════╦══════════════════════════╦══════════════════════════╦═══════╦════════════════════════╦═══════════════╗
║            account_id            ║        start_date        ║    effective_end_date    ║ price ║ contract_length_months ║ divided_price ║
╠══════════════════════════════════╬══════════════════════════╬══════════════════════════╬═══════╬════════════════════════╬═══════════════╣
║ 2c92a0fd5286d62801528d6578230fa7 ║ 2015-10-01T00:00:00.000Z ║ 2017-10-15T00:00:00.000Z ║  1440 ║                     24 ║            60 ║
╚══════════════════════════════════╩══════════════════════════╩══════════════════════════╩═══════╩════════════════════════╩═══════════════╝

预期结果：

╔════════╦════════════════╗
║ Month  ║      MRR       ║
╠════════╬════════════════╣
║ Oct 15 ║ 585            ║
║ Nov 15 ║ 585            ║
║ Dec 15 ║ 585            ║
║ Jan 16 ║ 351.6666666667 ║
╚════════╩════════════════╝

最佳答案

您可以使用generate_series()来获取月份，然后使用一些算法来获取数据。要获得一个客户的所有月份：

select t.*, price / count(*) over (partition by account_id) as monthy
from (select t.*,
             generate_series(start_date, end_date, interval '1 month') as yyyymm
      from t
     ) t;

然后，如果您想要每月的金额，您可以将其相加：

select yyyymm, sum(monthly)
from (select t.*,
             price / count(*) over (partition by account_id) as monthly
      from (select t.*,
                   generate_series(start_date, end_date, interval '1 month') as yyyymm
            from t
           ) t
      ) t
group by yyyymm
order by yyyymm;