Inner Join时避免笛卡尔积，确保sum(tbl2_amt)计算准确

问题 #

我正在处理一个需要关联table1与table2的业务场景，但因需包含特定字段，导致sum(tbl2_amt)的计算结果为实际值的3倍。

表结构及数据 #

Table1

col1    col2    col3    period  col4    col5    col6    col7    tbl1_amt
20110   dt      0000    202302  tcp             de      otfx    19169.19495
20110   dt      0000    202302  tcp             de      acpy    416134.8921
20110   dt      0000    202302  tcp             de      forx    -114689.5469

Table2

period  col1    col2    col3    col4    tbl2_amt
202302  20110   dt      0000    1711    31172.64171
202302  20110   dt      0000    1290    470.1353638
202302  20110   dt      0000    1208    -157.4978849
202302  20110   dt      0000    8158    18382.71997
202302  20110   dt      0000    1200    388217.7242
202302  20110   dt      0000    1200    290454.7377
202302  20110   dt      0000    1290    9422.588832
202302  20110   dt      0000    1711    505307.5402
202302  20110   dt      0000    1290    9980.002115
202302  20110   dt      0000    1290    139973.5195
202302  20110   dt      0000    0000    513661.3896
202302  20110   dt      0000    1200    138179.7272
202302  20110   dt      0000    1200    7159.475465
202302  20110   dt      0000    1236    268.0203046
202302  20110   dt      0000    1290    -480.3828257
202302  20110   dt      0000    1711    11272.73689
202302  20110   dt      0000    1200    1.057529611
202302  20110   dt      0000    1711    2084.263959
202302  20110   dt      0000    1711    3142.110829
202302  20110   dt      0000    1712    23.19162437
202302  20110   dt      0000    8307    11835.60702
202302  20110   dt      0000    1057    1230.964467
202302  20110   dt      0000    1903    271644.247
202302  20110   dt      0000    1290    2470.642978
202302  20110   dt      0000    1057    46684.64467
202302  20110   dt      0000    1290    23518.40102
202302  20110   dt      0000    1290    66276.70262
202302  20110   dt      0000    1290    244284.5812
202302  20110   dt      0000    1903    105582.0749
202302  20110   dt      0000    1290    196.4890017
202302  20110   dt      0000    1208    891.4974619
202302  20110   dt      0000    1711    288288.1557
202302  20110   dt      0000    1200    13.21912014
202302  20110   dt      0000    1200    2310.7022
202302  20110   dt      0000    1290    23006.66244
202302  20110   dt      0000    1185    32263.95939
202302  20110   dt      0000    1200    55201.96701
202302  20110   dt      0000    1299    156910.4484
202302  20110   dt      0000    1711    161.178088
202302  20110   dt      0000    1290    308384.6975
202302  20110   dt      0000    1057    3162.182741
202302  20110   dt      0000    1290    694.4585448
202302  20110   dt      0000    1200    262519.9979
202302  20110   dt      0000    1711    469633.0372

原SQL查询 #

Select 
    tbl1.period, 
    tbl1.col1, 
    tbl1.col2, 
    tbl1.col3, 
    tbl1.col4, 
    tbl1.col5, 
    tbl1.col6, 
    tbl1.col7, 
    tbl2.col4, 
    sum(tbl2_amt) 
from 
    tbl1 
join tbl2 
on tbl1.col1 = tbl2.col1
and tbl1.col2 = tbl2.col2
and tbl1.col3 = tbl2.col3
and tbl1.period = tbl2.period
group by tbl1.period, tbl1.col1, tbl1.col2, tbl1.col3, tbl1.col4, tbl1.col5, tbl1.col6, tbl1.col7, tbl2.col4

当前查询中sum(tbl2_amt)的结果是实际值的3倍，需要在获取tbl2.col4、tbl1.col7的同时，确保sum(tbl2_amt)计算准确，避免结果翻倍。

解决方案 #

问题根源是：Table1中有3条同col1/col2/col3/period的记录，每条都会与Table2中匹配的记录关联，导致Table2的每条记录被重复计算3次，最终sum结果是实际值的3倍。

要解决这个问题，需先对Table2按分组维度聚合得到准确sum值，再与Table1关联，具体有两种实现方式：

方法一：使用CTE先聚合Table2 #

-- 先计算Table2各维度的准确sum(tbl2_amt)
WITH tbl2_agg AS (
    SELECT 
        period, col1, col2, col3, col4,
        SUM(tbl2_amt) AS total_tbl2_amt
    FROM tbl2
    GROUP BY period, col1, col2, col3, col4
)
-- 关联Table1获取所需字段
SELECT 
    tbl1.period, 
    tbl1.col1, 
    tbl1.col2, 
    tbl1.col3, 
    tbl1.col4, 
    tbl1.col5, 
    tbl1.col6, 
    tbl1.col7, 
    tbl2_agg.col4, 
    tbl2_agg.total_tbl2_amt
FROM tbl1
JOIN tbl2_agg 
    ON tbl1.col1 = tbl2_agg.col1
    AND tbl1.col2 = tbl2_agg.col2
    AND tbl1.col3 = tbl2_agg.col3
    AND tbl1.period = tbl2_agg.period

方法二：使用子查询替代CTE（兼容不支持CTE的数据库） #

SELECT 
    tbl1.period, 
    tbl1.col1, 
    tbl1.col2, 
    tbl1.col3, 
    tbl1.col4, 
    tbl1.col5, 
    tbl1.col6, 
    tbl1.col7, 
    tbl2_sub.col4, 
    tbl2_sub.total_tbl2_amt
FROM tbl1
JOIN (
    SELECT 
        period, col1, col2, col3, col4,
        SUM(tbl2_amt) AS total_tbl2_amt
    FROM tbl2
    GROUP BY period, col1, col2, col3, col4
) AS tbl2_sub
    ON tbl1.col1 = tbl2_sub.col1
    AND tbl1.col2 = tbl2_sub.col2
    AND tbl1.col3 = tbl2_sub.col3
    AND tbl1.period = tbl2_sub.period

说明 #

两种方法都是先对Table2按period/col1/col2/col3/col4计算出准确的sum值，再与Table1关联，这样Table2的每条聚合结果只会和Table1的匹配记录关联一次，既避免了重复计算，又保留了tbl2.col4和tbl1.col7字段。

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