如何合并FULL JOIN结果中不同表的周数与品类列并汇总金额

场景说明 #

我有两个表，均包含week_number和product_category字段：

• incoming表：存储每周各品类的入库金额incoming_amount
• outgoing表：存储每周各品类的出库金额outgoing_amount

需要合并这两个表，得到按周、按品类统计出入库金额的汇总表。

示例数据 #

incoming表 #

outgoing表 #

我的尝试及问题 #

我用以下SQL做全外连接并分组汇总，但结果里week_number和product_category分成了两组（带NULL值）：

SELECT i.week_number 
      ,i.product_category 
      ,o.week_number 
      ,o.product_category 
      ,SUM(i.incoming_amount ) AS sum_incoming_amount 
      ,SUM(o.outgoing_amount ) AS sum_outgoing_amount 
FROM incoming AS i
FULL OUTER JOIN outgoing AS o
ON i.week_number = o.week_number AND i.product_category = o.product_category 
GROUP BY i.product_category, i.week_number, o.product_category, o.week_number;

得到的结果：

期望结果 #

希望合并week_number和product_category列，得到如下格式：

解决方案 #

可以通过两种方式实现：

方法1：合并分组字段，调整SELECT和GROUP BY #

使用COALESCE函数，优先取incoming表的字段，没有则取outgoing表的，合并成单一分组列：

SELECT COALESCE(i.week_number, o.week_number) AS week_number
      ,COALESCE(i.product_category, o.product_category) AS product_category
      ,SUM(i.incoming_amount) AS sum_incoming_amount
      ,SUM(o.outgoing_amount) AS sum_outgoing_amount
FROM incoming AS i
FULL OUTER JOIN outgoing AS o
ON i.week_number = o.week_number AND i.product_category = o.product_category
GROUP BY COALESCE(i.week_number, o.week_number), COALESCE(i.product_category, o.product_category);

方法2：先分别汇总，再全外连接 #

先对两个表各自按周和品类汇总，再做全外连接，逻辑更清晰，大数据量下性能更优：

WITH incoming_summary AS (
    SELECT week_number, product_category, SUM(incoming_amount) AS sum_incoming_amount
    FROM incoming
    GROUP BY week_number, product_category
),
outgoing_summary AS (
    SELECT week_number, product_category, SUM(outgoing_amount) AS sum_outgoing_amount
    FROM outgoing
    GROUP BY week_number, product_category
)
SELECT COALESCE(i.week_number, o.week_number) AS week_number
      ,COALESCE(i.product_category, o.product_category) AS product_category
      ,i.sum_incoming_amount
      ,o.sum_outgoing_amount
FROM incoming_summary i
FULL OUTER JOIN outgoing_summary o
ON i.week_number = o.week_number AND i.product_category = o.product_category;

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