sql - PostgreSQL:替换数组元素中大于特定限制的值

Question

我的 PostgreSQL 9.5 数据库中有 3 行数组(类型、文本)，如下所示:

ID     array
1      "112,292,19.3"
2      "203,383,22.1"
3      "136,226,18.9,286,346,27.1,346,406,6.5"

有些数组元素大于 360。我想替换它们的条件是 if any array element > 360 then element - 360 这样替换数组如下:

ID     array
1      "112,292,19.3"
2      "203,23,22.1"
3      "136,226,18.9,286,346,27.1,346,46,6.5"

如何替换大于 360 的值？

Answer 1

有modulo operator %在 Postgres 中:

with my_table(id, arr) as (
values
    (1, array[112,292,19.3]),
    (2, array[203,383,22.1]),
    (3, array[136,226,18.9,286,346,27.1,346,406,6.5])
)

select id, array_agg(unnest % 360 order by ordinality)
from my_table,
unnest(arr) with ordinality
group by 1;

 id |               array_agg                
----+----------------------------------------
  1 | {112,292,19.3}
  2 | {203,23,22.1}
  3 | {136,226,18.9,286,346,27.1,346,46,6.5}
(3 rows)