如何排序PostgreSQL中二维int数组? [英] How to sort two dimensional int array in PostgreSQL?
问题描述
{{5,23},{8.45},{1,12}}
我要根据每个子数组元素的第一个元素进行排序这阵,等;
{{ 1 中,12},{ 5 中,23},{ 8 45 }}
我怎么能这样做?
编辑:
这code工作;
创建或替换功能arraysortingaccordingfirstindexofsubarrayelements()
返回void作为$$
声明samplearraydata整数[] [];
声明sortedarraydata INT [] [];
开始
samplearraydata:= ARRAY [[5,8],[1,6],[3,9]];
执行'CREATE TABLE TEMP不是Temptable(
firstindex整数,
secondindex整数
)在提交降;';
WITH
数据(选择samplearraydata为ARR)
插入不是Temptable选择
改编由[i] [1],
改编[I] [2]的数据,
generate_subscripts((SELECT ARR从数据中),1)我
通过1级;
sortedarraydata:=(SELECT array_agg_mult(ARRAY [ARRAY [y.firstindex,y.secondindex]))FROM不是Temptableÿ;
提高通知'%',sortedarraydata;
结束;
$$语言PLPGSQL;CREATE AGGREGATE array_agg_mult(anyarray的)(
SFUNC = array_cat
,STYPE = anyarray的
,INITCOND ='{}'
);CREATE TABLE TEMP arrtbl(
firstindex整数,
secondindex整数
)在提交降;
贷欧文:)
聚合函数
内置的聚合函数 ARRAY_AGG()
当前(Postgres的9.4)仅适用于非数组的输入数据类型。既然我们要聚集的阵列的,我们需要想在这个相关答案详细的自定义聚合函数:
CREATE AGGREGATE array_agg_mult(anyarray的)(
SFUNC = array_cat
,STYPE = anyarray的
,INITCOND ='{}'
);
即将推出的的Postgres 9.5 船舶此聚集函数的内置版本(这是相当快)。 <一href=\"http://www.postgresql.org/docs/devel/static/functions-aggregate.html#FUNCTIONS-AGGREGATE-TABLE\"相对=nofollow>每DEVEL文档:
函数参数类型(S)返回类型
ARRAY_AGG(如pression)任何数组类型相同的参数的数据类型描述
输入数组串连成一个高维数组
(投入必须都具有相同的维度,并且不能为空或NULL)
块引用>访问数组
需要数组语法/如何访问数组的基本理解。阅读本章rel=\"nofollow\">,如果你不在那里,还没有。
查询
然后,根据今天相同的设置,因为这前面的回答(你可能想读的那一个,太):
SELECT arrtbl_id
,array_agg_mult(ARR [I:我] [LO2:UP2] ORDER BY改编[I:我] [LO2])
FROM(SELECT arrtbl_id,编曲
,array_lower(ARR,2)液氧
,array_upper(ARR,2)UP2
FROM arrtbl)笔
,generate_subscripts(t.arr,1)我
GROUP BY 1;说明
计算下限和上限在基表第2个数组维度,这是不是重复它为每一个数组片断便宜。
我不是简单地用指数1日开始支付可能的角落情况。 Postgres允许非标数组下标:
横向
加入的基表generate_subscripts(ARR,1)
来获得的第一个索引每个子阵列(无论多少尺寸)。这位前pression
改编[I:我] [LO2:UP2]
返回每个数组片断preserving尺寸。这适用于任何数量的元素和尺寸(大于1)。这位前pression
改编[I:我] [LO2:液氧]
返回每个阵列片,它定义排序次序中的第一个切片。对于严格2- dimnesional阵列,可以使用改编[I] [LO2]
返回第一的元素的每片为好,但前者作品的任何的维数大于1。与总
array_agg_mult()
这愉快地接受了(一切属于地方很好)合适的尺寸值。或者使用内置的ARRAY_AGG()
在PG 9.5+阵列。{{5,23}, {8,45}, {1,12}}
I want to sort this array according the first element of each sub-array elements, like;
{{1,12}, {5,23}, {8,45}}
How can I do that?
Edit:
This code works;
create or replace function arraysortingaccordingfirstindexofsubarrayelements() returns void as $$ declare samplearraydata integer[][]; declare sortedarraydata int[][]; begin samplearraydata:=ARRAY[[5,8], [1,6],[3,9]]; EXECUTE 'CREATE TEMP TABLE temptable ( firstindex integer, secondindex integer ) on commit drop;'; WITH data as (select samplearraydata as arr) insert into temptable select arr[i][1], arr[i][2] FROM data, generate_subscripts((SELECT arr FROM data), 1) i order by 1; sortedarraydata:=(SELECT array_agg_mult(ARRAY[ARRAY[y.firstindex, y.secondindex]])) FROM temptable y; raise notice '%', sortedarraydata; end; $$ language plpgsql; CREATE AGGREGATE array_agg_mult (anyarray) ( SFUNC = array_cat ,STYPE = anyarray ,INITCOND = '{}' ); CREATE TEMP TABLE arrtbl ( firstindex integer, secondindex integer ) on commit drop;
Credits to Erwin:)
解决方案Aggregate function
The built-in aggregate function
array_agg()
currently (Postgres 9.4) only works for non-array input data types. Since we are going to aggregate arrays, we need a custom aggregate function like detailed in this related answer:CREATE AGGREGATE array_agg_mult (anyarray) ( SFUNC = array_cat ,STYPE = anyarray ,INITCOND = '{}' );
The upcoming Postgres 9.5 ships a built-in version of this aggregate function (which is considerably faster). Per devel documentation:
Function Argument Type(s) Return Type array_agg(expression) any array type same as argument data type Description input arrays concatenated into array of one higher dimension (inputs must all have same dimensionality, and cannot be empty or NULL)
Accessing array
Basic understanding of array syntax / how to access arrays is required. Read this chapter of the manual if you aren't there, yet.
Query
Then, based on the same setup as this earlier answer today (you may want to read that one, too):
SELECT arrtbl_id , array_agg_mult(arr[i:i][lo2:up2] ORDER BY arr[i:i][lo2]) FROM (SELECT arrtbl_id, arr , array_lower(arr,2) AS lo2 , array_upper(arr,2) AS up2 FROM arrtbl) t , generate_subscripts(t.arr,1) i GROUP BY 1;
Explanation
Compute lower and upper bound for the 2nd array dimension in the base table, that's cheaper than repeating it for every array slice.
I am not simply starting with index 1 to cover a possible corner case. Postgres allows non-standard array subscripts:
LATERAL
join the base table togenerate_subscripts(arr,1)
to get the first index for each sub-array (no matter how many dimensions).The expression
arr[i:i][lo2:up2]
returns each array slice preserving dimensions. This works for any number of elements and dimensions (greater than 1).The expression
arr[i:i][lo2:lo2]
returns the first slice within each array slice, which defines sort order. For strictly 2-dimnesional arrays, you can usearr[i][lo2]
to return the first element of each slice as well, but the former works for any dimensionality greater than 1.Aggregate with
array_agg_mult()
which happily accepts values of appropriate dimension (everything falls into place nicely). Or use the built-inarray_agg()
for arrays in pg 9.5+.
这篇关于如何排序PostgreSQL中二维int数组?的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!