如何在PostgreSQL中对数组进行分组和连接 [英] How to Group by and concatenate arrays in PostgreSQL

问题描述

我在PostgreSQL中有一个表.我想在按 time 分组后将所有数组(即 col )连接起来.数组的尺寸各不相同.

I have a table in PostgreSQL. I want to concatenate all the arrays(i.e. col) after grouping them by time. The arrays are of varying dimensions.

| time  | col               |
|------ |------------------ |
| 1     | {1,2}             |
| 1     | {3,4,5,6}         |
| 2     | {}                |
| 2     | {7}               |
| 2     | {8,9,10}          |
| 3     | {11,12,13,14,15}  |

结果应如下:

| time  | col               |
|------ |------------------ |
| 1     | {1,2,3,4,5,6}     |
| 2     | {7,8,9,10}        |
| 3     | {11,12,13,14,15}  |

到目前为止，我想出的是:

What I have come up with so far is as follows:

SELECT ARRAY(SELECT elem FROM tab, unnest(col) elem);

但这不会进行分组.它只需要整个表并将其连接起来.

But this does not do the grouping. It just takes the entire table and concatenates it.

推荐答案

要保持数组的相同尺寸，您不能直接使用 array_agg()，所以首先我们 unnest 数组，并应用 distinct 删除重复项(1).在外部查询中，这是聚合的时间.要保留值排序，请在聚合函数中包括 order by :

To preserve the same dimension of you array you can't directly use array_agg(), so first we unnest your arrays and apply distinct to remove duplicates (1). In outer query this is the time to aggregate. To preserve values ordering include order by within aggregate function:

select time, array_agg(col order by col) as col
from (
  select distinct time, unnest(col) as col
  from yourtable
) t
group by time
order by time

(1)如果不需要重复删除，只需删除 distinct 字即可.

(1) If you don't need duplicate removal just remove distinct word.

这篇关于如何在PostgreSQL中对数组进行分组和连接的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！