基于历史累计值拆分付款金额至对应品类的SQL实现求助

问题说明 #

我有如下交易数据，需要新增NUT_AMT和BOLT_AMT两列，用于计算每笔交易对应到BOLTS或NUTS品类的金额。SALE类型的行处理逻辑简单，但PAYMENT类型的行需基于此前对应品类的累计未结清销售额拆分金额。我已用窗口函数实现新列的累计求和，但付款金额的拆分逻辑无法完成，附上输入输出示例表，寻求技术帮助。

输入数据 #

期望输出 #

解决方案（SQL实现） #

要实现付款金额按未结清品类销售额拆分，需按时间倒序处理交易，跟踪每个品类的累计应收款，再按规则分配付款金额。以下是基于PostgreSQL的实现代码：

WITH sorted_transactions AS (
    SELECT 
        *,
        ROW_NUMBER() OVER (PARTITION BY ACCOUNTID ORDER BY ENTRYDATE DESC) AS rn
    FROM transactions
),
initial_amt AS (
    SELECT
        *,
        CASE WHEN TRANSACTION = 'SALE' AND TYPE = 'NUTS' THEN AMOUNT ELSE 0 END AS NUT_AMT,
        CASE WHEN TRANSACTION = 'SALE' AND TYPE = 'BOLTS' THEN AMOUNT ELSE 0 END AS BOLT_AMT
    FROM sorted_transactions
),
running_balance AS (
    SELECT
        *,
        SUM(NUT_AMT) OVER (PARTITION BY ACCOUNTID ORDER BY rn) AS running_nut_balance,
        SUM(BOLT_AMT) OVER (PARTITION BY ACCOUNTID ORDER BY rn) AS running_bolt_balance
    FROM initial_amt
),
payment_split AS (
    SELECT
        *,
        CASE
            WHEN TRANSACTION = 'PAYMENT' THEN
                LEAST(-AMOUNT, running_nut_balance) * -1
            ELSE NUT_AMT
        END AS final_nut_amt,
        CASE
            WHEN TRANSACTION = 'PAYMENT' THEN
                (-AMOUNT - LEAST(-AMOUNT, running_nut_balance)) * -1
            ELSE BOLT_AMT
        END AS final_bolt_amt
    FROM running_balance
)
SELECT
    ACCOUNTID,
    ENTRYDATE,
    TRANSACTION,
    TYPE,
    AMOUNT,
    final_nut_amt AS NUT_AMT,
    final_bolt_amt AS BOLT_AMT
FROM payment_split
ORDER BY ENTRYDATE DESC;

逻辑说明 #

1. sorted_transactions：按账户分组，交易时间倒序排序，生成行号确保处理顺序从新到旧。
2. initial_amt：初始化SALE类型的品类金额，PAYMENT类型暂设为0。
3. running_balance：计算截至当前行的累计应收余额（倒序处理时，余额为未被付款覆盖的销售额）。
4. payment_split：付款行优先结清NUTS品类的应收余额，剩余部分分配给BOLTS品类，得到最终拆分金额。

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