By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
create table t (id int);
insert into t (id) values
(1), (2), (3), (4), (5),
(6), (7), (8), (9),
(11), (12), (13), (14), (15),
(16), (17), (18), (19), (20),
(22), (23), (24),
(26);
select t.*,
1 + sum( (id % 5) = 0 ) over (order by id rows between unbounded preceding and 1 preceding)
from t
order by id
| id | 1 + sum( (id % 5) = 0 ) over (order by id rows between unbounded preceding and 1 preceding) |
|---|---|
| 1 | null |
| 2 | 1 |
| 3 | 1 |
| 4 | 1 |
| 5 | 1 |
| 6 | 2 |
| 7 | 2 |
| 8 | 2 |
| 9 | 2 |
| 11 | 2 |
| 12 | 2 |
| 13 | 2 |
| 14 | 2 |
| 15 | 2 |
| 16 | 3 |
| 17 | 3 |
| 18 | 3 |
| 19 | 3 |
| 20 | 3 |
| 22 | 4 |
| 23 | 4 |
| 24 | 4 |
| 26 | 4 |