我正在try 按第一个元素对每行的多维数组进行重新排序.
当前表格:
| id | foobar |
|---|---|
| 1 | {{1.2,0.01}, {5.3, 0.05}, {3.5, 0.6}} |
所需表格:
| id | foobar_reorder |
|---|---|
| 1 | {{5.3, 0.05}, {3.5, 0.6}, {1.2,0.01}} |
我对SQL还不熟悉，但我想我可以将数组解嵌到一个表中，然后按列排序，然后再转换回一个array.我无法放入实际的表和列来创建输出表，但它适用于下面带有测试数组的代码.
在表中取消嵌套数组:
WITH data AS (
SELECT t.aij_wij AS arr from public.pipe_aggr t
)
SELECT
arr[i][1] AS xi,
arr[i][2] AS wi
FROM
data,
generate_subscripts((SELECT arr FROM data), 1) i
ERROR: more than one row returned by a subquery used as an expression
取消数组与测试数组的嵌套:
WITH data AS (
SELECT ARRAY[[1.2,0.01],[5.3, 0.05], [3.5,0.6]]::float8[] AS arr
)
SELECT
arr[i][1] AS xi,
arr[i][2] AS wi
FROM
data,
generate_subscripts((SELECT arr FROM data), 1) i
;