postgresql 排序数组元素
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/2913368/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Sorting array elements
提问by giri
I want to write a stored procedure that gets an array as input parameter and sort that array and return the sorted array.
我想编写一个存储过程，它获取一个数组作为输入参数并对该数组进行排序并返回排序后的数组。
Kindly help.
请帮忙。
回答by Craig Ringer
The best way to sort an array of integers is without a doubt to use the intarray extension, which will do it much, much, much faster than any SQL formulation:
毫无疑问，对整数数组进行排序的最佳方法是使用intarray 扩展，它比任何 SQL 公式都快得多：
CREATE EXTENSION intarray;
SELECT sort( ARRAY[4,3,2,1] );
A function that works for any array type is:
适用于任何数组类型的函数是：
CREATE OR REPLACE FUNCTION array_sort (ANYARRAY)
RETURNS ANYARRAY LANGUAGE SQL
AS $$
SELECT ARRAY(SELECT unnest() ORDER BY 1)
$$;
(I've replaced my version with Pavel's slightly faster oneafter discussion elsewhere).
（在其他地方讨论后，我已经用Pavel 稍微快一点的版本替换了我的版本）。
回答by Tometzky
In PostrgreSQL 8.4 and up you can use:
在 PostrgreSQL 8.4 及更高版本中，您可以使用：
select array_agg(x) from (select unnest(ARRAY[1,5,3,7,2]) AS x order by x) as _;
But it will not be very fast.
但它不会很快。
In older Postgres you can implement unnest like this
在较旧的 Postgres 中，您可以像这样实现 unnest
CREATE OR REPLACE FUNCTION unnest(anyarray)
RETURNS SETOF anyelement AS
$BODY$
SELECT [i] FROM
generate_series(array_lower(,1),
array_upper(,1)) i;
$BODY$
LANGUAGE 'sql' IMMUTABLE
And array_agg like this:
和 array_agg 像这样：
CREATE AGGREGATE array_agg (
sfunc = array_append,
basetype = anyelement,
stype = anyarray,
initcond = '{}'
);
But it will be even slower.
但它会更慢。
You can also implement any sorting algorithm in pl/pgsql or any other language you can plug in to postgres.
您还可以在 pl/pgsql 或任何其他可以插入 postgres 的语言中实现任何排序算法。
回答by Frank Heikens
Just use the function unnest():
只需使用函数 unnest()：
SELECT
unnest(ARRAY[1,2]) AS x
ORDER BY
x DESC;
See array functionsin the Pg docs.
请参阅Pg 文档中的数组函数。
回答by Adam Gent
This worked for me from http://www.pgsql.cz/index.php/PostgreSQL_SQL_Tricks_I#General_array_sort
这从http://www.pgsql.cz/index.php/PostgreSQL_SQL_Tricks_I#General_array_sort对我有用
CREATE OR REPLACE FUNCTION array_sort (ANYARRAY)
RETURNS ANYARRAY LANGUAGE SQL
AS $$
SELECT ARRAY(
SELECT [s.i] AS "foo"
FROM
generate_series(array_lower(,1), array_upper(,1)) AS s(i)
ORDER BY foo
);
$$;
Please see Craig's answer since he is far more more knowledgable on Postgres and has a better answer. Also if possible vote to delete my answer.
请参阅 Craig 的回答，因为他对 Postgres 的了解要多得多，并且有更好的答案。另外，如果可能的话，投票删除我的答案。
回答by Shankar
Very nice exhibition of PostgreSQL's features is general procedure for sorting by David Fetter.
PostgreSQL 特性的一个很好的展示是 David Fetter 的一般排序过程。
CREATE OR REPLACE FUNCTION array_sort (ANYARRAY)
RETURNS ANYARRAY LANGUAGE SQL
AS $$
SELECT ARRAY(
SELECT [s.i] AS "foo"
FROM
generate_series(array_lower(,1), array_upper(,1)) AS s(i)
ORDER BY foo
);
$$;
回答by Joshua Burns
If you're looking for a solution which will work across any data-type, I'd recommend taking the approach laid out at YouLikeProgramming.com.
如果您正在寻找适用于任何数据类型的解决方案，我建议您采用YouLikeProgramming.com 上列出的方法。
Essentially, you can create a stored procedure (code below) which performs the sorting for you, and all you need to do is pass your array to that procedure for it to be sorted appropriately.
本质上，您可以创建一个存储过程（下面的代码）来为您执行排序，您需要做的就是将数组传递给该过程，以便对其进行适当的排序。
I have also included an implementation which does not require the use of a stored procedure, if you're looking for your query to be a little more transportable.
我还包含了一个不需要使用存储过程的实现，如果您正在寻找更易于传输的查询。
Creating the stored procedure
创建存储过程
DROP FUNCTION IF EXISTS array_sort(anyarray);
CREATE FUNCTION
array_sort(
array_vals_to_sort anyarray
)
RETURNS TABLE (
sorted_array anyarray
)
AS $BODY$
BEGIN
RETURN QUERY SELECT
ARRAY_AGG(val) AS sorted_array
FROM
(
SELECT
UNNEST(array_vals_to_sort) AS val
ORDER BY
val
) AS sorted_vals
;
END;
$BODY$
LANGUAGE plpgsql;
Sorting array values (works with any array data-type)
排序数组值（适用于任何数组数据类型）
-- The following will return: {1,2,3,4}
SELECT ARRAY_SORT(ARRAY[4,3,2,1]);
-- The following will return: {in,is,it,on,up}
SELECT ARRAY_SORT(ARRAY['up','on','it','is','in']);
Sorting array values without a stored procedure
在没有存储过程的情况下对数组值进行排序
In the following query, simply replace ARRAY[4,3,2,1]with your array or query which returns an array:
在以下查询中，只需替换ARRAY[4,3,2,1]为您的数组或返回数组的查询：
WITH
sorted_vals AS (
SELECT
UNNEST(ARRAY[4,3,2,1]) AS val
ORDER BY
val
)
SELECT
ARRAY_AGG(val) AS sorted_array
FROM
sorted_vals
... or ...
... 或者 ...
SELECT
ARRAY_AGG(vals.val) AS sorted_arr
FROM (
SELECT
UNNEST(ARRAY[4,3,2,1]) AS val
ORDER BY
val
) AS vals
