选择整型数组的数组，数组的每一个元素 [英] Select every first element of array of integer arrays to array

问题描述

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

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,...}

推荐答案

因为PostgreSQL将允许要求一个切片数组的大小之外，并假设永远不会有超过999个子阵列，我们可以用这个怪物

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

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更大的如果需要的话，它仅仅是一个随机一大批我扔在那里。

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

之所以这样，是如此复杂的是，如果你只使用改编[1：999] [1] 你仍然会得到一个二维数组，但与只在第一个元素。在这种情况下 {{1}，{2}，{5}} 。如果我们使用 UNNEST（）我们可以把它变成一个集合，然后可以通过（）送入 ARRAY_AGG子查询中。

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.

这将是很好使用 ARRAY_AGG（UNNEST（ARR [1：999] [1]））但聚合函数不喜欢集，我不知道是否有将其转换上飞的方式。

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.

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

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

注意

如果该阵列可以由一个级别嵌套的，你可以只索引数组，然后使用 ARRAY_AGG（）将其转换回了很多简单的语法定义的数组

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

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]）。当然，这种工作意愿任意数量的输入阵列。

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.

这篇关于选择整型数组的数组，数组的每一个元素的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！