多表关联数组列时聚合结果重复问题求助

问题原因 #

当同时关联tbl_sponsor和tbl_tag时，两个表的匹配行会形成笛卡尔积——每个赞助商记录会和每个标签记录组合，最终生成的临时表中，同一个赞助商对应所有标签，聚合时自然会重复出现标签数量次。

解决方案 #

方法1：在array_agg中使用DISTINCT（简单但有局限性） #

直接在聚合函数中加入DISTINCT去重，适合数据量小、无同名赞助商/标签的场景：

select 
    tr.report_id,
    tr.sponsor_ids,
    array_agg(DISTINCT tsp.sponsor) as sponsors,
    array_agg(DISTINCT tg.tag_name) as tags -- 替换为tag表实际字段
from  public.tbl_report tr
left join public.tbl_sponsor tsp on tsp.sponsor_id = any(tr.sponsor_ids)
left join public.tbl_tag tg on tg.tag_id = any(tr.tags)
group by tr.report_id, tr.sponsor_ids
having tr.report_id = 109

⚠️ 注意：如果存在同名的赞助商/标签，DISTINCT会误将它们合并，导致数据丢失。

方法2：使用LATERAL子查询聚合（高效且准确） #

通过LATERAL子查询分别对每个关联表独立聚合，从根源避免笛卡尔积，是推荐的方案，后续新增关联表只需追加对应的子查询：

select 
    tr.report_id,
    tr.sponsor_ids,
    tsp.sponsors,
    tg.tags
from public.tbl_report tr
left join lateral (
    select array_agg(sponsor) as sponsors
    from public.tbl_sponsor
    where sponsor_id = any(tr.sponsor_ids)
) tsp on true
left join lateral (
    select array_agg(tag_name) as tags -- 替换为tag表实际字段
    from public.tbl_tag
    where tag_id = any(tr.tags)
) tg on true
where tr.report_id = 109

方法3：SELECT子句内嵌子查询（简洁写法） #

和LATERAL效果一致，写法更紧凑，适合查询逻辑简单的场景：

select 
    tr.report_id,
    tr.sponsor_ids,
    (select array_agg(sponsor) from public.tbl_sponsor where sponsor_id = any(tr.sponsor_ids)) as sponsors,
    (select array_agg(tag_name) from public.tbl_tag where tag_id = any(tr.tags)) as tags
from public.tbl_report tr
where tr.report_id = 109

内容的提问来源于stack exchange，提问作者Md. Parvez Alam