DB2中筛选未形成全额互抵配对的独立借记交易查询方法

核心逻辑拆解 #

针对集合级比对的卡点，将之前逐行匹配的逻辑转换为可量化的双维度集合校验，从根本上避免因漏校验集合完整性导致的误过滤：

• 基础集合边界：所有待筛选的目标为交易主表中type='D'的借记交易；每笔借记交易的固定配对贷记交易为同表内t_id = 借记交易t_id + 1且type='C'的记录
• 完全互抵的判定规则（同时满足才算可过滤）：
  • 若借记交易addinfo='N'：无合作方明细，不存在互抵可能，直接判定为需保留
  • 若借记交易addinfo='Y'：需同时满足两个条件才算可互抵
    • 借贷两笔交易在PTRANSACT表的合作方集合完全相等（无单边存在的合作方）
    • 同一合作方在两笔交易中的charge值加总为0（无金额差）
• 常见误判原因说明：逐行匹配逻辑仅能校验已命中的明细行金额是否互抵，无法校验单边存在的缺失合作方，例如t_id=100003的借记交易有AAA、BBB两个合作方，配对的t_id=100004的贷记交易仅存在AAA的明细，逐行匹配会因AAA行金额互抵误判为整体可互抵，进而错误过滤该笔交易。

可直接落地的DB2 SQL实现 #

所有语法均兼容DB2 9.7及以上版本，无自定义函数、无复杂关联逻辑：

WITH debit_pair AS (
    -- 第一步：关联所有借记交易与其固定配对的贷记交易
    SELECT 
        d.t_id AS debit_tid,
        d.type,
        d.price,
        d.addinfo,
        c.t_id AS credit_tid
    FROM TRANSACT d
    LEFT JOIN TRANSACT c 
        ON c.t_id = d.t_id + 1 
        AND c.type = 'C'
    WHERE d.type = 'D'
),
pair_partner_check AS (
    -- 第二步：按合作方维度做集合级校验，计算两个核心校验值
    SELECT 
        debit_tid,
        -- 统计单边存在的合作方数量：非0即代表两边合作方集合不匹配
        SUM(CASE WHEN partner_appear_cnt = 1 THEN 1 ELSE 0 END) AS unmatched_partner_cnt,
        -- 统计所有匹配合作方的charge总差值：非0即代表金额未完全互抵
        SUM(ABS(partner_charge_total)) AS total_charge_diff
    FROM (
        SELECT 
            dp.debit_tid,
            p.partner,
            -- 统计当前合作方在借贷两笔交易中的出现次数
            COUNT(DISTINCT p.t_id) AS partner_appear_cnt,
            -- 统计当前合作方在借贷两笔交易中的charge加总
            SUM(p.charge) AS partner_charge_total
        FROM debit_pair dp
        LEFT JOIN PTRANSACT p 
            ON p.t_id IN (dp.debit_tid, dp.credit_tid)
        GROUP BY dp.debit_tid, p.partner
    ) partner_group
    GROUP BY debit_tid
)
-- 第三步：筛选所有不满足完全互抵条件的借记交易
SELECT 
    dp.debit_tid AS t_id,
    dp.type,
    dp.price,
    dp.addinfo
FROM debit_pair dp
LEFT JOIN pair_partner_check pc
    ON dp.debit_tid = pc.debit_tid
WHERE 
    -- 无合作方的交易直接保留
    dp.addinfo = 'N'
    -- 有合作方的交易，只要存在任意一项不满足互抵条件就保留
    OR pc.unmatched_partner_cnt > 0
    OR pc.total_charge_diff > 0
    OR pc.debit_tid IS NULL -- 无任何合作方明细的交易直接保留

样例数据匹配验证结果 #

对照题目给出的样例数据集，SQL输出结果与要求完全一致：

• t_id=100001：配对贷记100002的合作方集合、金额均完全匹配，会被正常过滤
• t_id=100003：配对贷记100004仅存在AAA的明细，BBB为单边存在的合作方，unmatched_partner_cnt=1，不会被误过滤
• t_id=100005：addinfo='N'，直接保留
• t_id=100006：配对贷记100007的CCC合作方charge加总为300，total_charge_diff=300，直接保留

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