关联聚合表时避免数值重复计数的解决方案

我有两张存在父/子关系的表，父表中一条记录对应子表中的N条记录。两张表均包含amount字段，希望通过一次查询聚合这两个字段，查看父表和子表的总金额。但直接关联两张表后，父表的金额会因每个子记录被重复计数，导致聚合值不正确。

问题示例 #

以下是简化后的表结构、数据、错误查询及结果，以及期望的正确结果：

DROP TABLE IF EXISTS parent;
CREATE TABLE parent (
    id numeric,
    amount numeric,
    person text
);

DROP TABLE IF EXISTS child;
CREATE TABLE child (
    id numeric,
    parentId numeric,
    amount numeric,
    person text
);

INSERT INTO parent (id, amount, person) VALUES 
(1, 5, 'P1'),
(2, 15, 'P1'), 
(3, 5, 'P2'), 
(4, 20, 'P2');

INSERT INTO child (id, parentId, amount) VALUES 
(1, 1, 3),
(2, 1, 5), 
(3, 2, 10), 
(4, 3, 6),
(5, 4, 12), 
(5, 4, 8);

-- 错误查询：父表金额因子记录关联被重复计数
SELECT
    p.person,
    p.id,
    SUM(p.amount) AS parent_sum,
    SUM(c.amount) AS child_sum
FROM
    parent p
LEFT OUTER JOIN child AS c ON
    c.parentId = p.id
GROUP BY ROLLUP (p.person, p.id)
ORDER BY (p.person, p.id);

错误输出：

期望输出：

性能优先的解决方案 #

方案1：预聚合子表后关联（性能最优）

先对子表按parentId聚合，得到每个父记录对应的子表总金额，再与父表关联。这种方式大幅减少了关联的数据量，适合大数据量场景。

WITH child_agg AS (
    SELECT 
        parentId,
        SUM(amount) AS child_sum
    FROM child
    GROUP BY parentId
)
SELECT
    p.person,
    p.id,
    SUM(p.amount) AS parent_sum,
    COALESCE(SUM(c.child_sum), 0) AS child_sum
FROM parent p
LEFT JOIN child_agg c ON c.parentId = p.id
GROUP BY ROLLUP(p.person, p.id)
ORDER BY p.person, p.id;

方案2：子查询替代CTE（与方案1性能相当）

如果数据库对CTE支持有限，可用子查询实现相同逻辑：

SELECT
    p.person,
    p.id,
    SUM(p.amount) AS parent_sum,
    COALESCE(SUM(c.child_sum), 0) AS child_sum
FROM parent p
LEFT JOIN (
    SELECT parentId, SUM(amount) AS child_sum
    FROM child
    GROUP BY parentId
) c ON c.parentId = p.id
GROUP BY ROLLUP(p.person, p.id)
ORDER BY p.person, p.id;

方案3：使用聚合函数去重（适合小数据量场景）

如果父表的id是主键（每条id对应唯一一条记录），可直接用MAX(p.amount)替代SUM(p.amount)，因为每个父记录的amount在关联后会重复出现多次，取最大值即可得到原始值：

SELECT
    p.person,
    p.id,
    MAX(p.amount) AS parent_sum,
    SUM(c.amount) AS child_sum
FROM parent p
LEFT JOIN child c ON c.parentId = p.id
GROUP BY ROLLUP(p.person, p.id)
ORDER BY p.person, p.id;

方案对比 #

• 预聚合子表的方案（方案1、2）性能最优，因为它先将子表数据聚合为少量记录，再与父表关联，避免了大量重复数据的关联和计算。
• 方案3代码简洁，但仅适用于父表id为主键的场景，且当子表数据量较大时，关联后的数据量会远大于预聚合方案，性能劣势明显。

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