PostgreSQL如何按相同id聚合行 合并对应年份数组值

场景说明 #

现有表存储按用户、年份标记的标签数组，同一id+name的标签散落在多行，每行仅存储单个年份的非空标签值，需要按id、name分组后，将各年份的非空标签聚合为单行记录。
样例源数据：

id  name  2019           2020        2021
1   Ana   {fruit,health}      
1   Ana                             {veggie}
2   Bill  {beauty}
2   Bill                 {veggie}
2   Bill                            {health,veggie}

期望输出：

id  name  2019            2020        2021
1   Ana   {fruit, health}           {veggie}
2   Bill  {beauty}        {veggie}    {health,veggie}

实现方案 #

方案1：适用于样例场景（同组同年份列最多1个非空值） #

样例数据中每个分组下每个年份列最多只有1条非空记录，直接使用max()聚合即可自动跳过null值，拿到唯一的有效数组，写法最简单、执行效率最高：

-- 假设表名为 user_tag
SELECT
  id,
  name,
  max("2019") AS "2019",
  max("2020") AS "2020",
  max("2021") AS "2021"
FROM user_tag
GROUP BY id, name
ORDER BY id;

说明：PostgreSQL支持数组类型的大小比较，聚合时null会被直接忽略，因此同组下该列唯一的非空数组会被max()正确取出。

方案2：通用场景（同组同年份列存在多个非空值，需合并去重） #

如果业务中存在同个分组下同一年份有多条非空标签数组，需要把数组合并、标签去重的场景，可以使用数组聚合+展开去重的写法：

SELECT
  id,
  name,
  -- 聚合2019年所有标签，去重后生成数组
  array(SELECT DISTINCT unnest(array_accum("2019"))) AS "2019",
  array(SELECT DISTINCT unnest(array_accum("2020"))) AS "2020",
  array(SELECT DISTINCT unnest(array_accum("2021"))) AS "2021"
FROM user_tag
GROUP BY id, name
ORDER BY id;

说明：array_accum是PostgreSQL内置的聚合函数，会将同组下所有输入的数组合并为一个大数组、自动跳过空值；unnest将大数组拆分为单个标签元素，DISTINCT去重后再通过array()构造器重新组装为最终的标签数组。

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