如何在Oracle SQL中按balance移除等额轧差数值记录

需求说明 #

按balance字段分组，移除每组中数值绝对值相等、符号相反的等额配对记录（非汇总抵消，需逐行配对），仅保留无法找到配对的行。

原始数据 #

期望结果 #

解决方案SQL #

WITH numbered_rows AS (
    SELECT 
        t.*,
        -- 对同balance、同绝对值、同符号的行编号
        ROW_NUMBER() OVER (
            PARTITION BY balance, ABS(value), SIGN(value) 
            ORDER BY account, subaccount
        ) AS row_num,
        -- 统计同balance、同绝对值、同符号的行数
        COUNT(*) OVER (
            PARTITION BY balance, ABS(value), SIGN(value)
        ) AS same_sign_cnt
    FROM your_table t
),
pair_stats AS (
    SELECT 
        balance,
        ABS(value) AS abs_value,
        -- 统计该balance+绝对值下的正值行数
        MAX(CASE WHEN SIGN(value) = 1 THEN same_sign_cnt ELSE 0 END) AS positive_cnt,
        -- 统计该balance+绝对值下的负值行数
        MAX(CASE WHEN SIGN(value) = -1 THEN same_sign_cnt ELSE 0 END) AS negative_cnt
    FROM numbered_rows
    GROUP BY balance, ABS(value)
)
SELECT 
    nr.balance,
    nr.account,
    nr.subaccount,
    nr.value
FROM numbered_rows nr
JOIN pair_stats ps 
    ON nr.balance = ps.balance 
    AND ABS(nr.value) = ps.abs_value
WHERE 
    -- 正值行：保留编号超过负值数量的部分（无法配对的）
    (SIGN(nr.value) = 1 AND nr.row_num > ps.negative_cnt)
    -- 负值行：保留编号超过正值数量的部分（无法配对的）
    OR (SIGN(nr.value) = -1 AND nr.row_num > ps.positive_cnt);

代码说明 #

1. numbered_rows CTE：为每个balance分组内，相同绝对值、相同符号的行添加序号，并统计该分组内的总行数。排序字段account, subaccount用于确保配对的一致性（可根据实际业务调整）。
2. pair_stats CTE：按balance和绝对值分组，统计每组内正值、负值的总行数，明确可配对的数量上限。
3. 最终查询：通过关联两个CTE，筛选出无法配对的行——当正值数量多于负值时，保留超出部分的正值行；反之保留超出部分的负值行；数量相等时则全部移除。

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