Teradata多表关联后SUM非唯一记录去重求和问题

当通过ID和LINE_NUM左关联Adj_Orig、Adj_Reversal、Adj_Retro三张表时，若某张表中同一ID+LINE_NUM存在多条记录，会导致另外两张表的对应行被重复复制，最终SUM聚合时会重复计算原值（比如你遇到的$3.41被统计成$13.64，本质是被重复计算了4次）。

核心解决思路：先对单表按ID+LINE_NUM预聚合，再关联结果集，避免关联产生重复行。

分步实现 #

1. 单表预聚合
   对每张需要聚合的表，先按ID和LINE_NUM分组，计算出每个分组的唯一聚合值，确保每个ID+LINE_NUM仅对应一行数据：

• 处理Adj_Orig：

SELECT ID, LINE_NUM, SUM(ORIG_AMT) AS ORIG_AMT
FROM Adj_Orig
GROUP BY ID, LINE_NUM

• 处理Adj_Reversal：

SELECT ID, LINE_NUM, SUM(REVERSAL_AMT) AS REVERSAL_AMT
FROM Adj_Reversal
GROUP BY ID, LINE_NUM

• 处理Adj_Retro（假设需聚合字段为RETRO_AMT）：

SELECT ID, LINE_NUM, SUM(RETRO_AMT) AS RETRO_AMT
FROM Adj_Retro
GROUP BY ID, LINE_NUM

2. 关联预聚合结果
   将上述三个预聚合后的子查询通过ID和LINE_NUM左关联，即可得到无重复行的结果，此时聚合值不会被重复统计：

SELECT 
    COALESCE(o.ID, r.ID, rt.ID) AS ID,
    COALESCE(o.LINE_NUM, r.LINE_NUM, rt.LINE_NUM) AS LINE_NUM,
    COALESCE(o.ORIG_AMT, 0) AS ORIG_AMT,
    COALESCE(r.REVERSAL_AMT, 0) AS REVERSAL_AMT,
    COALESCE(rt.RETRO_AMT, 0) AS RETRO_AMT
FROM (
    SELECT ID, LINE_NUM, SUM(ORIG_AMT) AS ORIG_AMT
    FROM Adj_Orig
    GROUP BY ID, LINE_NUM
) o
LEFT JOIN (
    SELECT ID, LINE_NUM, SUM(REVERSAL_AMT) AS REVERSAL_AMT
    FROM Adj_Reversal
    GROUP BY ID, LINE_NUM
) r ON o.ID = r.ID AND o.LINE_NUM = r.LINE_NUM
LEFT JOIN (
    SELECT ID, LINE_NUM, SUM(RETRO_AMT) AS RETRO_AMT
    FROM Adj_Retro
    GROUP BY ID, LINE_NUM
) rt ON o.ID = rt.ID AND o.LINE_NUM = rt.LINE_NUM

全局聚合场景 #

如果需要计算所有行的总聚合值，直接在外层套SUM即可：

SELECT 
    SUM(COALESCE(o.ORIG_AMT, 0)) AS TOTAL_ORIG_AMT,
    SUM(COALESCE(r.REVERSAL_AMT, 0)) AS TOTAL_REVERSAL_AMT,
    SUM(COALESCE(rt.RETRO_AMT, 0)) AS TOTAL_RETRO_AMT
FROM (
    SELECT ID, LINE_NUM, SUM(ORIG_AMT) AS ORIG_AMT
    FROM Adj_Orig
    GROUP BY ID, LINE_NUM
) o
LEFT JOIN (
    SELECT ID, LINE_NUM, SUM(REVERSAL_AMT) AS REVERSAL_AMT
    FROM Adj_Reversal
    GROUP BY ID, LINE_NUM
) r ON o.ID = r.ID AND o.LINE_NUM = r.LINE_NUM
LEFT JOIN (
    SELECT ID, LINE_NUM, SUM(RETRO_AMT) AS RETRO_AMT
    FROM Adj_Retro
    GROUP BY ID, LINE_NUM
) rt ON o.ID = rt.ID AND o.LINE_NUM = rt.LINE_NUM

补充说明 #

• 如果某张表中ID+LINE_NUM本身就是唯一键，子查询中可以省略SUM，直接SELECT ID, LINE_NUM, ORIG_AMT即可，但保留SUM更通用，能兼容单分组多记录的情况。
• 若需要包含三张表中所有的ID+LINE_NUM组合，可将LEFT JOIN替换为FULL OUTER JOIN，根据实际业务需求调整。

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