Postgres表到二维数组 [英] Postgres table to two-dimensional array

问题描述

我想将具有三列的表转换为类型为 integer [] [] 的二维数组.有两列表示数组的两个维中的每个维(在示例中为 x 和 y )，一个 integer 列表示值.

I would like to convert a table with three columns to a two-dimensional array of type integer[][]. There are two columns to indicate each of the two dimensions of the array (x and y in the example) and one integer column to indicate the value.

数据中考虑了x和y的所有可能组合，但是如果可能的解决方案可以用 NULL 代替 x 和<代码> y .

All possible combinations of x and y are accounted for in the data, though it would be nice if a possible solution could substitute NULL for missing combinations of x and 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

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

How can I write this query to return a two-dimensional array?

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

Eg. the result of this specific query should be the following array:

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

推荐答案

这是一种方法:

select array_agg(dimension1 order by x) as dimension2
from (
  select b.x, array_agg(t1.val order by a.y) as dimension1
  from 
    (select y from t1 group by y) a
  full join (select x from t1 group by x) b on true
  left join t1 on a.y = t1.y and b.x = t1.x
  group by b.x
) a;

这与现代相似:

with 
  a as (select y from t1 group by y),
  b as (select x from t1 group by x)
select array_agg(dimension1  order by x) as dimension2
from (
  select b.x, array_agg(t1.val order by a.y) as dimension1
  from a
  full join b on true
  left join t1 on a.y = t1.y and b.x = t1.x
  group by b.x
  order by b.x
) c;

检查是否给出 null 删除一行:

to check if it gives null delete one row:

delete from t1 where x = 'A' and y = 'B';

您应该得到:

{{1,NULL,3},{4,5,6},{7,8,9}}

这篇关于Postgres表到二维数组的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！