在group by子句中连接数组

Question

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

给出以下输入：

| 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()。

有任何想法吗？

Answer 1

UNION ALL

您可以先进行“反向旋转” UNION ALL：

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，和xmlagg，以及类似用户定义集合函数，产生依赖于输入值的顺序上有意义不同的结果值。默认情况下未指定此顺序，但可以通过ORDER BY在聚合调用中编写一个子句来控制它，如第4.2.7节所示。另外， 通常也可以提供来自已排序子查询的输入值。

大胆强调我的。

[LATERAL](https://www.postgresql.org/docs/current/queries-table-

expressions.html#QUERIES-
LATERAL)带有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;

结果相同。只需要对表进行一次传递。

自定义集合函数

或者 您可以创建一个自定义聚合函数，如以下相关答案中所述：

• 选择数据到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()的 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();