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然后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

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)

Answer 2

尝试 https://www.postgresql.org/docs/9.5/static/functions-math.html mod

Y/x 的余数

例如：

x=# with a as (select * from unnest(array[136,226,18.9,286,346,27.1,346,46,6.5]) with ordinality a(i,o))
select array_agg(mod(i,360) order by o) from a;
               array_agg
----------------------------------------
 {136,226,18.9,286,346,27.1,346,46,6.5}
(1 row)