添加第二个JOIN后原有SUM聚合统计结果偏离预期如何解决

问题根因 #

新增JOIN后统计结果失真的核心原因是关联表LEVYDTA.SMPRTNM sm和原表REPIT.SMPDTA.SMPDTLM s存在一对多的匹配关系：原SMPDTLM表的1行数据，在SMPRTNM表中有多条符合关联条件的匹配行，JOIN后会把原表的1行拆成多行，后续对OB_UNITS等字段做SUM时就会重复计算，导致结果远大于预期值。

解决方案 #

方案1：先完成原有聚合逻辑，再关联第二张表 #

这是最稳妥的方案，先保证原有统计结果正确，再用聚合后的结果和SMPRTNM表关联，不会触发重复计算：

-- 第一步：先完成原有逻辑的聚合，得到正确的统计结果
WITH base_agg AS (
    SELECT s.DEAGY AS 'AGENCY', l.cmschn AS 'MASTER_CHAIN', s.DECHN AS 'CHAIN', s.DESTR AS 'STORE_NUMBER', s.DEWEDT AS 'WK_ENDING',  
    SUM(CASE WHEN s.DEIO='O' AND s.DEAGY = 10 AND s.DECHN = 'WM' THEN s.DEQTY ELSE 0 END) AS  'OB_UNITS',
    SUM(CASE WHEN s.DEIO='O' AND s.DEAGY = 10 AND s.DECHN = 'WM' THEN s.DEQTY * s.DERSP ELSE 0 END) AS OB_BILLING,
    SUM(CASE WHEN s.DEIO='O' AND s.DEAGY = 10 AND s.DECHN = 'WM' THEN s.DEQTY * s.DERPRC ELSE 0 END) AS OB_MSRP 
    FROM REPIT.SMPDTA.SMPDTLM s
    JOIN REPIT.LEVYDTA.LDSCHNM l
    ON s.DEAGY=l.CMAGY AND s.DECHN=l.CMCHN
    WHERE s.DEWEDT = 20210807 AND s.DEAGY=10 AND s.DECHN='WM'
    GROUP BY s.DEAGY, l.cmschn, s.DECHN, s.DESTR, s.DEWEDT
)
-- 第二步：用聚合后的结果关联第二张表，不会影响原有统计值
SELECT ba.*, sm.* -- 按需取SMPRTNM表的字段
FROM base_agg ba
JOIN LEVYDTA.SMPRTNM sm
ON ba.AGENCY=sm.DEAGY AND ba.CHAIN=sm.DECHN AND ba.STORE_NUMBER=sm.DESTR AND ba.WK_ENDING=sm.DEWEDT
ORDER BY ba.STORE_NUMBER;

方案2：先对第二张表去重，再关联原逻辑 #

如果你需要用到SMPRTNM表的字段参与原表的行级计算，可以先把SMPRTNM表按关联字段去重，保证每个关联组合只对应1行，再做JOIN：

SELECT s.DEAGY AS 'AGENCY', l.cmschn AS 'MASTER_CHAIN', s.DECHN AS 'CHAIN', s.DESTR AS 'STORE_NUMBER', s.DEWEDT AS 'WK_ENDING',  
CASE WHEN s.DEIO='O' AND s.DEAGY = 10 AND s.DECHN = 'WM' THEN s.DEQTY ELSE 0 END AS  'OB_UNITS',
CASE WHEN s.DEIO='O' AND s.DEAGY = 10 AND s.DECHN = 'WM' THEN s.DEQTY * s.DERSP ELSE 0 END AS OB_BILLING,
CASE WHEN s.DEIO='O' AND s.DEAGY = 10 AND s.DECHN = 'WM' THEN s.DEQTY * s.DERPRC ELSE 0 END AS OB_MSRP 
INTO ##temp1
FROM REPIT.SMPDTA.SMPDTLM s
JOIN REPIT.LEVYDTA.LDSCHNM l
ON s.DEAGY=l.CMAGY AND s.DECHN=l.CMCHN
-- 先对sm表按关联维度去重，保证1:1关联
JOIN (SELECT DISTINCT DEAGY, DECHN, DESTR, DEWEDT, DEITM# /*按需添加需要用到的sm表字段*/ FROM LEVYDTA.SMPRTNM) sm
ON s.DEAGY=sm.DEAGY AND s.DECHN=sm.DECHN AND s.DESTR=sm.DESTR AND s.DEWEDT=sm.DEWEDT AND s.DEITM#=sm.DEITM#
WHERE s.DEWEDT = 20210807 AND s.DEAGY=10 AND s.DECHN='WM'

SELECT AGENCY, MASTER_CHAIN, CHAIN, STORE_NUMBER, WK_ENDING,
SUM(OB_UNITS) as OB_UNITS, 
SUM(OB_BILLING) as OB_BILLING,
SUM(OB_MSRP) as OB_MSRP
FROM ##temp1
WHERE WK_ENDING = 20210807
GROUP BY AGENCY, MASTER_CHAIN, CHAIN, STORE_NUMBER, WK_ENDING
ORDER BY STORE_NUMBER;

额外优化点 #

• 如果你需要获取SMPRTNM表的聚合指标，不要直接和原表JOIN后聚合，应该单独对sm表做聚合后，再按维度和原有聚合结果关联
• 原代码中OB_BILLING的ELSE分支返回的是字符串'0'，建议修改为数字0避免隐式转换导致的性能问题和计算异常

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