FULL OUTER JOIN两个临时表后GROUP BY字段选择导致统计错误如何解决？

问题根源 #

你使用了FULL OUTER JOIN做两表关联，关联后会生成两类单边存在的数据行：

• 仅Actuals表有匹配记录，Forecast表所有字段为NULL
• 仅Forecast表有匹配记录，Actuals表所有字段为NULL

如果按单张表的LOC字段分组，另一张表无匹配的行对应的LOC就为NULL，会被排除在对应LOC的分组统计之外，自然会出现单列统计结果偏小的问题。

修复方案 #

用COALESCE函数合并两张表的LOC字段作为统一分组维度即可，原有表关联条件完全不需要修改，修复后代码如下：

WITH ACTUALS AS
(
  SELECT [LOC], [DMDUNIT], [DMDPostDate], 
  SUM(HistoryQuantity) AS 'Actuals'
  FROM SCPOMGR.HISTWIDE_CHAIN
  GROUP BY [LOC], [DMDUNIT], [DMDPostDate]    
),
Forecast AS
(
  SELECT [LOC], [DMDUNIT], [STARTDATE],
  SUM(TOTFCST) AS 'Forecast'
  FROM SCPOMGR.FCSTPERFSTATIC
  GROUP BY [LOC], [DMDUNIT], [STARTDATE]   
)
 SELECT 
  COALESCE(A.[LOC], F.[LOC]) AS LOC, 
  SUM(A.Actuals) AS 'Actuals', 
  SUM(F.Forecast) AS 'Forecast'
 FROM Actuals A FULL OUTER JOIN Forecast F 
  on A.[DMDUNIT] = F.[DMDUNIT] 
  AND f.[STARTDATE] = a.[DMDPostDate] 
  and a.[LOC] = f.[LOC]
 GROUP BY COALESCE(A.[LOC], F.[LOC])
 ORDER BY LOC

COALESCE会优先取A.LOC的值，如果为NULL就自动取F.LOC的值，确保所有单边存在的记录都能被归到正确的LOC分组下，两个指标的统计结果都会符合预期。

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