移除表中反向交易对的SQL实现问题咨询

我明白你现在的痛点：要从包含Account、Date、Amount、Row的表中剔除被反向冲销的交易，但遇到多笔重复交易时，简单删除反向对的方法完全不适用。先把你的场景和需求再明确下，然后给出可行的SQL解决方案。

问题场景与需求 #

反向交易的判定规则很清晰：同一Account下，Amount互为相反数。但不同的重复场景需要不同的处理逻辑，比如：

• Case1：单正向+单反向 → 反向冲销正向，两者都移除
• Case2：两正向+单反向 → 只冲销最早的那笔正向，保留另一笔
• Case3：两正向+两反向 → 全部冲销，保留后续的正向
• Case4：两正向+单反向（跨日期） → 冲销中间的正向，保留首尾的正向

示例原表 #

期望结果表 #

你之前尝试统计重复数和反向重复数的思路方向是对的，但没考虑到交易的顺序（冲销是按发生顺序来的），所以会出现匹配错误的情况。下面给出的方案会结合交易顺序来一一匹配冲销对。

解决方案思路 #

核心逻辑是按Account分组，对正向（Amount>0）和反向（Amount<0）交易分别按发生顺序排序，然后按序号一一匹配冲销，具体步骤：

1. 为每个Account下的所有交易，按Date+Row排序（保证顺序和实际业务发生顺序一致）
2. 给同一Account下的正向交易、反向交易分别生成组内序号
3. 比较正向交易的序号和对应反向交易的总数，反向交易的序号和对应正向交易的总数，标记哪些交易被冲销
4. 最后筛选出未被冲销的交易即可

实现代码 #

WITH ranked_transactions AS (
    -- 第一步：为每个Account下的正向/反向交易按发生顺序生成序号
    SELECT 
        Account,
        Date,
        Amount,
        Row,
        -- 正向交易的组内序号（按Date、Row升序，确保先发生的先被冲销）
        CASE WHEN Amount > 0 THEN 
            ROW_NUMBER() OVER (PARTITION BY Account, Amount ORDER BY Date, Row) 
        END AS pos_rank,
        -- 反向交易的组内序号（同样按发生顺序排序）
        CASE WHEN Amount < 0 THEN 
            ROW_NUMBER() OVER (PARTITION BY Account, Amount ORDER BY Date, Row) 
        END AS neg_rank
    FROM your_table_name -- 这里替换成你的实际表名
),
matched_pairs AS (
    -- 第二步：标记被冲销的交易
    SELECT 
        rt.Account,
        rt.Date,
        rt.Amount,
        rt.Row,
        -- 判断当前交易是否被冲销：
        -- 正向交易：如果它的序号 <= 对应反向交易的总数，说明被冲销
        -- 反向交易：如果它的序号 <= 对应正向交易的总数，说明被冲销
        CASE 
            WHEN rt.Amount > 0 THEN 
                CASE WHEN rt.pos_rank <= (SELECT COUNT(*) FROM ranked_transactions rt2 WHERE rt2.Account = rt.Account AND rt2.Amount = -rt.Amount) THEN 1 ELSE 0 END
            WHEN rt.Amount < 0 THEN 
                CASE WHEN rt.neg_rank <= (SELECT COUNT(*) FROM ranked_transactions rt2 WHERE rt2.Account = rt.Account AND rt2.Amount = -rt.Amount) THEN 1 ELSE 0 END
            ELSE 0 -- 如果有Amount=0的交易，这里默认保留，你可以根据业务调整
        END AS is_reversed
    FROM ranked_transactions rt
)
-- 第三步：筛选出未被冲销的交易，按原顺序输出
SELECT 
    Account,
    Date,
    Amount,
    Row
FROM matched_pairs
WHERE is_reversed = 0
ORDER BY Account, Date, Row;

代码说明 #

• ranked_transactions：这一步是关键，它给每个Account下的正向、反向交易分别按发生顺序编号，确保冲销是“先来先销”的逻辑，符合实际业务场景。
• matched_pairs：通过子查询统计对应反向/正向交易的总数，然后比较序号和总数的大小，判断是否被冲销。比如Account15有2笔正向51，1笔反向-51，那么第一笔正向的序号1<=1，会被标记为冲销，第二笔正向序号2>1，保留。
• 最终结果只保留is_reversed=0的行，完全符合你的期望结果。

这个方案可以覆盖你提到的所有Case，而且逻辑清晰，容易调整（比如如果需要“后来先销”，只需要把排序改成ORDER BY Date DESC, Row DESC即可）。

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