如何在Postgres中将所有记录中的整数数组合并为单个数组

Question

我有一个类型为整数数组的列。如何将它们全部合并成一个单一的整数数组？

例如：如果我执行查询：

select column_name from table_name

我得到的结果集如下：

-[RECORD 1]----------
column_name | {1,2,3}
-[RECORD 2]----------
column_name | {4,5}

如何将{1,2,3,4,5}作为最终结果？

- user1465266

Answer 1

您可以使用 unnest 打开数组，然后使用array_agg将它们重新组合在一起：

select array_agg(c)
from (
  select unnest(column_name)
  from table_name
) as dt(c);

- mu is too short

---

可能比我的效率高得多，但不一定保留元素顺序；你需要使用 with ordinality 来实现。 - Craig Ringer

@Craig：哪个版本的PostgreSQL有WITH ORDINALITY？不管怎样，自定义聚合函数还是很酷的。 - mu is too short

在PostgreSQL 9.4中添加，所以“即将推出”。我太习惯于使用git主分支了... - Craig Ringer

WITH ORDINALITY的详细信息。 - Erwin Brandstetter

Answer 2

定义一个简单的自定义聚合：

CREATE AGGREGATE array_cat_agg(anyarray) (
  SFUNC=array_cat,
  STYPE=anyarray
);

并使用它：

WITH v(a) AS ( VALUES (ARRAY[1,2,3]), (ARRAY[4,5,6,7]))
SELECT array_cat_agg(a) FROM v;

如果您想要特定的顺序，请将其放在聚合调用中，即array_cat_agg(a ORDER BY ...)。

这大约对于n行是O(n²)，所以它不适用于长行集。为了获得更好的性能，您需要用C语言编写它，在那里您可以使用更有效率（但难以使用）的PostgreSQL数组的C API，以避免每次迭代重新复制数组。

- Craig Ringer

---

就我个人而言，这个聚合在二次时间O(n^2)内运行，因此不适用于大型数据集。来源：我在生产中使用它处理了一些大型数据集，但最终不得不将其删除 =) - John Bledsoe

@JohnBledsoe 我很惊讶它是 O(n^2)，你确定吗？每次迭代它都会复制整个数组，包括所有之前的成员，当它构建一个新的数组时。无论如何，对于长输入来说，它仍然会非常慢。 - Craig Ringer

我已经离开计算机科学学校很长时间了，所以我不确定 =) 但是，如果我没记错的话，复制一个长度为N的数组N次的时间复杂度是O(n^2)。 - John Bledsoe

@JohnBledsoe 数组从长度1开始。每次复制它时，它会增加1个元素。假设每个输入数组的长度相同（在此目的下视为1个元素）：在n=1时复制1个元素。在n=2时复制3个元素。在n=3时复制6个元素。在n=4时复制10个元素。这是一个级数求和n∑n。即(n·n)/2或n²/2..所以O(n^2)。你说得很对。Pg在SQL级别上没有可变数组，因此您需要使用PL（例如，带有numpy或intarray的Python）或使用C来更有效地执行它。 - Craig Ringer

对于这里的数学我不确定，但从我的经验来看，它也非常慢。在一个有300K行的表上花费了很长时间（30秒后我放弃了），而mu-is-too-short的解决方案只需要215毫秒。 - Ezra Steinmetz

我成功地通过对行进行分组（https://gist.github.com/ezra100/1ab9be45eb21569b5aa0e6212495b1c9）将您的解决方案优化到了2.5秒，但是mu-is-too-short的方法更简单更快。 - Ezra Steinmetz

Answer 3

要合并数组，您可以使用|| 运算符更多信息

要将结果放入扁平列表中，请使用unnest函数

示例：

select unnest(ARRAY[1,2] || ARRAY[3,2] || ARRAY[4,5]) as number;

- Maxim

Answer 4

string_to_array(string_agg(array_to_string(column_name ,','),','),',')

这可能是您情况的一些线索。我已经这样做了。

- mouse500

---

请在您的回答中提供更多细节。目前的写法让人难以理解您的解决方案。 - Community

Answer 5

您可以使用“lateral subquery”来实现这一点：

select array_agg(u.a)
from (values (array[1, 2, 3]), (array[4, 5])) t (a)
    join lateral unnest(t.a) u (a) on true;

- Vladimir Aleshin

Answer 6

唯一的方法是在函数内部执行此操作：

CREATE FUNCTION merge_arrays() RETURNS int[] AS $$
DECLARE
  this record;
  res  int[];
BEGIN
  FOR this IN
    SELECT column_name FROM table_name
  LOOP
    array_cat(res, this.column_name);
  END LOOP;
  RETURN res;
END; $$ LANGUAGE plpgsql;

然后你可以

SELECT merge_arrays();

为了得到你所寻找的结果。

当然，这样做会将表定义硬编码到函数中，这可能是个问题（也可能不是）。此外，您可能想在循环查询中放置一个 WHERE 子句，以限制要添加其数组的记录；您可以使用另一个函数参数来实现这一点。

请记住，随着表大小的增加，您可能会得到一个非常大的数组，这可能会影响性能。您真的需要在一个大数组中获取所有记录的子数组吗？查看您的应用程序，看看是否可以在该级别上执行合并，而不是单个查询中执行合并。

- Patrick