问带连续整数的PostgreSQL unnest()按数字分组

EN

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)   

select id,num,sum(col) over(partition by id order by num) as grp
from (select id,num,case when num-lag(num,1,0) over(partition by id order by num)=1 then 0 else 1 end as col
      from (select id,unnest(array_column) as num from tbl) t
     ) t