PostgreSQL实现年度订阅订单按月拆分均摊金额SQL查询

场景说明 #

订单表中存储的均为年付订阅订单，需要将每笔年付订单拆分为12条月度收入记录：

• 拆分起始日期为订单原日期，后续每条记录日期逐次加1个月，最后一条记录为原日期加11个月，覆盖完整12个月服务周期
• 单条月度记录的收入为订单总金额除以12
• 源数据示例：

date, domain, total
2022-01-07, test.com, 120

• 预期输出共12条记录，日期从2022-01-07到2022-12-07，每条对应月分摊收入10

可直接运行的SQL #

利用PostgreSQL内置的generate_series序列生成函数即可快速实现行拆分，代码如下（请将代码中的你的订单表名替换为实际业务中的订单表名称）：

SELECT
    (ord.date + make_interval(months => seq.offset_num))::date AS date,
    ord.domain,
    ord.total / 12.0 AS total
FROM 你的订单表名 ord
CROSS JOIN generate_series(0, 11) AS seq(offset_num);

逻辑解释 #

• generate_series(0, 11)会生成0到11共12个连续整数，作为每个订单的月份偏移值，通过交叉连接让每个订单关联12条偏移记录，天然完成1行拆12行的需求
• 偏移值为0时加0个月，就是订单原日期；偏移值为11时加11个月，就是周期最后一个月的对应日期，和拆分规则完全匹配
• 金额计算直接用总金额除以12即可，如果业务要求保留固定小数位，可以套一层ROUND(ord.total/12.0, 2)保留两位小数
• 以上SQL针对示例数据运行，会输出和预期完全一致的12条分摊记录

注意：如果后续表中会混入非年付订单，需要在WHERE子句中增加年付订单的过滤条件，避免错误拆分。

内容的提问来源于stack exchange，提问作者Tomaž Bratanič