PostgreSQL的：group by子句中加入阵列

编程入门行业动态更新时间:2024-10-26 16:33:44

本文介绍了PostgreSQL的：group by子句中加入阵列的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！问题描述

我们有分组阵列成一个阵列的一个问题。我们想从两个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.

或您可以创建一个自定义的聚合函数像这些相关答案讨论：结果Selecting数据转换成一个Postgres阵列格式结果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;