arrays - Postgres 表到二维数组

Question

我想将一个包含三列的表转换为类型为 integer[][] 的二维数组.有两列分别表示数组的两个维度(示例中的 x 和 y)和一个 integer列来指示值。

x 和 y 的所有可能组合都包含在数据中，但如果可能的解决方案可以替代 NULL 就更好了对于 x 的缺失组合和 y .

表格看起来是这样的:

DROP TABLE IF EXISTS t1;

CREATE TABLE t1 (
    x VARCHAR,
    y VARCHAR,
    val INT
);

INSERT INTO t1 (x, y, val)
VALUES   ('A', 'A', 1),
         ('A', 'B', 2),
         ('A', 'C', 3),
         ('B', 'A', 4),
         ('B', 'B', 5),
         ('B', 'C', 6),
         ('C', 'A', 7),
         ('C', 'B', 8),
         ('C', 'C', 9);

SELECT * FROM t1

如何编写此查询以返回二维数组？

例如。此特定查询的结果应为以下数组:

SELECT '{{1,2,3},{4,5,6},{7,8,9}}'::integer[][]

Answer 1

一种可能是先按x 分组，然后使用array_agg() 获取内部数组。然后使用 array_agg() 再次聚合，将内部数组合并为一个外部数组。

SELECT array_agg(a ORDER BY x)
       FROM (SELECT x,
                    array_agg(val ORDER BY y) a
                    FROM t1
                    GROUP BY x) t;