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

How to select every first element of array of integer arrays to array?
{{1,2,3},{2,15,32},{5,16,14},...} -> {1,2,5,...}

如何选择整数数组的每个第一个元素到数组？ {{1,2,3}，{2,15,32}，{5,16,14}，...} - > {1,2,5，...}

3 个解决方案

#1

Since PostgreSQL will allow asking for a slice outside of the array size, and assuming there will never be more than 999 subarrays, we can use this monstrosity

由于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;

You can of course make the constant 999 larger if needed, it is just a random large number I threw in there.

你当然可以根据需要增加999的常数，这只是我投入的一个随机大数字。

The reason why this is so complicated is that if you would use just arr[1:999][1] you would still get a two-dimensional array, but with only the first elements. In this case {{1}, {2}, {5}}. If we use unnest() we can make it into a set, which can then be fed into array_agg() via subselect.

之所以如此复杂，是因为如果你只使用arr [1：999] [1]，你仍会得到一个二维数组，但只有第一个元素。在这种情况下{{1}，{2}，{5}}。如果我们使用unnest（），我们可以将它变成一个集合，然后可以通过subselect将其输入到array_agg（）中。

It would be nice to use array_agg(unnest(arr[1:999][1])) but the aggregation function doesn't like sets and I don't know if there is a way to convert it on the fly.

使用array_agg（unexst（arr [1：999] [1]））会很好，但聚合函数不喜欢集合，我不知道是否有办法在运行中转换它。

You can also use the actual array length, but it might cause unnecessary computation

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

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

Note

注意

If the arrays could be unnested by one level, you could just index the arrays and then use array_agg() to convert it back into an array with a lot simpler syntax

如果数组可以被一个级别取消，你可以只索引数组，然后使用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;

The CTE is there just for input data, the actual meat is the array_agg(arr[1]). This will of course work for any number of input arrays.

CTE只用于输入数据，实际的肉是array_agg（arr [1]）。这当然适用于任意数量的输入数组。

#2

Given this table:

鉴于此表：

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

Sample values:

样本值：

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);

This would do the job for all rows:

这将完成所有行的工作：

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

Why [-2147483648:2147483647]?

为什么[-2147483648：2147483647]？

• Normalize array subscripts for 1-dimensional array so they start with 1
• 规范化1维数组的数组下标，使它们从1开始

Result:

结果：

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

Note that rows with empty / NULL array in arr are dropped from the result.
Also, while the above usually works, rather use this safe syntax:

请注意，arr中的空/ NULL数组的行将从结果中删除。此外，虽然上述通常有效，但请使用以下安全语法：

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

Result:

结果：

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

Detailed explanation:

详细说明：

• PostgreSQL unnest() with element number
• 带有元素编号的PostgreSQL unnest（）

#3

Plpgsql solution:

Plpgsql解决方案：

create or replace function first_elements(arr anyarray)
returns int[] language plpgsql
as $$
declare
    i int;
    res int[];
begin
    for i in 1..array_length(arr, 1) loop
        res = array_append(res, arr[i][1]);
    end loop;
    return res;
end $$;  

with test as (
    select array[array[1,2,3], array[2,15,32], array[5,16,14]] a
    union
    select array[array[101,0], array[102,0]] a
    )
select first_elements(a) from test;

 first_elements
----------------
 {1,2,5}
 {101,102}
(2 rows)