sql - PostgreSQL unnest() 连续整数按数字分组

Question

给定一个不同的有序整数数组，我想取消嵌套数组并为每批连续整数分配一个组号。

例如:{2,3,5,7,8,9,10,20,21,25} 应该返回

elem | group_nr
-----+---------
   2 |  1
   3 |  1
   5 |  2
   7 |  3
   8 |  3
   9 |  3
  10 |  3
  20 |  4
  21 |  4
  25 |  5

Answer 1

使用 window functions lag() 和 sum():

with the_data(arr) as (
values (array[2,3,5,7,8,9,10,20,21,25])
)

select elem, sum(diff) over w as group_nr
from (
    select elem, (elem- 1 is distinct from lag(elem) over w)::int as diff
    from the_data, unnest(arr) as elem
    window w as (order by elem)
    ) s
window w as (order by elem);

 elem | group_nr 
------+----------
    2 |        1
    3 |        1
    5 |        2
    7 |        3
    8 |        3
    9 |        3
   10 |        3
   20 |        4
   21 |        4
   25 |        5
(10 rows)