将数组取消一层 [英] 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.
I want to take an array of n dimensions and return set containing rows of arrays of n-1 dimensions. For example, take the array ARRAY[[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 set 1,2,3,4,5,6,7,8,9.
我尝试从 PostgreSQL 8.4 中获取 unnest 函数，这似乎可以满足我的要求:
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;
$$;
然而，SELECT tstng.unnest2(ARRAY[[1,2,3], [4,5,6], [7,8,9]]); 返回集合 , ,(即:3 个空行).
However, SELECT tstng.unnest2(ARRAY[[1,2,3], [4,5,6], [7,8,9]]); returns the set , , (i.e.: 3 null rows).
我还发现 SELECT (ARRAY[[1,2,3], [4,5,6], [7,8,9]])[0]; 返回null，我认为这是我问题的根源.
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.
推荐答案
说明
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.我引用了关于此事的文档:
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 在这里没有特殊意义.此外，对于二维数组，您需要两个索引来获取基本元素.像这样:
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]
结果:
2
<小时>
您邮件的第一部分有点不清楚.
The first part of your message is a bit unclear.
SELECT array_dims(ARRAY[[1,2,3], [4,5,6], [7,8,9]])
结果:
[1:3][1:3]
这是两个维度，每个维度有 3 个元素(1 到 3 个)(9 个基本元素).
如果您想要 n-1 维，那么这是一个正确的结果:
That's two dimensions with 3 elements (1 to 3) each (9 base elements).
If you want n-1 dimensions then this is a correct result:
SELECT ARRAY (SELECT unnest('{{1,2,3}, {4,5,6}, {7,8,9}}'::int[]))
结果:
{1,2,3,4,5,6,7,8,9}
这是一个维度.unnest() 总是每行产生一个基本元素.我不确定你想要什么结果.您的示例只是另一个缺少一组大括号的二维数组......?
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}
<小时>
如果你想要一个数组切片，试试这个表示法:
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}}
要展平结果(获得一维数组):
To flatten the result (get a 1D array):
阅读更多此处的手册.
后来的测试表明，这个 plpgsql 函数快得多.需要 Postgres 9.1 或更高版本:
Later test revealed that this plpgsql function is much faster. Requires Postgres 9.1 or later:
CREATE OR REPLACE FUNCTION unnest_2d_1d(ANYARRAY, OUT a ANYARRAY)
RETURNS SETOF ANYARRAY AS
$func$
BEGIN
FOREACH a SLICE 1 IN ARRAY $1 LOOP
RETURN NEXT;
END LOOP;
END
$func$ LANGUAGE plpgsql IMMUTABLE;
见:
这是Lukas 发布的函数的改进和简化版本:
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;
对于 Postgres 版本 <8.4、array_agg()默认没有安装.先创建:
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='{}'
);
另外，generate_subscripts() 还没有出生.改用:
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
...
调用:
SELECT unnest_2d_1d(ARRAY[[1,2], [3,4], [5,6]]);
结果
{1,2}
{3,4}
{5,6}
这篇关于将数组取消一层的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
