sql - 转换另一个表 Postgres 中的 int[] 元素中的 id

Question

我有一个包含整数数组的表(我们称它为 lineTable)。该 int 数组表示另一个表 (pointTable) 中的唯一 ID 数组。点表依次包含 3 列(id、x、y)。

我希望能够将 lineTable.points 中的每一个整数转换为一组整数数组。

lineTable              pointTable
| id | points |        | id | x | y |
| int| int[]  |        | int|int|int|

lineTable
| 1 | {1, 2, 3, 4, 5} |
| 2 | {4, 7, 5, 2, 3} |
| 3 | {8, 1}          |
etc

pointTable
| 1 | 1 | 0 |
| 2 | 2 | 1 |
| 3 | 5 | 6 |
| 4 | 7 | 0 |
| 5 | 2 | 4 |
| 6 | 5 | 2 |
| 7 | 4 | 4 |
| 8 | 5 | 9 |

所以我希望最终的输出是

lineValues
| {{1, 0}, {2, 1}, {5, 6}, {7, 0}, {2, 4}} |
| {{7, 0}, {4, 4}, {2, 4}, {2, 1}, {5, 6}} |
| {{5, 9}, {1, 0}}                         |

注意:ID 可以是任意数字，不按顺序排列(可能是 1、2、5、10、11、18)

我正在使用 Postgre 9.2。

感谢您的帮助。如果您需要/想要任何其他信息，请告诉我。

Answer 1

如果您想在数组中使用复合类型(例如 native Point 类型)，这非常简单。像这样:

select dt.id, array_agg(point(p.x, p.y))
from pointtable p
join (
    select id, unnest(points) as p
    from linetable
) as dt on p.id = dt.p
group by dt.id
order by dt.id;

会给你这样的输出:

 id |                 array_agg                 
----+-------------------------------------------
  1 | {"(1,0)","(2,1)","(5,6)","(7,0)","(2,4)"}
  2 | {"(2,4)","(4,4)","(7,0)","(5,6)","(2,1)"}
  3 | {"(1,0)","(5,9)"}

然后你就可以使用这些点了。

如果你真的想要一个二维数组，那么你可以 create your own aggregate function :

create aggregate
array_cat_agg(int[])
(sfunc = array_cat, stype = int[], initcond = '{}');

然后:

select dt.id, array_cat_agg(array[[p.x, p.y]])
from pointtable p
join (
    select id, unnest(points) as p
    from linetable
) as dt on p.id = dt.p
group by dt.id
order by dt.id;

得到这个:

 id |          array_cat_agg          
----+---------------------------------
  1 | {{1,0},{2,1},{5,6},{7,0},{2,4}}
  2 | {{2,1},{5,6},{7,0},{2,4},{4,4}}
  3 | {{1,0},{5,9}}

注意 array[[...]] 技巧，如果你只是 array，你需要它来确保你得到正确版本的 array_cat [...] 那么你最终会得到一个扁平的数组，这会让你很伤心。你不能只使用 array_agg这里没有数组之类的数组，array_agg(array[p.x, p.y]) 想要创建一个数组之类的东西。因此自定义聚合。