SQL 如何将两个 PostgreSQL 列聚合到由括号分隔的数组中
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/39212832/
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
How to aggregate two PostgreSQL columns to an array separated by brackets
提问by Mattijn
I would like to concatenate two columns using a group-by query resulting in an array separed with brackets. I know this question is related to thisquestion, but as usual my use-case is a little different.
我想使用 group-by 查询连接两列，从而产生一个用括号分隔的数组。我知道这个问题与这个问题有关，但像往常一样，我的用例有点不同。
A simple example (also as SQL Fiddle). Currently my query returns the following:
一个简单的例子（也作为SQL Fiddle）。目前我的查询返回以下内容：
ID X Y
3 0.5 2.71
3 1.0 2.50
3 1.5 2.33
6 0.5 2.73
6 1.5 2.77
But where I would like concatenate/aggregate the X/Ycolumns to get the following:
但是我想连接/聚合X/Y列以获得以下内容：
ID XY
3 [[0.5,2.71],[1.0,2.50],[1.5,2.33]]
6 [[0.5,2.73],[1.5,2.77]]
Currently I've tried to concatenate the columns into one as follows:
目前，我尝试将列连接成一列，如下所示：
SELECT "ID",concat_ws(', ',"X", "Y") as XY FROM Table1;
Which returns:
返回：
ID xy
3 0.5, 2.71
3 1, 2.50
3 1.5, 2.33
6 0.5, 2.73
And used array_agg():
并使用array_agg()：
SELECT "ID",array_to_string(array_agg("X"),',') AS XY
FROM Table1
GROUP BY "ID";
Resulting in:
导致：
ID xy
3 0.5,1,1.5
6 0.5
I feel I'm getting closer, but a helping hand would be really appreciated.
我觉得我越来越近了，但真的很感激伸出援助之手。
回答by a_horse_with_no_name
Create an array from the two columns, the aggregate the array:
从两列创建一个数组，聚合数组：
select id, array_agg(array[x,y])
from the_table
group by id;
Note that the default text representation of arrays uses curly braces ( {..}) not square brackets ([..])
请注意，数组的默认文本表示使用花括号 ( {..}) 而不是方括号 ( [..])
回答by Erwin Brandstetter
In Postgres 9.5 or laterarray_agg()takes arraysas input to allow the simple syntax provided by @a_horse:
在 Postgres 9.5 或更高版本中，array_agg()将数组作为输入以允许@a_horse 提供的简单语法：
SELECT id, array_agg(ARRAY[x, y]) AS xy
FROM Table1
GROUP BY id;
In older versions, this isn't implemented yet. You can create your own aggregate function (once) to achieve the same:
在旧版本中，这尚未实现。您可以创建自己的聚合函数（一次）以实现相同的目的：
CREATE AGGREGATE array_agg_mult (anyarray) (
SFUNC = array_cat
, STYPE = anyarray
, INITCOND = '{}'
);
Then:
然后：
SELECT id, array_agg_mult(ARRAY[ARRAY[x,y]]) AS xy -- note the 2D array
FROM Table1
GROUP BY id;
Details:
细节：
Or you can concatenate a string:
或者你可以连接一个字符串：
SELECT id, '[[' || string_agg(concat_ws(',', x, y), '],[') || ']]' AS xy
FROM Table1
GROUP BY id;
Produces your desired result exactly. A string, not an array.
准确地产生您想要的结果。一个字符串，而不是一个数组。
