如何编写SQL消除聚合后仍存在的等值反向汇总余额

问题根因 #

现有SQL的NOT EXISTS逻辑仅能过滤单笔交易级别的等值反向记录，未覆盖「多笔交易聚合后，不同维度组合的总余额刚好等值反向」的场景，因此聚合后仍会残留待抵消的余额。

实现思路 #

• 先做全维度的基础聚合，得到各维度组合的原始总余额，生成中间聚合表
• 基于中间聚合表做反向匹配，保持原有核心匹配规则：公共维度相等、BUSINESS_UNIT与AFFILIATE互换、总余额互为相反数
• 新增配对去重逻辑，避免同一对反向余额被重复判定，确保仅抵消一次

具体实现SQL #

WITH agg_balance AS (
    -- 第一步：先做基础聚合，得到各维度的总余额
    SELECT 
        SETID, CONSOLIDATION_SET, TREE_NAME, TREE_NODE, CONSOL_ENTITY, 
        SET_IDENTIFIER, LOG_ENTRY_TYPE, AS_OF_DATE, BUSINESS_UNIT, 
        LEDGER, ACCOUNT, DEPTID, PRODUCT, AFFILIATE, CURRENCY_CD,
        SUM(POSTED_TOTAL_AMT) AS TOTAL_BALANCE
    FROM PS_CONSOL_LOG
    GROUP BY 
        SETID, CONSOLIDATION_SET, TREE_NAME, TREE_NODE, CONSOL_ENTITY, 
        SET_IDENTIFIER, LOG_ENTRY_TYPE, AS_OF_DATE, BUSINESS_UNIT, 
        LEDGER, ACCOUNT, DEPTID, PRODUCT, AFFILIATE, CURRENCY_CD
)
-- 第二步：过滤掉聚合后仍存在的等值反向余额
SELECT a.*
FROM agg_balance a
WHERE NOT EXISTS (
    SELECT 1 
    FROM agg_balance b
    WHERE 
        -- 公共维度完全匹配
        a.SETID = b.SETID
        AND a.CONSOLIDATION_SET = b.CONSOLIDATION_SET
        AND a.SET_IDENTIFIER = b.SET_IDENTIFIER
        AND a.CONSOL_ENTITY = b.CONSOL_ENTITY
        AND a.TREE_NAME = b.TREE_NAME
        AND a.TREE_NODE = b.TREE_NODE
        AND a.LOG_ENTRY_TYPE = b.LOG_ENTRY_TYPE
        AND a.AS_OF_DATE = b.AS_OF_DATE
        AND a.LEDGER = b.LEDGER
        AND a.ACCOUNT = b.ACCOUNT
        AND a.DEPTID = b.DEPTID
        AND a.PRODUCT = b.PRODUCT
        AND a.CURRENCY_CD = b.CURRENCY_CD
        -- 往来单位互换
        AND a.BUSINESS_UNIT = b.AFFILIATE
        AND a.AFFILIATE = b.BUSINESS_UNIT
        -- 余额互为相反数
        AND a.TOTAL_BALANCE = -b.TOTAL_BALANCE
        -- 去重逻辑：避免同一对互相匹配，仅保留单边判定，不会重复抵消
        AND a.BUSINESS_UNIT < b.BUSINESS_UNIT
)
-- 过滤掉余额已经为0的记录
AND a.TOTAL_BALANCE <> 0;

补充说明 #

• 也可以用CONCAT(LEAST(BUSINESS_UNIT,AFFILIATE),'-',GREATEST(BUSINESS_UNIT,AFFILIATE))生成唯一配对标识做去重，逻辑和上述代码中的单边判定一致
• 如果需要保留抵消记录的痕迹，可以把NOT EXISTS换成LEFT JOIN加标记字段，标识该条记录已被抵消
• 若存在多对一的抵消场景（比如3条记录总余额和1条记录余额反向相等），可以引入窗口函数按配对组排序后做金额轧差处理，适配更复杂的抵消规则

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