Postgres支付表如何剔除冲销交易对应的正负金额成对记录

前提假设 #

我们统一使用如下表结构做示例，你可根据实际业务字段调整匹配逻辑：
表名：payment_records
核心字段：

• id：交易唯一主键
• amount：交易金额，正数为支付记录，负数为冲销记录
• user_id：交易所属用户ID，用于限定同用户交易匹配，避免跨用户误匹配
• trade_no：交易单号（可选，可增加到分组维度提升匹配准确性）

方案1：单对正负等额交易剔除（稳定可靠，适配90%以上场景） #

针对你提到的5和-5这类一对一正负等额的冲销场景，直接通过分组统计正负数量即可实现：

WITH amount_abs_group AS (
    SELECT 
        *,
        ABS(amount) AS amount_abs,
        -- 标记每笔是正交易还是负冲销
        CASE WHEN amount > 0 THEN 1 ELSE -1 END AS amount_sign
    FROM payment_records
),
count_stat AS (
    SELECT 
        user_id,
        amount_abs,
        amount_sign,
        id,
        -- 同用户、同绝对值金额下，给同符号的交易编序号
        ROW_NUMBER() OVER (PARTITION BY user_id, amount_abs, amount_sign ORDER BY id) AS rn
    FROM amount_abs_group
),
pair_match AS (
    SELECT 
        c1.id AS pos_id,
        c2.id AS neg_id
    FROM count_stat c1
    JOIN count_stat c2 
        ON c1.user_id = c2.user_id
        AND c1.amount_abs = c2.amount_abs
        AND c1.amount_sign = 1
        AND c2.amount_sign = -1
        AND c1.rn = c2.rn
)
-- 最终返回所有没有匹配成对的交易
SELECT * FROM payment_records
WHERE id NOT IN (SELECT pos_id FROM pair_match UNION ALL SELECT neg_id FROM pair_match);

方案逻辑说明 #

1. 先对每笔交易计算绝对值和金额正负标记
2. 同用户、同绝对值金额下，给正交易和负交易分别按顺序编号
3. 同序号的正、负交易即可匹配为一对需要剔除的冲销组合
4. 最终排除所有匹配成功的id，剩下的就是有效支付记录

方案2：多笔交易总和为0的组剔除（扩展场景） #

如果存在多笔正交易加总后和多笔负交易加总为0的场景（比如2笔+5、1笔-10），可使用如下方案：

WITH recursive transaction_groups AS (
    SELECT 
        ARRAY[id] AS ids,
        amount AS total_amount,
        user_id
    FROM payment_records
    UNION ALL
    SELECT 
        tg.ids || pr.id,
        tg.total_amount + pr.amount,
        tg.user_id
    FROM transaction_groups tg
    JOIN payment_records pr 
        ON tg.user_id = pr.user_id
        AND pr.id > ALL(tg.ids) -- 避免重复组合
        AND tg.total_amount + pr.amount BETWEEN -1e-6 AND 1e-6 -- 浮点精度兼容，整数金额可直接写=0
)
SELECT * FROM payment_records
WHERE id NOT IN (
    SELECT unnest(ids) FROM transaction_groups WHERE total_amount = 0
);

注意事项 #

• 如果你的表中存在original_payment_id字段直接关联冲销记录和原错误交易，优先使用该字段做关联匹配，准确率100%
• 分组维度可根据业务调整，比如增加order_id、channel_id等字段到PARTITION BY子句中，避免不同业务场景的交易误匹配
• 浮点类型金额需要增加精度容错，避免因为精度问题导致匹配失败

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