垫数组用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 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 to0
forNULL
.- 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屋!