如何移除或避免嵌套数组时产生的重复行

问题分析 #

你遇到的情况是原表中存在重复的(date, id, type, topic_id, topic_name)记录，导致按date, id, type分组聚合后生成了重复的聚合行。要得到唯一的目标结果，可通过聚合前去重或聚合后去重两种方式实现。

方法一：聚合前去重（推荐） #

先对原表数据去重，确保每个(date, id, type, topic_id, topic_name)组合唯一，再执行聚合操作，能减少聚合计算的数据量，提升效率：

select 
  date,
  id,
  type,
  array_agg(
    struct(
      topic_id,
      topic_name
    )
  ) as topic
from (
  -- 先去除原表中的重复行
  select distinct date, id, type, topic_id, topic_name
  from table
) t
group by 1, 2, 3

如果存在同一topic_id对应不同topic_name的情况，可通过分组保留唯一的主题信息：

select 
  date,
  id,
  type,
  array_agg(
    struct(
      topic_id,
      topic_name
    )
  ) as topic
from (
  -- 按主题ID分组，确保每个主题唯一
  select date, id, type, topic_id, max(topic_name) as topic_name
  from table
  group by date, id, type, topic_id
) t
group by 1, 2, 3

方法二：聚合后去重 #

如果聚合后仍存在重复的聚合行，可在最外层查询添加DISTINCT关键字直接去重：

select distinct
  date,
  id,
  type,
  array_agg(
    struct(
      topic_id,
      topic_name
    )
  ) as topic
from table
group by 1, 2, 3

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