By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
select *
into t
from (values
('a', 4 ),
('a', 7 ),
('a', 12),
('a', 12),
('a', 13),
('b', 1 ),
('b', 2 ),
('b', 3 ),
('b', 4 ),
('b', 5 )
) v(id, value)
10 rows affected
with ts as (
select distinct t.id, t.value, dense_rank() over (partition by id order by value) as seqnum
from t
),
cte as (
select id, value, value as grp_value, 1 as within_seqnum, seqnum
from ts
where seqnum = 1
union all
select ts.id, ts.value,
(case when ts.value >= cte.grp_value + 3 then ts.value else cte.grp_value end),
(case when ts.value >= cte.grp_value + 3 then 1 else cte.within_seqnum + 1 end),
ts.seqnum
from cte join
ts
on ts.id = cte.id and ts.seqnum = cte.seqnum + 1
)
select *
from cte
where within_seqnum = 1
order by id, value
| id | value | grp_value | within_seqnum | seqnum |
|---|---|---|---|---|
| a | 4 | 4 | 1 | 1 |
| a | 7 | 7 | 1 | 2 |
| a | 12 | 12 | 1 | 3 |
| b | 1 | 1 | 1 | 1 |
| b | 4 | 4 | 1 | 4 |
select distinct t.id, t.value, dense_rank() over (partition by id order by value) as seqnum
from t
| id | value | seqnum |
|---|---|---|
| a | 4 | 1 |
| a | 7 | 2 |
| a | 12 | 3 |
| a | 13 | 4 |
| b | 1 | 1 |
| b | 2 | 2 |
| b | 3 | 3 |
| b | 4 | 4 |
| b | 5 | 5 |