将两列和多行聚合为一列(Aggregate two columns and rows into one)

将两列和多行聚合为一列(Aggregate two columns and rows into one)

我有以下表格结构

start|end 09:00|11:00 13:00|14:00

我知道

SELECT ARRAY_AGG(start), ARRAY_AGG(end)

会导致

start|end [09:00,13:00]|[11:00,14:00]

但是我怎样才能得到以下结果呢？ 结果

[09:00,11:00,13:00,14:00]

顺便说一下，我正在使用Postgres

I have the following table structure

start|end 09:00|11:00 13:00|14:00

I know

SELECT ARRAY_AGG(start), ARRAY_AGG(end)

Will result in

start|end [09:00,13:00]|[11:00,14:00]

But how can i get the following result? result

[09:00,11:00,13:00,14:00]

BTW, I'm using Postgres

最满意答案

您可以进行数组连接（如果顺序不重要）：

SELECT ARRAY_AGG(start) || ARRAY_AGG(end) FROM TABLE1

如果订单很重要，你可以使用戈登的方法但是 ：

添加聚合顺序array_agg(d order by d ASC)

使用unnest而不是union all ，因为Gordon的解决方案（ union all ）执行两次序列扫描。 如果表很大，那么使用性能会更好：

SELECT array_agg(d ORDER BY d ASC) FROM( SELECT unnest(ARRAY[start] || ARRAY[end]) as d from table1 ) sub

它只对表执行一次序列扫描（并且会更快）。

You could do array concatenation (if order is not important):

SELECT ARRAY_AGG(start) || ARRAY_AGG(end) FROM TABLE1

If order is important you could use Gordon's approach but:

add aggregate order array_agg(d order by d ASC)

use unnest instead of union all, because Gordon's solution (union all) performs two sequence scan. If table is big it could be better for performance to use:

SELECT array_agg(d ORDER BY d ASC) FROM( SELECT unnest(ARRAY[start] || ARRAY[end]) as d from table1 ) sub

which performs only one sequence scan on table (and will be faster).