PostgreSQL: increment column value in select statement based on previous values

Question

I am using Postgresql 13 and I have a table that looks something like this:

event_id	timestamp
1	2022-11-28 00:00:00
1	2022-11-28 00:00:10
2	2022-11-28 00:00:20
2	2022-11-28 00:00:30
2	2022-11-28 00:00:40
3	2022-11-28 00:00:50
3	2022-11-28 00:01:10
1	2022-11-28 00:01:20
2	2022-11-28 00:01:30
2	2022-11-28 00:01:40
3	2022-11-28 00:01:50
3	2022-11-28 00:02:10
3	2022-11-28 00:02:20
4	2022-11-28 00:02:30

I need to get monotonically increasing values for the event_id column based on the timestamp order. So the above table will become something like:

event_id	timestamp
1	2022-11-28 00:00:00
1	2022-11-28 00:00:10
2	2022-11-28 00:00:20
2	2022-11-28 00:00:30
2	2022-11-28 00:00:40
3	2022-11-28 00:00:50
3	2022-11-28 00:01:10
4	2022-11-28 00:01:20
5	2022-11-28 00:01:30
5	2022-11-28 00:01:40
6	2022-11-28 00:01:50
6	2022-11-28 00:02:10
6	2022-11-28 00:02:20
7	2022-11-28 00:02:30

Ideally I'd need this done in a single select statement, I tried a bunch of different approaches but nothing led me even close to what I need. Any suggestion? Thanks

Answer 1

Try the following using a running sum of a flag that is set to 1 whenever event_id is changed over the increasing of timestamp.

select sum(flag) over (order by timestamp) +1 as event_id,
       timestamp 
from
(
  select *,
  case 
   when lag(event_id, 1, event_id)
        over (order by timestamp) <> event_id
   then 1 else 0
  end as flag
  from table_name 
) T
order by timestamp 

See a demo.