如何在PostgreSQL中将年度金额均分到各月份？

需求描述 #

现有如下结构的订阅数据，包含用户ID、订阅起始日期（dd-mm-yyyy格式）和年度订阅金额：

userid startDate(dd-mm-yyyy)     amount
1      01-10-2020                120
1      01-10-2021                240
2      01-08-2020                60

需要将每笔年度金额均分到后续12个月份，每个月份生成一条记录，输出格式如下：

userid startDate(dd-mm-yyyy)     amount
1      01-10-2020                10
1      01-11-2020                10
1      01-12-2020                10
1      01-01-2021                10
1      01-02-2021                10
1      01-03-2021                10
1      01-04-2021                10
1      01-05-2021                10
1      01-06-2021                10
1      01-07-2021                10
1      01-08-2021                10
1      01-09-2021                10

1      01-10-2021                20
1      01-11-2021                20
1      01-12-2021                20
1      01-01-2022                20
1      01-02-2022                20
1      01-03-2022                20
1      01-04-2022                20
1      01-05-2022                20
1      01-06-2022                20
1      01-07-2022                20
1      01-08-2022                20
1      01-09-2022                20

2      01-08-2020                5
2      01-09-2020                5
2      01-10-2020                5
2      01-11-2020                5
2      01-12-2020                5
2      01-01-2021                5
2      01-02-2021                5
2      01-03-2021                5
2      01-04-2021                5
2      01-05-2021                5
2      01-06-2021                5
2      01-07-2021                5

（注：修正了原示例中第二组数据的日期错误，应为从2021-10到2022-09）

实现方法 #

核心是利用PostgreSQL的generate_series函数生成每个订阅对应的12个月份日期，再结合横向关联（LATERAL JOIN）实现每行数据扩展为12行。

假设原始表名为subscriptions，字段为userid、startdate（字符串类型）、amount，执行以下SQL即可得到目标结果：

SELECT
    s.userid,
    TO_CHAR(month_date, 'dd-mm-yyyy') AS "startDate(dd-mm-yyyy)",
    s.amount / 12.0 AS amount
FROM
    subscriptions s
CROSS JOIN LATERAL
    generate_series(
        TO_DATE(s.startdate, 'dd-mm-yyyy'),
        TO_DATE(s.startdate, 'dd-mm-yyyy') + INTERVAL '11 months',
        INTERVAL '1 month'
    ) AS month_date
ORDER BY
    s.userid,
    month_date;

代码说明 #

1. TO_DATE函数：将字符串格式的起始日期转换为PostgreSQL的DATE类型，方便日期运算。如果原始表中startdate已经是DATE类型，可直接替换为s.startdate。
2. generate_series函数：生成从起始日期开始，到起始日期加11个月结束的日期序列，步长为1个月，正好生成12个月份的日期。
3. CROSS JOIN LATERAL：让每一条订阅记录都与生成的12个日期记录做关联，实现一行转多行的效果。
4. TO_CHAR函数：将生成的日期转换为dd-mm-yyyy的字符串格式，匹配需求的输出样式。
5. 金额计算：直接用年度金额除以12得到每月均分金额，若需要保留小数位，可使用ROUND(s.amount / 12.0, 2)控制精度。
6. 排序：按用户ID和日期排序，确保结果顺序与需求一致。

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