PostgreSQL按id分组聚合category生成数组的正确SQL写法

测试表包含id、category两个字段，原始数据映射关系：

• id=1 对应category值为A、B
• id=2 对应category值为A、R、C
• id=3 对应category值为Z

预期输出要求为每个id单独占一行，通过categories字段返回当前id下所有category值组成的数组：

• id=1 对应{"A","B"}
• id=2 对应{"A","R","C"}
• id=3 对应{"Z"}

原有实现SQL如下：

SELECT DISTINCT id,
                ARRAY(SELECT DISTINCT category::VARCHAR FROM test) AS categories
FROM my_table

执行后所有id行的categories字段均返回全表所有category值组成的数组{"A","B","R","C","Z"}，未按id分组区分。

1. 子查询未与外层查询的id字段做关联，每次子查询执行都会扫描全表category数据，因此返回全量值集合。
2. 该需求属于典型的分组聚合场景，必须搭配GROUP BY子句和对应数组聚合函数才能实现按id归集值的效果。此前GROUP BY未生效，是因为没有使用正确的聚合函数，仅声明分组的前提下，数据库无法识别同组category值需要合并为数组。

以下写法适配当前使用的PostgreSQL环境（匹配现有ARRAY语法规则）：

推荐写法（性能最优） #

使用内置数组聚合函数array_agg，按id分组即可。如果同id下存在重复category值需要去重，可在聚合函数内添加DISTINCT关键字：

SELECT 
  id,
  array_agg(DISTINCT category::VARCHAR) AS categories
FROM test
GROUP BY id;

关联子查询写法（不推荐，性能较差） #

如果要保留原有ARRAY子查询的写法，必须在子查询内添加id关联条件，才能保证子查询只返回当前id对应的category值：

SELECT DISTINCT
  t1.id,
  ARRAY(
    SELECT DISTINCT category::VARCHAR 
    FROM test t2 
    WHERE t2.id = t1.id -- 核心：添加id关联逻辑
  ) AS categories
FROM test t1;

其他数据库适配说明：

• MySQL 8.0+ 可使用JSON_ARRAYAGG()函数，返回JSON格式数组
• Hive/Spark SQL 可使用collect_list()（保留重复值）或collect_set()（自动去重）实现相同效果
  核心逻辑均为：按id分组 + 数组类聚合函数归集同组字段值。

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