PostgreSQL中如何将值列表转换为固定宽度的二维数组

这个需求在PostgreSQL里可以通过窗口函数+分组聚合的组合来实现，核心思路是先给每个元素分配行号，再按行号分块，最后把每块聚合成子数组，再将所有子数组组合成最终的二维数组。

完整SQL语句 #

假设你的表名为someTable，字段为Number，要转换成宽度为4的二维数组，可以用以下语句：

SELECT array_agg(sub_arr) AS two_dimensional_array
FROM (
    -- 按分块聚合子数组
    SELECT array_agg(Number ORDER BY rn) AS sub_arr
    FROM (
        -- 给每个Number分配行号（确保顺序和Number一致）
        SELECT 
            Number, 
            row_number() OVER (ORDER BY Number) AS rn
        FROM someTable
    ) numbered_rows
    -- 按行号分块：(rn-1)//4 会把第1-4行归为块0，5-8行归为块1，以此类推
    GROUP BY (rn - 1) // 4
    -- 保证分块的顺序正确
    ORDER BY (rn - 1) // 4
) grouped_arrays;

逐段解释 #

1. 内层子查询numbered_rows：
   用row_number()窗口函数给每个Number分配一个连续的行号，并且通过ORDER BY Number确保行号和数字的顺序一致（如果你的表中Number已经是有序的，这个排序可以省略，但加上更稳妥）。

2. 中间子查询grouped_arrays：

   • 通过(rn - 1) // 4计算每个行所属的分块编号：比如行号1-4对应(1-1)//4=0、(4-1)//4=0，行号5-8对应(5-1)//4=1，以此类推。
   • 按分块编号分组，用array_agg(Number ORDER BY rn)把每个块内的数字聚合成子数组，同时保持子数组内的顺序。

3. 最外层查询：
   用array_agg(sub_arr)把所有子数组聚合成最终的二维数组，得到你需要的{{1,2,3,4}, {5,6,7,8}, {9,10,11,12}, {13,14,15}}格式。

灵活调整宽度 #

如果需要改变二维数组的宽度，只需要把SQL中的4替换成你想要的数字即可。比如换成3的话，最终会得到{{1,2,3}, {4,5,6}, {7,8,9}, {10,11,12}, {13,14,15}}。

内容的提问来源于stack exchange，提问作者p1ka2So