垫数组用NULL的最大长度为自定义聚合函数 [英] Pad arrays with NULL to maximum length for custom aggregate function

问题描述

从问题的答案如何使用VARCHAR [ARRAY_AGG（） ] ，

我们可以创建自定义的聚合函数来聚合n维数组在Postgres里，如：

We can create a custom aggregate function to aggregate n-dimensional arrays in Postgres like:

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

一个约束是该值必须共享的同一阵列程度和长度相同，处理空值和不同长度不起作用。

A constrain is that the values have to share the same array extents and same length, handling empty values and different lengths doesn't work.

从答案：

有是周围没有办法，阵列类型不允许这样的
  不匹配的Postgres。你可以垫你用NULL值，所以数组
  所有尺寸都匹配程度。

There is no way around that, the array type does not allow such a mismatch in Postgres. You could pad your arrays with NULL values so that all dimensions have matching extents.

我有一个像

------ arrayfield

-----  {1},
-----  {},
-----  {abc}

array_agg_mult(ARRAY[arrayfield]) AS customarray

我期待一个总resule像 {{1}，NULL，{} ABC}

但它会抛出

ERROR:  cannot concatenate incompatible arrays
DETAIL:  Arrays with differing element dimensions are not compatible for concatenation.

有什么办法，我们可以在自定义函数中添加填充值？

我发现这个问题是当阵列的长度的是不同的。 {一}，{空}，{1} 将聚集，但 {A，B}，{}，{1} 不会。

I found the issue is when the array length is different. {a},{null},{1} will aggregate, but {a,b},{},{1} will not.

所以我需要一个查询在那里我可以添加null元素，以现有阵列。

So I need a query where I can add NULL elements to existing arrays.

解决方案之一是追加两个空总是（2是最大长度要在该日） array_cat（ARR，ARRAY [NULL，NULL]）和修剪数组长度2：

One solution is to append two NULL always (2 is the max length going to be in that filed) array_cat(arr, ARRAY[NULL,NULL]) and trim the array to length 2:

   {1}   --> {1,NULL,NULL}     --> {1,NULL}
   {NULL}  --> {NULL,NULL,NULL}  --> {NULL,NULL}
   {abc, def}  --> {abc,def,NULL,NULL}  --> {abc, def} 

但我想不出在语法

推荐答案

使用自定义聚合函数 array_agg_mult（）想在这个相关答案定义的：

Using the custom aggregate function array_agg_mult() like defined in this related answer:

• Selecting数据转换成一个Postgres阵列格式

• Selecting data into a Postgres array format

您预期的结果是不可能的：

Your expected result is impossible:

{{1},NULL,{abc}}

必须是：

{{1},{NULL},{abc}}

简单的情况下，以0或1的数组元素

对于简单的情况下，只需更换空数组：
可以实现与

Simple case with 0 or 1 array elements

For the simple case to just replace the empty array: You can achieve that with:

WITH t(arr) AS (
    VALUES
      ('{1}'::text[])
     ,('{}')
     ,('{abc}')
   )
SELECT array_agg_mult(ARRAY[CASE WHEN arr = '{}' THEN '{NULL}' ELSE arr END])
FROM   t;

为n个元素

动态填充

使用 array_fill（）与null元素到最大长度垫数组：

Dynamic padding for n elements

Using array_fill() to pad arrays with NULL elements up to the maximum length:

SELECT array_agg_mult(ARRAY[
         arr || array_fill(NULL::text
                         , ARRAY[max_elem - COALESCE(array_length(arr, 1), 0)])
       ]) AS result
FROM   t, (SELECT max(array_length(arr, 1)) AS max_elem FROM t) t1;

不过只适用于 1维基本阵列。

• 子查询 T1 计算基本的一维数组的最大长度。


• COALESCE（array_length（ARR，1），0）计算该行中的数组的长度。结果
  COALESCE 默认为 0 为 NULL 。


• 生成与 array_fill（）。
在长度差异填充阵列

• 附加，为改编与 ||


• 总像上面 array_agg_mult（）。

• Subquery t1 computes the maximum length of the basic 1-dimensional array.
• COALESCE(array_length(arr, 1), 0) computes the length of the array in this row.
  COALESCE defaults to 0 for NULL.
• Generate padding array for the difference in length with array_fill().
• Append that to arr with ||
• Aggregate like above with array_agg_mult().

SQL小提琴。 示范全部。结果
<子>在SQL小提琴输出是误导性的，所以我投的结果为文本那里。

这篇关于垫数组用NULL的最大长度为自定义聚合函数的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！