在 postgresql 中对用 array_agg 创建的文本聚合进行排序
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/3461470/
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
Sort a text aggregate created with array_agg in postgresql
提问by Richard
I have a table in postgresql. The following table "animals" will do to explain my problem:
我在 postgresql 中有一张表。下表“动物”将解释我的问题：
name
------
tiger
cat
dog
Now I am using the following query:
现在我使用以下查询：
SELECT
array_to_string(array_agg("name"), ', ')
FROM
animals;
The result is: "tiger, cat, dog". But I would like to sort the aggregate, before it is converted into a string. So this is the result I am hoping for:
结果是：“老虎，猫，狗”。但是我想在将聚合转换为字符串之前对其进行排序。所以这是我希望的结果：
"cat, dog, tiger".
So how can I sort an string array in postgresql 8.4 before converting it to a string. ORDER BY on the row "name" does not work and the built-in sort function processes only integer values.
那么如何在将 postgresql 8.4 中的字符串数组转换为字符串之前对其进行排序。行“名称”上的 ORDER BY 不起作用，内置排序函数仅处理整数值。
Anyone a good idea, how to solve this in pure SQL?
任何一个好主意，如何在纯SQL中解决这个问题？
Thanx a lot Richard
非常感谢理查德
采纳答案by Matthew Wood
This will be available in PostgreSQL 9.0:
这将在 PostgreSQL 9.0 中可用：
http://www.postgresql.org/docs/9.0/static/release-9-0.html, Section E.1.3.6.1. Aggregates
http://www.postgresql.org/docs/9.0/static/release-9-0.html，第 E.1.3.6.1 节。聚合体
In the meantime, you could do something like this which may solve the problem (albeit clunky):
与此同时，你可以做这样的事情，这可能会解决问题（虽然笨重）：
SELECT array_agg(animal_name)
FROM (
SELECT "name" AS animal_name
FROM animals
ORDER BY "name"
) AS sorted_animals;
回答by Mike T
For modern PostgreSQL (since version 9.0), you can use an ORDER BYclause in an aggregate expression:
对于现代 PostgreSQL（从 9.0 版开始），您可以ORDER BY在聚合表达式中使用子句：
SELECT
array_to_string(array_agg(name ORDER BY name), ', ')
FROM
animals;
Also, for your specific purpose, you can use string_aggto simplify your query:
此外，对于您的特定目的，您可以使用string_agg来简化您的查询：
SELECT
string_agg(name, ', ' ORDER BY name)
FROM
animals;
回答by Joey Adams
Although Matthew Wood's answer is better for your case, here is a way to sort arrays in PostgreSQL 8.4 and up:
尽管 Matthew Wood 的答案更适合您的情况，但这里有一种在 PostgreSQL 8.4 及更高版本中对数组进行排序的方法：
SELECT array(
SELECT unnest(array[3,2,1]) AS x ORDER BY x
);
Knowing the arrayand unnestfunctions can be handy, since it also lets you do things like "map"over an array:
了解array和unnest函数会很方便，因为它还可以让您对数组进行“映射”之类的操作：
SELECT array(
SELECT x*x FROM (SELECT unnest(array[1,2,3]) AS x) as subquery
);
Again, this can be yours for the price of PostgreSQL 8.4 .
同样，以 PostgreSQL 8.4 的价格，这可以是你的。
回答by dave
Have you tried to use generate_series()on the array, and then do a SELECT...ORDER BYon that result (or just nest it inside of the SELECT) before you convert it to a string?
您是否尝试generate_series()在数组上使用，然后在将其转换为字符串之前SELECT...ORDER BY对该结果执行操作（或仅SELECT将其嵌套在 中）？
回答by jnas
Still, for version 8.4, using the solution suggested by Matthew Wood, if you need to do a grouping in the outer query, the inner query should be sorted, too, for the sorting to be consistent.
尽管如此，对于 8.4 版本，使用 Matthew Wood 建议的解决方案，如果您需要在外部查询中进行分组，则内部查询也应进行排序，以使排序保持一致。
SELECT family, array_agg(animal_name)
FROM (
SELECT family, "name" AS animal_name
FROM animals
ORDER BY family, "name"
) AS sorted_animals
group by family;
回答by Anna
To update on this question, Snowflake has implemented array sorting:
为了更新这个问题，Snowflake 实现了数组排序：
SELECT
array_sort(array_agg("name")
FROM
animals;
Can also use array_sort_byto sort an object
也可array_sort_by用于对对象进行排序
