如何高效将多列聚合为数组？SQL聚合优化方案咨询

针对将多列score按user聚合为单个数组的需求，相比UNION拆分再聚合的方法，单表扫描+数组拼接聚合的方式效率更高——它只需要遍历原表一次，避免了多次拆分合并带来的额外开销。

适用于PostgreSQL 14+的最优写法 #

利用array_concat_agg函数直接将每行的score列构造成数组，再按user拼接所有行的数组：

SELECT 
  "user",
  array_concat_agg(ARRAY[score_1, score_2, score_3]) AS scores
FROM your_table
GROUP BY "user";

效果验证： #

• 用户1的两行数据会分别生成数组[100,80,100]和[80,null,80]，拼接后正好得到[100,80,100,80,null,80]
• 用户2的单行数组[95,90,65]直接作为最终结果，完全符合预期

兼容PostgreSQL低版本（<14）的替代写法 #

如果你的PostgreSQL版本不支持array_concat_agg，可以用unnest展开每行的score数组，再按顺序聚合：

SELECT 
  "user",
  array_agg(score ORDER BY sort_idx) AS scores
FROM (
  SELECT 
    "user",
    unnest(ARRAY[score_1, score_2, score_3]) AS score,
    -- 生成排序索引，保证score1→score2→score3的顺序，且行之间的顺序不变
    (row_number() OVER (PARTITION BY "user") - 1) * 3 + idx AS sort_idx
  FROM your_table,
       generate_subscripts(ARRAY[score_1, score_2, score_3], 1) AS idx
) t
GROUP BY "user";

为什么比UNION方法高效？ #

UNION拆分三列的方式需要对原表进行三次逻辑扫描（即便用UNION ALL跳过去重，也需要三次扫描拆分），而上述方法只需要一次全表扫描，将每行的三个score直接打包成数组后完成聚合，IO和计算开销都显著降低。

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