将各种长度的1D数组聚合为2D数组 [英] Aggregate 1D arrays of various lengths into 2D array

问题描述

我有4个子查询返回零个以上长度为1+的SMALLINT []行。

I have 4 subqueries returning zero+ rows of SMALLINT[] of length 1+.

我想将所有子查询结果汇总到一行SMALLINT中[] []。

I would like to aggregate all of the subquery results into a single row of SMALLINT[][].

由于数组的长度不同，我不确定如何将它们汇总起来吗？使用9.5 ARRAY_AGG会返回无法累积不同维度的数组。

Because the arrays are of differing lengths I am unsure of how to roll them up? Using the 9.5 ARRAY_AGG returns "cannot accumulate arrays of different dimensionality".

推荐答案

您可以填充 array 和 NULL ：

CREATE TABLE tab(col smallint[]);

INSERT INTO tab(col) 
VALUES ('{1}'), ('{1,2}'), ('{1,2,3}'), ('{}');

SELECT array_agg(
          array_cat(
            col,
            array_fill(NULL::smallint, ARRAY[l-COALESCE(array_length(col, 1),0)])
          )
       ) AS result
FROM tab;
,LATERAL (SELECT MAX(array_length(col,1)) AS l
          FROM tab) s

输出：

╔═════════════════════════════════════════════════════╗
║                       result                        ║
╠═════════════════════════════════════════════════════╣
║ {{1,NULL,NULL},{1,2,NULL},{1,2,3},{NULL,NULL,NULL}} ║
╚═════════════════════════════════════════════════════╝

这篇关于将各种长度的1D数组聚合为2D数组的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！