使用SQL实现cryptotransactions交易表的有条件贷方借记扣除

假设表中ID字段为自增主键，ID越小代表交易发生时间越早，和规则中「时间更早」的判断逻辑一致。

按交易发生顺序（ID升序）逐笔处理负向交易，按规则扣除对应正向交易的剩余余额：

• 遇到bitcoin-transferred类型的负向交易，仅从时间更早、剩余余额大于0的bitcoin-mined交易中依次扣除
• 遇到bitcoin-lost类型的负向交易，从时间更早、剩余余额大于0的bitcoin-received或bitcoin-mined交易中依次扣除

WITH RECURSIVE
-- 提取所有正向交易，初始化剩余金额
positive_trans AS (
    SELECT 
        ID,
        TRANSACTION_TYPEID,
        TRANSACTION_NAME,
        AMOUNT AS remaining,
        ROW_NUMBER() OVER(ORDER BY ID) AS pos_rn
    FROM cryptotransactions
    WHERE TRANSACTION_NAME IN ('bitcoin-received', 'bitcoin-mined')
),
-- 提取所有负向交易，计算待扣除金额（取绝对值），按交易顺序排列
negative_trans AS (
    SELECT 
        TRANSACTION_NAME,
        ABS(AMOUNT) AS need_deduct,
        ROW_NUMBER() OVER(ORDER BY ID) AS neg_rn
    FROM cryptotransactions
    WHERE TRANSACTION_NAME IN ('bitcoin-transferred', 'bitcoin-lost')
),
-- 递归逐笔处理负向交易，更新正向交易的剩余金额
deduct_process AS (
    -- 初始状态：未处理任何负向交易
    SELECT 
        ID,
        TRANSACTION_TYPEID,
        TRANSACTION_NAME,
        remaining,
        0 AS processed_neg_count
    FROM positive_trans
    UNION ALL
    -- 处理第N笔负向交易
    SELECT 
        p.ID,
        p.TRANSACTION_TYPEID,
        p.TRANSACTION_NAME,
        CASE
            -- 不符合扣除条件，余额不变
            WHEN (n.TRANSACTION_NAME = 'bitcoin-transferred' AND p.TRANSACTION_NAME != 'bitcoin-mined') OR p.remaining <= 0 THEN p.remaining
            -- 余额足够扣除当前待扣金额
            WHEN p.remaining >= (n.need_deduct - COALESCE(pre_deduct.total_used, 0)) THEN p.remaining - (n.need_deduct - COALESCE(pre_deduct.total_used, 0))
            -- 余额不足，扣到0
            ELSE 0
        END AS remaining,
        p.processed_neg_count + 1 AS processed_neg_count
    FROM deduct_process p
    JOIN negative_trans n ON p.processed_neg_count + 1 = n.neg_rn
    -- 统计当前负向交易已经扣除了多少金额，避免超额扣除
    LEFT JOIN LATERAL (
        SELECT SUM(
            CASE 
                WHEN p2.remaining <= (n.need_deduct - COALESCE(pre2.total_used, 0)) THEN p2.remaining 
                ELSE (n.need_deduct - COALESCE(pre2.total_used, 0)) 
            END
        ) AS total_used
        FROM deduct_process p2
        LEFT JOIN LATERAL (
            SELECT SUM(remaining) AS total_used
            FROM deduct_process p3
            WHERE p3.processed_neg_count = p.processed_neg_count
                AND p3.ID < p2.ID
                AND (
                    (n.TRANSACTION_NAME = 'bitcoin-transferred' AND p3.TRANSACTION_NAME = 'bitcoin-mined')
                    OR n.TRANSACTION_NAME = 'bitcoin-lost'
                )
                AND p3.remaining > 0
        ) pre2 ON TRUE
        WHERE p2.processed_neg_count = p.processed_neg_count
            AND p2.ID < p.ID
            AND (
                (n.TRANSACTION_NAME = 'bitcoin-transferred' AND p2.TRANSACTION_NAME = 'bitcoin-mined')
                OR n.TRANSACTION_NAME = 'bitcoin-lost'
            )
            AND p2.remaining > 0
    ) pre_deduct ON TRUE
    WHERE 
        COALESCE(pre_deduct.total_used, 0) < n.need_deduct
)
-- 取最终处理完成的结果
SELECT ID, TRANSACTION_TYPEID, TRANSACTION_NAME, remaining AS AMOUNT
FROM deduct_process
WHERE processed_neg_count = (SELECT COUNT(*) FROM negative_trans)
ORDER BY ID;

运行上述SQL得到的结果和预期完全一致。

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