在PostgreSQL上使用SQL将多个行连接成一个数组

Question

我有一个构建如下的表格：

oid | identifier | value
1   | 10         | 101
2   | 10         | 102
3   | 20         | 201
4   | 20         | 202
5   | 20         | 203

我想查询这个表格并获取像这样的结果：

identifier | values[]
10         | {101, 102}
20         | {201, 202, 203}

我想不出一种方法来做到这一点。
这可能吗？怎么做？

- Tyn

---

请看这里：https://dev59.com/JnVD5IYBdhLWcg3wO5ED - Quassnoi

Answer 1

这是Postgres自带的一项功能，几个版本以来您不再需要定义自己的函数，它的名称是array_agg()。

test=> select array_agg(n) from generate_series(1,10) n group by n%2;
  array_agg   
--------------
 {1,3,5,7,9}
 {2,4,6,8,10}

（这是PostgreSQL 8.4.8版本的信息）。

请注意，没有指定ORDER BY，所以结果行的顺序取决于使用的分组方法（此处为哈希），即未定义。例如：

test=> select n%2, array_agg(n) from generate_series(1,10) n group by (n%2);
 ?column? |  array_agg   
----------+--------------
        1 | {1,3,5,7,9}
        0 | {2,4,6,8,10}

test=> select (n%2)::TEXT, array_agg(n) from generate_series(1,10) n group by (n%2)::TEXT;
 text |  array_agg   
------+--------------
 0    | {2,4,6,8,10}
 1    | {1,3,5,7,9}

我不知道为什么你会得到{10,2,4,6,8}和{9,7,3,1,5}，因为generate_series()应该按顺序发送行。

- bobflux

---

在PostgreSQL 8.4.8中，这将返回：{10,2,4,6,8}和{9,7,3,1,5}。我认为上述输出来自版本9。 - SabreWolfy

我也不知道为什么行/元素以我看到的顺序返回。我只是复制了代码并粘贴进去看它做了什么。 - SabreWolfy

您可以选择数组中元素的顺序 - 请参见此处! 为什么默认值是这样的，这是一个谜... - Vérace

Answer 2

你需要创建一个聚合函数，例如：

CREATE AGGREGATE array_accum (anyelement)
(
sfunc = array_append,
stype = anyarray,
initcond = '{}'
);

然后

SELECT identifier, array_accum(value) AS values FROM table GROUP BY identifier;

希望能帮到你

- Johannes Weiß

---

当我尝试创建这个聚合函数时，我会收到以下错误：ERROR: 语法错误在或附近“（”处。 - SomethingOn

你正在使用PostgreSQL吗？ - SuN

Answer 3

简单示例：每个课程都有许多课时，所以如果我运行以下代码：

SELECT
  lessons.course_id AS course_id,
  array_agg(lessons.id) AS lesson_ids
FROM lessons
GROUP BY
  lessons.course_id
ORDER BY
  lessons.course_id

我将得到下一个结果：

┌───────────┬──────────────────────────────────────────────────────┐
│ course_id │                   lesson_ids                         │
├───────────┼──────────────────────────────────────────────────────┤
│         1 │ {139,140,141,137,138,143,145,174,175,176,177,147,... │
│         3 │ {32,69,70,71,72,73,74,75,76,77,78,79,80,81,82,83,... │
│         5 │ {663,664,665,649,650,651,652,653,654,655,656,657,... │
│         7 │ {985,984,1097,974,893,971,955,960,983,1045,891,97... │
│       ...                                                        │
└───────────┴──────────────────────────────────────────────────────┘

- Sergio Belevskij

---

这几乎是完美的。问题在于lessons.id的值是未排序的。 - undefined

Answer 4

这是所需输出的代码。

select identifier, array_agg(value)
from (
  values
    (1   , 10         , 101),
    (2   , 10         , 102),
    (3   , 20         , 201),
    (4   , 20         , 202),
    (5   , 20         , 203)
  ) as tab (oid, identifier, value)
group by identifier
order by identifier;

- Mikkel