这个有点意思。假设表为tab,字段名是num
with t_flag as
(select num
from tab t
where not exists (select 1
from tab
where num < t.num
and num > t.num - 3))
select num,
num_flag,
row_number() over(partition by num_flag order by num) seq
from (select num,
(select max(num) num_flag from t_flag where num <= tab.num) num_flag
from tab)
with t_flag as
(select num
from tab t
where not exists (select 1
from tab
where num < t.num
and num > t.num - 3))
select num,
num_flag,
row_number() over(partition by num_flag order by num) seq
from (select num,
(select max(num) num_flag from t_flag where num <= tab.num) num_flag
from tab)
with t1 as
(select n
from (select n,
count(1) over(order by n range between current row and 3 following) cnt,
row_number() over(order by n) rn
from tab)
start with rn = 1
connect by rn = prior (rn + cnt))
select n, row_number() over(partition by max_n order by n)
from (select t2.n, max(t1.n) over(order by t2.n) max_n
from t1
right join test t2
on (t1.n = t2.n))