如何从2d数组中选择1d数组? [英] How to select 1d array from 2d array?
问题描述
我有2d数组,只想选择它的第一个元素,即1d数组。
我该怎么做?
I have 2d array and want to select only first element of it, which is 1d array.
How do I do that?
推荐答案
获取
To get the first slice of an array:
SELECT my_arr[1:1];
结果数组具有相同的数组尺寸作为输入。
在上一个答案中的详细信息:
The resulting array has the same array dimensions as the input.
Details in my previous answer here:
- Unnest array by one level
To 展平结果:
SELECT ARRAY(SELECT unnest(my_arr[1:1]));
或更干净:
SELECT ARRAY(SELECT * FROM unnest(my_arr)[1:1]));
示例
Examples
SELECT (ARRAY[[1,2,3], [4,5,6], [7,8,9]])[1:1];
结果:
{{1,2,3}} -- 2D array
或:
SELECT ARRAY(
SELECT unnest((ARRAY[[1,2,3], [4,5,6], [7,8,9]])[1:1])
);
结果:
{1,2,3} -- 1D array
模拟<$ c $ Postgres 8.3中的c> unnest()
回复您的评论:
您链接到的Wiki页面有点误导。我用2维数组的代码对其进行了更新。
Emulate unnest()
in Postgres 8.3
Response to your comment:
The Wiki page you are linking to was a bit misleading. I updated it with code for 2-dimensional arrays.
unnest()
1维数组:
CREATE OR REPLACE FUNCTION unnest_1d(anyarray)
RETURNS SETOF anyelement AS
$func$
SELECT $1[i]
FROM generate_series(array_lower($1,1), array_upper($1,1)) i
$func$ LANGUAGE sql IMMUTABLE;
unnest()
用于二维数组:
CREATE OR REPLACE FUNCTION unnest_2d(anyarray)
RETURNS SETOF anyelement AS
$func$
SELECT $1[d1][d2]
FROM generate_series(array_lower($1,1), array_upper($1,1)) d1
, generate_series(array_lower($1,2), array_upper($1,2)) d2
$func$ LANGUAGE sql IMMUTABLE;
聚合函数 array_agg()
不是默认安装在Postgres 8.3中:
The aggregate function array_agg()
is not installed by default in Postgres 8.3:
CREATE AGGREGATE array_agg(anyelement) (
SFUNC = array_append,
STYPE = anyarray,
INITCOND = '{}'
);
将2d数组嵌套到1d数组:
Unnest 2d array to 1d arrays:
CREATE OR REPLACE FUNCTION unnest_2d_1d(anyarray)
RETURNS SETOF anyarray AS
$func$
SELECT array_agg($1[d1][d2])
FROM generate_series(array_lower($1,1), array_upper($1,1)) d1
, generate_series(array_lower($1,2), array_upper($1,2)) d2
GROUP BY d1
ORDER BY d1
$func$ LANGUAGE sql IMMUTABLE;
这篇关于如何从2d数组中选择1d数组?的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!