SQL Postgres - 将两列聚合为一项
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/34163209/
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
Postgres - aggregate two columns into one item
提问by SoluableNonagon
I would like to aggregate two columns into one "array" when grouping.
我想在分组时将两列聚合成一个“数组”。
Assume a table like so:
假设有一个像这样的表：
friends_map:
=================================
user_id friend_id confirmed
=================================
1 2 true
1 3 false
2 1 true
2 3 true
1 4 false
I would like to select from this table and group by user_id and get friend_id and confirmed as a concatenated value separated by a comma.
我想从这个表中选择并按 user_id 分组并获得friend_id 并确认为由逗号分隔的连接值。
Currently I have this:
目前我有这个：
SELECT user_id, array_agg(friend_id) as friends, array_agg(confirmed) as confirmed
FROM friend_map
WHERE user_id = 1
GROUP BY user_id
which gets me:
这让我：
=================================
user_id friends confirmed
=================================
1 [2,3,4] [t, f, f]
How can I get:
我怎样才能得到：
=================================
user_id friends
=================================
1 [ [2,t], [3,f], [4,f] ]
采纳答案by Hart CO
You can concatenate the values together prior to feeding them into the array_agg()function:
您可以在将值输入array_agg()函数之前将它们连接在一起：
SELECT user_id, array_agg('[' || friend_id || ',' || confirmed || ']') as friends
FROM friends_map
WHERE user_id = 1
GROUP BY user_id
Demo: SQL Fiddle
演示：SQL 小提琴
回答by Ivan Burlutskiy
SELECT user_id, array_agg((friend_id, confirmed)) as friends
FROM friend_map
WHERE user_id = 1
GROUP BY user_id
user_id | array_agg
--------+--------------------------------
1 | {"(2,true)","(3,false)","(4,false)"}
回答by Eggplant
You could avoid the ugliness of the multidimentional array and use some json which supports mixed datatypes:
您可以避免多维数组的丑陋并使用一些支持混合数据类型的 json：
SELECT user_id, json_agg(json_build_array(friend_id, confirmed)) AS friends
FROM friends_map
WHERE user_id = 1
GROUP BY user_id
Or use some key : valuepairs since json allows that, so your output will be more semanticif you like:
或者使用一些key : value对，因为 json 允许这样做，因此如果您愿意，您的输出将更具语义：
SELECT user_id, json_agg(json_build_object(
'friend_id', friend_id,
'confirmed', confirmed
)) AS friends
FROM friends_map
WHERE user_id = 1
GROUP BY user_id;
回答by klin
In Postgres 9.5you can obtain array of arrays of text:
在Postgres 9.5 中，您可以获得文本数组的数组：
SELECT user_id, array_agg(array[friend_id::text, confirmed::text])
FROM friend_map
WHERE user_id = 1
GROUP BY user_id;
user_id | array_agg
---------+--------------------------------
1 | {{2,true},{3,false},{4,false}}
(1 row)
or array of arrays of int:
或 int 数组的数组：
SELECT user_id, array_agg(array[friend_id, confirmed::int])
FROM friend_map
WHERE user_id = 1
GROUP BY user_id;
user_id | array_agg
---------+---------------------
1 | {{2,1},{3,0},{4,0}}
(1 row)
