将NULL填充到最大长度以填充自定义聚合函数的数组

小编典典

将NULL填充到最大长度以填充自定义聚合函数的数组

sql

我们可以创建一个自定义的聚合函数来聚合Postgres中的n维数组，例如：

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

一个约束是值必须共享 相同的数组范围和相同的长度 ，处理空值和不同的长度是行不通的。

从答案：

没有办法解决，数组类型不允许在Postgres中出现这种不匹配的情况。您可以使用NULL值填充数组，以便所有维都具有匹配范围。

我有像这样的行

------ arrayfield

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

array_agg_mult(ARRAY[arrayfield]) AS customarray

我期望像这样的总结果 {{1},NULL,{abc}}

但它会抛出

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

有什么方法可以在自定义函数中添加填充值？

我发现问题是数组长度不同时。{a},{null},{1}将聚集，但{a,b},{},{1}不会聚集。

因此，我需要一个查询，可以在其中向现有数组添加NULL元素。

一种解决方案是始终附加两个NULL（2是该字段中的最大长度）array_cat(arr, ARRAY[NULL,NULL])并将数组修剪为长度2：

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

但是我不知道语法。

阅读 232

2021-04-07

共1个答案

小编典典

使用array_agg_mult()此相关答案中定义的自定义聚合函数：

选择数据到Postgres数组中

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

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

必须是：

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

具有0或1个数组元素的简单情况

对于只替换空数组的简单情况：您可以使用以下方法实现：

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元件直到最大长度垫数组：

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;

仍然仅适用于一维基本数组。

解释

子查询t1计算基本一维数组的最大长度。
COALESCE(array_length(arr, 1), 0)计算此行中数组的长度。
COALESCE默认为0for NULL。
用生成长度差异的填充数组array_fill()。
追加，要arr与||
像上面一样用进行汇总array_agg_mult()。

SQL提琴。 展示一切。
SQL Fiddle中的输出具有误导性，因此我将结果投射到此处的文本。

2021-04-07