选择整数数组的每个第一个元素到数组

Question

如何将整数数组的每个第一个元素选择到数组中？ {{1,2,3},{2,15,32},{5,16,14},...} 转换为 {1,2,5,...}。

- Shamil Yakupov

---

更多关于您的上下文信息会更合适。Postgres版本？表定义？ - Erwin Brandstetter

Answer 1

由于PostgreSQL允许请求超出数组大小的片段，并且假设永远不会有超过999个子数组，因此我们可以使用这个怪物。

WITH data AS (
  SELECT array[array[1,2,3], array[2,15,32], array[5,16,14]] as arr)
SELECT array_agg(arr)
  FROM (SELECT unnest(arr[1:999][1]) as arr from data) data2;

如果需要，您当然可以将常量999变得更大，我只是随意添加了一个大数字。

这个过程很复杂的原因是，如果你只使用arr[1:999][1]，你仍然会得到一个二维数组，但只有第一个元素。在这种情况下是{{1}, {2}, {5}}。如果我们使用unnest()，我们可以将它转换成一个集合，然后通过子查询将其馈送到array_agg()中。

使用array_agg(unnest(arr[1:999][1]))也是不错的选择，但聚合函数不喜欢集合，我不知道是否有一种方法可以即时转换。

您也可以使用实际数组长度，但这可能会导致不必要的计算。

SELECT unnest(arr[1:array_length(arr, 1)][1]) as arr from data

注意

如果数组可以解嵌套一级，你只需要对数组进行索引，然后使用array_agg()将其转换回一个数组，语法会更加简单。

WITH data AS
  (SELECT array[1,2,3] as arr
   UNION ALL SELECT array[2,15,32] as arr
   UNION ALL SELECT array[5,16,14] as arr)
SELECT array_agg(arr[1]) from data;

CTE仅用于输入数据，真正的核心在于array_agg(arr[1])。当然，这对任意数量的输入数组都适用。

- Sami Kuhmonen

---

应用于一组行的查询结果不正确。 - Adam Piotrowski

没有使用unnest的相同查询返回了我所需的数组。 - Jarrod L

Answer 2

给定以下表格和数值：

CREATE TABLE arrtbl (
   arrtbl_id serial PRIMARY KEY
 , arr int[]
);

INSERT INTO arrtbl (arr) VALUES 
  ('{{1,2,3},{2,15,32},{5,16,14}}')
, ('{{17,22},{1,15},{16,14}}')   -- dimensions can vary across rows!
, ('{}')
, (null);

这将为所有行完成此任务：

SELECT arrtbl_id, array_agg(a) AS a1
FROM   arrtbl t
    ,  unnest(t.arr[:][1]) a
GROUP  BY 1;

为什么使用[:]？

• 将一维数组的下标规范化，使其从1开始

只有在可能存在非标准数组下标的情况下才需要使用。

结果:

arrtbl_id | a1
----------+-----------
1         | '{1,2,5}'
2         | '{17,1,16}'

如果arr中包含空/NULL数组，则这些行将从结果中删除。
此外，虽然上述方法通常有效，但最好使用以下的安全语法：

SELECT arrtbl_id, array_agg(a.a ORDER BY a.ordinality)
FROM   arrtbl t
LEFT   JOIN LATERAL unnest(t.arr[:][1]) WITH ORDINALITY a ON true
GROUP  BY 1;

在子查询中，相同、更明确和单一排序通常更快：

SELECT arrtbl_id, array_agg(elem)
FROM  (
   SELECT t.arrtbl_id, a.elem
   FROM   arrtbl t
   LEFT   JOIN LATERAL unnest(t.arr[:][1]) WITH ORDINALITY a(elem, ord) ON true
   ORDER  BY t.arrtbl_id, a.ord
   ) sub
GROUP  BY 1
ORDER  BY 1;

结果：

arrtbl_id | a1
----------+-----------
1         | '{1,2,5}'
2         | '{17,1,16}'
3         | null
4         | null

db<>fiddle 这里

详细说明：

• PostgreSQL unnest() 函数的元素编号

- Erwin Brandstetter