UNNEST数组一个级别 [英] Unnest array by one level
问题描述
我要带阵列 N
尺寸和回报集合包含 N-1
尺寸的阵列排。例如,以数组 ARRAY [[1,2,3],[4,5,6],[7,8,9]]
键,返回一组 {1,2,3},{4,5,6},{7,8,9}
。使用UNNEST返回集合 1,2,3,4,5,6,7,8,9
。
我试过和PostgreSQL 8.4,这似乎是它抓住了UNNEST函数会做什么,我在寻找:
CREATE OR REPLACE FUNCTION tstng.unnest2(anyarray的)
RETURNS SETOF为anyelement
语言PLPGSQL
IMMUTABLE
AS $$
开始
返回查询选择$ 1 [I]
从generate_series(array_lower($ 1,1),array_upper($ 1,1))我;
结束;
$$;
然而, SELECT tstng.unnest2(ARRAY [[1,2,3],[4,5,6],[7,8,9]]);
返回集合,
(即:3个空行)。
我还发现, SELECT(ARRAY [[1,2,3],[4,5,6],[7,8,9]])[0];
返回null,我认为是我的问题的根源。
说明
SELECT(ARRAY [[1,2,3],[4,5,6],[7,8,9]])[0]
返回相同的
SELECT(ARRAY [[1,2,3],[4,5,6],[7,8,9]])[17]
这是NULL。我引用关于该事项中的文档:
缺省情况下,阵列的尺寸的下界指数值
设置为1。
块引用>
0
在这里有没有特殊含义。
此外,与二维数组,你需要的两个指标,以获得一个基本元素。像这样的:SELECT(ARRAY [[1,2,3],[4,5,6],[7,8,9]])[1] [2]
结果:
2
你的信息的第一部分是有点不清楚。
SELECT array_dims(ARRAY [[1,2,3],[4,5,6],[7,8,9]])
结果:
[1:3] [1:3]
这就是两个的尺寸有3个元素(1〜3)各(9基本元素)。结果
如果你想N-1
尺寸,那么这是一个正确的结果:选择Array(SELECT UNNEST('{{1,2,3},{4,5,6},{7,8,9}}':: INT [] ))
结果:
{} 1,2,3,4,5,6,7,8,9
这就是有一个的尺寸。
UNNEST()
总是产生每行一个基本元素。我不知道是什么导致你的愿望完全吻合。你举的例子只是另一种2 dimenstional阵列缺少一套大括号...?{1,2,3},{4,5,6},{7,8,9}
如果你想在阵列的片,试试这个符号:
SELECT(ARRAY [[1,2,3],[4,5,6],[7,8,9]])[1:2]
结果:
{{1,2,3},{4,5,6}}
或者这样:
SELECT(ARRAY [[1,2,3],[4,5,6],[7,8,9]])[2:2] [1:2 ]
结果:
{{4,5}}
要平展的结果(获得一维数组):
在手册中了解更多这里。
功能
这是卢卡斯发布功能:
CREATE OR REPLACE FUNCTION unnest_2d_1d(anyarray的)
RETURNS SETOF anyarray的AS
$ FUNC $
SELECT ARRAY_AGG($ 1 [D1] [D2])
从generate_subscripts($ 1,1)D1
,generate_subscripts(1,2 $)D2
GROUP BY D1
ORDER BY D1
$ FUNC $
SQL语言不可变的;有关的Postgres版本< 8.4,
ARRAY_AGG()
不是默认安装的。首先创建一个:CREATE AGGREGATE ARRAY_AGG(anyelement的)(
SFUNC = array_append,
STYPE = anyarray的,
INITCOND ='{}'
);此外,
generate_subscripts()
是不是天生的,没有。使用来代替:...
从generate_series(array_lower($ 1,1),array_upper($ 1,1))D1
,generate_series(array_lower(1,2 $),array_upper($ 1,2))D2
...电话:
SELECT unnest_2d_1d(ARRAY [1,2],[3,4],[5,6]]);
结果
{1,2}
{3,4}
{5,6}I want to take an array of
n
dimensions and return set containing rows of arrays ofn-1
dimensions. For example, take the arrayARRAY[[1,2,3], [4,5,6], [7,8,9]]
and return a set{1,2,3}, {4,5,6}, {7,8,9}
. Using unnest returns the set1,2,3,4,5,6,7,8,9
.I tried grabbing the unnest function from PostgreSQL 8.4, which seems like it would do what I'm looking for:
CREATE OR REPLACE FUNCTION tstng.unnest2(anyarray) RETURNS SETOF anyelement LANGUAGE plpgsql IMMUTABLE AS $$ BEGIN RETURN QUERY SELECT $1[i] FROM generate_series(array_lower($1,1), array_upper($1,1)) i; END; $$;
However,
SELECT tstng.unnest2(ARRAY[[1,2,3], [4,5,6], [7,8,9]]);
returns the set, ,
(i.e.: 3 null rows).I've also found that
SELECT (ARRAY[[1,2,3], [4,5,6], [7,8,9]])[0];
returns null, which I believe to be the root of my problem.解决方案Explain
SELECT (ARRAY[[1,2,3], [4,5,6], [7,8,9]])[0]
returns the same as
SELECT (ARRAY[[1,2,3], [4,5,6], [7,8,9]])[17]
which is NULL. I quote the docs on that matter:
By default, the lower bound index value of an array's dimensions is set to one.
0
has no special meaning here. Also, with a two-dimensional arrays, you need two indexes to get a base element. Like this:SELECT (ARRAY[[1,2,3], [4,5,6], [7,8,9]])[1][2]
Result:
2
The first part of your message is a bit unclear.
SELECT array_dims(ARRAY[[1,2,3], [4,5,6], [7,8,9]])
Result:
[1:3][1:3]
That's two dimensions with 3 elements (1 to 3) each (9 base elements).
If you wantn-1
dimensions then this is a correct result:SELECT ARRAY (SELECT unnest('{{1,2,3}, {4,5,6}, {7,8,9}}'::int[]))
Result:
{1,2,3,4,5,6,7,8,9}
That's one dimension.
unnest()
always produces one base element per row. I am not sure what result you desire exactly . Your example is just another 2-dimenstional array with a missing set of curly brackets ... ?{1,2,3}, {4,5,6}, {7,8,9}
If you want a slice of the array, try this notation:
SELECT (ARRAY[[1,2,3], [4,5,6], [7,8,9]])[1:2]
Result:
{{1,2,3},{4,5,6}}
Or this:
SELECT (ARRAY[[1,2,3], [4,5,6], [7,8,9]])[2:2][1:2]
Result:
{{4,5}}
To flatten the result (get a 1D array):
Read more in the manual here.
Function
This is an improved and simplified version of the function Lukas posted:
CREATE OR REPLACE FUNCTION unnest_2d_1d(anyarray) RETURNS SETOF anyarray AS $func$ SELECT array_agg($1[d1][d2]) FROM generate_subscripts($1,1) d1 , generate_subscripts($1,2) d2 GROUP BY d1 ORDER BY d1 $func$ LANGUAGE sql IMMUTABLE;
For Postgres versions < 8.4,
array_agg()
is not installed by default. Create it first:CREATE AGGREGATE array_agg(anyelement) ( SFUNC=array_append, STYPE=anyarray, INITCOND='{}' );
Also,
generate_subscripts()
is not born, yet. Use instead:... FROM generate_series(array_lower($1,1), array_upper($1,1)) d1 , generate_series(array_lower($1,2), array_upper($1,2)) d2 ...
Call:
SELECT unnest_2d_1d(ARRAY[[1,2], [3,4], [5,6]]);
Result
{1,2} {3,4} {5,6}
这篇关于UNNEST数组一个级别的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!