在group by子句中连接数组

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

我们在将数组分组为单个数组时遇到问题. 我们希望将两列中的值连接到一个数组中，并将这些包含多行的数组聚合在一起.

We have a problem grouping arrays into a single array. We want to join the values from two columns 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()函数:

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()在group by子句中合并数组.

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

有什么想法吗?

推荐答案

UNION ALL

您可以先用UNION ALL反向旋转":

UNION ALL

You could "counter-pivot" with UNION ALL first:

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;

适于产生您以后请求的值的顺序. 手册:

Adapted to produce the order of values you later requested. The manual:

集合函数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.

强调粗体.

LATERAL需要 Postgres 9.3 或更高版本.

SELECT t.name, array_agg(c) AS c_arr FROM (SELECT * FROM tbl ORDER BY name, id) t CROSS JOIN LATERAL (VALUES (t.col1), (t.col2)) v(c) GROUP BY 1;

相同的结果.只需对表进行一次遍历.

Same result. Only needs a single pass over the table.

或者，您可以创建一个自定义聚合函数，如以下相关答案中所述:

Or you could create a custom aggregate function like discussed in these related answers:

• 选择数据到Postgres数组中
• PostgreSQL中是否有类似zip()的函数，该函数结合了两个数组?

• Selecting data into a Postgres array
• 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 standard SQL:

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:

a | {1,2,3,4} b | {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;

哪个会生成二维数组:

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

最后一个可以用 Postgres 9.5 或更高版本中的内置array_agg()替换(并且应该是，因为它更快！)，它具有聚合数组的功能: >

The last one can be replaced (and should be, as it's faster!) with the built-in array_agg() in Postgres 9.5 or later - with its added capability of aggregating arrays:

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

相同的结果. 手册:

输入数组串联成一个高维数组(输入 必须都具有相同的尺寸，并且不能为空或为空)

input arrays concatenated into array of one higher dimension (inputs must all have same dimensionality, and cannot be empty or null)

因此与我们的自定义聚合函数array_agg_mult()不完全相同；

So not exactly the same as our custom aggregate function array_agg_mult();