在 group by 子句中连接数组

我们在将数组分组为单个数组时遇到问题。 我们希望将两列中的值连接到一个数组中，并将这些多行数组聚合起来。

给定以下输入：

| id | name | col_1 | col_2 |
| 1  |  a   |   1   |   2   |
| 2  |  a   |   3   |   4   |
| 4  |  b   |   7   |   8   |
| 3  |  b   |   5   |   6   |

我们想要以下输出：

| a | { 1, 2, 3, 4 } |
| b | { 5, 6, 7, 8 } |

元素的顺序很重要，并且应与聚合行的 id 相关。

我们尝试了array_agg()功能：

SELECT array_agg(ARRAY[col_1, col_2]) FROM mytable GROUP BY name;

不幸的是，这个声明引发了一个错误：

ERROR: could not find array type for data type character varying[]

使用 group by 子句合并数组似乎是不可能的array_agg().

有任何想法吗？

UNION ALL

你可以“取消旋转”UNION ALL first:

SELECT name, array_agg(c) AS c_arr
FROM  (
   SELECT name, id, 1 AS rnk, col1 AS c FROM tbl
   UNION ALL
   SELECT name, id, 2, col2 FROM tbl
   ORDER  BY name, id, rnk
   ) sub
GROUP  BY 1;

进行调整以产生您稍后请求的值的顺序。手册：

聚合函数array_agg, json_agg, string_agg, and xmlagg, 以及类似的用户定义的聚合函数，产生 根据顺序的不同，结果值会有不同的意义 输入值。默认情况下未指定此顺序，但可以 通过写一个来控制ORDER BY聚合调用中的子句，如 如第 4.2.7 节所示。或者，提供输入值 排序子查询通常可以工作。

大胆强调我的。

LATERAL子查询 with VALUES表达

LATERAL需要Postgres 9.3或稍后。

SELECT t.name, array_agg(c) AS c_arr
FROM  (SELECT * FROM tbl ORDER BY name, id) t
CROSS  JOIN LATERAL (VALUES (t.col1), (t.col2)) v(c)
GROUP  BY 1;

相同的结果。只需要在桌子上经过一次即可。

自定义聚合函数

Or您可以创建一个自定义聚合函数，如这些相关答案中讨论的那样：

• 选择数据到 Postgres 数组中
• PostgreSQL 中是否有类似 zip() 函数的东西可以组合两个数组？

CREATE AGGREGATE array_agg_mult (anyarray)  (
    SFUNC     = array_cat
  , STYPE     = anyarray
  , INITCOND  = '{}'
);

然后你可以：

SELECT name, array_agg_mult(ARRAY[col1, col2] ORDER BY id) AS c_arr
FROM   tbl
GROUP  BY 1
ORDER  BY 1;

或者，通常更快，但不是标准 SQL：

SELECT name, array_agg_mult(ARRAY[col1, col2]) AS c_arr
FROM  (SELECT * FROM tbl ORDER BY name, id) t
GROUP  BY 1;

所添加的ORDER BY id（可以附加到此类聚合函数）保证您想要的结果：

a | {1,2,3,4}
b | {5,6,7,8}

或者您可能对这个替代方案感兴趣：

SELECT name, array_agg_mult(ARRAY[ARRAY[col1, col2]] ORDER BY id) AS c_arr
FROM   tbl
GROUP  BY 1
ORDER  BY 1;

它产生二维数组：

a | {{1,2},{3,4}}
b | {{5,6},{7,8}}

最后一个可以用内置的替换（并且应该是，因为它更快！）array_agg() in Postgres 9.5或更高版本 - 增加了聚合数组的功能：

SELECT name, array_agg(ARRAY[col1, col2] ORDER BY id) AS c_arr
FROM   tbl
GROUP  BY 1
ORDER  BY 1;

相同的结果。手册：

输入数组连接成一个更高维度的数组（输入 必须全部具有相同的维度，并且不能为空或 null）

所以和我们自定义的聚合函数不完全一样array_agg_mult();