PostgreSQL实现按分期数分摊销售金额的SQL查询方案

原始数据 #

期望结果 #

单条SQL解决方案 #

WITH numbered_sales AS (
    SELECT 
        sale_id,
        sale_date,
        installments,
        total_value,
        ROW_NUMBER() OVER (PARTITION BY sale_id ORDER BY sale_date) AS row_num
    FROM sales
)
SELECT 
    ns.sale_id,
    ns.sale_date,
    ns.installments,
    COALESCE(ROUND(payment.amount, 1), 0.0) AS total_value
FROM numbered_sales ns
LEFT JOIN LATERAL (
    SELECT 
        (source.total_value / source.installments) AS amount
    FROM numbered_sales source
    WHERE source.sale_id = ns.sale_id
        AND source.total_value > 0
        AND ns.row_num BETWEEN source.row_num AND source.row_num + source.installments - 1
) payment ON true
ORDER BY ns.sale_id, ns.sale_date;

思路说明 #

1. 行号标记：通过CTE numbered_sales 给每个sale_id分组内的记录按sale_date排序分配行号，便于精准定位分摊的行范围。
2. 关联分摊源：使用LATERAL JOIN将每一行关联到对应的分摊源记录——即同sale_id下total_value>0的行，且当前行的行号落在源行号到源行号+分期数-1的区间内（包含源行自身）。
3. 计算分摊金额：将源行的总金额除以分期数得到单期金额，用COALESCE处理无分摊的行，保留为0.0，ROUND确保金额格式与示例一致。
4. 性能优化：如果数据量较大，可在(sale_id, row_num)和(sale_id, total_value, row_num, installments)字段上创建复合索引，提升关联查询效率。

内容的提问来源于stack exchange，提问作者Leandro Guimarães