PostgreSQL的：group by子句中加入阵列 [英] PostgreSQL: joining arrays within group by clause

问题描述

我们有分组阵列成一个阵列的一个问题。
我们想从两个colums加入值到一个单一的阵列和聚集多个行的这些阵列。

We have a problem grouping arrays into a single array. We want to join the values from two colums into one single array and aggregate these arrays of multiple rows.

由于以下输入：

| id | name | col_1 | col_2 |
| 1  |  a   |   1   |   2   |
| 2  |  a   |   3   |   4   |
| 4  |  b   |   7   |   8   |
| 3  |  b   |   5   |   6   |

我们希望下面的输出：

| a | { 1, 2, 3, 4 } |
| b | { 5, 6, 7, 8 } |

的元素的顺序是重要的，并应与聚集行的ID相关联。

The order of the elements is important and should correlate with the id of the aggregated rows.

我们尝试了ARRAY_AGG功能：

We tried the array_agg function:

SELECT array_agg(ARRAY[col_1, col_2]) FROM mytable GROUP BY name;

不幸的是，这一说法引发错误：

Unfortunately, this statement raises an error:

ERROR: could not find array type for data type character varying[]

这似乎是不可能的条款使用ARRAY_AGG合并组中的数组。

It seems to be impossible to merge arrays in a group by clause using array_agg.

任何想法？

推荐答案

您可以反支点用 UNION ALL 第一：

SELECT name, array_agg(c) AS c_arr
FROM  (
   SELECT name, id, 1 AS rnk, col1 AS c FROM tbl
   UNION ALL
   SELECT name, id, 2, col2 FROM tbl
   ORDER  BY name, id, rnk
   ) sub
GROUP  BY 1;

适用于生产以后的请求值的顺序。 每文档：

集合函数 ARRAY_AGG ， json_agg ， string_agg 和 XMLAGG ，
  以及类似的用户定义的聚合函数，产生
  视的的顺序有意义不同的结果值
  输入值。这个顺序是默认指定，但也可以是
  通过编写 ORDER BY 子句汇总调用内部控制，如
  在4.2.7节中。可替代地，提供输入从值
  排序的子查询通常会工作。

The aggregate functions array_agg, json_agg, string_agg, and xmlagg, as well as similar user-defined aggregate functions, produce meaningfully different result values depending on the order of the input values. This ordering is unspecified by default, but can be controlled by writing an ORDER BY clause within the aggregate call, as shown in Section 4.2.7. Alternatively, supplying the input values from a sorted subquery will usually work.

或您可以创建一个自定义的聚合函数像这些相关答案讨论：结果
<一href=\"http://stackoverflow.com/questions/11762398/selecting-data-into-a-postgres-array-format/11763245#11763245\">Selecting数据转换成一个Postgres阵列格式结果
<一href=\"http://stackoverflow.com/questions/12414750/is-there-something-like-a-zip-function-in-postgresql-that-combines-two-arrays/12414884#12414884\">Is有什么样PostgreSQL中的zip（）函数，结合两个数组？

Custom aggregate function

Or you could create a custom aggregate function like discussed in these related answers:
Selecting data into a Postgres array format
Is there something like a zip() function in PostgreSQL that combines two arrays?

CREATE AGGREGATE array_agg_mult (anyarray)  (
    SFUNC     = array_cat
   ,STYPE     = anyarray
   ,INITCOND  = '{}'
);

然后，您可以：

SELECT name, array_agg_mult(ARRAY[col1, col2] ORDER BY id) AS c_arr
FROM   tbl
GROUP  BY 1
ORDER  BY 1;

或者，通常更快，而不是SQL标准：

Or, typically faster, while not SQL standard:

SELECT name, array_agg_mult(ARRAY[col1, col2]) AS c_arr
FROM  (SELECT * FROM tbl ORDER BY name, id) t
GROUP  BY 1;

添加的 ORDER BY ID （可追加到这种聚合函数）保证你想要的结果：

The added ORDER BY id (which can be appended to such aggregate functions) guarantees your desired result:

{1,2,3,4}
{5,6,7,8}

或者，你可能有兴趣在这个替代方案：

Or you might be interested in this alternative:

SELECT name, array_agg_mult(ARRAY[ARRAY[col1, col2]] ORDER BY id) AS c_arr
FROM   tbl
GROUP  BY 1
ORDER  BY 1;

将会产生2维数组：

Which produces 2-dimensional arrays:

{{1,2},{3,4}}
{{5,6},{7,8}}

这篇关于PostgreSQL的：group by子句中加入阵列的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！