PostgreSQL：使用双聚合函数时如何避免关联导致的重复结果

问题场景 #

现有三张关联表：

create table boxes
(
    id   bigserial primary key,
    name varchar(255)
);

create table animals
(
    id     bigserial primary key,
    name   varchar(255),
    age    numeric,
    box_id bigint constraint animals_boxes_id references boxes
);

create table vegetables
(
    id     bigserial primary key,
    name   varchar(255),
    weight numeric,
    box_id bigint constraint vegatables_box_id references boxes
);

插入测试数据：

insert into boxes (name) values ('First box');
insert into animals (box_id, name, age) values (1, 'Cat', 2);
insert into animals (box_id, name, age) values (1, 'Cat', 3);
insert into animals (box_id, name, age) values (1, 'Dog', 5);
insert into vegetables (box_id, name, weight) values (1, 'Tomato', 20);
insert into vegetables (box_id, name, weight) values (1, 'Cucumber', 30);
insert into vegetables (box_id, name, weight) values (1, 'Potato', 50);

单独查询盒子中的动物名称，结果正常：

select b.name                                 as box_name,
       string_agg(a.name, ', ' order by a.id) as animal_names
from boxes as b
         left join animals a on b.id = a.box_id
group by b.name;

结果：

但同时关联vegetables表查询时，会因笛卡尔积导致聚合结果重复：

select b.name                                 as box_name,
       string_agg(a.name, ', ' order by a.id) as animal_names,
       string_agg(v.name, ', ' order by v.id) as vegatable_names
from boxes as b
         left join animals a on b.id = a.box_id
         left join vegetables v on b.id = v.box_id
group by b.name;

错误结果：

期望结果：

直接用DISTINCT不可行的原因：

• 表中存在重复名称（如两只Cat），DISTINCT会过滤掉合法的重复项，得到Cat, Dog而非正确的Cat, Cat, Dog
• string_agg带ORDER BY时加DISTINCT会触发错误：ERROR: in an aggregate with DISTINCT, ORDER BY expressions must appear in argument list，即使去掉ORDER BY也无法解决第一个问题

这个问题同样适用于sum、array_agg等其他聚合函数，比如关联蔬菜表后计算动物年龄总和会得到30（正确值为10）。

解决方案 #

方法1：子查询预聚合 #

先分别对animals和vegetables按box_id完成聚合，再和boxes关联，从根源避免笛卡尔积。

示例代码（string_agg场景）：

select 
    b.name as box_name,
    coalesce(a.animal_names, '') as animal_names,
    coalesce(v.vegatable_names, '') as vegatable_names
from boxes b
left join (
    select 
        box_id,
        string_agg(name, ', ' order by id) as animal_names
    from animals
    group by box_id
) a on b.id = a.box_id
left join (
    select 
        box_id,
        string_agg(name, ', ' order by id) as vegatable_names
    from vegetables
    group by box_id
) v on b.id = v.box_id;

示例代码（sum场景）：

select 
    b.name as box_name,
    coalesce(a.total_age, 0) as total_animal_age,
    coalesce(v.total_weight, 0) as total_veg_weight
from boxes b
left join (
    select box_id, sum(age) as total_age
    from animals
    group by box_id
) a on b.id = a.box_id
left join (
    select box_id, sum(weight) as total_weight
    from vegetables
    group by box_id
) v on b.id = v.box_id;

方法2：使用LATERAL JOIN（横向连接） #

LATERAL JOIN允许子查询引用外部表的列，逻辑更直观，适合需要对每个盒子单独处理聚合的场景。

示例代码：

select 
    b.name as box_name,
    coalesce(a.animal_names, '') as animal_names,
    coalesce(v.vegatable_names, '') as vegatable_names
from boxes b
left join lateral (
    select string_agg(name, ', ' order by id) as animal_names
    from animals
    where box_id = b.id
) a on true
left join lateral (
    select string_agg(name, ', ' order by id) as vegatable_names
    from vegetables
    where box_id = b.id
) v on true;

方法3：结合DISTINCT ON和聚合（补充方案） #

如果聚合依赖唯一标识（比如id），可以通过拼接唯一标识区分重复名称的不同记录，再完成聚合后拆分。这种方法写法繁琐，仅作为特殊场景补充。

示例代码：

select 
    b.name as box_name,
    -- 拼接id确保重复名称的不同记录被保留，再拆分回原名称
    regexp_replace(string_agg(distinct concat(a.name, '|', a.id), ', ' order by a.id), '\|.*?($|,)', '\1', 'g') as animal_names,
    regexp_replace(string_agg(distinct concat(v.name, '|', v.id), ', ' order by v.id), '\|.*?($|,)', '\1', 'g') as vegatable_names
from boxes b
left join animals a on b.id = a.box_id
left join vegetables v on b.id = v.box_id
group by b.name;

各方法对比 #

• 子查询预聚合：性能最优，提前聚合减少了关联后的数据量，适合大数据量场景
• LATERAL JOIN：逻辑清晰直观，适合复杂的单盒子聚合逻辑，性能与子查询接近
• DISTINCT ON拼接法：仅作为补充方案，不推荐常规使用

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